Mobile Application Backend Legislative Districts GIS Documentation
State and Federal level GIS legislative district boundary data is sourced from U.S. Census Bureau TIGER shapefiles. A Python script named 'build_gis.py' located in the 'GIS' directory of the Vote Smart 'utilities' git repository (http://git.votesmart.org/gitweb.cgi?p=utilities.git) automates the entire ETL process which builds the 'legislativedistricts_gis' table from start to finish. The script:
- downloads each state's legislative district shapefiles
- opens them up for reading
- transforms geographic coordinate systems data to coordinate systems that we can use for internet GIS services
- matches legislative district name data to a district name record in database 'pvsadmin' table 'districtname'
- inserts records into table 'legislativedistricts_gis'
Records inserted into table 'legislativedistricts_gis' conform to the schema:
| affgeoid | districtname_id | state_id | office_id | name | geom |
|---|---|---|---|---|---|
| varchar | int | varchar | int | varchar | geometry(Geometry, 4326) |
affgeoid: Census bureau generated UID.
districtname_id: Foreign key mapped from shapefile district name -> database table 'districtname'.
state_id: Two-letter state abbreviation foreign key mapped from the shapefile FIPS code -> fips_codes.txt -> database table 'state'.
office_id: Level and office of government, inferred via the name of the shapefile.
name: Original district name string pulled from census bureau shapefile records.
geom: Geographic geometry.
Quick PostGIS Overview
PostGIS is a spatial database extension for PostgreSQL databases. Geographic data can be stored in a variety of geometry formats and can be queried via various PostGIS SQL functions. For the purposes of the mobile application, we'll be querying latitude/longitude input point data from users against geo-referenced polygon data representing voter districts stored in database table 'legislativedistricts_gis'. Queries should return the single record of the polygon that the input point falls inside of:
SELECT * FROM legislativedistricts_gis WHERE ST_INTERSECTS(ST_GeometryFromText('POINT(longitude latitude)', 4326), geom);
SELECT * FROM legislativedistricts_gis WHERE ST_INTERSECTS(ST_GeometryFromText('POINT(-110.902344 32.224770)', 4326), geom);
SELECT affgeoid, districtname_id, state_id, office_id, name FROM legislativedistricts_gis WHERE ST_INTERSECTS(ST_GeometryFromText('POINT(-113.3064932 46.3298812)', 4326), geom);
