Maps by Decade

maps-by-decade.sql

SELECT decade * 10 AS decade,
  ST_AsGeoJSON(ST_Union(geometry))::json AS geometry,
  COUNT(*) AS count
FROM (
  SELECT
    geometry, ((
      date_part('year', lower(validsince)) +
      date_part('year', upper(validuntil))
    ) / 2)::int / 10 AS decade
  FROM
    pits
  WHERE
    dataset = 'mapwarper' AND
    ST_Contains(ST_Envelope(
      ST_SetSRID(ST_MakeLine(
        ST_MakePoint(-74.347229, 40.436495), ST_MakePoint(-73.424377, 41.155910)), 4326)
    ), geometry) AND
    (data->>'masked')::boolean = true AND
    daterange('1800-01-01', '1949-12-31') @> validsince AND
    daterange('1800-01-01', '1949-12-31') @> validuntil AND
    ST_Area(Geography(geometry)) < 1000000 -- 1 square km (250 acres)
) d
GROUP BY decade
ORDER BY decade