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.

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

  • 1
    I added emphasis on the actual question, that is, the opposite of what you are describing at length... ;) – geozelot Mar 30 '20 at 9:14

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 
   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
      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.

  • No need for any outer join. Or any subquery at all, really...better use SELECT COUNT(inj.*) FROM injuries_2263 AS inj JOIN nyc_cd_2263 AS bo ON ST_Intersects(bo.geom, inj.geom) WHERE NOT EXISTS (SELECT 1 FROM bike_routes_2015 WHERE ST_DWithin(geom, inj.geom, 15) GROUP BY bo.gid;. – geozelot Mar 29 '20 at 18:09
  • Yes, that also works, though I doubt it makes any practical difference to run-time -- though, could be wrong, due to the CTE optimization fence. Personally, I find CTEs easier to read, than WHERE NOT EXISTS subquerie -- see, you can't get away from the need for a subquery, no matter how you want to name it. – John Powell Mar 30 '20 at 7:36
  • Hm. I may be super dumb here, but as I see it your query does a rather complicated job of finding all (not distinct!) points within the threshold (equivalent to a simple (pseudo) INNER JOIN ON ST_DWithin), and then grouping them by intersection. It eluded me at first, too, but OP actually asks for non-proximity injuries. – geozelot Mar 30 '20 at 9:14
  • Yeah, I know, that is what the RIGHT JOIN syntax does in first CTE. It should be functionally equivalent to WHERE NOT EXISTS, but, I haven't tested it. I agree that yours is shorter, but, both involve two spatial joins, a GROUP BY, and a NOT NULL/NOT EXISTS clause. I suspect that if one looked at an EXPLAIN statement, they would be essentially identical, assuming I got my join syntax correct. I think it is apples and oranges, but I am most likely wrong. Perhaps the OP could test both for us, seeing as they have the data. – John Powell Mar 30 '20 at 9:28
  • 1
    @PieterB ST_Disjoint requires creating a polygon representing the area that is "close to" ALL the bike lanes (probably by using ST_Buffer and ST_Union). Using ST_DWithin avoids having to compute the buffer polygon and is likely faster. – dr_jts Mar 31 '20 at 6:23

I finally made it work

SELECT bo.*, COUNT(Q.*) AS coun
FROM nyc_cd_2263 AS bo
                 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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