Skip to content

Instantly share code, notes, and snippets.

@andy-esch

andy-esch/README.md

Created May 27, 2016 16:37
Show Gist options
  • Save andy-esch/f877734e41193b6a77e600fa7f129578 to your computer and use it in GitHub Desktop.
Save andy-esch/f877734e41193b6a77e600fa7f129578 to your computer and use it in GitHub Desktop.
Download ZIP
SQL used in blog post on congressional districts
Raw
README.md

Get district boundaries

INSERT INTO us_congressional_districts(the_geom, geoid)
SELECT the_geom, geom_refs
  FROM cdb_observatory.OBS_GetBoundariesByGeometry(
         ST_MakeEnvelope(-179.5, 13.4, -42.4, 74.4, 4326),
         'us.census.tiger.congressional_district')

Top Ten Smallest Congressional Districts

SELECT the_geom, the_geom_webmercator,
       ST_Area(the_geom::geography) / (1609 * 1609) As area_sq_miles,
       rank() OVER (ORDER BY ST_Area(the_geom::geography) ASC)
FROM
  us_congressional_districts
ORDER BY 3 ASC
LIMIT 10

Top Ten Largest Congressional Districts

SELECT the_geom, the_geom_webmercator,
       ST_Area(the_geom::geography) / (1609 * 1609) As area_sq_miles,
       rank() OVER (ORDER BY ST_Area(the_geom::geography) DESC)
FROM
  us_congressional_districts
ORDER BY 3 DESC
LIMIT 10
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment