[postgis-users] Status of geocoding functions in PostGIS?

[Jeff Hoffmann]

Alex Smith wrote:
> Did some searching of the archives and found this article stating that a 
> geocoder for PostGIS is in the works (see 
> http://postgis.refractions.net/pipermail/postgis-users/2004-September/005762.html).  
> Just wondering the status of this project and if there is a time line 
> for the release dates. 

I don't know the status of this project, but here's a quick and dirty 
alternative in the meantime:

- install the perl module "Geo::Coder::US" (either through CPAN or at 
http://geocoder.us) & create a geocoder database from TIGER
- create language plperlu in the database you want the geocoder function
- create the following function, replacing "/path/to/geocoder.db" with 
the location of the geocoder database you created from TIGER

CREATE OR REPLACE FUNCTION geocode(text) returns geometry as '
   use Geo::Coder::US;
   Geo::Coder::US->set_db("/path/to/geocoder.db");
   my @res = Geo::Coder::US->geocode($_[0]);
   return "SRID=4269;POINT(".$res[0]->{''long''}.
                         " ".$res[0]->{''lat''}.")";
' LANGUAGE plperlu IMMUTABLE;

Error checking (and all the rest of the fun stuff) is left as an 
exercise for the reader.  That should be enough to get you started, 
though.

-- 
Jeff Hoffmann
jeff at propertykey.com