2011-09-16 - In A District Deep Geek

in-district-deep-geek.txt

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!