Page Comparison

Table of Contents
Overview

...

Stage Number	Stage	Category	Summary	Environment	Iterations	Estimated Person Time	Estimated Computer Time
1	Import and parse reference dataset (Optional)	Parsing	This optional step in the Bulk Loader process is to cross check an address for a match in a reference data set. If a source address is found in the reference dataset the address makes it to the next step. If not found the address is put aside in an exclusion set for later review.	Python 3 PostgreSQL / pgAdmin	Once per Bulk Loader process	1 hour	10 minutes
2	Import, parse and filter source dataset	Parsing	Import the dataset destined for the EAS. Parse and filter the set.	Python 3 PostgreSQL / pgAdmin	Once per Bulk Loader process	90 minutes	15 minutes
3	Geocode and filter	Geocoding	Geocode the set and filter further based on geocoder score and status.	ArcMap	Once per Bulk Loader process	1 hour	5 minutes
4	Export full set (single batch) or subset (multiple batches)	Geocoding	For large datasets, create one of many subsets that will be run through the Bulk Loader in many batches.	ArcMap	One or more batches for each Bulk Loader process	30 minutes per batch	5 minutes per batch
5	Bulk Load batch (full set or subset)	Bulk Loading	Run each subset batch through the Bulk Loader.	EAS <environment>⁽⁺⁾ PostgreSQL / pgAdmin	One or more batches for each Bulk Loader process	1 hour per batch	5 minutes per batch
6	Extract results	Bulk Loading	Extract and archive the list of addresses that were added to the EAS . Also archive the unique EAS 'change request id' associated with this batch. Also archive the addresses that were rejected by the Bulk Loader in this batch.	PostgreSQL / pgAdmin	One or more batches for each Bulk Loader process	1 hour per batch	5 minutes per batch
7	Cleanup and Restoration	Bulk Loading	Clean up database, restore services and in the event of a failure, restore from backup.		One or more batches for each Bulk Loader process

...

Anchor
stage1
stage1
Stage 1 - Import and parse reference dataset (Optional)

This optional stage is run once per Bulk Loader process. This stage can be skipped if the reference dataset is already available or if the optional 'filter by reference' step is skipped.

...

Script Name (*) csv2pg.py
URL https://bitbucket.org/sfgovdt/sfgis-general-address-parser/src/master/csv2pg.py
Important arguments
1. input_file - The relative path to the raw CSV file.
2. output_table - The name of the table for the imported records.

Example usage

Import CSV file into PostgreSQL table (^++<odbc>)

Code Block

language	bashtext
firstline	1
title	csv2pg
linenumbers	true

python csv2pg.py 
  --odbc_server=<odbc-server> -- Name or IP address of the database server, e.g. localhost
  --odbc_port=<odbc-port> -- Port of the database server, e.g. 5432
  --odbc_database=<odbc-database> -- Name of the database, e.g. awgdb
  --odbc_uid=<odbc-uid> -- Database user name
  --odbc_pwd=<odbc-pwd> -- Database user password
  --input_file=./path/to/raw_reference_file.csv
  --output_table=reference_raw

Output table
1. This step generates a new table. The name of the new table is passed as a required command line argument.
Output fields
1. All columns in the input CSV file are imported as fields in the new table.
2. sfgisgapid: a new serial, not null, primary key
Artifacts (**)
1. reference_raw.csv - The input CSV table serves as the artifact for this step.

...

Script Name (*) csv2pg.py
URL https://bitbucket.org/sfgovdt/sfgis-general-address-parser/src/master/csv2pg.py
Important arguments
1. input_file: The relative path to the raw CSV file.
2. output_table: The name of the table for the imported records.

Example usage

Import CSV file into PostgreSQL table (^++<odbc>)

Code Block

language	text
firstline	1
title	csv2pg
linenumbers	true

python csv2pg.py \
  --odbc_server=<odbc-server> \
  --odbc_port=<odbc-port> \
  --odbc_database=<odbc-database> \
  --odbc_uid=<odbc-uid> \
  --odbc_pwd=<odbc-pwd> \ 
  --input_file=./path/to/raw_source_file.csv \
  --output_table=addresses_raw

