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"?<br><br>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"?
<br><br>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.
<br><br>Thank you very much.<br><br><div><span class="gmail_quote">On 6/28/07, <b class="gmail_sendername">Stephen Frost</b> <<a href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
* Shuo Liu (<a href="mailto:delphet@gmail.com">delphet@gmail.com</a>) wrote:<br>> I'm working on a GIS project and trying to use TIGER Geocoder from the<br>> refractions website on TIGER data. The two sql files in the Geocoder
<br>> generated some errors when being loaded, complaining that some tables<br>> ("gazetteer_places", "tiger_geocode_roads", "place_lookup", "roads_local",<br>> and "countysub_lookup") are missing. Some messages from the mailing list say
<br>> that gazetteer tables should be loaded from the Census Bureau gazetteer<br>> files. But that doesn't help find "tiger_geocode_roads" and "roads_local"<br>> which don't exist in the loaded TIGER database. I used ogr2ogr to load the
<br><br>roads_local is essentially completechain. tiger_geocode_roads is built<br>off of that by mashing the two sides together into one file and removing<br>the address from/to columns. Takes a while to process but the SQL isn't
<br>terribly complex:<br><br>DROP SEQUENCE IF EXISTS tiger_geocode_roads_seq;<br>CREATE SEQUENCE tiger_geocode_roads_seq;<br><br>DROP TABLE IF EXISTS tiger_geocode_roads;<br>CREATE TABLE tiger_geocode_roads (<br> id INTEGER,
<br> tlid INTEGER,<br> fedirp VARCHAR(2),<br> fename VARCHAR(30),<br> fetype VARCHAR(4),<br> fedirs VARCHAR(2),<br> zip INTEGER,<br> state VARCHAR(2),<br> county VARCHAR(90),<br> cousub VARCHAR(90),
<br> place VARCHAR(90)<br>);<br><br>INSERT INTO tiger_geocode_roads<br> SELECT<br> nextval('tiger_geocode_roads_seq'),<br> tlid,<br> fedirp,<br> fename,<br> fetype,<br> fedirs,<br> zip,<br>
state,<br> county,<br> cousub,<br> place<br> FROM<br> (SELECT<br> tlid,<br> fedirp,<br> fename,<br> fetype,<br> fedirs,<br> zipl as zip,<br> sl.abbrev as state,<br>
<a href="http://co.name">co.name</a> as county,<br> <a href="http://cs.name">cs.name</a> as cousub,<br> <a href="http://pl.name">pl.name</a> as place<br> FROM<br> roads_local rl<br> JOIN state_lookup sl on (
rl.statel = sl.st_code)<br> LEFT JOIN county_lookup co on (rl.statel = co.st_code AND rl.countyl = co.co_code)<br> LEFT JOIN countysub_lookup cs on (rl.statel = cs.st_code AND rl.countyl = cs.co_code AND rl.cousubl
= cs.cs_code)<br> LEFT JOIN place_lookup pl on (rl.statel = pl.st_code AND rl.placel = pl.pl_code)<br> WHERE fename IS NOT NULL<br> UNION<br> SELECT<br> tlid,<br> fedirp,<br> fename,<br> fetype,
<br> fedirs,<br> zipr as zip,<br> sl.abbrev as state,<br> <a href="http://co.name">co.name</a> as county,<br> <a href="http://cs.name">cs.name</a> as cousub,<br> <a href="http://pl.name">pl.name
</a> as place<br> FROM<br> roads_local rl<br> JOIN state_lookup sl on (rl.stater = sl.st_code)<br> LEFT JOIN county_lookup co on (rl.stater = co.st_code AND rl.countyr = co.co_code)<br> LEFT JOIN countysub_lookup cs on (
rl.stater = cs.st_code AND rl.countyr = cs.co_code AND rl.cousubr = cs.cs_code)<br> LEFT JOIN place_lookup pl on (rl.stater = pl.st_code AND rl.placer = pl.pl_code)<br> WHERE fename IS NOT NULL<br> ) AS sub;<br>
<br>CREATE INDEX tiger_geocode_roads_zip_soundex_idx ON tiger_geocode_roads (soundex(fename), zip, state);<br>CREATE INDEX tiger_geocode_roads_place_soundex_idx ON tiger_geocode_roads (soundex(fename), place, state);
<br>CREATE INDEX tiger_geocode_roads_cousub_soundex_idx ON tiger_geocode_roads (soundex(fename), cousub, state);<br>CREATE INDEX tiger_geocode_roads_place_more_soundex_idx ON tiger_geocode_roads (soundex(fename), soundex(place), state);
<br>CREATE INDEX tiger_geocode_roads_cousub_more_soundex_idx ON tiger_geocode_roads (soundex(fename), soundex(cousub), state);<br>CREATE INDEX tiger_geocode_roads_state_soundex_idx ON tiger_geocode_roads (soundex(fename), state);
<br><br>> TIGER data (<a href="http://docs.codehaus.org/display/GEOSDOC/Loading+TIGER+basedata">http://docs.codehaus.org/display/GEOSDOC/Loading+TIGER+basedata</a>)<br>> and it seems that some fields required by the Geocoder are in
<br>> "completechain" but not all. Can anybody who have experience share some hint<br>> on this problem? Thank you very much.<br><br>I'm curious what fields you think aren't available...? Perhaps the
<br>folding from above will solve that for you...<br><br> Enjoy,<br><br> Stephen<br><br><br>-----BEGIN PGP SIGNATURE-----<br>Version: GnuPG v1.4.6 (GNU/Linux)<br><br>iD8DBQFGg+UerzgMPqB3kigRAlihAJ4k/8Sf0ijbBnwkIvpwHQqv02IgjwCfXRiz
<br>0rNEYkBA3OCWr2le08i74ys=<br>=eDK5<br>-----END PGP SIGNATURE-----<br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net
</a><br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br><br></blockquote></div><br>