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

[Mark Thomas]

Jeff Hoffmann <jeff <at> propertykey.com> writes:

> 
> 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  <at> 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.
> 


Here's a working script for Jeff's idea...

CREATE OR REPLACE FUNCTION geocode(text) returns geometry as $$
begin
    return GeomFromEWKT(geocode_impl($1));
end;
$$ language plpgsql;

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

Also, I am using the tiger2005fe dataset, so if anyone needs it drop me a line
and I can save you some time.