Closed ruckeralex closed 5 years ago
aaronhans
commented
5 years ago Don had a good suggestion last night, use the already associated zip code to lookup the associated legislative info, there are some available ways to do this without doing the GIS point in shape boundary calculation
ruckeralex
commented
5 years ago Dear lord - Brilliant! I'll verify with CWC which of these are the legislative classifications they want; then I can use Excel to create the association of water system : district. https://sdmg.senate.ca.gov/zipcodedirectory
On Wed, May 29, 2019 at 9:12 AM Aaron Hans notifications@github.com wrote:
Don had a good suggestion last night, use the already associated zip code to lookup the associated legislative info, there are some available ways to do this without doing the GIS point in shape boundary calculation
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ALXRBD5YKFIT57TWQCQO7EDPX2TNTA5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWP263Q#issuecomment-497004398, or mute the thread https://github.com/notifications/unsubscribe-auth/ALXRBD7TCINOJPAUBIC3ZUDPX2TNTANCNFSM4HISI4BQ .
ruckeralex
commented
5 years ago Just heard back from CWC. Apparently many legislative districts are split across the same zip codes, which is why they want to use GIS.
I've reached out to a former classmate within the UC Berkeley student community to see if they might have a GIS expert who can help.
On Wed, May 29, 2019 at 9:56 AM Rucker Alex ruckeralex@gmail.com wrote:
Dear lord - Brilliant! I'll verify with CWC which of these are the legislative classifications they want; then I can use Excel to create the association of water system : district. https://sdmg.senate.ca.gov/zipcodedirectory
On Wed, May 29, 2019 at 9:12 AM Aaron Hans notifications@github.com wrote:
Don had a good suggestion last night, use the already associated zip code to lookup the associated legislative info, there are some available ways to do this without doing the GIS point in shape boundary calculation
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ALXRBD5YKFIT57TWQCQO7EDPX2TNTA5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODWP263Q#issuecomment-497004398, or mute the thread https://github.com/notifications/unsubscribe-auth/ALXRBD7TCINOJPAUBIC3ZUDPX2TNTANCNFSM4HISI4BQ .
ruckeralex
commented
5 years ago Sounds like Diana Y is able to perform this GIS task!
The goal is to produce an Excel spreadsheet that provides 1 row per existing water system and 3 related associated columns: for water system, legislative district, and regional water board. NOTE: There should be around 3,297 systems, so there will be approx 3,300 rows on the final Excel sheet.
1) Access a summary of the system name / zip code here: https://www.waterboards.ca.gov/water_issues/programs/hr2w/docs/data/2019-04-03_hr2w_web_data_summary.xls
2) Associate each water system with its legislative district (https://services.gis.ca.gov/arcgis/rest/services/Boundaries/CA_Legislative_Districts/MapServer)
3) Associate each water system with its regional board (https://www.waterboards.ca.gov/waterboards_map.html)
For now, we can define GIS points for water systems via centroid of associated zip code. You can access the GIS map shapefile here: https://www.waterboards.ca.gov/water_issues/programs/hr2w/docs/data/ec_summary_feb2019.zip.
dilufapi
commented
5 years ago Here is the link to the map: https://arcg.is/SKLzy (one thing that bothers me is how the assembly name displays. I tried to modify the size of those brown labels, but they seem to be locked by the layer). Please let me know if you have any comments or questions. Have a great weekend.
aaronhans
commented
5 years ago This is fabulous! Thank you @dilufapi!
I see everything we need on the interactive map. Could you help me extract the associations between water systems and legislative and water districts? We will use the association between water system and district to list the systems in tabular views by district the CWC has requested and create sums for stats like District X has Y systems out of compliance affecting Z total people.
ruckeralex
commented
5 years ago Amazing! Thank you Diana!!
Would you be able to take the final step and extract the data to an Excel or .csv file? ( I don’t have GIS software anymore.) Once it’s in Excel Aaron and team can then affiliate that information with the bi-monthly data set the tool uses for reports.
So, final step: Extract data from map to produce an Excel/csv spreadsheet that provides 1 row per existing water system and 2 related associated columns: name of legislative district, and name of regional water board. NOTE: There should be around 3,297 systems, so there will be approx 3,300 rows on the final Excel sheet.
Thank you SO much! This is a key final puzzle piece for the basic version of the tool!
Cheers, Rucker
On Jun 8, 2019, at 6:11 PM, Aaron Hans notifications@github.com wrote:
This is fabulous! Thank you @dilufapi!
I see everything we need on the interactive map. Could you help me extract the associations between water systems and legislative and water districts? We will use the association between water system and district to list the systems in tabular views by district the CWC has requested and create sums for stats like District X has Y systems out of compliance affecting Z total people.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.
dilufapi
commented
5 years ago I opened the map in ArcGIS Desktop, and it looks like the CA_Legislative_District and CA Regional Water Board Offices layers are locked. It does not allow me to edit, extract, or merge the data. Maybe there is a way to get around it, but I do not know it yet. I will keep researching about how to troubleshoot this. I sent some screenshots to Rucker to explain.
ruckeralex
commented
5 years ago Hi Diana,
Sorry, I was offline during my vacation. Thank you for putting together the troubleshooting memo!
This snafu is way above my GIS pay grade but I just emailed a former colleague to see if he can help. I assume you're using the same layers provided by Ariel at SWRCB (just re-forwarded)? We can try asking Ariel and Wendy in case it's a problem with the files themselves.
Cheers Rucker
On Sun, Jun 30, 2019 at 12:37 AM Diana Yitbarek notifications@github.com wrote:
I opened the map in ArcGIS Desktop, and it looks like the CA_Legislative_District and CA Regional Water Board Offices layers are locked. It does not allow me to edit, extract, or merge the data. Maybe there is a way to get around it, but I do not know it yet. I will keep researching about how to troubleshoot this. I sent some screenshots to Rucker to explain.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ALXRBDYLG7IEYXPBE66ZP5LP5BPFHA5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODY4G75A#issuecomment-507015156, or mute the thread https://github.com/notifications/unsubscribe-auth/ALXRBDYYBGRJS2UDQTJH5E3P5BPFHANCNFSM4HISI4BQ .
aaronhans
commented
5 years ago I found a way to retrieve the CA state Senate and Assembly District from the lat,lon: https://openstates.org/find_your_legislator/?lat=37.8056148&lon=-122.2725945
dilufapi
commented
5 years ago Hi Aaron - That sounds great! Does that mean you have all the data you need? ...If not, I participated in the Water Data Science Symposium and Datathon last week and brought up the GIS data layer question to a couple of people. They recommended reaching out directly to the data manager to request the raw data or the unlocked layers. Therefore I just sent an email to the GIS contact (Franklin.Stieringer@waterboards.ca.gov) I got from the Division of Drinking Water Staff. I will let you know if I hear anything.
ruckeralex
commented
5 years ago Ooh! That’s a great update. I believe we don’t yet have what we need via Aaron’s method either and my other reach outs were unable to help.
Cheers, Rucker
On Jul 9, 2019, at 5:23 PM, Diana Yitbarek notifications@github.com wrote:
Hi Aaron - That sounds great! Does that mean you have all the data you need? ...If not, I participated in the Water Data Science Symposium and Datathon last week and brought up the GIS data layer question to a couple of people. They recommended reaching out directly to the data manager to request the raw data or the unlocked layers. Therefore I just sent an email to the GIS contact (Franklin.Stieringer@waterboards.ca.gov) I got from the Division of Drinking Water Staff. I will let you know if I hear anything.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.
aaronhans
commented
5 years ago I have been able to map all the systems to the CA legislators.
I haven't associated them all with their Water District
ruckeralex
commented
5 years ago Nayana - can you please take a stab at outlining the steps needed to associate a legislative district to each of the 3000+ water systems using this file that Aaron sent: Link shapefiles with each latitude-longitude to each water system ID: https://raw.githubusercontent.com/aaronhans/community-water-center/master/data/locations.json
@aaronhans
ruckeralex
commented
5 years ago This problem is driving me nuts. Just tested to see if we can associate most of the water systems to their legislative assembly district using zip code only... and the answer is: we can only do this for a third of the systems (1,010 out of 3,297 systems; of those, just over 10% are actually out-of-compliance). Of the rest, 947 are not associated with legislative district and the remaining have between 2 and 7 (!) districts associated with the zip code. Attaching file-- see "system by assembly district" tab, which contains the 1.010 districts we can associate to water systems. Water systems by CA Assembly district.xlsx
aaronhans
commented
5 years ago I pushed an update last week which includes a new file for each water district with the associated CA House and Assembly District and current legislators: https://github.com/aaronhans/community-water-center/commit/1c279eb9a7eacaa0b98b9b9c7422d83ba77300c7
We'll need to rerun that script after each election when those members change.
I plan to start some analysis this evening to compare districts so we can identify the politicians representing areas with the least and most reported affected populations
The ability to associate a water system id with one of the 24 CA Water Districts is still outstanding.
ruckeralex
commented
5 years ago Hi Aaron-- thanks! Looking forward to seeing you / talking tonight about this... want to make sure I understand what you mean by "CA Water District." (Is that the Regulating Agency?)
nayanab565
commented
5 years ago Hi! Here are a couple of plots that I made using the data! Thanks! https://github.com/nayanab565/CA-Water-Project/blob/master/CAWaterProjectGraphs.ipynb
ruckeralex
commented
5 years ago @mnorelli - thank you so much for your help! Could you associate for each of the ~3,300 water systems the Regional Water Board (#s 1 through 9) that is is associated with?
Some resources:
Map with (locked?) layers: https://arcg.is/SKLzy
Fact sheet of regional boards: https://www.waterboards.ca.gov/publications_forms/publications/factsheets/docs/region_brds.pdf
Data source: https://www.waterboards.ca.gov/water_issues/programs/hr2w/docs/data/2019-04-03_hr2w_web_data_summary.xls
ruckeralex
commented
5 years ago Hi @nayanab565 and @dilufapi -- it turns out that @aaronhans HAS used code to successfully associate legislative districts with each of the 3,300 water systems in California! So, that problem is now solved. Thank you so much, Aaron.
ruckeralex
commented
5 years ago @mnorelli - Here is a link to the layer for the regional water boards-
https://waterboards.maps.arcgis.com/home/item.html?id=ad93520333dc46239ac22a40fe6299dc
mnorelli
commented
5 years ago Complete.
This Excel table: WaterSystemsByRegionalBoard.xlsx shows, for each water system point derived from zip code centroids, the regional water board name it falls in, in three columns: Name like "North Coast" or "Santa Ana" OFFICE usually blank, except where same Name occurs more than once, like "Central Valley" RB_OFF the Region Number, 1-9, and where there is an OFFICE, RB_OFF will be followed with letter(s), which are the abbreviation of the office name, like "6SLT" for Lahontan, South Lake Tahoe office.
Next step: find real water systems polygons and redo this overlay process in GIS. I'll look for that, but forward any leads or results of conversations with Board staff...
mnorelli
commented
5 years ago More! Here are the all the water system points in Excel, with regional water board designations as above, plus CA Assembly and CA Senate district numbers in the rightmost columns: WaterSystemsByRegionalBoardAndLegDistricts2.xlsx
ruckeralex
commented
5 years ago Amazing!!!!!!!
The Community Water Center and State Board will be thrilled to receive this, and I'll mention your name as the volunteer who pulled it together.
I've cleaned up the formatting a bit and would like to add the regional board and leg district sources... can you provide links or name of the source data used?
On Thu, Jul 18, 2019 at 12:03 AM Michael Norelli notifications@github.com wrote:
More! Here are the all the water system points in Excel, with regional water board designations as above, plus CA Assembly and CA Senate district numbers in the rightmost columns: WaterSystemsByRegionalBoardAndLegDistricts.xlsx https://github.com/r-b-g-b/clean-water-tool/files/3405178/WaterSystemsByRegionalBoardAndLegDistricts.xlsx
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ALXRBDZCBPXVQBN76FXPPRDQAAIUXA5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD2HRAWA#issuecomment-512692312, or mute the thread https://github.com/notifications/unsubscribe-auth/ALXRBDZTFPCD2GNX7J7BYCTQAAIUXANCNFSM4HISI4BQ .
mnorelli
commented
5 years ago Regional Water Quality Control Board jurisdictional boundaries (I used your link sent 7/16/19, above) Site: https://waterboards.maps.arcgis.com/home/item.html?id=ad93520333dc46239ac22a40fe6299dc KML geodata files: https://gispublic.waterboards.ca.gov/arcgis/rest/services/Administrative/RB_OfficeAreas/MapServer/generateKml
Legislative Districts Sites: https://wedrawthelines.ca.gov/maps-final-draft-assembly-districts/ https://wedrawthelines.ca.gov/maps-final-draft-senate-districts/ GIS Shapefiles: https://wedrawthelines.ca.gov/wp-content/uploads/sites/64/2011/08/viz_20110728_q2_ad_finaldraft_shp.zip https://wedrawthelines.ca.gov/wp-content/uploads/sites/64/2011/08/viz_20110728_q2_sd_finaldraft_shp.zip
ruckeralex
commented
5 years ago Excellent! Attaching updated file with sources, and sent copy (with credit to you and Aaron) to CWC and Water Board. Thank you!!!
mnorelli
commented
5 years ago Should we open a new Issue to remind us to get actual water system boundaries, instead of the zip code analogs?
On Thu, Jul 18, 2019, 4:29 PM ruckeralex notifications@github.com wrote:
Closed #22 https://github.com/r-b-g-b/clean-water-tool/issues/22.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ABFFW3IXXUF5MHK42GX4SV3QAD4F5A5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOSSWBLRI#event-2494305733, or mute the thread https://github.com/notifications/unsubscribe-auth/ABFFW3MPZJHFGSW2LWJ46I3QAD4F5ANCNFSM4HISI4BQ .
ruckeralex
commented
5 years ago I looked into that last year and apparently it is confidential information (don’t want terrorists targeting the systems?). The water board themselves use zip code centroids to create maps. But if you can find precise boundaries with your awesome sleuthing skills, great!!
Cheers, Rucker
On Jul 18, 2019, at 4:33 PM, Michael Norelli notifications@github.com wrote:
Should we open a new Issue to remind us to get actual water system boundaries, instead of the zip code analogs?
On Thu, Jul 18, 2019, 4:29 PM ruckeralex notifications@github.com wrote:
Closed #22 https://github.com/r-b-g-b/clean-water-tool/issues/22.
— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/r-b-g-b/clean-water-tool/issues/22?email_source=notifications&email_token=ABFFW3IXXUF5MHK42GX4SV3QAD4F5A5CNFSM4HISI4B2YY3PNVWWK3TUL52HS4DFWZEXG43VMVCXMZLOORHG65DJMZUWGYLUNFXW5KTDN5WW2ZLOORPWSZGOSSWBLRI#event-2494305733, or mute the thread https://github.com/notifications/unsubscribe-auth/ABFFW3MPZJHFGSW2LWJ46I3QAD4F5ANCNFSM4HISI4BQ .
— You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub, or mute the thread.
The Community Water Center (the non-profit we are working with) has requested that the search tool be able to classify water systems by the following types:
1) Legislative districts - associate each water system with its legislative district (https://services.gis.ca.gov/arcgis/rest/services/Boundaries/CA_Legislative_Districts/MapServer)
2) Regional water board - associate each water system with its regional board (https://www.waterboards.ca.gov/waterboards_map.html)
For now, we can define GIS points for water systems via centroid of associated zip code. You can access the GIS map shapefile here: https://www.waterboards.ca.gov/water_issues/programs/hr2w/docs/data/ec_summary_feb2019.zip.
NOTE: There should be around 3,297 systems; you can access a summary of the system name / zip code here: https://www.waterboards.ca.gov/water_issues/programs/hr2w/docs/data/2019-04-03_hr2w_web_data_summary.xls
Please let me know if you have any questions! ruckeralex@gmail.com
Thank you!