Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
2011-09-16 - In A District Deep Geek
2011-09-16 - In A District Deep Geek
(supplement to post at: http://johnkeefe.net/dollars-in-a-district )
###################################
# Batch Geocoding
###################################
# Went to http://www.gpsvisualizer.com/geocoder/
# Pasted data into top window
# used "tabular data" and "yahoo" as options
# takes a while
###################################
# Load Assembly District shapefile to PostgreSQL
###################################
# Got NY State Legislative shapefile from
# http://www.census.gov/cgi-bin/geo/shapefiles2010/main
# details on shp2pgsql at
# http://postgis.refractions.net/docs/ch04.html#shp2pgsql_usage
# psql flags: -d = database, -f = temp file name
shp2pgsql -c -D -G /path/to/file/tl_2010_36_sldl10.shp public.assemblyshapes > assembly.sql
psql -d firstgeo -f assembly.sql
###############################
# Load in Donations Csv File to a New PostgreSQL Table
###############################
# Example From http://www.ensode.net/postgresql_csv_import.html
# COPY FROM documentation at http://www.postgresql.org/docs/8.2/static/sql-copy.html
# connect to the database (mine is called firstgeo):
psql -d firstgeo
# Step 1: Make the new table
# (note "Espinal" is on of the candidates in my project;
# gdesc and color were added by the batch geocoder
# at http://www.gpsvisualizer.com/geocoder/ )
CREATE TABLE assembly54_donors_espinal (NAME varchar, ADDRESS varchar, AMOUNT NUMERIC, latitude double precision, longitude double precision, gdesc varchar, color varchar);
\COPY assembly54_donors_espinal FROM '/Users/JohnKeefe/Desktop/espinal-final-geocoded.csv' DELIMITERS ',' CSV;
# note use of \COPY instead of COPY. The former executes under the username
# I'm logged in as (the latter executes as the pgsql service,
# which often has limited rights)
# Ooops:
# ERROR: invalid input syntax for type numeric: "AMOUNT"
# ... seems to be looking at the first line as data!? Deleted that line and it worked.
# Repeated for other two candidates
###################
# Created a geographic POINT column based on the latitudes and longitudes
###################
# This is necessary so PostGIS can do the calculations
# Details on usage at: http://postgis.refractions.net/docs/AddGeometryColumn.html
SELECT AddGeometryColumn( 'public', 'assembly54_donors_espinal', 'address_point', 4326, 'POINT', 2 );
UPDATE assembly54_donors_espinal
SET address_point = ST_SetSRID(ST_Point( longitude, latitude),4326);
# Repeated for other two candidates
###################
# Loaded data into QGIS
###################
# Fired up QGIS
# loaded the district 54 shapefile from the Postgres database onto the QGIS screen
Layer -> Add PostGIS Layer -> Connect -> Public -> assemblyshapes -> Build Query
WHERE
"sldlst10" = '054'
# Loaded in each donor layer directly from Postgres database
Layer -> Add PostGIS Layer -> Public -> assembly54_donors_espinal -> Add
# repeated for other two candidates
# be sure to select the table with the geometry in it (not "no geometry"!)
###################
# Do the in-a-district calculations with QGIS
###################
Vector -> Geoprocessing Tools -> Intersect
# put donor in the top file, district in the bottom file
# named the new shapefile
# added it to the project as "candidate-intersect"
# Repeated for each candidate
###################
# Exported all data layers as shapefiles
###################
# highlighted each layer and used ...
Layer -> Save As ...
# .. to save as an ESRI shapefile
# did for all "total contributions" and "intersect" shapefiles
# also did for the district shapefile
###################
# Got all shapefiles into Fusion Tables
###################
# Saved all shapefiles (and their sister files) into one folder, compress / zip it
# Uploaded entire .zip file to Fusion Tables with http://shpescape.com
# Opened the new tables (made from each shapefile) in fusion tables
# Here's a total-donors table:
# http://www.google.com/fusiontables/DataSource?dsrcid=1466803
# Here's an "intersect" table:
# http://www.google.com/fusiontables/DataSource?dsrcid=1466711
###################
# Calculated dollars and donations, from total-donors and intersect (in the district)
###################
# In each shape file, got total number of contributions and total dollar
# values with aggregation:
Options -> Aggregate -> Amount [X] sum -> Apply
###################
# Added district to donor maps
###################
# To show both points AND the district:
# Copy the "geometry" field from your district table
# here's mine: http://www.google.com/fusiontables/DataSource?dsrcid=1432315
# Go back to each candidate's donor table and add a row. Paste that code
# into the "geometry field". See the last row of this for example:
# http://www.google.com/fusiontables/DataSource?dsrcid=1466803
# Then, in the candidate's total-donor table, go to:
Visualize -> Map -> Configure Styles
Fill color -> click on dropdown -> Make opacity 0%
Border color -> make it something bright
Border width -> I used 3px
# Now when viewing the table as a map, I get points for the addresses
# and the district outline
###################
# Error Checking
###################
# Find and fix geocode errors. In Fusion Tables:
Options -> Filter: geometry CONTAINS 0,0
# For each hit
# - copy the address
# - fix the address (apartments and floors trip it up)
# - Run the addresses in http://getlatlon.com
# - edit the geometry field for that row in this format
# (note order is longitude,latitude,zero):
# <Point><coordinates>-73.815638,40.589834,0</coordinates></Point>
# here's one example of mine:
360 CLINTON AVE., #3F BROOKLYN NY 11238
360 CLINTON AVE., BROOKLYN NY 11238
40.6872851, -73.9679161
<Point><coordinates>-73.9679161,40.6872851,0</coordinates></Point>
# IMPORTANT! Look at the map and see if the new point is in the district.
# If it is, adjust dollar and donor-count figures accordingly!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment