GA-CCRi Analytical Development Services

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.