Thank you so much, Stephen.. I'll check it out.<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;">* Marvin (<a href="mailto:delphet@gmail.com">delphet@gmail.com</a>) wrote:<br>> Thanks, Stephen. Your explanation helps me a lot. So you think that it is
<br>> possible just to change "roads_local" in the script to "completechain"?<br><br>Well, looking back I actually recreate road_local from completechain by<br>doing some simple type conversions and whatnot:
<br><br>create table roads_local (<br> ogc_fid INTEGER,<br> geom GEOMETRY,<br> module VARCHAR(8),<br> tlid INTEGER,<br> side1 INTEGER,<br> source CHAR(1),<br> fedirp VARCHAR(2),
<br> fename VARCHAR(30),<br> fetype VARCHAR(4),<br> fedirs VARCHAR(2),<br> cfcc VARCHAR(3),<br> fraddl INTEGER,<br> toaddl INTEGER,<br> fraddr INTEGER,<br> toaddr INTEGER,
<br> friaddl CHAR(1),<br> toiaddl CHAR(1),<br> friaddr CHAR(1),<br> toiaddr CHAR(1),<br> zipl INTEGER,<br> zipr INTEGER,<br> aianhhfpl INTEGER,<br> aianhhfpr INTEGER,
<br> aihhtlil CHAR(1),<br> aihhtlir CHAR(1),<br> census1 CHAR(1),<br> census2 CHAR(1),<br> statel INTEGER,<br> stater INTEGER,<br> countyl INTEGER,<br> countyr INTEGER,
<br> cousubl INTEGER,<br> cousubr INTEGER,<br> submcdl INTEGER,<br> submcdr INTEGER,<br> placel INTEGER,<br> placer INTEGER,<br> tractl INTEGER,<br> tractr INTEGER,
<br> blockl INTEGER,<br> blockr INTEGER<br>);<br><br>insert into roads_local<br>select<br> ogc_fid,<br> wkb_geometry,<br> trim(module),<br> tlid,<br> side1,<br> trim(source),<br> trim(fedirp),<br> trim(fename),
<br> trim(fetype),<br> trim(fedirs),<br> trim(cfcc),<br> to_number(fraddl,'99999999999'),<br> to_number(toaddl,'99999999999'),<br> to_number(fraddr,'99999999999'),<br> to_number(toaddr,'99999999999'),
<br> trim(friaddl),<br> trim(toiaddl),<br> trim(friaddr),<br> trim(toiaddr),<br> zipl,<br> zipr,<br> aianhhfpl,<br> aianhhfpr,<br> trim(aihhtlil),<br> trim(aihhtlir),<br> trim(census1),<br> trim(census2),<br> statel,
<br> stater,<br> countyl,<br> countyr,<br> cousubl,<br> cousubr,<br> submcdl,<br> submcdr,<br> placel,<br> placer,<br> tractl,<br> tractr,<br> blockl,<br> blockr<br>from completechain;<br><br>CREATE INDEX roads_local_tlid_idx ON roads_local (tlid);
<br>CREATE INDEX roads_local_geom_idx ON roads_local USING GIST (geom public.gist_geometry_ops);<br>CREATE INDEX roads_local_cfcc_idx ON roads_local (cfcc);<br><br>> One more question, there is another table missing ("tiger_geocode_join").
<br><br>Oh, yea, *that* thing. It's a pointless table, honestly. The idea<br>behind it was to link from tiger_geocode_roads to roads_local, but if<br>you just keep the tlid (as I did) from completechain in roads_local and
<br>then in tiger_geocode_roads you don't need that side-table. That does<br>require changing up the actual pl/pgsql code some though. Basically go<br>through all the queries, remove the join against '_join' and change the
<br>references to tlid to us the field from tiger_geocode_roads directly.<br>The sequence in tiger_geocode_roads ends up not being used for anything.<br><br>> One message says that "tiger_geocode_join" links "roads_local" and
<br>> "tiger_geocode_roads" but I can't seem to figure out what that means. Is<br>> there a piece of script to create "tiger_geocode_join"?<br><br>There could be, but it'd be ugly and silly. :)
<br><br>> As for the missing fields in "completechain", I meant "zip" and so on since<br>> TIGER keeps both left and right zip and I had no idea what zip<br>> "tiger_geocode_roads" uses. Now I can tell from your script.
<br>><br>> Thank you very much.<br><br>Sure.<br><br> Stephen<br><br>> On 6/28/07, Stephen Frost <<a href="mailto:sfrost@snowman.net">sfrost@snowman.net</a>> wrote:<br>> ><br>> >* 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",<br>> >"roads_local",
<br>> >> and "countysub_lookup") are missing. Some messages from the mailing list<br>> >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
<br>> >"roads_local"<br>> >> which don't exist in the loaded TIGER database. I used ogr2ogr to load<br>> >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=
<br>> >co.co_code)<br>> > LEFT JOIN countysub_lookup cs on (rl.statel = cs.st_code AND<br>> >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 =<br>> >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=<br>> >co.co_code)<br>> > LEFT JOIN countysub_lookup cs on (rl.stater = cs.st_code AND<br>> >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 =<br>> >pl.pl_code)<br>> > WHERE fename IS NOT NULL<br>> > ) AS sub;<br>> ><br>> >CREATE INDEX tiger_geocode_roads_zip_soundex_idx ON<br>> >tiger_geocode_roads (soundex(fename), zip, state);
<br>> >CREATE INDEX tiger_geocode_roads_place_soundex_idx ON<br>> >tiger_geocode_roads (soundex(fename), place, state);<br>> >CREATE INDEX tiger_geocode_roads_cousub_soundex_idx ON<br>> >tiger_geocode_roads (soundex(fename), cousub, state);
<br>> >CREATE INDEX tiger_geocode_roads_place_more_soundex_idx ON<br>> >tiger_geocode_roads (soundex(fename), soundex(place), state);<br>> >CREATE INDEX tiger_geocode_roads_cousub_more_soundex_idx ON<br>
> >tiger_geocode_roads (soundex(fename), soundex(cousub), state);<br>> >CREATE INDEX tiger_geocode_roads_state_soundex_idx ON<br>> >tiger_geocode_roads (soundex(fename), state);<br>> ><br>> >> TIGER data (
<br>> ><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<br>> >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>> ><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><br>-----BEGIN PGP SIGNATURE-----
<br>Version: GnuPG v1.4.6 (GNU/Linux)<br><br>iD8DBQFGg+vrrzgMPqB3kigRAvgDAJ9CjEYQNhlQHOTjBHUBqWO9IXfyzACfQ0jL<br>fM058UoPBAQfpbX07Uz90TU=<br>=mfHr<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>