A quick and dirty summary of GNAF address counts by street name w.r.t. distance

addresses_by_street_name.sql

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;