puma_1m_density.sql

SELECT d.state, d.puma, 
       pm.pop pop, ST_Area(pm.geom) A,
       d.hs, d.ba, ba_pop,
       d.cum_pop, d.cum_area,
       d.cum_hs, d.cum_ba,
       d.distance, pl.geometry
FROM PlotPuma() pl
JOIN puma pm ON -- plotting version, distorted AK
  pl.state = pm.state AND pl.puma = pm.puma
JOIN ( -- for calculations.
  SELECT
    DISTINCT ON (state, puma)
    state, puma, cum_pop, cum_area, distance,
    hs, ba, cum_hs, cum_ba, ba_pop
  FROM
  (
    SELECT
      p1.state, p1.puma, 
      1.*p1.hs / p1.adults hs, 1.*p1.ba / p1.adults ba, p1.ba ba_pop,
      ST_Distance(p1.centroid, p2.centroid) AS distance,
      SUM(p2.pop) OVER (PARTITION BY p1.state, p1.puma
                        ORDER BY ST_Distance(p1.centroid, p2.centroid)) AS cum_pop,
      SUM(ST_Area(p2.geom)) OVER (PARTITION BY p1.state, p1.puma
                                  ORDER BY ST_Distance(p1.centroid, p2.centroid)) AS cum_area,
      (1.*SUM(p2.hs)     OVER (PARTITION BY p1.state, p1.puma
                               ORDER BY ST_Distance(p1.centroid, p2.centroid))) /
         (SUM(p2.adults) OVER (PARTITION BY p1.state, p1.puma
                               ORDER BY ST_Distance(p1.centroid, p2.centroid))) AS cum_hs,
      (1.*SUM(p2.ba)     OVER (PARTITION BY p1.state, p1.puma
                               ORDER BY ST_Distance(p1.centroid, p2.centroid))) /
         (SUM(p2.adults) OVER (PARTITION BY p1.state, p1.puma
                               ORDER BY ST_Distance(p1.centroid, p2.centroid))) AS cum_ba
    FROM
      puma p1, puma p2
    ORDER BY p1.state, puma, distance
  ) AS d
  WHERE cum_pop >= 1000000 OR 
    -- Don't come all the way back 
    -- to the continental US.
    (state = 2 AND cum_pop >= 700000) 
) AS d ON
  d.state = pl.state AND d.puma = pl.puma;