I am using the Postgis ST_DWithin function like this:
SELECT *,
ST_X(geometry) as longitude,
ST_Y(geometry) as latitude,
ST_Distance_Sphere(
ST_SetSRID(
ST_MakePoint(-118.478255,34.303046999999999),
4326),
geometry) / 1609.34 as DISTANCE
FROM poi
WHERE ( category =10980400 )
AND ST_DWithin(geometry::geography,
(ST_SetSRID(
ST_MakePoint(-118.478255,34.303046999999999),
4326)::geography
),
80467.2,
false)
ORDER BY DISTANCE
LIMIT 10
I am not sure if the performance degrades with increasing distance like 5, 10, 20, 40 miles. Please help me understand if it degrades then what can be the reason ? Is it going to be exponential or will it depend on the number of points ?
I am using it for finding nearest points (like ATMs, restaurants) within a radius.
I have indexes available for geometry and geography. When I "explain analyze" the query I can see the usage of the spatial index. I am using geography because I am getting user input in meters, kilometers etc.
Is there a way by which I can stop degradation over large distances ? We are supporting maximum 50 miles.
The problem with using KNN is that it degrades in those cases where it doesn't find 10 results and keep on searching till any distance. Initially we were doing a subquery using KNN and then filtering the results on distance outside the subquery. It timed out in those cases where it started searching for let's say an ATM and kept on searching till it found 10. If the radius by user is only 1 KM then also it kept searching outside that radius till it found 10.