Page Comparison

Table of Contents

Overview

...

...

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 (Step 2.5) is skipped.

•  

  Step 1.1 - Import reference dataset

1. Script Name (*) csv2pg.py
2. URL https://bitbucket.org/sfgovdt/sfgis-general-address-parser/src/master/csv2pg.py
3. 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.
4. Example usage

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

      Code Block
      language text 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

      
      

5. Output table
   1. This step generates a new table. The name of the new table is passed as a required command line argument.
6. 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
7. Artifacts (**)
   1. reference_raw.csv - The input CSV table serves as the artifact for this step.

...

1. Script Name (*) pg2bulkloader.py
2. URL https://bitbucket.org/sfgovdt/sfgis-general-address-parser/src/master/pg2bulkloader.py
3. Important arguments
   1. input_table - The name of the table containing addresses to be geocoded.
   2. output_file_name - The name of the CSV created from the input table.
   3. input_source - The text associated with the data source of this batch of addresses. The value entered here appears in the EAS user interface (under the source of a given base address or unit).  This field has a 32 character limit. Exceeding 32 characters will crash cause the Bulk Loader !to crash.
4. Example usage

   1. Create table addresses_to_geocode

      Code Block
      language sql firstline 1 title addresses_to_geocode linenumbers true
      -- Select, order and save as 'addresses_to_geocode' CREATE TABLE addresses_to_geocode AS SELECT * FROM addresses_in_reference ORDER BY street_name, street_name_pre_directional, street_name_pre_type, street_name_post_type, street_name_post_directional, address_number, address_number_suffix, subaddress_type, subaddress_identifier, place_name

      
      

   2. Add counter field

      Code Block
      language sql firstline 1 title counter linenumbers true
      -- Add and index a counter field ALTER TABLE addresses_to_geocode DROP COLUMN IF EXISTS counter; ALTER TABLE addresses_to_geocode ADD COLUMN counter SERIAL; CREATE INDEX ON addresses_to_geocode (counter);

      
      

   3. Export PostgreSQL table to CSV file for Bulk Loading (^++<odbc>)

      Code Block
      language text firstline 1 title pg2bulkloader linenumbers true
      python pg2bulkloader.py \ --odbc_server=<odbc-server> \ --odbc_port=<odbc-port> \ --odbc_database=<odbc-database> \ --odbc_uid=<odbc-uid> \ --odbc_pwd=<odbc-pwd> \ --input_table=addresses_to_geocode \ --output_file_name=./output/addresses_to_geocode \ --input_source='Imported Addresses (20XX-XX-XX)' \ --limit=-1

      
      

   4. Archive a copy of table addresses_to_geocode to artifact addresses_to_geocode.csv
5. Output table
   1. addresses_to_geocode
6. Output file
   1. This step generates a new CSV file. The name of the new file is passed as a required command line argument.
7. Artifacts (**)
   1. addresses_to_geocode.csv - A CSV file of addresses ready for geocoding.

Anchor
stage3 stage3

Stage 3 - Geocode and filter

•  

  Step 3.1 - Geocode source dataset

1. Input - addresses_to_geocode.csv
2. Output - addresses_geocoded.shp

3. Detailed Substeps

   1. Create folder for storing all input and output artifacts for this iteration of the Bulk Loader process.

      • For example, R:\Tec\..\Eas\_Task\2018_2019\path\to\archive\bulkloader_process_YYYYMMDD

   2. Create new ArcMap map document (ArcMap 10.6.1) 

      • For example, bulkloader_YYYYMMDD.mxd

   3. Add streets (optional)

      • See StClines_20190129.shp in R:\Tec\...\Eas\_Task\2018_2019\20181128_248_DocumentBulkLoader\Data

   4. Create Personal Geodatabase

      1. Right click Home in Folder Connections in Catalog

      2. Select New → Personal Geodatabase

   5. Import CSV into File Geodatabase

      1. Right-click new personal geodatabase: Import → Table (single)

      2. Browse to addresses_to_geocode.csv

      3. Specify output table addresses_to_geocode

      4. Click OK and time on stopwatch. Wait up to 5 minutes for TOC to update.

   6. Geocode with Virtual Address Geocoder
      
      1. Right-click the table addresses_to_geocode in the ArcMap table of contents and select Geocode Addresses
      2. In the dialog 'Choose an address geocoder to use' select 'Add'. 
         1. Browse to and select R:\311\...\StClines_20150729_VirtualAddressLocator
         2. Click 'OK'
      3. Under 'Address Input Fields' select 'Multiple Fields'
         1. Street or intersection: address
         2. ZIP Code: zip
      4. Under Output
         1. Click folder icon. In popup change 'Save as type' to 'Shapefile'.

         2. Save shapefile in path dedicated to artifacts for this Bulk Loader Progress

            1. For example, R:\Tec\..\Eas\_Task\2018_2019\path\to\archive\bulkloader_YYYYMMDD\geocoder\

            2. addresses_geocoded.shp

      5. Time on stopwatch and take note of execution time.

4. Artifacts (**)

   1. bulkloader_process_YYYYMMDD.mxd

   2. addresses_geocoded.shp 

...

Anchor
stage4 stage4

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

...

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

...

1. 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

        
        

...