7

I got a database with a column of type geography(Point,4326), the data contained looks like 0101000020E610000092E057F66EF73140930035B56CBD4840. How can I convert it to GPS coordinates?

Elwhis
  • 173
  • 1
  • 1
  • 4

2 Answers2

11

The ST_X/ST_Y functions are only defined on the geometry type, so cast there before calling them.

SELECT ST_X(geogcolumn::geometry), ST_Y(geogcolumn::geometry) FROM thetable;
Paul Ramsey
  • 19,865
  • 1
  • 47
  • 57
8

It looks like this column is in Well Known Binary (WKB) or Extended Well Known Binary (EWKB) Format. You should try to query it using something like:

select astext(your_column_name) from your_table_name

That will give you the textual representation. Not sure what you mean by GPS co-ordinates? Do you want to convert it into a .gpx file or just get the lat/lon. In which case the former will do that, although you might have to clean up the output in text editor or something.

The alternative would be

select ST_X(your_column_name),ST_Y(your_column_name) from your_table_name

which will give you the numeric lats and longs in two columns as your query table.

---- EDIT

These are functions on Geometry columns --- on Geography columns, the following is correct:

 select ST_AsEWKT(your_column_name) from your_table_name
Stev_k
  • 6,689
  • 2
  • 34
  • 46
  • What I mean by GPS coordinates is just lat/lon. I've tried the functions you proposed but both returned an error ERROR: function astext(geography) does not exist ERROR: function st_x(geography) does not exist. Is there something I have to install to get it working? – Elwhis Sep 02 '12 at 21:28
  • Try ST_AsEWKT(your_column_name) – Stev_k Sep 03 '12 at 00:41