Skip to main content
edited tags; edited title; edited tags
Link
PolyGeo
  • 65.4k
  • 29
  • 114
  • 345

Buffer multiple RD'sroads to find points within and compare?

deleted 709 characters in body
Source Link

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.

***** UPDATE ***** I found out about WITH and I put this together and it is close to what I need to do but I am still having trouble with how to compare and separate.

 WITH first AS(
  SELECT aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'DAIRY FARM' 
  AND ST_Intersects(aa_points.the_geom_webmercator,  
  ST_Buffer(aa_road.the_geom_webmercator,25))), second AS (SELECT  aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'AUTUMN VALLEY' 
  AND ST_Intersects(aa_points.the_geom_webmercator,
  ST_Buffer(aa_road.the_geom_webmercator,25)))
  SELECT * FROM second

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.

***** UPDATE ***** I found out about WITH and I put this together and it is close to what I need to do but I am still having trouble with how to compare and separate.

 WITH first AS(
  SELECT aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'DAIRY FARM' 
  AND ST_Intersects(aa_points.the_geom_webmercator,  
  ST_Buffer(aa_road.the_geom_webmercator,25))), second AS (SELECT  aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'AUTUMN VALLEY' 
  AND ST_Intersects(aa_points.the_geom_webmercator,
  ST_Buffer(aa_road.the_geom_webmercator,25)))
  SELECT * FROM second

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.

added 713 characters in body
Source Link

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.

***** UPDATE ***** I found out about WITH and I put this together and it is close to what I need to do but I am still having trouble with how to compare and separate.

 WITH first AS(
  SELECT aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'DAIRY FARM' 
  AND ST_Intersects(aa_points.the_geom_webmercator,  
  ST_Buffer(aa_road.the_geom_webmercator,25))), second AS (SELECT  aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'AUTUMN VALLEY' 
  AND ST_Intersects(aa_points.the_geom_webmercator,
  ST_Buffer(aa_road.the_geom_webmercator,25)))
  SELECT * FROM second

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.

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.

***** UPDATE ***** I found out about WITH and I put this together and it is close to what I need to do but I am still having trouble with how to compare and separate.

 WITH first AS(
  SELECT aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'DAIRY FARM' 
  AND ST_Intersects(aa_points.the_geom_webmercator,  
  ST_Buffer(aa_road.the_geom_webmercator,25))), second AS (SELECT  aa_points.int_id
  FROM      aa_points, 
            aa_road 
  WHERE aa_road.st_nam = 'AUTUMN VALLEY' 
  AND ST_Intersects(aa_points.the_geom_webmercator,
  ST_Buffer(aa_road.the_geom_webmercator,25)))
  SELECT * FROM second
Source Link
Loading