Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

The AVS addresses are imported into EAS via a one time ETL.
Once EAS goes live, this ETL will be turned off.
Here I describe in general the processing that occurs during the ETL.

You can see the most of the code at these URLs

http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_init_avs_addresses_ddl.sql
http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_load_ddl.sql
http://code.google.com/p/eas/source/browse/trunk/etl/src/commands.py#1799
http://code.google.com/p/eas/source/browse/#svn%2Ftrunk%2Fetl%2Fsql%2Favs_load

but the execution path is not trivial.

In any case here I walk through the process mostly in english.
I will try to call out the places where I have to generalize.

We start by doing to blanket validations and standardizing some values.
This is done in this db proc: http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_init_avs_addresses_ddl.sql

and includes the following

'invalid street number suffix'
The domain values are here http://code.google.com/p/eas/source/browse/trunk/etl/sql/migrate_1-0_beta_to_1-0_prod/d_address_base_number_suffix_ddl.sql

'street name does not exist'
The domain values are specified by DPW.
We do not use fuzzy string matching.

'street suffix does not exist in street dataset'
The domain values are specified by DPW.
We do not use fuzzy string matching.

'street - street suffix combination does not exist'
The domain values are specified by DPW.
We do not use fuzzy string matching.

'referenced parcel has no geometry'
parcel data from DPW

'no matching block - lot'
parcel data from DPW

'block lot values are inconsistent'
Check for consistency across the columnes block, lot, and block-lot.

'length of concatenated unit num exceeds 10'
We concatenate avsa.unit and avsa.unit_sfx using http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_concatenate_unit_ddl.sql
The result must fit into char 10.

'invalid end date value'
Some of the end date values cannot be cats into the date type.

We also standardize dates, "unit number" values, and street number suffixes.

At this point we have "excepted" addresses that we know we cannot process.
We take each remaining address and try to load it into EAS.
This proceeds from the base address, to the unit address, to the unit address - parcel link.
This is detailed here in http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_load_ddl.sql

  • No labels