[postgis-users] TIGER geocoder question
Stephen Frost
sfrost at snowman.net
Thu Jun 28 10:12:11 PDT 2007
* Marvin (delphet at gmail.com) wrote:
> 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"?
Well, looking back I actually recreate road_local from completechain by
doing some simple type conversions and whatnot:
create table roads_local (
ogc_fid INTEGER,
geom GEOMETRY,
module VARCHAR(8),
tlid INTEGER,
side1 INTEGER,
source CHAR(1),
fedirp VARCHAR(2),
fename VARCHAR(30),
fetype VARCHAR(4),
fedirs VARCHAR(2),
cfcc VARCHAR(3),
fraddl INTEGER,
toaddl INTEGER,
fraddr INTEGER,
toaddr INTEGER,
friaddl CHAR(1),
toiaddl CHAR(1),
friaddr CHAR(1),
toiaddr CHAR(1),
zipl INTEGER,
zipr INTEGER,
aianhhfpl INTEGER,
aianhhfpr INTEGER,
aihhtlil CHAR(1),
aihhtlir CHAR(1),
census1 CHAR(1),
census2 CHAR(1),
statel INTEGER,
stater INTEGER,
countyl INTEGER,
countyr INTEGER,
cousubl INTEGER,
cousubr INTEGER,
submcdl INTEGER,
submcdr INTEGER,
placel INTEGER,
placer INTEGER,
tractl INTEGER,
tractr INTEGER,
blockl INTEGER,
blockr INTEGER
);
insert into roads_local
select
ogc_fid,
wkb_geometry,
trim(module),
tlid,
side1,
trim(source),
trim(fedirp),
trim(fename),
trim(fetype),
trim(fedirs),
trim(cfcc),
to_number(fraddl,'99999999999'),
to_number(toaddl,'99999999999'),
to_number(fraddr,'99999999999'),
to_number(toaddr,'99999999999'),
trim(friaddl),
trim(toiaddl),
trim(friaddr),
trim(toiaddr),
zipl,
zipr,
aianhhfpl,
aianhhfpr,
trim(aihhtlil),
trim(aihhtlir),
trim(census1),
trim(census2),
statel,
stater,
countyl,
countyr,
cousubl,
cousubr,
submcdl,
submcdr,
placel,
placer,
tractl,
tractr,
blockl,
blockr
from completechain;
CREATE INDEX roads_local_tlid_idx ON roads_local (tlid);
CREATE INDEX roads_local_geom_idx ON roads_local USING GIST (geom public.gist_geometry_ops);
CREATE INDEX roads_local_cfcc_idx ON roads_local (cfcc);
> One more question, there is another table missing ("tiger_geocode_join").
Oh, yea, *that* thing. It's a pointless table, honestly. The idea
behind it was to link from tiger_geocode_roads to roads_local, but if
you just keep the tlid (as I did) from completechain in roads_local and
then in tiger_geocode_roads you don't need that side-table. That does
require changing up the actual pl/pgsql code some though. Basically go
through all the queries, remove the join against '_join' and change the
references to tlid to us the field from tiger_geocode_roads directly.
The sequence in tiger_geocode_roads ends up not being used for anything.
> 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"?
There could be, but it'd be ugly and silly. :)
> 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.
Sure.
Stephen
> 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
> >
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- 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/49a9bcd0/attachment.pgp>
More information about the postgis-users
mailing list