I am currently learning PostGIS and following the course offered by Datapolitan.
I want to do the steps mentioned in slide 200 https://training.datapolitan.com/qgis-training/Intermediate_GIS/#200 using a spatial query
Data can be found in https://github.com/Datapolitan-Training/qgis-training/tree/gh-pages/Intermediate_GIS/data I used the following layers:
The steps are pretty simple and can be done by QGIS easily as follows:
- Buffer injuries by 15 feet
- Select injuries near bike lanes (where the buffers intersect with the bike lanes
- Invert selection to select only injuries far from bike lane
- Count off lane injuries by district
I want to do that on PostGIS and I don't seem to get the grasp of it. The query which I assume should be correct is taking too long and making my computer run out of memory
SELECT bo.*, COUNT(Q.*) as numb FROM nyc_cd_2263 bo LEFT OUTER JOIN (SELECT bik.boro, bik.gid, bik.instdate, inj.geom FROM injuries_2263 inj INNER JOIN bike_routes_2015 bik ON ST_DWithin(inj.geom, bik.geom, 15)) Q ON ST_Within(Q.geom, bo.geom) GROUP BY bo.gid
This query is intended to select and count the injuries close to the bike lanes.
I want it to do the opposite and select the injuries away from the bike lanes.
I though of using NOT ST_DWithin but this makes the query take enormous time to run.
Any idea how this could be worked out