5

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?

  • The Order By ST_DistanceSphere result is obviously wrong... what if you put the order by in a query, with the rest in a subquery select * from (select uid, [...])a order by d LIMIT 5. But actually that should not be necessary. – pLumo Dec 15 '17 at 15:56
  • I think it's the other way around. Filtering by ST_DistanceSphere returns points that are closer (in terms of Earth Distance) than filtering by <->. However I am now using a subquery :-) – Joaquim d'Souza Dec 15 '17 at 16:23
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.

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.