Select points within a buffer around a line

Question

Working with PostgreSQL. I have a Table with streets (Linestrings), and a Table with accidents (Points). The streets are divided into sections. I want to find the accidents within 50m around a certain street. I used ST_Union to combine the Street segments to select a certain street, then made a Buffer around it, but when I try to select the accidents within the buffer QGIS crashes, guess there might be something wrong.

SELECT 1 as Id, c.accidents  
ST_UNION (a.geom)   
FROM Roads a, roads b, accidents c  
WHERE a.roadname = 'A3'  
AND st_distance (a.geom, b.geom) < 50  
AND ST_Within (c.geom, ST_Buffer (a.geom, 50))  
GROUP BY a.geom, c.accidents

Answer 1

For proximity searches better use ST_DWithin, as it is highly optimized to use the spatial index:

SELECT DISTINCT
       a.accidents,
       a.geom
FROM   roads AS r
JOIN   accidents AS a
  ON   ST_DWithin(r.geom, a.geom, <distance>)
WHERE  r.roadname = 'A3'
;

This will find all accidents that are within <distance>¹ of any segment with name A3; the DISTINCT will ensure that no accidents is returned more than once.

I do not ST_Union (or ST_Collect) the segments; the index works best on small geometric entities (and on-the-fly created geometries are (usually, if not explicitly functionally indexed) not covered by the index)

---

¹ the <distance> will be treated as CRS units! If you are working with a projected CRS (with units as meter), using 50 is correct; if your data is referenced in a geographic CRS, units will be degrees! In that case, a cast toGEOGRAPHY is the way to go.