...
You can see the most of the code at these URLs
...
...
...
...
...
but the execution path is not trivial.
...
We start by doing to blanket validations and standardizing some values.
This is done in this db proc:
and includes the following
'invalid street number suffix'
The domain values are here
'street name does not exist'
The domain values are specified by DPW.
We do not use fuzzy string matching.
...
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
The main load proc calls into these procs in this order
- http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_process_address_base_ddl.sql
- http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_process_address_unit_ddl.sql
- http://code.google.com/p/eas/source/browse/trunk/etl/sql/avs_load/f_process_address_parcel_link_ddl.sql
With the most interesting work being done by f_process_address_base.
I will describe this now and will do a good bit of generalizing.
We try to use an existing adddress and create a new one if necessary.
If we create a new one, we have to find the best matching street segment.
This is easily the most complicated process.
The segment must be within 500 feet of the specified parcel, and the street name and street suffix must match the source data.
Again, we do not use fuzzy string matching (such as Levenshtein etc).
If all goes well, we insert the base address.
There are various constraints that protect data integrity, specifically to prevent duplicate active base addresses.
You can see the trigger enforced constraints here
If make it past finding or creating the base address, we insert the unit address if it is specified.
Again, we do not allow multiple active duplicates.
Finally, we insert an "address - parcel link".
Here again we do not allow duplicates.
At the end of all this processing we compile results at the summary and detail detailed level and provide a QA report on the data.
An example of this attached to this page.