[postgis-users] TIGER geocoder question

[Stephen Frost]

* 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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070628/a53b1a78/attachment.pgp>