[postgis-users] RE: Geocoding

[Mark Thomas]

Here's a simple PERL geocoder for Google.

-- $Id: google_geocode.sql 171 2007-01-26 02:35:09Z mark $ --

DROP TABLE google_api_key;

CREATE TABLE google_api_key (
    key text primary key
);

INSERT INTO google_api_key values ('your key here');

CREATE OR REPLACE FUNCTION geocode(text) returns geometry as $$
declare
    mykey text;
begin
    select key into mykey from google_api_key;
    return GeomFromEWKT(geocode_impl($1, mykey));
end;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION geocode_impl(text, text) returns text as '
   use LWP::Simple;
   $csv = get("http://maps.google.com/maps/geo?output=csv&q=
".$_[0]."&key=".$_[1]);
   @tokens = split(/,/, $csv);
   if ($tokens[0] == "200") {
       return "SRID=4326;POINT(" . $tokens[3] . " " . $tokens[2] . ")";
   } else {
       return "";
   }
' LANGUAGE plperlu IMMUTABLE;

google uses tmerc, so you can call a function like this to get correct SRID
for point and use transform

create or replace function getUTMZoneEPSG(geometry) returns int as $$
declare
    i int := 32600;
    j numeric;
begin
    j := abs((-180 - X($1)) / 6);
    if j < 0.5 then
        j := 1;
    end if;
    i := i + round(j);
    if Y($1) < 0 then
        i := i + 30;
    end if;
    return i;
end;
$$ language plpgsql;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070203/47cd7032/attachment.html>