Skip to content

Instantly share code, notes, and snippets.

@kgjenkins

kgjenkins/readme.md

Last active July 19, 2016 21:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kgjenkins/7b9554a9d36f686e756dce27d4a4104c to your computer and use it in GitHub Desktop.
Save kgjenkins/7b9554a9d36f686e756dce27d4a4104c to your computer and use it in GitHub Desktop.
Download ZIP
Calculate what percent of each county is within two miles of a site
Raw
readme.md

Method using Carto SQL

WITH buf AS (
  -- 2mi (3218.69m) point buffers
  SELECT
  ST_Collect(ST_Buffer(the_geom::geography, 3218.69)::geometry) AS the_geom
  FROM sites
)
SELECT
c.*,
ST_Area(ST_Intersection(c.the_geom, buf.the_geom)::geography, true) /
  ST_Area(c.the_geom::geography, true) AS pctwithin
FROM counties AS c, buf

How it works

First, it calculates the 2-mile buffers for all the sites, and collects them all into a single multi-polygon called buf.

Then, for each county, it calculates the area of intersection with the buffers and divides it by the area of the whole county.

It returns the original county table with the addition of a single column called pctwithin (which will be between 0.0 and 1.0, but you could multiply by 100 if you prefer).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment