Skip to content

Instantly share code, notes, and snippets.

@stevevance

stevevance/query.sql

Last active August 17, 2024 21:15
Show Gist options
  • Save stevevance/7fa3bbf6ac95333a9d9d604324bae023 to your computer and use it in GitHub Desktop.
Save stevevance/7fa3bbf6ac95333a9d9d604324bae023 to your computer and use it in GitHub Desktop.
Download ZIP
Area of select Chicago wards (with and without parks) in square feet. Park data comes from OpenStreetMap, June 2024.
Raw
query.sql
/* the main query */
select
st_area(first(vp.geom)) area_ward,
st_area(st_difference(first(vp.geom), st_union(am.geom))) area_ward_without_parks,
vp.metadata::int as ward
from view_places as vp, view_amenities_combined_2024_06_19b as am
where vp.type = 'ward'
and vp.metadata in (
'33',
'35',
'39',
'40',
'46',
'47',
'48',
'49',
'50'
)
and am.type = 'park'
and st_intersects(vp.geom, am.geom)
group by vp.metadata
order by vp.metadata::int;
/* show the park names and calculate overlapping area between a park and the ward for a specific ward for testing purposes */
select
am.name,
st_area(st_intersection(vp.geom, am.geom))
from view_places as vp, view_amenities_combined_2024_06_19b as am
where vp.type = 'ward'
and vp.metadata = '40'
and am.type = 'park'
and st_intersects(vp.geom, am.geom)
Raw
wards_parks.tsv
area_ward area_ward_without_parks ward
62147720.366452634 58890845.904669225 33
76102719.34435719 74650786.51998703 35
181855374.8095112 177832318.83243406 39
96674592.44028759 90441649.92891149 40
55467424.036558464 54638269.64261395 46
84841976.9379378 83339323.64181162 47
45086058.769495696 43830625.006226875 48
51041678.078528486 47861250.21266767 49
74167195.64719687 71733737.15008394 50
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment