[Django]-PostGIS Nearest Neighbor Search Results Out of Order?

by

3👍

✅

UPDATED ANSWER:

Postgis has two approximate solutions for kNN neighbors functionality, since September 2011:

  • Using the <-> operator, you get the nearest neighbour using the centers of the bounding boxes to calculate the inter-object distances.
  • Using the <#> operator, you get the nearest neighbour using the bounding boxes themselves to calculate the inter-object distances.

Your problem is, that both are approximate, so they are not perfect. So, if you want the best 250 results, you can use any of them to retrieve for example the best 1000 results and then order the same results by ST_DISTANCE and LIMIT 250 to get the best 250 results out of those approximate 1000.

Example: 

SELECT * FROM 
    (SELECT *,ST_DISTANCE(current_point::geography, 'SRID=4326;POINT(" + str(lon) + " " + str(lat) + ")'::geography ) AS st_dist
    FROM ua
    ORDER BY current_point::geometry <-> 'SRID=4326;POINT(" + str(lon) + " " + str(lat) + ")'::geometry 
    LIMIT 1000) AS s
    ORDER BY st_dist LIMIT 250;
👤Alexandros

Leave a comment