Back to Main Page

1. Unpack raw shapefiles

unpack data needed for QWI production

mkdir ~/workspace/postgis_tiger/qwi_tiger_current
cd ~/workspace/postgis_tiger/qwi_tiger_current
ln -s /data/production/prod/current/extiger/us/*YYYY*{state,county,cousub,cbsa,place,tract}*gz .
gunzip *.gz -f
cd ..

2. Set environment variables

This file defines a standard set of environment variables used by the geography processing modules. In order to keep distinct vintages separate there exists a schema prefix which will be appended to the beginning of the postgresql schema. For example, if 'otm2018' is set as the schema prefix, data will be stored in 'otm2018public', 'otm2018final', 'otm2018names' and so on. Set the new schema prefix and environment variables prior to running any new vintage as it will drop/overwrite existing schemas and tables. 

vi 00_setenvvars.bash

3. Load shp→ db

runtime: 2 minutes

This program loads TIGER shapefiles into the public schema. Providing a path (/path otm) and type will load all application files expected for that type. Providing a path and single layer will load a single layer.

time ./04_shapeloader.bash qwi_tiger_current qwi

4. Unpack wib csvs

unpack the csv definitions

mkdir ~/workspace/postgis_tiger/qwi_wib_csvs
cd ~/workspace/postgis_tiger/qwi_wib_csvs
ln -s /data/production/prod/current/exwib/us/*YYYY*gz .
gunzip *.gz -f
chmod u+w *.csv
cd ..

5. Load WIB Defs

runtime: 10 seconds

This program loads the wib csv definitions into the working.wib_definitions table. This process will also correct expected encoding issues in the wib csv definitions.

time ./05_wibcsvloader.bash path/to/wib_csvs/

6. Create public WIBs

runtime: 1 minute

This program creates a wib table of the component parts from county, county sub, and place tables in the prefixed public schema. A scratch table called working.wib_geometry is also used.

time ./06_wibshape.bash wib

7. Validate Schemas

This program validates the table schemas in postgres vs the csv schemas in the ./schemas directory. The postgis tables checked are in the otmgeo database. The schemas verified are of raw tables only - those loaded in the public schema. Check that the output lists the expected tables as "validated". Any errors that occur here will require manual adjustment of downstream processes.

./07_checkschemas_qa.bash [otm|qwi]

8. Cleanup

Remove unpacked files

rm -rf ~workspace/postgis_tiger/qwi_tiger_current
rm -rf ~workspace/postgis_tiger/qwi_wib_csvs

9. Project Geography

runtime: process dependent

This program projects tables in the public schema into new tables in the working schema. All non-geomtry data is copied over unchanged. A set of predefined tables and projections are associated with the 'otm' and 'qwi' keywords but single layers can be projected as needed. See the program help instructions for more info.

time ./08_project.bash [otm|qwi]

10. Remove unpopulated

runtime: 6 minutes

This program removes unpopulated and unused areas from the geography tables, either all tables or individual. Guam, American Samoa, and the volcanic Hawiian chain are removed. A working.invalid table is created that contains all of the geographies to be deleted. This table persists so it can be used to delete elements from the tables that created it.

The block definitions for the AK islands is now required. Located at: ./input_data/ak_island_blocks.csv

time ./10_removeunpopulated.bash qwi

11. Create the formatted names

runtime: 1 second

Create the formatted names for all OnTheMap layers.

time ./15_createnames.bash qwi

12. Generate water mask

runtime: 20 minutes

Generate the coastal mask from the difference between the CBF and TIGER.

Important
 The CBF layer is hardcoded as it’s not released on the same schedule as tiger. Verify the most recent CBF release and update the year reference in the program below if necessary. 
time ./17_generatecbfwatermask.bash qwi

13. Finalize the layer tables

runtime: 8 hours

Create a final version of all polygon layer tables in the expected layout.

time ./18_finalizegeo.bash qwi

14. Export shapefiles

runtime: 1 minute

Export all the shapefiles for QWI and J2J. This process generates checksums and gzips the output files.

time ./24_export.bash /some/folder qwi