Skip to content

Instantly share code, notes, and snippets.

@stevevance

stevevance/query_v2.sql

Last active August 17, 2024 22:46
Show Gist options
  • Save stevevance/60e414f57183647723432dc2df7a3f4d to your computer and use it in GitHub Desktop.
Save stevevance/60e414f57183647723432dc2df7a3f4d to your computer and use it in GitHub Desktop.
Download ZIP
areas of selected wards, areas of the wards' overlapping ZIP codes, and areas of the overlap of the wards and IZP codes
Raw
query_v2.sql
with zips as (
select
vp1.zcta as name,
-- am.name as park,
st_difference(first(vp1.geom), st_union(am.geom)) as geom,
st_area(st_difference(first(vp1.geom), st_union(am.geom))) area_zip_without_parks,
st_area(first(vp1.geom)) area_zip
from b_zcta_il_in_2020 as vp1, b_wards_2022 AS vp2, view_amenities_combined_2024_08_14 as am
where st_intersects(vp1.geom, vp2.geom)
and am.type = 'park'
and st_intersects(vp1.geom, am.geom)
and vp2.slug in (
'ward-33',
'ward-35',
'ward-39',
'ward-40-no-rosehill-cemetery',
'ward-46',
'ward-47',
'ward-48',
'ward-49',
'ward-50'
)
and vp1.slug in (
'zip-60076',
'zip-60202',
'zip-60613',
'zip-60618',
'zip-60625-no-rosehill-cemetery',
'zip-60626',
'zip-60630',
'zip-60631',
'zip-60639',
'zip-60640',
'zip-60641',
'zip-60645',
'zip-60646',
'zip-60647',
'zip-60657',
'zip-60659',
'zip-60660',
'zip-60712'
)
group by vp1.zcta
--, am.name
), wards as (
select
vp.ward as name,
st_difference(first(vp.geom), st_union(am.geom)) as geom,
st_area(first(vp.geom)) area_ward,
st_area(st_difference(first(vp.geom), st_union(am.geom))) area_ward_without_parks,
vp.ward::int as ward
from b_wards_2022 as vp, view_amenities_combined_2024_08_14 as am
where vp.slug in (
'ward-33',
'ward-35',
'ward-39',
'ward-40-no-rosehill-cemetery',
'ward-46',
'ward-47',
'ward-48',
'ward-49',
'ward-50'
)
and am.type = 'park'
and st_intersects(vp.geom, am.geom)
group by vp.ward
)
--select * from zips where name = '60640';
select
zips.name as zip,
st_area(zips.geom) as area_zip_without_parks,
wards.name as ward,
st_area(wards.geom) as area_ward_without_parks,
st_area(st_intersection(zips.geom, wards.geom)) as area_overlap
from wards, zips;
Raw
results_v2.tsv
zip area_zip ward area_ward area_overlap
60076 136068898.40465313 50 71733737.15008394 2956.9106035611926
60076 136068898.40465313 33 58890845.904669225 0
60076 136068898.40465313 47 83339323.64181162 0
60076 136068898.40465313 48 37709228.04691055 0
60076 136068898.40465313 40 77430865.90808904 0
60076 136068898.40465313 39 177832318.83243406 0
60076 136068898.40465313 35 74650786.51998703 0
60076 136068898.40465313 49 47861250.21266767 0
60076 136068898.40465313 46 38962686.32282952 0
60202 95042435.64550018 50 71733737.15008394 2283.065555886845
60202 95042435.64550018 33 58890845.904669225 0
60202 95042435.64550018 47 83339323.64181162 0
60202 95042435.64550018 48 37709228.04691055 0
60202 95042435.64550018 40 77430865.90808904 0
60202 95042435.64550018 39 177832318.83243406 0
60202 95042435.64550018 35 74650786.51998703 0
60202 95042435.64550018 49 47861250.21266767 4278.239110815975
60202 95042435.64550018 46 38962686.32282952 0
60613 62125052.9406008 50 71733737.15008394 0
60613 62125052.9406008 33 58890845.904669225 0
60613 62125052.9406008 47 83339323.64181162 18095776.670559675
60613 62125052.9406008 48 37709228.04691055 0
60613 62125052.9406008 40 77430865.90808904 0
60613 62125052.9406008 39 177832318.83243406 0
60613 62125052.9406008 35 74650786.51998703 0
60613 62125052.9406008 49 47861250.21266767 0
60613 62125052.9406008 46 38962686.32282952 22939544.59123346
60618 136068770.00166723 50 71733737.15008394 0
60618 136068770.00166723 33 58890845.904669225 32096082.906973027
60618 136068770.00166723 47 83339323.64181162 30594664.504546955
60618 136068770.00166723 48 37709228.04691055 0
60618 136068770.00166723 40 77430865.90808904 127223.48970610081
60618 136068770.00166723 39 177832318.83243406 2324749.086214466
60618 136068770.00166723 35 74650786.51998703 39194598.08952147
60618 136068770.00166723 49 47861250.21266767 0
60618 136068770.00166723 46 38962686.32282952 0
60625 (no Rosehill Cemetery) 88917955.38910288 50 71733737.15008394 0
60625 (no Rosehill Cemetery) 88917955.38910288 33 58890845.904669225 26683599.846263964
60625 (no Rosehill Cemetery) 88917955.38910288 47 83339323.64181162 12431076.212905152
60625 (no Rosehill Cemetery) 88917955.38910288 48 37709228.04691055 0
60625 (no Rosehill Cemetery) 88917955.38910288 40 77430865.90808904 30849572.22082536
60625 (no Rosehill Cemetery) 88917955.38910288 39 177832318.83243406 18953707.109108485
60625 (no Rosehill Cemetery) 88917955.38910288 35 74650786.51998703 0
60625 (no Rosehill Cemetery) 88917955.38910288 49 47861250.21266767 0
60625 (no Rosehill Cemetery) 88917955.38910288 46 38962686.32282952 0
60626 50961879.27922321 50 71733737.15008394 419775.1529035757
60626 50961879.27922321 33 58890845.904669225 0
60626 50961879.27922321 47 83339323.64181162 0
60626 50961879.27922321 48 37709228.04691055 1427.6135053803753
60626 50961879.27922321 40 77430865.90808904 3853575.1037795013
60626 50961879.27922321 39 177832318.83243406 0
60626 50961879.27922321 35 74650786.51998703 0
60626 50961879.27922321 49 47861250.21266767 40411984.05011893
60626 50961879.27922321 46 38962686.32282952 0
60630 130214620.18264508 50 71733737.15008394 0
60630 130214620.18264508 33 58890845.904669225 110591.79885008193
60630 130214620.18264508 47 83339323.64181162 0
60630 130214620.18264508 48 37709228.04691055 0
60630 130214620.18264508 40 77430865.90808904 0
60630 130214620.18264508 39 177832318.83243406 52551710.75986649
60630 130214620.18264508 35 74650786.51998703 0
60630 130214620.18264508 49 47861250.21266767 0
60630 130214620.18264508 46 38962686.32282952 0
60631 102516004.83023559 50 71733737.15008394 0
60631 102516004.83023559 33 58890845.904669225 0
60631 102516004.83023559 47 83339323.64181162 0
60631 102516004.83023559 48 37709228.04691055 0
60631 102516004.83023559 40 77430865.90808904 0
60631 102516004.83023559 39 177832318.83243406 733.9612747108959
60631 102516004.83023559 35 74650786.51998703 0
60631 102516004.83023559 49 47861250.21266767 0
60631 102516004.83023559 46 38962686.32282952 0
60639 132416407.8886706 50 71733737.15008394 0
60639 132416407.8886706 33 58890845.904669225 0
60639 132416407.8886706 47 83339323.64181162 0
60639 132416407.8886706 48 37709228.04691055 0
60639 132416407.8886706 40 77430865.90808904 0
60639 132416407.8886706 39 177832318.83243406 0
60639 132416407.8886706 35 74650786.51998703 6117583.552072361
60639 132416407.8886706 49 47861250.21266767 0
60639 132416407.8886706 46 38962686.32282952 0
60640 65342533.00877356 50 71733737.15008394 0
60640 65342533.00877356 33 58890845.904669225 0
60640 65342533.00877356 47 83339323.64181162 18527158.443389118
60640 65342533.00877356 48 37709228.04691055 14789136.999478465
60640 65342533.00877356 40 77430865.90808904 10707460.65240056
60640 65342533.00877356 39 177832318.83243406 0
60640 65342533.00877356 35 74650786.51998703 0
60640 65342533.00877356 49 47861250.21266767 0
60640 65342533.00877356 46 38962686.32282952 14428640.619754475
60641 109363661.1381649 50 71733737.15008394 0
60641 109363661.1381649 33 58890845.904669225 571.3525822020875
60641 109363661.1381649 47 83339323.64181162 0
60641 109363661.1381649 48 37709228.04691055 0
60641 109363661.1381649 40 77430865.90808904 0
60641 109363661.1381649 39 177832318.83243406 7611194.26221967
60641 109363661.1381649 35 74650786.51998703 75.305860564231
60641 109363661.1381649 49 47861250.21266767 0
60641 109363661.1381649 46 38962686.32282952 0
60645 56635927.419072255 50 71733737.15008394 43724854.45526852
60645 56635927.419072255 33 58890845.904669225 0
60645 56635927.419072255 47 83339323.64181162 0
60645 56635927.419072255 48 37709228.04691055 0
60645 56635927.419072255 40 77430865.90808904 5719452.755169839
60645 56635927.419072255 39 177832318.83243406 0
60645 56635927.419072255 35 74650786.51998703 0
60645 56635927.419072255 49 47861250.21266767 7191620.208633913
60645 56635927.419072255 46 38962686.32282952 0
60646 121296941.30330862 50 71733737.15008394 0
60646 121296941.30330862 33 58890845.904669225 0
60646 121296941.30330862 47 83339323.64181162 0
60646 121296941.30330862 48 37709228.04691055 0
60646 121296941.30330862 40 77430865.90808904 0
60646 121296941.30330862 39 177832318.83243406 75883943.79812898
60646 121296941.30330862 35 74650786.51998703 0
60646 121296941.30330862 49 47861250.21266767 0
60646 121296941.30330862 46 38962686.32282952 0
60647 109904635.56523271 50 71733737.15008394 0
60647 109904635.56523271 33 58890845.904669225 0
60647 109904635.56523271 47 83339323.64181162 0
60647 109904635.56523271 48 37709228.04691055 0
60647 109904635.56523271 40 77430865.90808904 0
60647 109904635.56523271 39 177832318.83243406 0
60647 109904635.56523271 35 74650786.51998703 29338529.57253268
60647 109904635.56523271 49 47861250.21266767 0
60647 109904635.56523271 46 38962686.32282952 0
60657 67991771.25452046 50 71733737.15008394 0
60657 67991771.25452046 33 58890845.904669225 0
60657 67991771.25452046 47 83339323.64181162 3690647.8104106826
60657 67991771.25452046 48 37709228.04691055 0
60657 67991771.25452046 40 77430865.90808904 0
60657 67991771.25452046 39 177832318.83243406 0
60657 67991771.25452046 35 74650786.51998703 0
60657 67991771.25452046 49 47861250.21266767 0
60657 67991771.25452046 46 38962686.32282952 1594501.1118416528
60659 62997375.83848284 50 71733737.15008394 27503473.56100217
60659 62997375.83848284 33 58890845.904669225 0
60659 62997375.83848284 47 83339323.64181162 0
60659 62997375.83848284 48 37709228.04691055 0
60659 62997375.83848284 40 77430865.90808904 14990992.119408859
60659 62997375.83848284 39 177832318.83243406 20502900.049275056
60659 62997375.83848284 35 74650786.51998703 0
60659 62997375.83848284 49 47861250.21266767 0
60659 62997375.83848284 46 38962686.32282952 0
60660 40338630.481297255 50 71733737.15008394 73083.66606382492
60660 40338630.481297255 33 58890845.904669225 0
60660 40338630.481297255 47 83339323.64181162 0
60660 40338630.481297255 48 37709228.04691055 22918663.43392673
60660 40338630.481297255 40 77430865.90808904 11127714.440452876
60660 40338630.481297255 39 177832318.83243406 0
60660 40338630.481297255 35 74650786.51998703 0
60660 40338630.481297255 49 47861250.21266767 253367.7148039599
60660 40338630.481297255 46 38962686.32282952 0
60712 70916269.2967124 50 71733737.15008394 7310.338686407026
60712 70916269.2967124 33 58890845.904669225 0
60712 70916269.2967124 47 83339323.64181162 0
60712 70916269.2967124 48 37709228.04691055 0
60712 70916269.2967124 40 77430865.90808904 0
60712 70916269.2967124 39 177832318.83243406 3379.8063462572322
60712 70916269.2967124 35 74650786.51998703 0
60712 70916269.2967124 49 47861250.21266767 0
60712 70916269.2967124 46 38962686.32282952 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment