Batch Geocoding with TIGER Geocoder 2010

I’m compiling a database of all the microbreweries in my state.  So far I’ve got a list of 43 breweries and their addresses.  I’ve geocoded them against the 2010 TIGER street centerlines.  I used a batch geocoding example from PostGIS in Action the basis for this query.

UPDATE brewery
SET (rating, geom) =
(g.rating, ST_SnapToGrid(ST_Transform(g.geomout,26916), 0.000001))
FROM (SELECT DISTINCT ON (gid) gid, name, (g1.geo).*
FROM (SELECT gid, name, (geocode(addy)) As geo
FROM breweries.brewery As b
WHERE b.rating is null AND b.addy is not null) As g1
ORDER BY gid, rating) As g
WHERE g.gid = brewery.gid;

I created the fields “addy” and “rating” when I made the breweries table, addy is a varchar field and rating is an integer.  The “the_geom” field was created with the PostGIS AddGeometryColumn() function.
The geocoder did a fairly good job, most of the results are pretty close to where they should be.  There is at least one that is over 90 miles from where it is supposed to be.  I’m not sure why it fell so far out of line, the address is straightforward.  The geocoder gave that point a confidence rating of 27, which is the third highest out of the 43 (lower rating is better).  I’m still trying to figure out how far off the 30 and 60 rated points are.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: