[postgis-devel] TIGER Geocoder updates/maintenance
Stephen Frost
sfrost at snowman.net
Thu Jun 28 16:30:17 PDT 2007
Greetings,
Small bit of history, from the -users list:
* Stephen Frost (sfrost at snowman.net) wrote:
> * 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?
I'd be willing to maintain it, since we're actively using it (or more
specifically, we're using what I've already modified/cleaned up) and
would love to see it maintained.
Mark Leslie suggested I email this list with a request to have access
to update the tarball on the website (or at least, I think that's what
was being suggested), so here it is. :) Alternatively, I could just
email updates to this list using patches, or put the tarball on my
site for people to review, etc...
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
>
> _______________________________________________
> 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-devel/attachments/20070628/08e42fed/attachment.sig>
More information about the postgis-devel
mailing list