[postgis-users] TIGER geocoder question
Marvin
delphet at gmail.com
Thu Jun 28 10:42:36 PDT 2007
Thank you so much, Stephen.. I'll check it out.
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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070628/ce6461e1/attachment.html>
More information about the postgis-users
mailing list