Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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