CODEWITHSUNDEEP
postgis
Niklas Möller
Niklas Möller

Reputation: 115

PostGIS accuracy of ST_DWithin results

I'm testing a function in a PostGIS spatial database: ST_DWithin. On edge cases I sometimes get true and sometimes false.

SELECT ST_DWithin(
    ST_GeomFromText('POINT(-90.01 30)','4326'),
    ST_GeomFromText('POINT(-90 30)','4326'),
    '0.01'
)
st_dwithin -> false

and

SELECT ST_DWithin(
    ST_GeomFromText('POINT(-90.1 30)','4326'),
    ST_GeomFromText('POINT(-90 30)','4326'),
    '0.1'
)
st_dwithin -> true

Shouldn't both be either true or false? Can anybody explain the results to me?

Upvotes: 1

Views: 323

Answers (1)

JGH
JGH

Reputation: 17906

This is caused by the fact that underlying computations uses double precision and not the exact numeric type. Therefore, the computed distance between points is accurate up to 15 digits only.

Select ST_Distance(ST_GeomFromText('POINT(-90.01 30)','4326'),
        ST_GeomFromText('POINT(-90 30)','4326')) d1,
    ST_Distance(ST_GeomFromText('POINT(-90.1 30)','4326'),
        ST_GeomFromText('POINT(-90 30)','4326')) d2;
==>
         d1         |         d2
--------------------+--------------------
 0.0100000000000051 | 0.0999999999999943
(1 row)

We can see that both computed distances are inexact. Moreover, floating point equality comparison should always consider the precision. If not done, we get unexpected results (like d1 > .01 and d2 < .1)

You can read Postgresql doc on floats and google floating point comparison

Upvotes: 1

Related Questions