Skip to content

Instantly share code, notes, and snippets.

@JoeGermuska

JoeGermuska/comm_zip_overlap.csv

Created June 6, 2014 15:30
Show Gist options
  • Save JoeGermuska/d2cfe9764620e5c18180 to your computer and use it in GitHub Desktop.
Save JoeGermuska/d2cfe9764620e5c18180 to your computer and use it in GitHub Desktop.
Download ZIP
ZIP Code to Chicago Community area coverage map.
Raw
comm_zip_overlap.csv
zip area_number pct_covered
46320 52 5.65666700010227e-05
46320 55 0.000829988063627611
60007 76 0.0374161100203793
60018 76 0.537940977303293
60068 10 0.000114681511398346
60068 76 6.56825603108841e-06
60068 9 0.00048889250274846
60076 2 0.0021111871228966
60077 12 7.94752653466832e-07
60106 76 0.160872114085127
60131 76 0.0223626913654986
60202 1 0.000262468833493582
60202 2 1.23948113854653e-05
60171 17 4.28273337183183e-07
60171 76 1.00518845720922e-06
60302 25 9.07655597302302e-05
60419 54 6.67218416587663e-06
60176 76 0.00218220488980659
60409 54 3.57735829563157e-06
60409 55 0.000102226348648644
60304 25 3.3061876800071e-06
60305 25 1.09149206744803e-08
60402 56 1.26365016640066e-08
60406 75 3.39035058569841e-05
60453 70 7.66853164497759e-11
60453 74 5.977727895674e-05
60456 70 0.000294010589824868
60459 65 1.28860089539418e-06
60459 70 3.73635732669918e-06
60501 56 3.45148768148567e-06
60501 64 4.79741649097326e-06
60603 32 0.0871863951656263
60601 8 0.00163334611980303
60601 32 0.229741628322015
60602 32 0.0523732193593238
60604 32 0.0556981360398871
60605 28 7.41014783349671e-05
60605 33 0.359307541986951
60605 32 0.361631153941692
60619 42 2.23438733122108e-07
60619 43 0.0991743475627284
60619 44 0.743740047690803
60619 45 0.554619886326735
60619 47 0.999904584528677
60619 48 0.142699306494254
60619 49 0.0822448072430348
60619 50 0.00597216863124667
60619 51 7.76633326055077e-06
60619 69 0.443318072861312
60620 44 0.256259952309197
60620 49 0.0742406883292362
60620 67 2.50886764837242e-06
60620 68 0.0129823775069069
60620 69 0.11029664044414
60620 70 0.106390222526059
60620 71 0.996098019219869
60620 72 0.0313516584288566
60620 73 0.366095497548414
60621 37 5.28827673470544e-06
60621 40 0.0970837521123271
60621 61 2.31464021077236e-06
60621 67 1.02347312326071e-06
60621 68 0.969158389105229
60621 69 0.16860870866485
60621 71 0.00138555259596703
60622 22 0.0346578109535347
60622 23 0.0225390307843245
60622 24 0.494619197175481
60622 28 2.84131013717761e-06
60623 25 1.77913532956843e-09
60623 29 0.732211114503884
60623 30 0.671724294270268
60623 57 0.00242886180053667
60623 58 0.00448795632696761
60606 24 4.07154978356097e-08
60606 28 0.0182809713311311
60606 8 0.000653837443310406
60606 32 0.0901100040577472
60607 24 2.8718485197385e-05
60607 28 0.3921431224349
60607 31 1.34638957892181e-07
60607 33 0.000508909515996484
60607 32 0.0540053802535713
60608 28 0.19759522524616
60608 29 0.0834472251299899
60608 30 0.182642443266763
60608 31 0.919154640344842
60608 34 0.0002400013934907
60608 59 0.420771274371562
60608 58 0.00145447400569054
60608 60 0.482786767239459
60609 35 0.0496573550200197
60609 37 0.999994711723274
60609 38 0.150504014410254
60609 40 0.0630813545400755
60609 34 0.142330224440587
60609 59 0.569885550979171
60609 58 0.021781309341607
60609 60 0.270040786178338
60609 61 0.999994800366566
60609 63 0.100845736380073
60609 67 1.6474026934273e-05
60609 68 1.22178775389119e-05
60624 23 0.241836490697985
60624 25 0.00202187046466474
60624 26 0.99999996826943
60624 27 0.481065021853534
60624 29 0.133883914018606
60625 4 0.849245975443649
60625 13 0.203254828284993
60625 14 0.636499332247899
60625 16 1.75820209251775e-06
60625 2 2.47185237149129e-06
60625 5 1.0257046933554e-05
60625 77 1.13481617021797e-06
60610 24 0.00364839264013869
60610 8 0.392738849129087
60610 7 0.02433469744532
60634 15 0.269724292437788
60634 17 0.970978941066606
60634 18 0.384917205275251
60634 19 0.155573889026272
60634 76 0.0976451263861004
60611 8 0.296976613816605
60611 32 0.0643937800262641
60612 23 0.0924868901210754
60612 24 0.0852455867537728
60612 27 0.518934978146463
60612 28 0.32765370424414
60612 29 0.0492649353064735
60803 74 0.000129309323455286
60803 75 9.05105788275398e-05
60804 25 0.00226216862573131
60804 29 0.00119280947467733
60804 30 0.000652427856784214
60804 56 0.0258698653029766
60804 57 1.37500613874779e-06
60613 4 5.66624510184673e-07
60613 3 0.385101419286446
60613 6 0.334790001678274
60613 5 0.118824380281198
60614 22 0.0540050173159107
60614 24 0.000490973406787478
60614 8 0.000323221150832346
60614 6 0.0176777373669061
60614 5 0.00475686372116142
60614 7 0.927960953680218
60615 38 0.282287744415771
60615 39 0.5827939870457
60615 40 0.251290660730028
60615 41 0.456812904422882
60616 35 0.749081462264658
60616 36 2.32889534265586e-05
60616 28 0.0119602601949103
60616 31 0.0808452250161993
60616 33 0.640183548497056
60616 34 0.85742977416592
60616 32 0.000598092315188869
60616 60 0.247172446582203
60617 43 3.23804202456751e-06
60617 45 0.445380093783892
60617 46 0.975943320974995
60617 47 8.12463566576716e-05
60617 48 0.857300693505744
60617 50 0.00451835888605134
60617 51 0.496585380527487
60617 52 0.999908953584648
60617 55 0.101091660820028
60618 4 0.00355933715245647
60618 14 0.0256730647789727
60618 16 0.512303044635564
60618 21 0.882304717656484
60618 22 0.00711553789905941
60618 5 0.772264437971368
60618 7 2.2016025420182e-06
60626 1 0.90436338970635
60626 2 0.00999604224656989
60626 77 2.51233100949724e-06
60628 47 1.41691146601971e-05
60628 49 0.8435122579539
60628 50 0.978297593607088
60628 51 0.000156725405331921
60628 53 0.700612698291389
60628 54 0.491562203531576
60628 73 0.197343755809095
60628 75 0.00757865126817522
60629 56 0.013049609595174
60629 62 0.507211611271984
60629 63 0.316870177423848
60629 64 0.0910332685172641
60629 65 0.738658531215148
60629 66 0.839792143209576
60629 70 0.0188061965653796
60630 11 0.752020674175095
60630 12 0.129322912187806
60630 13 0.232795405835269
60630 14 0.335622059545304
60630 15 0.260312029257025
60630 16 0.100673106999789
60630 10 0.00643651188634215
60631 10 0.481494739979597
60631 76 0.0295705579755716
60631 9 0.999488052573781
60632 30 0.144980834606188
60632 59 0.00934317464926556
60632 56 0.0978503341297628
60632 57 0.997569763193323
60632 58 0.972276260325737
60632 61 1.44264846674504e-06
60632 62 0.492788388728013
60632 63 0.525523512553204
60633 50 0.0112118788756171
60633 51 0.503250127733928
60633 52 3.44797453489148e-05
60633 54 0.00474049598659807
60633 55 0.897976124767684
60636 61 1.44234475837584e-06
60636 63 0.0567605736428743
60636 66 0.160147290423427
60636 67 0.999979993632293
60636 68 0.0178470155103234
60636 70 0.000805416953408396
60636 71 0.00251642818416576
60637 40 0.58854423261757
60637 41 0.543187095577112
60637 42 0.829233136663544
60637 43 0.0499218669556476
60637 69 0.277776578029703
60638 56 0.863226726847906
60638 64 0.908961378965845
60638 65 0.000770192464561631
60639 18 2.98901256586093e-05
60639 19 0.65049457858091
60639 20 0.776953989710729
60639 21 0.00849102851281873
60639 22 0.0235850202008612
60639 23 0.0618356600083832
60639 25 0.153784892438033
60640 4 0.138935743431918
60640 3 0.614565224154989
60640 6 3.95570066671298e-06
60640 5 3.87755663165366e-06
60640 77 0.356064033328082
60641 14 0.00220310230062361
60641 15 0.469785368017933
60641 16 0.387022090162559
60641 19 0.141819416293531
60641 20 0.179877764402101
60641 21 0.0863058304247494
60641 22 8.24307403582574e-07
60642 22 0.0139212737443002
60642 24 0.277739323474894
60642 28 8.52420408224718e-05
60642 8 0.140517515020561
60642 7 0.00796587615063456
60643 49 2.24647382821899e-06
60643 53 0.298071609295543
60643 72 0.771670114314192
60643 73 0.436560746642477
60643 75 0.78282371261156
60644 25 0.491430651235611
60644 26 3.17305674989949e-08
60644 29 1.56637195144396e-09
60645 1 0.0813179755018496
60645 2 0.589831357756722
60646 11 0.247977054523097
60646 12 0.850088985312069
60646 13 0.270868950429081
60646 14 2.44112719973965e-06
60646 10 0.132909868111201
60647 20 0.0431682458871719
60647 21 0.0228984234059468
60647 22 0.866714515578936
60647 23 0.0941655677115835
60647 24 0.103743737816555
60647 5 0.000202486271980026
60647 7 0.0121881927790617
60649 42 0.170766639897722
60649 43 0.850900547439608
60649 45 1.98893744467715e-08
60649 46 0.0240566790249986
60651 23 0.48713636067665
60651 24 0.00329308548208646
60651 25 0.241309445155637
60652 64 5.55100402206163e-07
60652 65 0.260569987719395
60652 66 6.05663669971965e-05
60652 70 0.873623284468597
60653 35 0.201261182715321
60653 36 0.999976711046579
60653 38 0.567208241173973
60653 39 0.417206012954301
60654 24 0.0300052323639278
60654 28 0.00083729130426795
60654 8 0.167156617319806
60654 32 0.00366415000307331
60655 72 0.19696169484786
60655 74 0.999702987686195
60655 75 0.209472575129618
60656 11 1.56417183829541e-06
60656 10 0.378491281313652
60656 76 0.111103047647326
60657 6 0.647528305254153
60657 5 0.103937697150719
60657 7 0.0275480783422169
60659 4 0.00633943895359767
60659 12 0.00262719254668433
60659 13 0.293072174705266
60659 2 0.358196984138736
60660 4 0.00191893839387424
60660 1 0.0140561659583088
60660 2 0.0398494015096326
60660 77 0.643932319524739
60661 24 0.00118571168566694
60661 28 0.0513672404151926
60706 11 7.07129971086265e-07
60706 15 0.000178310287250858
60706 17 2.18398297003699e-05
60706 10 0.000437086457074121
60706 76 0.000899596882409158
60707 17 0.0289987908303559
60707 18 0.615052904599083
60707 19 0.0521121160992863
60707 25 0.109096887638861
60712 12 1.87772952887711e-05
60712 13 8.64074538476339e-06
60712 2 1.60561684713335e-07
60714 12 0.0179413379055079
60714 10 0.00011583074074015
60714 9 2.30549234942662e-05
60805 70 7.71324627178779e-05
60805 72 1.65324091056787e-05
60805 74 0.000107925711372261
60827 53 0.00131569241307172
60827 54 0.50368705093936
60827 75 6.46905956371951e-07
Raw
compute_overlap.sql
drop table comm_zip_geocontainment;
create table comm_zip_geocontainment as
select zip.name as zip, ca.area_numbe as area_number, ST_Area(ST_Intersection(zip.geom,ca.geom))/ST_Area(ca.geom) as pct_covered
from chizips zip, commareas ca
WHERE ST_Area(ST_Intersection(zip.geom,ca.geom))/ST_Area(zip.geom) > 0;
copy comm_zip_geocontainment to '/Users/germuska/Downloads/chizips/comm_zip_overlap.csv' csv header;
Raw
setup_postgis.sh
# had a comm areas shape file lying around.
shp2pgsql -s 4326 -d -I ~/trib/GIS/CommAreas_4326/CommAreas.shp commareas | psql -d germuska
# downloaded zips in Chicago from Census Reporter
shp2pgsql -s 4326 -d -I acs2012_5yr_B01001_86000US46327/acs2012_5yr_B01001_86000US46327.shp chizips | psql -d germuska
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment