Page Comparison

Table of Contents

Overview

...

Table of Contents

Overview

• The Bulk Loader is a process that is used to add several new addresses to the EAS at one time.
• There are several stages that make up the Bulk Loader process, outlined below in Summary and Details.

...

...

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.

...

Anchor
stage3 stage3

Stage 3 - Geocode and filter

•  

  Step 3.1 - Geocode source dataset

...

Anchor
stage4 stage4

Stage 4 - Export shapefile - 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. Archive the entire address_extract table.

   1. Use a query tool such as pgAdmin to query and save the table as a CSV file.

      Code Block
      language sql firstline 1 title address_extract linenumbers true
      SELECT * FROM bulkloader.address_extract;

      
      

   2. Save the file in the network folder dedicated to artifacts for the Bulk Loader iteration.
      1. Save as artifact address_extract.csv
2. Archive the addresses that raised exceptions during the Bulk Loader process

   1. Query subtotals

      Code Block
      language sql firstline 1 title exception_text_counts linenumbers true
      SELECT exception_text, Count(*) FROM bulkloader.address_extract GROUP BY exception_text ORDER BY exception_text;

      1. Save artifact as artifact  exception_text_counts.csv

   2. Query all exception text records

      Code Block
      language sql firstline 1 title exception_text linenumbers true
      SELECT * FROM bulkloader.address_extract WHERE NOT(exception_text IS NULL) ORDER BY exception_text, id;

      1. save Save artifact as artifact  exception_text.csv
3. Artifacts
   1. address_extract.csv - Results of every address submitted to the Bulk Loader.
   2. exception_text_counts.csv - Counts of the records that were not loaded due to the error indicated in the 'exception_text' field.
   3. exception_text.csv - Subset of the just the records that were not loaded due to the error indicated in the 'exception_text' field

...

1. Get the unique EAS change_request_id created by the Bulk Load operation. The value of <change_request_id> will be used in the next steps to count addresses added to the EAS.
   

   1. Query the 'public.change_requests' table for the new 'change_request_id' value.

      Code Block
      language sql firstline 1 title change_request_id linenumbers true
      SELECT change_request_id FROM public.change_requests WHERE requestor_comment LIKE 'bulk load change request' ORDER BY change_request_id DESC LIMIT 1;

      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\

      ;

      
      

   2. Save artifact as change_request_id.csv
2. Artifacts
   1. change_request_id.csv - The unique EAS change_request_id created by the Bulk Load operation.

...

1. Get all the address records (including units) added to the EAS during the Bulk Loader operation..
   

   1. Query the public.addresses table on the new change_request_id value.

      Code Block
      language sql firstline 1 title addresses linenumbers true
      SELECT * FROM public.addresses WHERE activate_change_request_id = <change_request_id>;

      
      

   2. 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\artifact as addresses.csv
2. Extract sample unit address from the output
   1. Pick a random record from the results where unit_num is not NULL. Gather the value in the address_base_id field.
   2. 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.
   3. Make note of this URL for use in Step 7 when testing EAS after services are restored.
3.  Artifacts
   1. addresses.csv - All the address records (including units) added to the EAS during the Bulk Loader operation.

...

1. Get all the base records added to the EAS during the Bulk Loader operation.
   

   1. Query the public.address_base table on the new change_request_id value.

      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>;

      
      

   2. 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\artifact as address_base.csv
2. Extract sample base address from the output
   1. Pick a random record from the results. Gather the value in the address_base_id field.
   2. 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.
   3. Make note of this URL for use in Step 7 when testing EAS after services are restored.
3. Artifacts
   1. address_base.csv - All the base records added to the EAS during the Bulk Loader operation.

...