Select points falling outside of buffer and count

Question

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:

• nyc_bike_routes_2015
• nycd_16a
• 20150601_.........Injury.csv

The steps are pretty simple and can be done by QGIS easily as follows:

1. Buffer injuries by 15 feet
2. Select injuries near bike lanes (where the buffers intersect with the bike lanes
3. Invert selection to select only injuries far from bike lane
4. 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.

Question:
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

Answer 1

Joining on ST_DWithin is correct, as this is fast assuming you have appropriate spatial indices. However, you want to use, in pseudo code, a RIGHT JOIN ... WHERE injuries IS NULL type syntax, to find all those injuries that took place more than 15 metres from a bike route. (Equally, this could be written as a LEFT JOIN WHERE somthing IS NULL query, the point being, that this is how you find elements of one table that do not appear when joined on another table). Once you have those accident geometries, more than 15m from a bike route, you can use ST_Intersects to join on the boroughs table and do a group by/count.

So, without testing, something like:

WITH injuries_outside_bikelanes (geom) AS 
 SELECT 
   inj.id, inj.geom 
   FROM injuries_2263 inj 
      RIGHT JOIN bike_routes_2015 bik ON ST_DWithin(inj.geom, bik.geom, 15) 
  WHERE inj.id IS NULL
 )
 SELECT 
      bo.*, count(inj.id)
  FROM  nyc_cd_2263 bo, injuries_outside_bikelanes inj
 WHERE ST_Intersects(bo.geom, inj.geom) 
 GROUP BY bo.gid;

It is always worth running an EXPLAIN on your queries to ensure that indices are being used correctly. If you have spatial indices on all the geometry columns here, you should see two index scans on spatial indices come back from the explain statement. If you don't, this will probably explain your timing issues.

Answer 2

I finally made it work

SELECT bo.*, COUNT(Q.*) AS coun
FROM nyc_cd_2263 AS bo
LEFT OUTER JOIN (SELECT  inj.*
                 FROM injuries_2263 inj 
                 LEFT JOIN bike_routes_2015 bik ON ST_DWithin(inj.geom, bik.geom, 15) 
                 WHERE bik.gid IS NULL) AS Q
ON ST_Within(Q.geom, bo.geom)
GROUP BY bo.gid

The first selection query needed to be modified to a LEFT JOIN where bik.gid is null, otherwise what was returned were all the bike lanes (bik) that returned NULL to the DWithin predicate.