PostGIS convert geography(Point,4326) to GPS

Question

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?

Answer 1

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

Answer 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;