EM Geography Standard Operating Procedure

This process is nearly identical to the OnTheMap process as the requirements are very similar. To reduce code complexity the full set of LODES geography is used in the EM process but deleted at the end. In this way there are less chances for divergence in handling the data. These OTM attributes are dropped from the output data during export. EM specific instructions are documented below.

runtime: 10 minutes

Unpack faces data needed for xwalk production. The full set of faces and facesah are used in creating the block crosswalk files. This step loads the raw data into the database.

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.

runtime: 3 hours

This program loads the faces or facesah shape and dbf files into the prefixed public postgres schema. All files in the provided directory that match the form of faces.shp or facesah.dbf will be loaded.

runtime: 3.5 hours

This program takes the raw faces data in the prefixed public schema and formats the fields into crosswalkable identifiers. Fields are given 9 flags and state fips codes if necessary.

runtime: 10 minutes

Unpack the shape data needed for processing the full suite of polygons and crosswalks. As of this writing the needed tables are identical to those required for OnTheMap although this can change and be trimmed in future iterations.

runtime: 30 minutes

This program loads TIGER shapefile 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.

unpack the wib csv data

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.

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.

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.

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.

runtime: 10 seconds

This program removes undefined congressional districts, state legislative districts (upper and lower), and county subdivisions

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

runtime: 20 seconds

This program creates the schools layer from the unsd and elsd tables in the prefixed working schema

runtime: 1 second

This program adjusts names on individual layers. The current iteration only makes adjustments to the mil layer.

runtime: 3 days

This program creates the takes the faces data and chooses the centermost face on a block to use for an internal point. The result data, block_xwalk_intpt in the prefixed working schema, represents the block level data. All non-centermost faces have been deleted in this file.

runtime: 20 seconds

Verify that this is the correct congress session, then download the CD116 Block Splits pdf file and convert to a csv of the form:

Use the following commands to load and patch the crosswalk with the updated congressional districts.

Create the formatted names for all OnTheMap layers.

runtime: 14 hrs

Take the exoids from the crosswalk and update the references in each layer table

runtime: 45 minutes

runtime: 20 minutes

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

runtime: 1 day

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

runtime: 1.5 hours

Create a directory to store the final crosswalk csv and export all relevant columns. There are a few different versions of the crosswalk available. Choose as needed.

runtime: 15 minutes

Export all the shapefiles for EM needed by the acs_xwalk. This process generates checksums and gzips the output files.

Export all the shapefiles for EM needed by the acs_xwalk. This process generates checksums and gzips the output files.

Once all layers are complete, hand ownership of tables over to the otmgeo user