4

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.

  • I believe there is a performance benefit by choosing a projection in meters rather than using geography. This should improve accuracy also. However if the points are in mixed projections, then geography is going to yield better performance. As for the second question which should be asked as another post, you might try wrapping your statement into a subquery to take advantage of the spatial index first. Though this is just a guess since I'm no expert on either matter. – kttii Jan 12 '17 at 16:54
  • Thanks @kttii . I have created a separate question for btree vs spatial index. – Rahul Bansal Jan 12 '17 at 18:01
5

Well the performance will degrade as the number of items selected by the filter grows, and this is true of any filter, not just ST_DWithin(). However, your query has another design problem which may or may not be causing performance issues (you don't include any information on it): the lack of an index to support the filter you are running.

You have a column with geometry type, but you run a filter that uses the geography type. Without a supporting index, you aren't going to get the performance you want.

There are many ways to fix this

  • As a commenter suggests, just working entirely in geometry in a planar projection is the simplest and fastest alternative. If your data are local to a particular region, and likely to remain so, then this is the recommended solution. You probably already have a geometry index, but if not, the syntax is just CREATE INDEX poi_gx ON poi USING GIST (geometry)
  • Change the column type to geography, and do everything in geography space. This is the second best solution, as it keeps everything relatively simple. Geography is marginally slower than geometry due to the extra complexity of spherical math, but at least you won't be casting things all the time. The syntax for a simple geography index is the same as for geometry, CREATE INDEX poi_gx ON poi USING GIST (geography_column)
  • Build a functional index to support your existing query. If you change your geometry::geography casts to the equivalent functional form, geography(geometry) you can build a geography index that will support your query, CREATE INDEX poi_gg_x ON poi USING GIST (geography(geometry))

Since you are interested in "nearest within a distance" you're going to see the result sets get larger as you increase radii, so effectively you'll pull multiple thousand results, sort them, and then throw away all but a handful.

The solution is to recast the problem using the KNN distance operations instead, get the 10 nearest that way, and then just filter them to strip out any that are beyond your target distance, in the case of a small result set.

SELECT *,
    ST_X(geometry) AS longitude, 
    ST_Y(geometry) AS latitude,
    ST_Distance(
        geometry::geography, 
        Geography(ST_MakePoint(-118.478, 34.303))) AS distance
FROM poi 
ORDER BY geography(geometry) <-> Geography(ST_MakePoint(-118.478, 34.303))
LIMIT 10

I'm not 100% sure that this will pick up the geography index to evaluate the KNN ordering, but if it doesn't you can make it work by actually changing the column types to geography, worst case scenario. Also, you might be able to apply the radius filter right in the main query, but if not you can apply it by running the KNN in a CTE, then filtering that.

  • Thanks a lot Paul. I have spatial indexes. I have also edited the question to include this information. I have created a new question for btree and spatial index. Please have a look at the edited question and kindly suggest. – Rahul Bansal Jan 12 '17 at 18:05
  • 1
    I recently wrote about some more esoteric options for distance queries in this post – dbaston Jan 12 '17 at 18:08
  • KNN might fit your use case much better. Added a section on that. – Paul Ramsey Jan 12 '17 at 18:54
  • @PaulRamsey, the problem with 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. – Rahul Bansal Jan 12 '17 at 19:32
  • @dbaston, great post !! Can you do a KNN vs ST_DWithin comparison. Please see my comment above. – Rahul Bansal Jan 12 '17 at 19:42

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.