Loading TIGER polygons

There are two ways to do this. One is to use OGR/GDAL's tigerpoly.py script. Unfortunately, this sometimes produces bad polygons. I wrote a "how to fix up bad polygons" document as well but its long and very complex (but easy to follow).

I wrote a Java script (using Geotools and JTS) that does, basically, the same thing as the tigerpoly.py script, but is much more robust and does significantly more error checking. This is the recommended load procedure.

It assumes you have your TIGER data in a postgis database and have Java (1.4.2+). Loading TIGER basedata

If you want to see the old (OGR/GDAL/tigerpoly.py) load proceedure see here Loading TIGER polygons tigerpoly.pl.

This process is much faster than the OGR/GDAL process. This takes about 15seconds/county, and the ORG/GDAL takes about 2 minutes/county.

TIGER 2005fe error

The tiger2005fe has 2 errors - this will fix it:
update pip set wkb_geometry = 'SRID=1;POINT (-81.30734482864119 28.615619314157783)'::geometry
where module='TGR12117' and polyid = 8709;

update pip set wkb_geometry = 'SRID=1;POINT (-82.06129672922174 39.53997601515791)'::geometry
where module='TGR39009' and polyid = 5710;

update pip set wkb_geometry = 'SRID=1;POINT (-92.5198529528275 36.10732487177976)'::geometry
where module='TGR05089' and polyid = 5052;

update pip set wkb_geometry = 'SRID=1;POINT (-90.5673377761759 29.986304278927115)'::geometry
where module='TGR22095' and polyid = 3980;

update pip set wkb_geometry = 'SRID=1;POINT (-90.38182741292844 29.92608334553412)'::geometry
where module='TGR22089' and polyid = 5204;

update pip set wkb_geometry = 'SRID=1;POINT (-82.32978809930067 31.20110701961316)'::geometry
where module='TGR13299' and polyid = 7263;

update pip set wkb_geometry = 'SRID=1;POINT (-82.84969652715823 30.88904911060222)'::geometry
where module='TGR13065' and polyid = 2222;

update pip set wkb_geometry = 'SRID=1;POINT (-83.42376615233437 33.86629106442862)'::geometry
where module='TGR13219' and polyid = 2164;

- major data error in Guam
update completechain
set wkb_geometry = 'SRID=1;LINESTRING(144.86912 13.499629,144.870032 13.499872,144.870064 13.499926,144.870144 13.500002,144.870512 13.500181,144.870704 13.500268,144.870752 13.500349,144.87072 13.500414,144.870656 13.500452,144.87048 13.500441,144.870128 13.500311,144.869072 13.5)'::geometry
where module = 'TGR66010' and tlid=204580888;

- now make 5157 polygon not have the upper left rectangle in it.
update polychainlink
set polyidl = 31
WHERE module = 'TGR66010' and tlid=204535507;
update polychainlink
set polyidl = 31
WHERE module = 'TGR66010' and tlid=204538093;
update polychainlink
set polyidr = 31
WHERE module = 'TGR66010' and tlid=204552787;

- another major data error in Guam

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.802832 13.456777, 144.80264 13.457018999999999, 144.802336 13.457408, 144.80208 13.457727, 144.80192 13.45783)'::geometry
WHERE module = 'TGR66010' and tlid =204581409 ;

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.803008 13.457828, 144.802912 13.457507999999999, 144.802864 13.457142999999999, 144.802832 13.456777)'::geometry
WHERE module = 'TGR66010' and tlid =204569152 ;

delete from polychainlink
WHERE module = 'TGR66010' and tlid=204552845;

- another major data error in Guam 2

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.714096 13.458386, 144.713936 13.458114, 144.71379199999998 13.457286999999999, 144.714224 13.455983999999999, 144.714352 13.455057)'::geometry
WHERE module = 'TGR66010' and tlid =204553710 ;

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.714352 13.455057, 144.714384 13.454868999999999, 144.714 13.454016999999999, 144.71384 13.453848)'::geometry
WHERE module = 'TGR66010' and tlid =204552196 ;

delete from polychainlink
WHERE module = 'TGR66010' and tlid=204540591 ;

- another major data error in Guam 3 (poly1540)

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.71384 13.453848,144.71336692401346 13.453342093154188, 144.713088 13.453169)'::geometry
WHERE module = 'TGR66010' and tlid =204552195 ;

update completechain
set wkb_geometry = 'SRID=1;LINESTRING (144.713088 13.453169, 144.712784 13.452979)'::geometry
WHERE module = 'TGR66010' and tlid = 204552189;

delete from polychainlink
WHERE module = 'TGR66010' and tlid=204552193 ;

Finding and correcting errors

The above errors were found by running the polygonization program and then investigating the errors. There are three types:
1. the dataset contains polygons and the TIGER dataset doesnt have PIP (point-in-polygon) for it. Solution - run that group with the "allowMissingPIP" option.
2. the dataset has a polygon with 0 pips. Usually this is an very thin polygon with it PIP just barely outside it. Solution - move the point so its inside the polygon.
3. (only found in Guam) Other error - correct the dataset.

1. Geotools required a primary key on the table

alter table pip add primary key (ogc_fid);
alter table completechain add primary key (ogc_fid);
alter table polychainlink add primary key (ogc_fid);

2. Create the poly2 table

This is where the polygons will be put:

create table poly2 (the_geom geometry, polyid numeric(10,0), module char(8)) with oids;
insert into geometry_columns values ('','public','poly2','the_geom',2,1,'POLYGON');

3. Download the "tigerpolygonformation.zip" file and unzip it

Available here
This contains the java program (and jars) to run the load. Make sure you have java 1.4.2+ installed.

4. Edit the go.txt script

You may have to edit the postgresql connection string. Use a search-and-replace function.

NOTE: if you are running this on windows, you'll have to replace all the ":" with ";". Use a search-and-replace function.

5. run the go.txt script

source go.txt

6. QA/QC checks

--should be 0 (check for bad polys)
-- see below!!
SELECT module,polyid FROM poly2 WHERE not(isvalid(the_geom)) OR (the_geom isnull);

--make sure all the polygons formed
--- should be 3232
selct count(distinct module) from poly2;

insert into qa_qc
 select module,'P',count(*) from poly2 group by module;

SELECT module,count FROM qa_qc  where filetype = 'P'
EXCEPT
SELECT county,nfeats FROM counts_txt WHERE type='p';
- should return none! (if it does return something, then there was a load error)

SELECT county,nfeats FROM counts_txt WHERE type='p'
except
SELECT module,count FROM qa_qc  where filetype = 'P'
- should return none! (if it does return something, then there was a load error)

isvalid() problem

When you execute:
SELECT module,polyid FROM poly2 WHERE not(isvalid(the_geom)) OR (the_geom isnull);

You may get these three polygons:

module | polyid
--------+------
TGR26045 | 1745
TGR33011 | 280
TGR12057 | 4775

These are not actually invalid() - its a bug in the GEOS/JTS isvalid() code. Its been reported and a fix will hopefully make it to PostGIS in the next few releases.

Users Guide

Developers Guide

Tutorials

FAQ

Javadocs

License

Contributors

Page Operations

Browse Space