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.

After storing lon lat values as a GEOGRAPHY data type, how do I retrieve the individual lat lon values?

Failed attempt:

SELECT id, geog, ST_X(geog), ST_Y(geog) FROM locations;

Error:

No function matches the given name and argument types. You might need to add explicit type casts.
share|improve this question

2 Answers 2

up vote 3 down vote accepted

The ST_X(point) function only supports the geometry (as for now).

One workaround is to cast the geog to geom using ::geometry.

Hense your query should be like this:

SELECT id, geog, ST_X(geog::geometry), ST_Y(geog::geometry) FROM locations; 

And since it's geog the SRID would be 4326.

Also here's a good source for the geog type.

share|improve this answer
    
Thanks your solution works well (when querying using pgAdmin3). However when I am querying from PHP (nginx + php5-fpm), I get the error: Undefined object: 7 ERROR: type "geometry" does not exist. Any ideas? stackoverflow.com/questions/15780931/… –  Nyxynyx Apr 3 '13 at 7:18
    
Good, it works well for me. –  Felipe Micaroni Lalli Jun 4 '13 at 17:27

try to use this query.

SELECT X(the_geom), Y(the_geom), ST_AsEWKT(the_geom)
      FROM locations LIMIT 10;

i hope it helps you

share|improve this answer
    
The X() and Y() functions don't seem to exist. Where did you get those? –  Zoltán Feb 4 at 23:27
    
@Zoltán I think X() and Y() are valid in an older revision of PostGIS--perhaps 8.x?. Updated, that query would use ST_X() and ST_Y() instead. –  elrobis yesterday

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.