Loader_Generate_Census_Script
Name
Loader_Generate_Census_Script — Generates a shell script for the specified platform for the specified states that will download Tiger census state tract, bg, and tabblocks data tables, stage and load into
tiger_data
schema. Each state script is returned as a separate record.
Synopsis
setof text
loader_generate_census_script
(
text[]
param_states
, text
os
)
;
Description
Generates a shell script for the specified platform for the specified states that will download Tiger data census state
tract
, block groups
bg
, and
tabblocks
data tables, stage and load into
tiger_data
schema. Each state script is returned as a separate record.
It uses unzip on Linux (7-zip on Windows by default) and wget to do the downloading. It uses Section 4.1.5.2, “Using the Shapefile Loader” to load in the data. Note the smallest unit it does is a whole state. It will only process the files in the staging and temp folders.
It uses the following control tables to control the process and different OS shell syntax variations.
-
loader_variables
keeps track of various variables such as census site, year, data and staging schemas -
loader_platform
profiles of various platforms and where the various executables are located. Comes with windows and linux. More can be added. -
loader_lookuptables
each record defines a kind of table (state, county), whether to process records in it and how to load them in. Defines the steps to import data, stage data, add, removes columns, indexes, and constraints for each. Each table is prefixed with the state and inherits from a table in the tiger schema. e.g. createstiger_data.ma_faces
which inherits fromtiger.faces
Availability: 2.0.0
Loader_Generate_Script includes this logic, but if you installed tiger geocoder prior to PostGIS 2.0.0 alpha5, you'll need to run this on the states you have already done to get these additional tables. |
Examples
Generate script to load up data for select states in Windows shell script format.
SELECT loader_generate_census_script(ARRAY['MA'], 'windows'); -- result -- set STATEDIR="\gisdata\www2.census.gov\geo\pvs\tiger2010st\25_Massachusetts" set TMPDIR=\gisdata\temp\ set UNZIPTOOL="C:\Program Files\7-Zip\7z.exe" set WGETTOOL="C:\wget\wget.exe" set PGBIN=C:\projects\pg\pg91win\bin\ set PGPORT=5432 set PGHOST=localhost set PGUSER=postgres set PGPASSWORD=yourpasswordhere set PGDATABASE=tiger_postgis20 set PSQL="%PGBIN%psql" set SHP2PGSQL="%PGBIN%shp2pgsql" cd \gisdata %WGETTOOL% http://www2.census.gov/geo/pvs/tiger2010st/25_Massachusetts/25/ --no-parent --relative --accept=*bg10.zip,*tract10.zip,*tabblock10.zip --mirror --reject=html del %TMPDIR%\*.* /Q %PSQL% -c "DROP SCHEMA tiger_staging CASCADE;" %PSQL% -c "CREATE SCHEMA tiger_staging;" cd %STATEDIR% for /r %%z in (*.zip) do %UNZIPTOOL% e %%z -o%TMPDIR% cd %TMPDIR% %PSQL% -c "CREATE TABLE tiger_data.MA_tract(CONSTRAINT pk_MA_tract PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); " %SHP2PGSQL% -c -s 4269 -g the_geom -W "latin1" tl_2010_25_tract10.dbf tiger_staging.ma_tract10 | %PSQL% %PSQL% -c "ALTER TABLE tiger_staging.MA_tract10 RENAME geoid10 TO tract_id; SELECT loader_load_staged_data(lower('MA_tract10'), lower('MA_tract')); " %PSQL% -c "CREATE INDEX tiger_data_MA_tract_the_geom_gist ON tiger_data.MA_tract USING gist(the_geom);" %PSQL% -c "VACUUM ANALYZE tiger_data.MA_tract;" %PSQL% -c "ALTER TABLE tiger_data.MA_tract ADD CONSTRAINT chk_statefp CHECK (statefp = '25');" :
Generate sh script
STATEDIR="/gisdata/www2.census.gov/geo/pvs/tiger2010st/25_Massachusetts" TMPDIR="/gisdata/temp/" UNZIPTOOL=unzip WGETTOOL="/usr/bin/wget" export PGBIN=/usr/pgsql-9.0/bin export PGPORT=5432 export PGHOST=localhost export PGUSER=postgres export PGPASSWORD=yourpasswordhere export PGDATABASE=geocoder PSQL=${PGBIN}/psql SHP2PGSQL=${PGBIN}/shp2pgsql cd /gisdata wget http://www2.census.gov/geo/pvs/tiger2010st/25_Massachusetts/25/ --no-parent --relative --accept=*bg10.zip,*tract10.zip,*tabblock10.zip --mirror --reject=html rm -f ${TMPDIR}/*.* ${PSQL} -c "DROP SCHEMA tiger_staging CASCADE;" ${PSQL} -c "CREATE SCHEMA tiger_staging;" cd $STATEDIR for z in *.zip; do $UNZIPTOOL -o -d $TMPDIR $z; done : :