AVS-EAS field mapping
- Paul McCullough (Unlicensed)
To help clarify the purpose of each field, I use the FGDC Street Address Data Standard.
Click here is the latest and most complete reference or you can just take a look at draft 2 which is usually adequate.
base_address_id
EAS Field: address_base.base_address_id (int)
XML Field: base_address_id
FGDC Field: na
AVS Field: na
Example:
Comment: EAS primary key
base_address_prefix
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: keep this field but disable it for 1.1
base_address_num
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.
6) add a new char 10 column to AVS to hold the full number suffix
(this is a naive attempt since I do not know DBI business)
Add trigger code to keep the existing number suffix column in sync with the new number suffix column.
When there is an insert or update on new number suffix column
if there is one char, copy it into the existing number suffix column.
if there is more than one char, copy a special char into the existing number suffix column.
When there is an insert or update on existing number suffix column take the character and put it into the new number suffix column.
For this to work there must be a way for the user to disambiguate apparently identical columns.
longitude
EAS Field: address_base.geometry.longitude (double)
XML Field: base_address/longitude
FGDC Field: address longitude (double)
AVS Field:
Example:
Comment:
latitude
EAS Field: address_base.geoemtry.latitude (double)
XML Field: base_address/latitude
FGDC Field: address latitude (double)
AVS Field:
Example:
Comment:
street name
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
street suffix
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
base addresss create_tms
EAS Field: address_base.create_tms (datetime)
XML Field: base_address/create_tms
FGDC Field:
AVS Field:
Example:
Comment:
address base last_change_tms
EAS Field: address_base.last_change_tms (datetime)
XML Field: base_address/last_change_tms
FGDC Field:
AVS Field:
Example:
Comment:
address base retire_tms
EAS Field: address_base.retire_tms (datetime)
XML Field: base_address/retire_tms
FGDC Field:
AVS Field:
Example:
Comment:
address address_id
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
addresses unit_num_prefix
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
addresses unit_num
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.
addresses unit_num_suffix
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
addresses unit_type_description
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?
addresses disposition_code
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?
addresses mailable_flg
EAS Field: addresses.mailable_flg (boolean)
XML Field: unit_address/mailing
FGDC Field: na
AVS Field: na
Example:
Comment:
addresses create_tms
EAS Field: addresses.create_tms (datetime)
XML Field: unit_address.create_tms
FGDC Field: na
AVS Field:
Example:
Comment:
addresses last_change_tms
EAS Field: addresses.last_change_tms (datetime)
XML Field: unit_address.last_change_tms
FGDC Field: na
AVS Field:
Example:
Comment:
addresses retire_tms
EAS Field: addresses.retire_tms (datetime)
XML Field: unit_address.retire_tms
FGDC Field: na
AVS Field: na
Example:
Comment:
address_x_parcel parcel block_num
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:
address_x_parcel parcel lot_num
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?
address_x_parcel parcel blk_lot
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:
address_x_parcel create_tms
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
address_x_parcel retire_tms
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
address_x_parcel retire_tms
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
structure_number character varying(3)
EAS Field:
XML Field:
FGDC Field: na
AVS Field: structure_number character varying(3)
Example:
Comment:
TODO: determine mapping
address_kind
EAS Field:
XML Field:
FGDC Field: na
AVS Field: address_kind character varying(11)
Example:
Comment:
TODO: determine mapping
address_type
EAS Field:
XML Field:
FGDC Field: na
AVS Field: address_type character varying(11)
Example:
Comment:
TODO: determine mapping
avs_street_status
EAS Field:
XML Field:
FGDC Field: na
AVS Field: avs_street_status character varying(10)
Example:
Comment:
TODO: determine mapping