CODEWITHSUNDEEP
subquerypostgis
user1621922
user1621922

Reputation: 21

Using ST_DWithin in subquery (PostGIS)

I have a geometry table with points. All points have unique ID's. Now I want to make a query where I select one point (with id 34567) and make a bufferzone analysis of 5 km around this point. My goal is to select all points more than 5 km away from the selected point. All data is stored in one table.

I have tried with the following,

SELECT D.id, D.geometry, S.id, S.geometry
FROM points AS D, points AS S
WHERE not ST_DWithin(D.geometry, S.geometry, 5000) AND D.id not like '34567'

But the query runs forever and ever.

What am I doing wrong?

All answers appreciated

Upvotes: 2

Views: 1712

Answers (2)

wesselnv
wesselnv

Reputation: 99

Thanks to John Barça for this. I'm a newbie but all the examples in the PostGIS manual include the geom features as text, which is not very practical in normal life. The thing is that the result will also return the point that is taken as reference (34567). So setting the distance as 0, returns the original point, or count=1. I suppose the quickest solution is to do an EXCEPT:

SELECT S.id, S.geometry FROM points AS S WHERE not ST_Dwithin(S.geometry, 
(select geometry from points where id=34567), 5000)
EXCEPT
SELECT S.id from POINTS where id=34567

But then you have to put the id twice, which doesn't strike me as very efficient either.

Upvotes: 0

John Powell
John Powell

Reputation: 12581

The query as originally written is a very inefficient way of answering this question as it involves essentially a spatial self-join, whereas in fact, the OP only wanted to know the points that were more than 5km away from the single point with id=34567. A self joins would be used if you wanted to find all points more than 5km away from all other points for every point, which is essentially an 0(n^2) operation.

An explain on the original query will show a nested loop with two full sequence scans (ie, points A and points B) plus the spatial join of ST_DWithin.

The query can be much better written as 

SELECT count(S.id) FROM points AS S WHERE not ST_Dwithin(S.geometry, 
(select geometry from points where id=34567), 5000);

assuming that there is an index on id and a spatial index on geometry.

Upvotes: 3

Related Questions