2

I'm new to PostGIS (and SQL in general), and am running through the foss4g workshop, using their nyc data. My code below works as intended : For every metro stop, I calculate the number of streets in a 100m radius and 200m radius.

CREATE TABLE close200 AS SELECT 
nyc_subway_stations.name, 
     count(DISTINCT nyc_streets.name) count200
FROM nyc_subway_stations, nyc_streets
WHERE  ST_DWITHIN(nyc_subway_stations.geom, nyc_streets.geom, 200)
GROUP BY nyc_subway_stations.name
ORDER BY count200 DESC;

CREATE TABLE close100 AS SELECT 
nyc_subway_stations.name, 
     count(DISTINCT nyc_streets.name) count100
FROM nyc_subway_stations, nyc_streets
WHERE  ST_DWITHIN(nyc_subway_stations.geom, nyc_streets.geom, 100)
GROUP BY nyc_subway_stations.name
ORDER BY count100 DESC;

CREATE TABLE proxMetro AS SELECT 
close100.name, close100.count100, close200.count200
FROM close100, close200
WHERE close100.name = close200.name;
DROP TABLE close100, close200;
SELECT * FROM proxMetro;

I feel that this isn't the best way to write this out, but I'm not sure how to shorten my code or make it less heavy. I shouldn't have to create two extra tables and should be able to select the information directly from my original tables. My very first intuition was to use nested queries :

SELECT 
    nyc_subway_stations.name, 
    (SELECT count(DISTINCT nyc_streets.name) count100
        FROM nyc_subway_stations, nyc_streets
        WHERE  ST_DWITHIN(nyc_subway_stations.geom, nyc_streets.geom, 100)),
    (SELECT count(DISTINCT nyc_streets.name) count200
        FROM nyc_subway_stations, nyc_streets
        WHERE  ST_DWITHIN(nyc_subway_stations.geom, nyc_streets.geom, 200))
FROM nyc_subway_stations, nyc_streets
GROUP BY nyc_subway_stations.name
ORDER BY count100 DESC;

Which returns a same number for every row, which I'm not sure corresponds to anything. Is this second method on the right track? If so what am I doing wrong?

I'm using PostGreSQL 9 with PostGis 2

2

You don't need to select the same thing twice as the 100m result is fully included in the 200m result and rather do a conditional count(case when).

Additionally, I prefer a Join and shorter aliases, which makes it more clear. (Using a LEFT JOIN will also return stations with no streets within 200m).

SELECT 
    stations.name,
    count(distinct (case when ST_DWITHIN(stations.geom, streets.geom, 100) then streets.name end)) count100,
    count(distinct streets.name) count200
FROM
    nyc_subway_stations stations
LEFT JOIN
    nyc_streets streets
ON
    ST_DWITHIN(stations.geom, streets.geom, 200)
GROUP BY
    stations.name
ORDER BY
    count100, count200 DESC

Your second approach does not work, because the grouping needs to be on the same level as the count().

Make sure to have indexes at least for the geom columns for good performance.

  • Thanks for your answer. ~~I run into an error (missing FROM clause for table "streets") (line4) when running your script.~~ (Nevermind, you are just missing an 'r' (and the ';'). So if I understand correctly you first filter by (DWITHIN 200) when JOINing the two tables, and consider it as the normal result (line4), then run another (DWITHIN 100) on this previous filter? – K_Hart Sep 20 '17 at 15:31
  • There was a typo. An r missing – pLumo Sep 20 '17 at 15:35
  • You can use the 9.6 filter syntax too.. COUNT(DISTINCT streets.name) FILTER (WHERE ST_DWITHIN(stations.geom, streets.geom, 100)). – Evan Carroll Sep 20 '17 at 17:37

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.