Output table
1. This step generates a new table. The name of the new table is passed as a required command line argument.
Output fields
1. All columns in the input CSV file are imported as fields in the new table.
2. sfgisgapid: a new serial, not null, primary key
Artifacts (**)
1. addresses_raw.csv - The input CSV table serves as the artifact for this step.

...

Anchor
stage3
stage3
Stage 3 - Geocode and filter

Step 3.1 - Geocode source dataset

...

Anchor
stage4
stage4
Stage 4 - Export full set (single batch) or subset (multiple batches)

Note

title	A Note about Batches

Stages 4, 5 and 6 can be run one time with the results from Stage 3, or they can be run in multiple batches of subsets.

A major consideration of when to run the full set at once versus in batches is the number of records being Bulk Loaded.

The size of each Bulk Loader operation affects the following aspects of the EAS:

The disk space consumed by the database server
The EAS user interface section that lists addresses loaded in a given Bulk Loader operation
The weekly email attachment listing new addresses added to the EAS

For medium-to-large datasets (input sets with over 1000 records) it is recommended to run the process on a development server and assess the implications of the operation. Where appropriate, perform the Bulk Loading process in batches over several days or weeks.

The remaining steps will document one example iteration of a multi-batch process.

...

Anchor
stage5
stage5
Stage 5 - Run the Bulk Loader

For a complete set of steps and background about the Bulk Loader, see also Running the Bulk Loader, a page dedicated to its input, operation and results.

...

Anchor
stage6
stage6
Stage 6 - Extract results

Step 6.1 - Archive exceptions

...

If the Bulk Loader Process was run on the production server then restore services
1. Turn on production-to-replication service
  - TODO: add steps
2. Turn on downstream database propagation service(s)
  - Resume downstream replication to internal business system database (SF PROD WEB).
    Code Block
    language text
    firstline 1
    title start xmit
    sudo /var/www/html/eas/bin/xmit_change_notifications.bsh start
3. Enable front-end access to EAS
  - Place the Web servers into live mode (SF PROD WEB, DR PROD WEB).
    Code Block
    language bash
    linenumbers true
    cd /var/www/html sudo ./set_eas_mode.sh LIVE

...

Versions Compared

Old Version 100

New Version 101

Key

Table of Contents
Overview

Anchor
stage1
stage1
Stage 1 - Import and parse reference dataset (Optional)

Anchor
stage3
stage3
Stage 3 - Geocode and filter

Step 3.1 - Geocode source dataset

Anchor
stage4
stage4
Stage 4 - Export full set (single batch) or subset (multiple batches)

Anchor
stage5
stage5
Stage 5 - Run the Bulk Loader

Anchor
stage6
stage6
Stage 6 - Extract results

Step 6.1 - Archive exceptions

Turn on production-to-replication service

Turn on downstream database propagation service(s)

Enable front-end access to EAS

Page Comparison

Versions Compared

Old Version 100

New Version 101

Key

Anchorstage1stage1Stage 1 - Import and parse reference dataset (Optional)

Anchorstage3stage3Stage 3 - Geocode and filter

Step 3.1 - Geocode source dataset

Anchorstage4stage4Stage 4 - Export full set (single batch) or subset (multiple batches)

Anchorstage5stage5Stage 5 - Run the Bulk Loader

Anchorstage6stage6Stage 6 - Extract results

Step 6.1 - Archive exceptions

Turn on production-to-replication service

Turn on downstream database propagation service(s)

Enable front-end access to EAS

Anchor
stage1
stage1
Stage 1 - Import and parse reference dataset (Optional)

Anchor
stage3
stage3
Stage 3 - Geocode and filter

Anchor
stage4
stage4
Stage 4 - Export full set (single batch) or subset (multiple batches)

Anchor
stage5
stage5
Stage 5 - Run the Bulk Loader

Anchor
stage6
stage6
Stage 6 - Extract results