Take the 2-minute tour ×

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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
share|improve this answer
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 2 '12 at 21:28
Try ST_AsEWKT(your_column_name) –  Stev_k Sep 3 '12 at 0:41
Yep, that worked, thank you very much! –  Elwhis Sep 3 '12 at 1:13
add comment

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;
share|improve this answer
According to my tests (postgres 9.2, postgis 2.0.1), this is a bit faster than ST_AsEWKT –  Laurent Debricon Sep 18 '12 at 15:15
add comment

Your Answer


