Get Lon Lat values from a GEOGRAPHY data type in PostGIS

Question

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.

For geography datatype use valid function, ex. ST_MaxX() and ST_MaxY(). — Peter Krauss, Oct 28 '15 at 10:02

score 32 · Accepted Answer · answered Apr 03 '13 at 07:13

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.

answered Apr 03 '13 at 07:13

nickves

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? http://stackoverflow.com/questions/15780931/postgresql-query-error-when-using-pdo-driver-in-php-laravel-4 – Nyxynyx Apr 03 '13 at 07:18
Good, it works well for me. – Felipe Jun 04 '13 at 17:27
Thanks, it helped me to share the query with my client. You saved my another day – Sumit Kumar Gupta Jul 14 '21 at 07:51

score 1 · Answer 2 · edited Jun 11 '20 at 15: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.

edited Jun 11 '20 at 15:27

Community

answered Oct 28 '15 at 10:05

Peter Krauss

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. – r0estir0bbe Jun 15 '16 at 14:57

score 0 · Answer 3 · answered Apr 03 '13 at 07:18

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

answered Apr 03 '13 at 07:18

urcm

1

The X() and Y() functions don't seem to exist. Where did you get those? – Zoltán Feb 04 '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
2

Even PostGIS 2.2 have no ST_X(geography ), see http://postgis.net/docs/manual-2.2/ST_X.html – Peter Krauss Oct 28 '15 at 10:04