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?

10
  • 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. Commented Jul 10, 2017 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
    Commented Jul 10, 2017 at 15:08
  • @JohnPowellakaBarça do you need more information? Which numbers shall I provide? Commented Jul 11, 2017 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. Commented Jul 11, 2017 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. Commented Jul 11, 2017 at 12:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.