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

latitude longitudepostgis

I have some lat/long coordinates eg:

(64.123456789123456, -110.12341234123412)

I store them in the table using a query that looks like this:

INSERT INTO myTable (name, geo) VALUES ('test', ST_GeogFromText('SRID=4326;POINT(-110.12341234123412 64.123456789123456)') );

but when I try to retrieve the lat-long back from the table, they seem to be rounded off, in this case, I get

lat: 64.1234567891234

and

long:-110.123412341234

select ST_X(geo::geometry) as long, ST_Y(geo::geometry) as lat from myTable;

I am probably not using the right data types resulting in loss of values, but haven't been able to figure out.

I am new to PostgreSQL.

How do I not lose the coordinate precision?

Best Answer

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!

Related Question