Here is the mapping between AVS and EAS.
To help clarify the purpose of each field, I use the FGDC Street Address Data Standard.
Here is the latest and most complete reference or you can just take a look at draft 2 which is usually adequate.
Click Here for number suffix.
Click Here for unit number.
EAS Field: address_base.base_address_id (int)
XML Field: base_address_id
FGDC Field: na
AVS Field: na
Example:
Comment: EAS primary key
EAS Field: address_base.base_address_prefix (char 10)
XML Field: base_address/base_number_prefix
FGDC Field: address number prefix (text)
AVS Field: na
Example:
Comment: EAS has no data in this field
TODO: add field to AVS?
EAS Field: address_base.base_address_num (int)
XML Field: base_address/base_number
FGDC Field: address number (int)
AVS Field: AVS_ADDRESSES.STREET_NUMBER (NUMBER 6)
Example:
Comment:
number suffix
EAS Field: address_base.base_address_suffix (char 10)
XML Field: base_address/base_number_suffix
FGDC Field: address number suffix (text)
AVS Field: AVS_ADDRESSES.STREET_NUMBER_SFX (VARCHAR2 1)
Example: "1/2"
Comment: MAD-122
TODO: resolve column width issue
Discussion
The issue here is that widening the AVS column would trigger significant changes in many parts of DBIs systems.
Moreover, much of that work would be tossed out when they adopt the new permitting system within 1-2 years.
Here are the alternatives
1) Change the AVS column to char 10.
As stated above, this will be expensive and the ROI will be low.
2) Column definitions stay the same in both systems...and in the interface between the systems use only the first character from the EAS field.
This will cause problem with uniqueness because "100 1 Main St" would equal "100 1/2 Main St".
3) Change EAS so that it allows only 1 character in this field.
This would work as an interim solution with the following caveats.
It is moving us in the wrong direction - away from a well know standard (FGDC).
When someone wants to create something like "100 1/2 Main St" we won't be able to support it properly.
4) Column definitions stay the same in both systems...
In the interface between the systems, we convert the EAS char 10 field into a single unique "lookup character" and use that character in the interface.
In this solution, the interface would convert "100 1/2 Main St" to "100 â Main St" where the "â" character is
- arbitrary
- generated by the interface
- will not collide with the existing ascii characters (0-127) in this field thereby supporting uniqueness properly
AVS would store the single character and displays it in the UI in the usual way.
The AVS databse will have to be able to support the insertion of unicode characters (encoding of UTF-8 or equivalent).
The downside of this solution is that for the "â" (for example) to be meaningful, the user must take an extra step look up the address to see what the "â" really means.
We can easily provide that service, but the non-meaningful character is a violation of a number of UI design principals.
That said, the existence of addresses with legitimate street number suffixes is relatively rare.
Therefore, I would anticipate that operational issues here will be a lack of familiarity of this kind of address and how to look it up, etc.
On the other hand, I think any char 1 solution here is going to have this problem.
This is because street number suffix could be "1" or "A" or "1A" or whatever.
5) change the column width in AVS but do not change the forms and reports
This is not viable. For details read on. Expanding AVS's STREET_NUMBER_SFX field to 10 characters has expected results: in some cases Oracle Forms truncates it to 1 character ("H" or "1", for "HALF" or "1/2"), in some cases, the form just fails to handle/process the address correctly, in some cases the form displays a popup that a text field is not wide enough. As mentioned before, each of the 100s of form/reports/web applications that uses addresses would need to be modified to handle the expanded field.
EAS Field: address_base.geometry.longitude (double)
XML Field: base_address/longitude
FGDC Field: address longitude (double)
AVS Field:
Example:
Comment:
EAS Field: address_base.geoemtry.latitude (double)
XML Field: base_address/latitude
FGDC Field: address latitude (double)
AVS Field:
Example:
Comment:
EAS Field: address_base.street_segment.st_name (char 29)
XML Field: base_address/street_name
FGDC Field: street name (text)
AVS Field: AVS_STREETS.STREET_NAME VARCHAR2(28)
Example:
Comment:
TODO: AVS must truncate width
EAS Field: address_base.street_segment.st_type (char 6)
XML Field: base_address/street_name_suffix
FGDC Field: street name post type text
AVS Field: AVS_STREET_SUFFIXES.STREET_SFX (VARCHAR2 2)
Example:
Comment: data type mismatch is accomodated in xml mapping
EAS Field: zone.zipcode (int)
XML Field: base_address/zipcode
FGDC Field: zip code
AVS Field: ?
Example:
Comment:
TODO: add this to EAS change notification message (and remove "jurisdiction"); map to AVS field
EAS Field: address_base.create_tms (datetime)
XML Field: base_address/create_tms
FGDC Field:
AVS Field:
Example:
Comment:
EAS Field: address_base.last_change_tms (datetime)
XML Field: base_address/last_change_tms
FGDC Field:
AVS Field:
Example:
Comment:
EAS Field: address_base.retire_tms (datetime)
XML Field: base_address/retire_tms
FGDC Field:
AVS Field:
Example:
Comment:
EAS Field: addresses.address_id (int)
XML Field: unit_address/address_id
FGDC Field: na
AVS Field: na
Example:
Comment: EAS primary key for unit address
EAS Field: addresses.unit_num_prefix (char 5)
XML Field: na
FGDC Field: na
AVS Field:
Example:
Comment:
TODO: EAS will drop this field from database schema
EAS Field: addresses.unit_num (char 20)
XML Field: base_address/unit_number
FGDC Field: unit identifier (text)
AVS Field: AVS_ADDRESSES.UNIT_SFX (VARCHAR2 10)
Example:
Comment:
TODO: change addresses.unit_num (char 20) to addresses.unit_num (char 10) (no data loss)
Discussion: We put the unit_num into the AVS unit suffix as a whole because unit_num could be any arbitrary char 10 string with or without an integer at the beginning.
EAS Field: addresses.unit_num_suffix (char 10)
XML Field: na
FGDC Field: na
AVS Field: na
Example:
Comment:
TODO: EAS will drop this field from database scehma
EAS Field: addresses.unit_type_id->d_unit_type.unit_type_description (varchar 50)
XML Field: unit_address/unit_type
FGDC Field: unit type (text)
AVS Field: na
Example: suite, apartment
Comment:
TODO: EAS will add this field to the XML - do we need to map it to an AVS field?
EAS Field: addresses.disposition_code (int)
XML Field: unit_address/disposition
FGDC Field: address lifecycle status (text)
AVS Field:
Example: provisional, offical
Comment: FGDC field is approx
TODO: do we need to map this to an AVS field?
EAS Field: addresses.mailable_flg (boolean)
XML Field: unit_address/mailing
FGDC Field: na
AVS Field: na
Example:
Comment:
EAS Field: addresses.create_tms (datetime)
XML Field: unit_address.create_tms
FGDC Field: na
AVS Field:
Example:
Comment:
EAS Field: addresses.last_change_tms (datetime)
XML Field: unit_address.last_change_tms
FGDC Field: na
AVS Field:
Example:
Comment:
EAS Field: addresses.retire_tms (datetime)
XML Field: unit_address.retire_tms
FGDC Field: na
AVS Field: na
Example:
Comment:
EAS Field: address_x_parcel.parcel.block_num (char 5)
XML Field: address_parcel_link/parcel/block
FGDC Field: na
AVS Field: AVS_STRUCTURES.BLOCK (VARCHAR2 5)
Example:
Comment:
EAS Field: address_x_parcel.parcel.lot_num (char 5)
XML Field: address_parcel_link/parcel/lot
FGDC Field: na
AVS Field: AVS_STRUCTURES.LOT (VARCHAR2 4)
Example:
Comment:
TODO: field width mismatch - widen AVS field?
EAS Field: address_x_parcel.parcel.blk_lot (char 9)
XML Field: address_parcel_link/parcel/apn
FGDC Field: na
AVS Field: na
Example:
Comment:
EAS Field: address_x_parcel.create_tms (datetime)
XML Field: address_parcel_link/create_tms
FGDC Field: na
AVS Field:
Example:
Comment:
TODO: determine mapping
EAS Field: address_x_parcel.retire_tms (datetime)
XML Field: address_parcel_link/retire_tms
FGDC Field: na
AVS Field: ???
Example:
Comment:
TODO: determine mapping
0 Comments