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 <[EMAIL PROTECTED]> wrote:
* Shuo Liu ([EMAIL PROTECTED]) 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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
