| Table of Contents |
|---|
...
| 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. | 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. | Once per Bulk Loader process | 90 minutes | 15 minutes | |
| Geocode and filter | Geocoding | Geocode the set and filter further based on the 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 multiple 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 the entire batch or each subset batch through the Bulk Loader. | 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. | PostgreSQL / pgAdmin | One or more batches for each Bulk Loader process | 1 hour per batch | 5 minutes per batch |
...
| Anchor | ||||
|---|---|---|---|---|
|
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.
...
Anchor stage3 stage3
Stage 3 - Geocode and filter
| stage3 | |
| stage3 |
-
Step 3.1 - Geocode source dataset
...
Anchor stage4 stage4
Stage 4 - Export shapefile - full set (single batch) or subset (multiple batches)
| stage4 | |
| stage4 |
| Note | ||
|---|---|---|
| ||
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:
For medium-to-large datasets (input sets with over 1,000 records) it is recommended that the Bulk Loading process be run in batches over several days or weeks. Reminder! It is required that the process first be run on a development server to assess the implications of the operation. The remaining steps will document a single batch iteration. Repeat these steps in a multi-batch process. |
...
Anchor stage5 stage5
Stage 5 - Run the Bulk Loader
| stage5 | |
| stage5 |
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
| stage6 | |
| stage6 |
-
Step 6.1 - Archive exceptions
...
- Get all the base records added to the EAS during the Bulk Loader operation.
Query the
public.address_basetable on the newchange_request_idvalue.Code Block language sql firstline 1 title address_base linenumbers true SELECT activate_change_request_id, address_id, public.address_base.* FROM public.address_base, public.addresses WHERE public.address_base.address_base_id = public.addresses.address_base_id AND public.addresses.address_base_flg = TRUE AND public.addresses.activate_change_request_id = <change_request_id>;
- Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
- For example,
R:\Tec\..\Eas\_Task\path\to\archive\bulkloader_YYYYMMDD\bulkloader\batch_NNN\address_base.csv
- For example,
- Extract sample base address from the output
- Pick a random record from the results. Gather the value in the address_base_id field.
- Construct a URL from this value like this: http://eas.sfgov.org/?address=NNNNNN
- Where NNNNNN is the value from the address_base_id field.
- Make note of this URL for use in Step 7 when testing EAS after services are restored.
- Artifacts
- address_base.csv - All the base records added to the EAS during the Bulk Loader operation.
...
-
Step 7.6 - Archive artifacts
- List of artifacts
- address_base.csv
- address_extract.csv
- addresses.csv
- bulk_loader_CLI_output.txt
- change_request_id.csv
- df.txt
- exception_text.csv
- exception_text_counts.csv
Contents of progress and summary artifact, BulkLoader_Process_YYYYMMDD.xlsx
Progress - This sheet contains a table of relevant totals for each batch
Batch number
Batch date
Input record counts
New base record counts
New unit record counts
Sample addresses
Email Jobs - This sheet contains a table of details related to the weekly 'Address Notification Report' automated email job
Batch range
Record count in batch range
Email Timestamp
Total record counts in email
Subtotal of records generated as a result of the Bulk Loader
Size of email attachment
Batch N - This sheet tracks the before and after record counts for all tables in the EAS database. There is a sheet for each batch loaded. Within each sheet is a section for the 'before' records, a section for the 'after' record counts, and a 'diff' column showing the change in record counts.
END OF STEPS
Notes
| Anchor | ||||
|---|---|---|---|---|
|
...