5

I have been playing with the ST_Distance function available within PostGIS and to my surprise, I have encountered some rather strange behavior...

In the simplest setting, I have just two tables N and P. Table N contains point coordinates, while table P contains multipolygons representing administrative divisions. Now, I would like to calculate the distance between a particular node and given range of (multi)polygons. To this end, I just invoked following command:

demo=> SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography, false)
       FROM N, P WHERE (N.node_id=9 AND P.iid=25);

  node_id |  iid  |   st_distance
 ---------+-------+------------------
        9 |    25 | 1732718.95934996
 (1 row)

which seems to produce the expected result. However, if I slightly modify the WHERE condition:

demo=> SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography, false)
       FROM N, P WHERE (N.node_id=9 AND P.iid>=20 AND P.iid<=25);
 node_id |  iid  |   st_distance
---------+-------+------------------
       9 |    20 | 1738836.97788419
       9 |    21 | 1723001.48856517
       9 |    22 | 1736600.89344563
       9 |    23 | 1718786.25166805
       9 |    24 | 1503840.93486047
       9 |    25 |                0
(6 rows)

the result for pair 9, 25 suddenly changes. Am I missing something obvious? I am using PostgreSQL 9.4.5 (Postgis 2.1.8) on CentOS 7.

EDIT:

It seems that the problem persists even if I test only the "suspicious" polygon versus several points, i.e., let's consider only following points

 node_id |       st_astext        
---------+------------------------
       8 | POINT(-3.9018 42.8116)
       9 | POINT(-6.1947 41.5758)
      10 | POINT(-6.0858 41.4912)

and only polygon 25 (http://pastebin.com/7deJPC3u). Then I get:

demo=> SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography, false)
       FROM N, P WHERE (N.node_id IN (9) AND P.iid=25);
 node_id |  iid  |   st_distance    
---------+-------+------------------
       9 |    25 | 1732718.95934996
(1 row)

as well as:

demo=> SELECT N.node_id, P.iid, ST_Distance(P.outline::geography, N.location::geography, false)
       FROM N, P WHERE (N.node_id IN (8,9,10) AND P.iid=25);
 node_id |  iid  |   st_distance    
---------+-------+------------------
       8 |    25 | 1731869.60412558
       9 |    25 |                0
      10 |    25 | 1729708.79737747
(3 rows)
  • This looks odd. Would you be able to share the geometries with us for testing? – tilt Feb 5 '16 at 8:48
  • I included the point coordinates as well as the "affected" polygon. I did similar calculations with several other polygons (around 10000), but it seems that this happens only in this particular case... – ewcz Feb 5 '16 at 9:39
  • 1
    A vague proposition: what if you use ST_ForceRHR (postgis.net/docs/ST_ForceRHR.html) at first!? – Stefan Feb 5 '16 at 9:52
  • 1
    What PostGIS version are you running? This looks like a disagreement between the cached tree-based distance algorithm and the brute force algorithm. There were a number of such issues in 2.1 that got fixed over time in patch releases. Using the latest patch release would be recommendation #1. – Paul Ramsey Feb 5 '16 at 18:10
  • 1
    OK, you can confirm that the algorithms differ by comparing the results of _ST_DistanceUnCached(geog,geog) to _ST_DistanceTree(geog,geog) for your one suspect pairing. If they do in fact differ, please put the pairing into a postgis ticket: you have found a bug. – Paul Ramsey Feb 5 '16 at 18:36
4

You can confirm that the discrepancy is a bug by comparing the results of _ST_DistanceUnCached(geog,geog) to _ST_DistanceTree(geog,geog)

What is happening under the covers is that the "tree" algorithm only kicks in when a geometry shows up in a query a couple times in a row. At that point the geometry is cached and a tree built on it, for faster distance calculation. This can result in the odd results seen above, where the order in which geometries hit the function can change the results. When the bad pairing of geometries hit the function first, the brute-force, uncached algorithm was used. When it hit later, the tree algorithm was used.

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.