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?

2 Answers 2


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;
  • According to my tests (postgres 9.2, postgis 2.0.1), this is a bit faster than ST_AsEWKT Sep 18, 2012 at 15:15

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
  • 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, 2012 at 21:28
  • Try ST_AsEWKT(your_column_name)
    – Stev_k
    Sep 3, 2012 at 0:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.