I noticed a question on the Analytics X Prize forum about how to determine the zip code for homicides with latitude and longitude values. While there are a plethora of online tools (Google Maps, etc) that will do this for you, I thought I’d describe a simple way to do it using PostgreSQL/PostGIS as it illustrates one aspect of the multitude of open source tools that aid in spatial analysis. Also, the described method can be easily automated in combination with a shell script and some db insert triggers.
First, I retrieved the incident data from the resource described in an earlier post. After some awk and sed wrangling, I got the data into a format where it could be imported into a PostgreSQL table with the following structure:
philly=# \d incidents Table "public.incidents" Column | Type | Modifiers -----------+--------------------------+----------- id | bigint | date | timestamp with time zone | geom | geometry | zip | integer | Indexes: "inc_gist_idx" gist (geom)
Notice that there is a geometry column which specified the geocoded location of the homicide. The data came down projected using SRID 26918 – UTM Zone 18. I had to reproject the zip code geometries as they came as unprojected lat/lon. The zip code table (which I retrieved from the source listed in an earlier post) had the following structure:
philly=# \d philly Table "public.philly" Column | Type | Modifiers ------------+-----------------------+----------- gid | integer | area | numeric | perimeter | numeric | zt42_d00_ | bigint | zt42_d00_i | bigint | zcta | character varying(5) | name | character varying(90) | lsad | character varying(2) | lsad_trans | character varying(50) | the_geom | geometry | Indexes: "philly_gist_idx" gist (the_geom)
Now, the zip code column of the incidents table is empty. I used the following select statement to populate the zip code column with the proper zip code which it falls in:
update incidents set zip= (select cast(name as integer) from philly where contains(transform(the_geom,26918), geom));
The statement is selecting the zip code name from the zip code table where the incident point falls within the zip code polygon.Share this post: