Page Comparison

...

address	block	lot	unit	unit_sfx	st_num	st_num_sfx	st_name	st_suffix*	st_type	st_prefix
1 SOUTH VAN NESS AVE	3506	001			1		VAN NESS		AVE	SOUTH
20 25TH AVE NORTH	1301	018			20		25TH	NORTH	AVE
127 A CENTRAL AVE	1233	007			127	A	CENTRAL		AVE
5900 03RD ST 2217	5431B	093	2217		5900		03RD		ST
100 BROADWAY	0141	003B			100		BROADWAY
200 MAIN ST (APARTMENT) 2D	3739	008	2	D	200		MAIN		ST

...

address	block	lot	unit	unit_sfx	street_number	street_number_sfx	street_name	street_sfx*	street_type
1 SOUTH VAN NESS AVE	3506	001			1		SOUTH VAN NESS		AVE
20 25TH AVE NORTH	1301	018			20		25TH	NORTH	AVE
127 A CENTRAL AVE	1233	007			127	A	CENTRAL		AVE
5900 03RD ST 2217	5431B	093	2217		5900		03RD		ST
100 BROADWAY	0141	003B			100		BROADWAY
200 MAIN ST (APARTMENT) 2D	3739	008	2	D	200		MAIN		ST

...

Addresses that were successfully bulk-loaded will have a date in the field load_tms and a NULL value in the field exception_text.

Code Block

language	sql

-- Count records successfully bulk-loaded:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);

-- View records successfully bulk-loaded:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(load_tms IS NULL) AND (exception_text IS NULL);

The presence of exception text indicates a problem with the source data.

Code Block

language	sql

-- Count records with exception:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);

-- View records with exception:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(exception_text IS NULL);

-- View totals for each distinct 'exception_text' value
SELECT exception_text, Count(*) 
FROM bulkloader.address_extract
GROUP BY exception_text
ORDER BY exception_text;

To rectify bulk-load exceptions, do not correct any exception records in the output table bulkloader.address_extract. Instead, correct the corresponding records in the source data and then re-run the Bulk Loader.

If block and lot values were included in the input data, a value should be found in the field address_x_parcel_id. Run the query below to find any records that had block and lot input values but did not get assigned a value for address_x_parcel_id.

Code Block

language	sql

-- Count records where block and lot were provided but no address_x_parcel was assigned:
SELECT COUNT(*) FROM bulkloader.address_extract
WHERE (NOT (block IS NULL) OR NOT (block IS NULL))
AND address_x_parcel_id IS NULL;

Base addresses that were submitted by the Bulk Loader but were already in the EAS are indicated by values in the fields address_base_id and load_tms, and NULL values in the fields std_unit, street_segment, zone_id, address_id and exception_text.

Code Block

language	sql

-- Count base addresses that were already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (std_unit IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (address_id IS NULL)
      AND (exception_text IS NULL);

-- View base addresses that were already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT(address_base_id IS NULL)
      AND NOT(load_tms IS NULL)
      AND (std_unit IS NULL)
      AND (street_segment_id IS NULL)
      AND (zone_id IS NULL)
      AND (address_id IS NULL)
      AND (exception_text IS NULL);

Count and view new base addresses by looking for non-NULL values in the street_segment_id field. Cross-check with the 'public' schema by counting new 'public.address_base' records as shown below in Evaluate 'public' schema.

Code Block

language	sql

-- Count base addresses that were not already in the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL);

-- View base addresses that were not already in the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (street_segment_id IS NULL);

Evaluate records with non-NULL address_id values to count/view unit addresses that were added to the EAS.

Code Block

language	sql

-- Count unit addresses added to the EAS:
SELECT COUNT(*)
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL);

-- View unit addresses added to the EAS:
SELECT *
FROM bulkloader.address_extract
WHERE NOT (address_id IS NULL);

...

Find the change_request_id of the most recent bulk load.

Code Block

language	sql

-- Get the change_request_id of the most recent bulk load:
SELECT change_request_id
FROM public.change_requests
WHERE requestor_comment LIKE 'bulk load change request'
ORDER BY change_request_id DESC
LIMIT 1;

-- Make note of this id for the queries in the subsequent steps.

Count and view new base addresses associated with the change request.

Code Block

language	sql

-- Count new base addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND unit_num IS NULL;

-- View new base addresses
SELECT *
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND unit_num IS NULL;

Count and view new unit addresses associated with the change request.

Code Block

language	sql

--- Count new unit addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND NOT unit_num IS NULL;

-- View new unit addresses
SELECT *
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here
AND NOT unit_num IS NULL;

Count and view all new 'public.addresses' records associated with the change request.

Code Block

language	sql

-- Count all new addresses
SELECT COUNT (*)
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here;

-- View all new addresses
SELECT *
FROM public.addresses
WHERE activate_change_request_id = change_request_id_goes_here;

Count and view new 'public.address_base' records added to the EAS. Cross-check with the 'bulkloader' schema as shown above in Evaluate 'bulkloader' schema.

Code Block

language	sql

-- Count new 'public.address_base' records
SELECT COUNT(*) 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_goes_here;

-- View new 'public.address_base' records
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_goes_here;

Optional: count all records in the database for use in a before/after record count comparison.
Code Block
language sql
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY schemaname,relname,n_live_tup;

...

Versions Compared

Old Version 21

New Version Current

Key