2

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
1
  • PostGIS has an ST_DWithin operator. Use that instead. Also, multiple tables in FROM is an ancient practice -- use a JOIN operator. More details about the "crash" are necessary.
    – Vince
    Commented Dec 31, 2019 at 12:33

1 Answer 1

4

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.

2
  • Also, you may want to avoid naming columns like the tables they are in (I read that your id column is accidents.accidents?); next to overall confusion and the general practice of relational naming, there are situations where PostgreSQL is sensitive about this.
    – geozelot
    Commented Dec 31, 2019 at 12:42
  • After deleting a.geom (SELECT ...a.geom) it worked. Thaaaaank you for the explanations. I know "join" is better, but sometimes I forget to use it. Commented Jan 2, 2020 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.