Created
December 21, 2017 03:56
-
-
Save minus34/936c6368e2e7f82da6cb576239a64ea6 to your computer and use it in GitHub Desktop.
A quick and dirty summary of GNAF address counts by street name w.r.t. distance
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH streets AS ( | |
SELECT upper(street_name) as streetname, SUM(ST_Length(ST_Transform(geom, 3577)))/1000.0::integer AS lenkm | |
FROM routing.streets | |
WHERE array_length(string_to_array(street_name, ' '), 1) = 2 | |
AND upper(street_name) NOT LIKE'% HWY' | |
GROUP BY street_name | |
), sts AS ( | |
SELECT (string_to_array(streetname, ' '))[1] AS streetname, SUM(lenkm) AS lenkm | |
FROM streets | |
GROUP BY (string_to_array(streetname, ' '))[1] | |
), adrs AS ( | |
SELECT streetname, Count(*) AS cnt | |
FROM gnaf.addresses | |
GROUP BY streetname | |
), res AS ( | |
SELECT adrs.streetname, adrs.cnt, sts.lenkm, adrs.cnt::float / sts.lenkm AS adr_km | |
FROM adrs | |
INNER JOIN sts ON adrs.streetname = sts.streetname | |
) | |
SELECT * FROM res ORDER BY lenkm DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment