Help with Data

I need your help.

My name is Frank Wilder and I am a volunteer with Indivisible Berks. The Python program I wrote uses a Google Sheet as input. I need volunteers to help fill out the data for each county in PA. The Python program I wrote uses this Google Sheet as the data input (see the bottom of the page from more details on the program).

The Google Sheet looks like this:

It has the 7 columns :

  • County
  • Branch - defines which layer the district should be drawn on
  • District
  • Representative
  • Party
  • Color - defines color of district on map. Select a color in the dropdown list
  • Text in information box :

A) This is what the text information box looks like on a Google Map, when the both the Representative & District columns for a district are plain text (This is the default view):


B) Here is an example of a text information box on a Google Map, when both the Representative & District columns have a hyperlink. These columns can have hyperlinks by either :

1) selecting and copying a hyperlink from a web page, and pasting the clipboard contents into a cell. Google Sheets automatically converts most valid URL types when typed/pasted into a cell .

or

2) You can use the Google Sheet Hyperlink formula.

Syntax = HYPERLINK(url, [link_label])

Example: HYPERLINK("http://www.google.com/","Google")

C) Here is an example of a text information box on a Google Map, when the row has a description types in Text in information box column.

The text for this information box was created in a text editor (Notepad, Editpad,...). It was then copied & pasted into a cell, by first pressing the F2 key (Edit Cell), then pasting the text (If you hit the F2 key first, formatting will be preserved, but not font color, font face, etc.... if you don't, all the line returns will be lost)

In my opinion, this the best option, but it will take a little more work.


If you would like to help, send me an email and I will add you as an editor on the Google Sheet.

Thanks



Note : Program Details

I have KML shape files for all the counties in PA, as well as all the legislative districts (PA Senate, PA House & US House). My program basically :

  1. Loads all the KML files into memory (kmlStSenate, kmlStHouse, kmlUsHouse, kmlStCounties)
  2. Extracts the coordinates for each legislative district from kml XXX objects and store them in a dictionary
  3. Loads the Google Sheet into memory (county_rows)
  4. For each county in county_rows:
  • Create a folder for the county
  • Create a county KML file in the folder (from kmlStCounties)
  • For each legislative type (State Senate, State Rep., US Rep.), create a new KML file with a polygon for each district in that county. Use the color column for the polygon color and add note to the polygon, using the logic above.

Once the program is done, I use the Google Maps site to create a custom maps for each county, and import the 5 kml files as layers in the map.