ST_DistanceSphere and KNN discrepancy

Question

I have a table containing thousands of data points around London.

I'm trying to write a query to find the closest data points to a given latitude and longitude.

I'm getting different results based on whether I order by the <-> distance operator or the result of ST_DistanceSphere. Here are the two queries and their results:

---

Order By <->
------------
SELECT 
uid,
ST_DistanceSphere(
  ST_PointFromText('POINT(-0.186243362115208 51.5128590051459)', 4326),
  the_geom
) as d
FROM points 
ORDER BY the_geom <-> ST_SetSRID(ST_Point(-0.186243362115208, 51.5128590051459), 4326) LIMIT 5;

Results:

  uid   |         st_astext          |       d
---------+----------------------------+---------------
6997685 | POINT(-0.186723 51.500052) | 1424.46281677
6997684 | POINT(-0.187011 51.500056) | 1424.62219563
6997686 | POINT(-0.186435 51.500047) | 1424.69367394
6997683 | POINT(-0.187299 51.500061) | 1424.94947768
6997687 | POINT(-0.186147 51.500043) |    1425.09232

---

Order By ST_DistanceSphere
--------------------------
SELECT 
uid,
ST_DistanceSphere(
  ST_PointFromText('POINT(-0.186243362115208 51.5128590051459)', 4326),
  the_geom
) as d
FROM points 
ORDER BY d LIMIT 5;

Results:

   uid   |         st_astext          |       d
---------+----------------------------+---------------
6997686 | POINT(-0.186435 51.500047) | 1424.69367394
6997685 | POINT(-0.186723 51.500052) | 1424.46281677
6997687 | POINT(-0.186147 51.500043) |    1425.09232
6997688 | POINT(-0.185859 51.500039) | 1425.76968591
6997684 | POINT(-0.187011 51.500056) | 1424.62219563

As you can see, the first query returns 6997683 in the results, but the second query does not (and returns 6997688 instead). 6997683 is the closer value of the two, so 6997688 should not be returned by either query.

The type of the_geom is ST_Point and the SRID (checked with Find_SRID) is 4326.

What might be causing the difference, and what am I doing wrong?

Answer 1

I think this is because <-> uses ST_Distance, as when I order explicitly by ST_Distance I get the same results.

I have amended my query to this:

SELECT 
  points.*,
  ST_AsText(points.geolocation) as geolocation, 
  ST_DistanceSphere(points.geolocation, ST_Point(:x, :y)) as distance

FROM (
  SELECT * FROM points ORDER BY geolocation <-> ST_SetSRID(ST_Point(:x, :y),4326) LIMIT 50
) as points

ORDER BY ST_DistanceSphere(points.geolocation, ST_Point(:x, :y)) LIMIT 5

I make use of the efficiency of <-> to narrow down my results to the 'nearest' 50, and then filter this list by the more accurate ST_DistanceSphere. And I think it gives me the results I want.