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