15

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.
  • 1
    For geography datatype use valid function, ex. ST_MaxX() and ST_MaxY(). – Peter Krauss Oct 28 '15 at 10:02
18

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.

  • 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 Jun 4 '13 at 17:27
1

Not need cast

Try to use this query.

SELECT ST_XMax(geog), ST_YMax(geog), ST_AsEWKT(geog)
FROM locations LIMIT 10;

it works for me.

  • 1
    What PostGIS & PostgreSQL version are you using? With 9.5 and 2.2, running the query SELECT ST_XMax(ST_GeographyFromText('SRID=4326;POINT(-73.968504 40.779741)')); gives me ERROR: function st_xmax(geography) does not exist. – r0estir0bbe Jun 15 '16 at 14:57
0

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

  • 1
    The X() and Y() functions don't seem to exist. Where did you get those? – Zoltán Feb 4 '14 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 Oct 20 '14 at 23:26
  • Even PostGIS 2.2 have no ST_X(geography ), see postgis.net/docs/manual-2.2/ST_X.html – Peter Krauss Oct 28 '15 at 10:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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