3

I want to find the closest polygon to a point. My idea was to use the <-> operator for it. According to the docs it says:

For PostgreSQL below 9.5 only gives centroid distance of bounding boxes and for PostgreSQL 9.5+, does true KNN distance search giving true distance between geometries, and distance sphere for geographies.

I use PostGIS 2.2 and PostgreSQL 9.5.7. So, the <-> operator should use the "true KNN distance", but it does not: it still orders by the centroid_distance.

enter image description here

SELECT id,
       ST_Distance(my_point.geom, 
                   my_polygons.geom) AS real_distance,
       ST_Distance(my_point.geom,  
                   ST_Centroid(my_polygons.geom)) AS centroid_distance



FROM (
    SELECT 'blue' AS id, ST_Polygon(ST_GeomFromText('LINESTRING(0 3, 2 3,  2 4, 0 4 ,0 3)'),4326) AS geom
    UNION
    SELECT 'green' AS id,  ST_Polygon(ST_GeomFromText('LINESTRING(2 0, 8 0 , 8 2, 2 2 ,2 0)'),4326) AS geom

) AS my_polygons,

(
    SELECT ST_SetSRID(ST_Point(1, 1),4326) AS geom
) AS my_point

ORDER BY my_point.geom <-> my_polygons.geom 

Result:

|       | real_distance | centroid_distance |
---------------------------------------------
| blue  |       2       |        2.5        |
| green |       1       |        4          |

I expected that the <-> operator orders the polygons according to the real_distance and not the centroid_distance.

Am I doing something wrong?

  • 1
    The <-> operator is finding the closest polygon by the kNN distance and ordering results accordingly, but your actual distance metric is being determined by ST_Distance, which will definitely return the distance to the nearest point on exterior of the polygon, not the centroid. It is a bit hard to tell without some raw numbers, though. – John Powell Jul 10 '17 at 12:56
  • Can you add a field ST_Distance(ST_Point(1,2)::geography, ST_Centroid(my_polygons.geom)) AS cent_distance to be sure of this? – tilt Jul 10 '17 at 15:08
  • @JohnPowellakaBarça do you need more information? Which numbers shall I provide? – coding_bird Jul 11 '17 at 7:36
  • 1
    Well, you say the answer is incorrect, but it is impossible for us to test without some geometries and the expected results. The query itself looks ok. – John Powell Jul 11 '17 at 7:40
  • 1
    OK. It seems that PostGIS 2.2.2 does not use "real KNN distance" - whereas 2.3.2 does support it. – coding_bird Jul 11 '17 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.