CodeForPhilly
Add what districts they touch
Which council/state rep districts (and who the rep is) and what percentage of the ward or district it makes up
From @andrewbt:
OK, so here's a council district:
https://team.cartodb.com/u/athompson/tables/athompson.council_district/public/map
Here's a ward leader that overlaps that district but not entirely:
https://team.cartodb.com/u/athompson/tables/athompson.ward_leader/public/map
Run this SQL on the ward_leader table:
SELECT ward_leader.cartodb_id, st_intersection(ward_leader.the_geom_webmercator, council_district.the_geom_webmercator) as the_geom_webmercator FROM ward_leader, council_district WHERE st_intersects(ward_leader.the_geom, council_district.the_geom)
And you get this result:
https://team.cartodb.com/u/athompson/tables/athompson.council_district_owned_by_ward_leader/public/map
You can use PostGIS to calculate the area of the result, the area of the council district, and divide the two:
http://docs.cartodb.com/tips-and-tricks.html#calculate-area-of-a-polygon
We only have to do this once, as it only changes every 10 yrs with redistricting
Thought about this some more and it's really not about surface area; it's about the number of voters. Given that divisions are roughly based on the number of voters within them (I believe), we could base this on the number of divisions within each district. Probably changes the math a bit, but worth it I think.
Yeah, I mean, don't divisions nest evenly into council districts? Then it's pretty easy.
If divisions, as the smallest political geography in the city, don't nest evenly with every other political geography , that makes me want to amend the Home Rule Charter or something.
On Sun, Apr 5, 2015 at 11:13 PM, Tim Wisniewski [email protected] wrote:
Thought about this some more and it's really not about surface area; it's about the number of voters. Given that divisions are roughly based on the number of voters within them (I believe), we could base this on the number of divisions within each district. Probably changes the math a bit, but worth it I think.
— Reply to this email directly or view it on GitHub https://github.com/timwis/philly-ward-leaders/issues/32#issuecomment-89901011 .
Andrew Thompson, Sales Engineer
CartoDB | 143 Roebling St, 2A, New York, NY 11211 [email protected] | T +1 508.333.7265 <+15083337265> Web cartodb.com | Twitter @cartodb http://twitter.com/cartodb Map, analyze and build applications with your data
Tim, I just committed two CSV's to the project. Let me know if that is what you are looking for or if I should make changes.
Awesome, thanks Dan!! I'll take a dive in at Code for Philly this week and get them on the app. Great work. link for quick access
No problem. Glad to help out! I just added shapefiles and other files I used to prepare the final tables. I wasn't sure what format you preferred so the final tables are sort of like samples. I don't think I will be able to make the next session, but let me know how you prefer the tables and I am happy to adjust. Also, let me know if you want me to join the final Ward table to the original Ward shapefile. I think it might be cool to show the information on a map too. We could probably do it on google maps or cartodb. Have a good Sunday!
On Sat, May 9, 2015 at 2:09 PM, Tim Wisniewski [email protected] wrote:
Awesome, thanks Dan!! I'll take a dive in at Code for Philly this week and get them on the app. Great work. link for quick access https://github.com/Ruprict0/philly-ward-leaders/tree/gh-pages/data
— Reply to this email directly or view it on GitHub https://github.com/timwis/philly-ward-leaders/issues/32#issuecomment-100525717 .