[postgis-users] TIGER geocoder question
Stephen Frost
sfrost at snowman.net
Thu Jun 28 14:26:59 PDT 2007
* Marvin (delphet at gmail.com) wrote:
> Thank you so much, Stephen.. I'll check it out.
Sure. I'd really like to see some of this stuff end up back in the
tiger geocoder on the website... I don't think anyone is actively
maintaining it, which, in my view, is a real shame. :( Does anyone know
of an official maintainer of it? Seems like it was a do-once and then
dropped on the PostGIS website but it could benefit *alot* from others
working on it and improving it.
There are some 'bugs' in a couple of the table definitions too, I
think... If you try to create a primary key on the street_type_lookup
it bombs, iirc.
Is there someone I can work with on improving it? Submit patches to,
etc? Or should I just find a way to post what I've done that's not
specific to my organization and assume maintenance?
Thanks,
Stephen
> On 6/28/07, Stephen Frost <sfrost at snowman.net> wrote:
> >
> >* 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
> >
> >
> >-----BEGIN PGP SIGNATURE-----
> >Version: GnuPG v1.4.6 (GNU/Linux)
> >
> >iD8DBQFGg+vrrzgMPqB3kigRAvgDAJ9CjEYQNhlQHOTjBHUBqWO9IXfyzACfQ0jL
> >fM058UoPBAQfpbX07Uz90TU=
> >=mfHr
> >-----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/41f7a673/attachment.pgp>
More information about the postgis-users
mailing list