3

What I am trying to do is buffer a main st and a side street and get all of the points that intersect that buffer, however they need to be mutually exclusive. In the example below, my results would give me a duplicate at the intersection of Epping Forest Rd and Point No Point Dr.

enter image description here

Logic: I need to buffer Epping Forst Rd and get all points that intersect that buffer and buffer Point No Point Dr and get all points that intersect that buffer then compare both results and drop any points that are duplicate.

So far I have this SQL statement:

SELECT  aa_points.int_id
    FROM    aa_points, 
            road_ln 
WHERE road_ln.st_nam = 'EPPING FOREST' 
AND ST_Intersects(aa_points.the_geom_webmercator,
ST_Buffer(road_ln.the_geom_webmercator,10))

That queries one road but I am at a loss on how to do the second road in a nested statement and how to compare/drop records.

  • 1
    Do you want to completely drop those two points (so they are in neither selection), or just not have the same points in two selections? If the latter, what criteria determines which selection they should belong to? – Chris W Aug 26 '14 at 19:56
3

To begin with you might consider using ST_DWithin instead of creating a buffer and using ST_Intersects. It will be faster and simpler.

That being said, to get all the points that are in one buffer but not in both buffers you need to get the symmetric difference of both sets.

Symmetric differences look something like this: (a EXCEPT b) UNION (b EXCEPT a).

For your specific problem try the following:

WITH 
eppingforest AS
    (
    SELECT aa_points.int_id
    FROM road_ln, aa_points
    WHERE road_ln.st_nam = 'EPPING FOREST'
        AND ST_DWithin(aa_points.the_geom_webmercator, road_ln.the_geom_webmercator, 10)
    ),
pointnopoint AS
    (
    SELECT aa_points.int_id
    FROM road_ln, aa_points
    WHERE road_ln.st_nam = 'POINT NO POINT'
        AND ST_DWithin(aa_points.the_geom_webmercator, road_ln.the_geom_webmercator, 10)
    ),
aexceptb AS
    (
    SELECT * FROM eppingforest
    EXCEPT
    SELECT * FROM pointnopoint
    ),
bexcepta AS
    (
    SELECT * FROM pointnopoint
    EXCEPT
    SELECT * FROM eppingforest
    ),
symmetricpoints AS
    (
    SELECT * FROM aexceptb
    UNION
    SELECT * FROM bexcepta
    )
SELECT aa_points.int_id
FROM symmetricpoints
;

If you don't need the symmetric difference just no duplicates try the following:

SELECT DISTINCT aa_points.int_id
FROM aa_points, road_ln 
WHERE road_ln.st_nam IN ('EPPING FOREST','POINT NO POINT')
  AND ST_DWithin(aa_points.the_geom_webmercator, road_ln.the_geom_webmercator, 10);

The DISTINCT will filter out duplicates.

  • This is fantastic I just found out about the with statement but I never seen or heard of EXCEPT thank you Gregory. This give me all points however those two points are not included. I need those two points to be included just not duplicated. So My resulting table should include 12 results weither it be 4 points on Epping rd and 8 on point no point or 6 on Epping and 6 on point no point. Is this possible? – CrazySpatial Aug 27 '14 at 12:37
0

Don't buffer to find intersections—it's expensive. Use a distance-based function instead, like ST_DWithin. To get the points you want:

SELECT aa_points.int_id
FROM aa_points, road_ln 
WHERE road_ln.st_nam IN ('EPPING FOREST', 'POINT NO POINT')
  AND ST_DWithin(aa_points.the_geom_webmercator, road_ln.the_geom_webmercator, 10);

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.