[GIS] Get Lon Lat values from a GEOGRAPHY data type in PostGIS

coordinate systempostgispostgresql

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.

Best Answer

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.

Related Solutions

[GIS] Adding Geometry column in PostGIS

First, as others have said, make sure you have PostGIS installed. Run this command to ensure that you have PostGIS installed,

SELECT postgis_version();

If you don't have postgis_version() then you need to install PostGIS on the database. If it's not installed, you can try this when connected to the specific database you want to install PostGIS on,

CREATE EXTENSION postgis;

If that fails than you need to install or build PostGIS which then becomes a question of what operating system you're using. If you've got the extension downloaded and installed, run this in psql

\dfS AddGeometryColumn

If that works, then you have the function installed too but you're providing it the wrong types. We need the actual function call. Provide us what you're calling AddGeometryColumn() with. Also, if your version of PostGIS is newer than 2.0, do not use AddGeometryColumn(). It is not needed. Instead use the ALTER TABLE ADD COLUMN with the subtype. Here is an example to add a geometry point in srs 4326.

ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

[GIS] Lat-Long values get rounded off when stored in a column type Geography in postgres

You're asking for a lot of precision there.

From my understanding, PostGIS stores coordinates in a double precision float, so a 64 bit floating point data type, and so that's where your precision limit is coming from.

From my testing with Python and Numpy, I tested precision of a 32 and 64 bit floating point and get this result:

>>> import numpy
>>> float32 = numpy.float32(123.123456789123456789)
>>> float32
123.12346
>>> float64 = numpy.float64(123.123456789123456789)
123.12345678912345

So, comparing my number with yours looks like:

118.382812781149
123.12345678912345

For some reason, you're losing two digits, and that may be because you're storing as geography and retrieving from a cast to geometry, but I'm not sure. Regardless, that's just how precise the numbers are in this digital system!

Best Answer

Related Solutions

[GIS] Adding Geometry column in PostGIS

[GIS] Lat-Long values get rounded off when stored in a column type Geography in postgres

Related Question