27

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

3 Answers 3

32

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.

3
  • 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, 2013 at 7:18
  • Good, it works well for me.
    – Felipe
    Jun 4, 2013 at 17:27
  • Thanks, it helped me to share the query with my client. You saved my another day Jul 14, 2021 at 7:51
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
  • 4
    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. Jun 15, 2016 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

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

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.