[postgis-users] TIGER geocoder question

[Marvin]

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"?

One more question, there is another table missing ("tiger_geocode_join").
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"?

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.

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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070628/1a19b4fe/attachment.html>