[postgis-devel] TIGER Geocoder updates/maintenance

Mark Leslie mark at refractions.net
Fri Jun 29 10:24:19 PDT 2007


Stephen,
We would be happy for you to take over maintenance of the geocoder.  I'm
in the process of getting you setup with svn access, and I'll be
contacting you off the list shortly with details.
Mark Leslie
Refractions Research

Stephen Frost wrote:
> Greetings,
> 
>   Small bit of history, from the -users list:
> 
> * Stephen Frost (sfrost at snowman.net) wrote:
>> * Marvin (delphet at gmail.com) wrote:
>>> Thank you so much, Stephen.. I'll check it out.
>> Sure.  I'd really like to see some of this stuff end up back in the
>> tiger geocoder on the website...  I don't think anyone is actively
>> maintaining it, which, in my view, is a real shame. :(  Does anyone know
>> of an official maintainer of it?  Seems like it was a do-once and then
>> dropped on the PostGIS website but it could benefit *alot* from others
>> working on it and improving it.
>>
>> There are some 'bugs' in a couple of the table definitions too, I
>> think...  If you try to create a primary key on the street_type_lookup
>> it bombs, iirc.
>>
>> Is there someone I can work with on improving it?  Submit patches to,
>> etc?  Or should I just find a way to post what I've done that's not
>> specific to my organization and assume maintenance?
> 
>   I'd be willing to maintain it, since we're actively using it (or more
>   specifically, we're using what I've already modified/cleaned up) and
>   would love to see it maintained.
> 
>   Mark Leslie suggested I email this list with a request to have access
>   to update the tarball on the website (or at least, I think that's what
>   was being suggested), so here it is. :)  Alternatively, I could just
>   email updates to this list using patches, or put the tarball on my
>   site for people to review, etc...
> 
>   	Thanks,
> 
> 		Stephen
> 
>>> On 6/28/07, Stephen Frost <sfrost at snowman.net> wrote:
>>>> * Marvin (delphet at gmail.com) wrote:
>>>>> Thanks, Stephen. Your explanation helps me a lot. So you think that it
>>>> is
>>>>> possible just to change "roads_local" in the script to "completechain"?
>>>> Well, looking back I actually recreate road_local from completechain by
>>>> doing some simple type conversions and whatnot:
>>>>
>>>> create table roads_local (
>>>>  ogc_fid       INTEGER,
>>>>  geom          GEOMETRY,
>>>>  module        VARCHAR(8),
>>>>  tlid          INTEGER,
>>>>  side1         INTEGER,
>>>>  source        CHAR(1),
>>>>  fedirp        VARCHAR(2),
>>>>  fename        VARCHAR(30),
>>>>  fetype        VARCHAR(4),
>>>>  fedirs        VARCHAR(2),
>>>>  cfcc          VARCHAR(3),
>>>>  fraddl        INTEGER,
>>>>  toaddl        INTEGER,
>>>>  fraddr        INTEGER,
>>>>  toaddr        INTEGER,
>>>>  friaddl       CHAR(1),
>>>>  toiaddl       CHAR(1),
>>>>  friaddr       CHAR(1),
>>>>  toiaddr       CHAR(1),
>>>>  zipl          INTEGER,
>>>>  zipr          INTEGER,
>>>>  aianhhfpl     INTEGER,
>>>>  aianhhfpr     INTEGER,
>>>>  aihhtlil      CHAR(1),
>>>>  aihhtlir      CHAR(1),
>>>>  census1       CHAR(1),
>>>>  census2       CHAR(1),
>>>>  statel        INTEGER,
>>>>  stater        INTEGER,
>>>>  countyl       INTEGER,
>>>>  countyr       INTEGER,
>>>>  cousubl       INTEGER,
>>>>  cousubr       INTEGER,
>>>>  submcdl       INTEGER,
>>>>  submcdr       INTEGER,
>>>>  placel        INTEGER,
>>>>  placer        INTEGER,
>>>>  tractl        INTEGER,
>>>>  tractr        INTEGER,
>>>>  blockl        INTEGER,
>>>>  blockr        INTEGER
>>>> );
>>>>
>>>> insert into roads_local
>>>> select
>>>>  ogc_fid,
>>>>  wkb_geometry,
>>>>  trim(module),
>>>>  tlid,
>>>>  side1,
>>>>  trim(source),
>>>>  trim(fedirp),
>>>>  trim(fename),
>>>>  trim(fetype),
>>>>  trim(fedirs),
>>>>  trim(cfcc),
>>>>  to_number(fraddl,'99999999999'),
>>>>  to_number(toaddl,'99999999999'),
>>>>  to_number(fraddr,'99999999999'),
>>>>  to_number(toaddr,'99999999999'),
>>>>  trim(friaddl),
>>>>  trim(toiaddl),
>>>>  trim(friaddr),
>>>>  trim(toiaddr),
>>>>  zipl,
>>>>  zipr,
>>>>  aianhhfpl,
>>>>  aianhhfpr,
>>>>  trim(aihhtlil),
>>>>  trim(aihhtlir),
>>>>  trim(census1),
>>>>  trim(census2),
>>>>  statel,
>>>>  stater,
>>>>  countyl,
>>>>  countyr,
>>>>  cousubl,
>>>>  cousubr,
>>>>  submcdl,
>>>>  submcdr,
>>>>  placel,
>>>>  placer,
>>>>  tractl,
>>>>  tractr,
>>>>  blockl,
>>>>  blockr
>>> >from completechain;
>>>> CREATE INDEX roads_local_tlid_idx ON roads_local (tlid);
>>>> CREATE INDEX roads_local_geom_idx ON roads_local USING GIST (geom
>>>> public.gist_geometry_ops);
>>>> CREATE INDEX roads_local_cfcc_idx ON roads_local (cfcc);
>>>>
>>>>> One more question, there is another table missing
>>>> ("tiger_geocode_join").
>>>>
>>>> Oh, yea, *that* thing.  It's a pointless table, honestly.  The idea
>>>> behind it was to link from tiger_geocode_roads to roads_local, but if
>>>> you just keep the tlid (as I did) from completechain in roads_local and
>>>> then in tiger_geocode_roads you don't need that side-table.  That does
>>>> require changing up the actual pl/pgsql code some though.  Basically go
>>>> through all the queries, remove the join against '_join' and change the
>>>> references to tlid to us the field from tiger_geocode_roads directly.
>>>> The sequence in tiger_geocode_roads ends up not being used for anything.
>>>>
>>>>> One message says that "tiger_geocode_join" links "roads_local" and
>>>>> "tiger_geocode_roads" but I can't seem to figure out what that means. Is
>>>>> there a piece of script to create "tiger_geocode_join"?
>>>> There could be, but it'd be ugly and silly. :)
>>>>
>>>>> As for the missing fields in "completechain", I meant "zip" and so on
>>>> since
>>>>> TIGER keeps both left and right zip and I had no idea what zip
>>>>> "tiger_geocode_roads" uses. Now I can tell from your script.
>>>>>
>>>>> Thank you very much.
>>>> Sure.
>>>>
>>>>        Stephen
>>>>
>>>>> On 6/28/07, Stephen Frost <sfrost at snowman.net> wrote:
>>>>>> * Shuo Liu (delphet at gmail.com) wrote:
>>>>>>> I'm working on a GIS project and trying to use TIGER Geocoder from
>>>> the
>>>>>>> refractions website on TIGER data. The two sql files in the Geocoder
>>>>>>> generated some errors when being loaded, complaining that some tables
>>>>>>> ("gazetteer_places", "tiger_geocode_roads", "place_lookup",
>>>>>> "roads_local",
>>>>>>> and "countysub_lookup") are missing. Some messages from the mailing
>>>> list
>>>>>> say
>>>>>>> that gazetteer tables should be loaded from the Census Bureau
>>>> gazetteer
>>>>>>> files. But that doesn't help find "tiger_geocode_roads" and
>>>>>> "roads_local"
>>>>>>> which don't exist in the loaded TIGER database. I used ogr2ogr to
>>>> load
>>>>>> the
>>>>>>
>>>>>> roads_local is essentially completechain.  tiger_geocode_roads is built
>>>>>> off of that by mashing the two sides together into one file and
>>>> removing
>>>>>> the address from/to columns.  Takes a while to process but the SQL
>>>> isn't
>>>>>> terribly complex:
>>>>>>
>>>>>> DROP SEQUENCE IF EXISTS tiger_geocode_roads_seq;
>>>>>> CREATE SEQUENCE tiger_geocode_roads_seq;
>>>>>>
>>>>>> DROP TABLE IF EXISTS tiger_geocode_roads;
>>>>>> CREATE TABLE tiger_geocode_roads (
>>>>>>    id      INTEGER,
>>>>>>    tlid    INTEGER,
>>>>>>    fedirp  VARCHAR(2),
>>>>>>    fename  VARCHAR(30),
>>>>>>    fetype  VARCHAR(4),
>>>>>>    fedirs  VARCHAR(2),
>>>>>>    zip     INTEGER,
>>>>>>    state   VARCHAR(2),
>>>>>>    county  VARCHAR(90),
>>>>>>    cousub  VARCHAR(90),
>>>>>>    place   VARCHAR(90)
>>>>>> );
>>>>>>
>>>>>> INSERT INTO tiger_geocode_roads
>>>>>>  SELECT
>>>>>>    nextval('tiger_geocode_roads_seq'),
>>>>>>    tlid,
>>>>>>    fedirp,
>>>>>>    fename,
>>>>>>    fetype,
>>>>>>    fedirs,
>>>>>>    zip,
>>>>>>    state,
>>>>>>    county,
>>>>>>    cousub,
>>>>>>    place
>>>>>>  FROM
>>>>>>   (SELECT
>>>>>>      tlid,
>>>>>>      fedirp,
>>>>>>      fename,
>>>>>>      fetype,
>>>>>>      fedirs,
>>>>>>      zipl as zip,
>>>>>>      sl.abbrev as state,
>>>>>>      co.name as county,
>>>>>>      cs.name as cousub,
>>>>>>      pl.name as place
>>>>>>    FROM
>>>>>>      roads_local rl
>>>>>>      JOIN state_lookup sl on (rl.statel = sl.st_code)
>>>>>>      LEFT JOIN county_lookup co on (rl.statel = co.st_code AND
>>>> rl.countyl=
>>>>>> co.co_code)
>>>>>>      LEFT JOIN countysub_lookup cs on (rl.statel = cs.st_code AND
>>>>>> rl.countyl = cs.co_code AND rl.cousubl = cs.cs_code)
>>>>>>      LEFT JOIN place_lookup pl on (rl.statel = pl.st_code AND
>>>> rl.placel =
>>>>>> pl.pl_code)
>>>>>>    WHERE fename IS NOT NULL
>>>>>>    UNION
>>>>>>    SELECT
>>>>>>      tlid,
>>>>>>      fedirp,
>>>>>>      fename,
>>>>>>      fetype,
>>>>>>      fedirs,
>>>>>>      zipr as zip,
>>>>>>      sl.abbrev as state,
>>>>>>      co.name as county,
>>>>>>      cs.name as cousub,
>>>>>>      pl.name as place
>>>>>>    FROM
>>>>>>      roads_local rl
>>>>>>      JOIN state_lookup sl on (rl.stater = sl.st_code)
>>>>>>      LEFT JOIN county_lookup co on (rl.stater = co.st_code AND
>>>> rl.countyr=
>>>>>> co.co_code)
>>>>>>      LEFT JOIN countysub_lookup cs on (rl.stater = cs.st_code AND
>>>>>> rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code)
>>>>>>      LEFT JOIN place_lookup pl on (rl.stater = pl.st_code AND
>>>> rl.placer =
>>>>>> pl.pl_code)
>>>>>>    WHERE fename IS NOT NULL
>>>>>>    ) AS sub;
>>>>>>
>>>>>> CREATE INDEX tiger_geocode_roads_zip_soundex_idx          ON
>>>>>> tiger_geocode_roads (soundex(fename), zip, state);
>>>>>> CREATE INDEX tiger_geocode_roads_place_soundex_idx        ON
>>>>>> tiger_geocode_roads (soundex(fename), place, state);
>>>>>> CREATE INDEX tiger_geocode_roads_cousub_soundex_idx       ON
>>>>>> tiger_geocode_roads (soundex(fename), cousub, state);
>>>>>> CREATE INDEX tiger_geocode_roads_place_more_soundex_idx   ON
>>>>>> tiger_geocode_roads (soundex(fename), soundex(place), state);
>>>>>> CREATE INDEX tiger_geocode_roads_cousub_more_soundex_idx  ON
>>>>>> tiger_geocode_roads (soundex(fename), soundex(cousub), state);
>>>>>> CREATE INDEX tiger_geocode_roads_state_soundex_idx        ON
>>>>>> tiger_geocode_roads (soundex(fename), state);
>>>>>>
>>>>>>> TIGER data (
>>>>>> http://docs.codehaus.org/display/GEOSDOC/Loading+TIGER+basedata)
>>>>>>> and it seems that some fields required by the Geocoder are in
>>>>>>> "completechain" but not all. Can anybody who have experience share
>>>> some
>>>>>> hint
>>>>>>> on this problem? Thank you very much.
>>>>>> I'm curious what fields you think aren't available...?  Perhaps the
>>>>>> folding from above will solve that for you...
>>>>>>
>>>>>>        Enjoy,
>>>>>>
>>>>>>                Stephen
>>>>>>
>>>>>>
>>>>>> -----BEGIN PGP SIGNATURE-----
>>>>>> Version: GnuPG v1.4.6 (GNU/Linux)
>>>>>>
>>>>>> iD8DBQFGg+UerzgMPqB3kigRAlihAJ4k/8Sf0ijbBnwkIvpwHQqv02IgjwCfXRiz
>>>>>> 0rNEYkBA3OCWr2le08i74ys=
>>>>>> =eDK5
>>>>>> -----END PGP SIGNATURE-----
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at postgis.refractions.net
>>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>>
>>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>> -----BEGIN PGP SIGNATURE-----
>>>> Version: GnuPG v1.4.6 (GNU/Linux)
>>>>
>>>> iD8DBQFGg+vrrzgMPqB3kigRAvgDAJ9CjEYQNhlQHOTjBHUBqWO9IXfyzACfQ0jL
>>>> fM058UoPBAQfpbX07Uz90TU=
>>>> =mfHr
>>>> -----END PGP SIGNATURE-----
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel




More information about the postgis-devel mailing list