Created
September 17, 2011 02:24
-
-
Save jkeefe/1223552 to your computer and use it in GitHub Desktop.
2011-09-16 - In A District Deep Geek
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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