1

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?

  • 2
    the 13th decimal place will measure in the dimension of Ångström (1 being roughly half the size of an atom)...you are talking about losing the 14th and 15th!!? refer to this beautiful answer to get a feeling for precision and accuracy maybe...and then go and lose 8 digits more ,) – ThingumaBob Jan 25 '18 at 8:50
  • As per the Tour please do not include thanks and other chit chat in your questions. – PolyGeo Jan 31 '18 at 12:38
5

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!

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.