Address Change Publisher

preamble

On this page we, (Paul, Val, Sam) discuss the form of the address change messages.

The latest accepted revision is "example 10".
Here is a sample xml file and here is the field mapping.

The latest proposed revision is "example 11".
Here is a sample xml file.

questions/answers

Q1
From DBI's perspective, we are either going to be inserting addresses or updating addresses with your data. (Addresses are never deleted but retired on our end and yours I believe). How will we tell the difference if it is an address update or a brand new address? via an XML tag?

A1
Agreed.
Jump ahead to example 3 to see a proposed solution.
We will publish every insert and update.
A retire is a special kind of update.
While addresses can be retired, they cannot be deleted.

Q2
The first example you give below does not have an APN (what DBI refers to as block/lot). DBI cannot make use of any address without an APN (block/lot). These should be excluded until they are complete.

A2.1
This part might be interesting.
Please read this entire page to get a sense of what we need to do.
In particular, please look at example 3 as well as example 5 along with the questions posed at the end of the example.
MAD addresses can be associated with APNs or not.
By and large I think they are.

A2.2
A MAD address is mostly immutable.
You make change 3 things on an address

retire
status (provisional, offical...)
location
You may not change anything else.
Once the APNs are set, you cannot change them.
If you need to change the associated APNs, you do a retire/replace.

A2.3
We can use APN or BlkLot for this XML - your choice - most municipalities use APN.

Q3
2655 HYDE ST below has a bunch of APN's associated with it. For convenience, we would like each APN sent to us as a separate complete <address> .

A3
No problem.
Take a look at example 3 which is fairly close to what I think we want.

Q4
Also, the 2655 Hyde is broken up into units(<number>) (318), but there doesn't seem to be a connection between the unit(<number>) and the APN in the data below, but perhaps I am misreading it.

A4
My apologies, I had it wrong.
Please take a look at example 5.

Q5
Do you have a unique ID on your end for all addresses. We might store your ID on our end and/or use it to make updates easier. Also if we ever eliminate our DBIAVS completely in favor of MAD it will make it easier to transition.

A5
I will add the native primary key to the messages.

[Sam] Can you be more specific here? My understanding is that in the MAD data model, both the base address and unit address tables have "native primary keys". If that's correct, then A5 is ambiguous.

Q6
DBI does not track addresses with no ownership but MAD does.
For example, an apartment build with 10 aprtments will have 10 MAD addresses but only 1 DBI address.
HOw will we handle this?

A6
The publisher will send only addresses that have APNs.

Q7
Parsing the blk lot out of the APN is tedious, can you please just give us the blk, lot?

A7
Agreed and easily done.

Q8
Val,
As of july 28 2010, the EAS will publish address changes like this.
I originally agreed to this because this is easier for AVS consumer.
I'd like to discuss doing it this way.
Here's why. Say that during the xmit the power goes out.
When we come back up, the daemon will retransmit some or all of the messages.
This is because of the transaction scope, which in EAS is naturally at the unit address level.
I'd like to avoid sending out duplicates if at all possible.
Now, if necessary, I can add some extra code (and a table) to handle this completely on my end.
But I'd like to get your feedback.

A8
The final form is here.

Example 1

A simple example to get us started.
This example is MAD centric.
I suspect that we'll end up with something a alot different at the end of this discussion.
Here is a single family dwelling with one owner:

    <address>
        <number>14</number>
        <street>MAPLE ST</street>
        <unit>
            <base>true</base>
            <number></number>
            <disposition>official</disposition>
            <create_tms>2010-07-02 08:18:50.937000</create_tms>
            <retire_tms>None</retire_tms>
            <apns>
                <apn>1234001</apn>
            </apns>
            <action>insert</action>
        </unit>
    </address>

The "action" tag domain is

    insert
    update
    retire

I think this addresses question 1 above.

The "base" tag domain is:

    true
    false

This is a MAD artifact - DBI probably won't want it.
But let me explain the purpose.
A MAD "address" is represented using the concept of a base address and a unit address.
A base address always has one corresponding unit address.
There may be additional unit addresses or not.
If the base tag is marked true, then this unit information is directly associated with the base address.
If the base tag is marked false, then this unit information is not directly associated with the base address.

[Sam] Can you elaborate on what is meant by "directly associated" in this context? Are there additional associations possible between a unit address and a base address (besides "not direct")?

Example 2

Now in the case of an apartment bldg, say
14 MAPLE ST,
apartments a & b
MAD represents the world this way:

    <address>
        <number>14</number>
        <street>MAPLE ST</street>
        <unit>
            <base>true</base>
            <number>100</number>
            ...
            <apns>
                <apn>1234001</apn>
            </apns>
            <action>insert</action>
        </unit>
        <unit>
            <base>false</base>
            <number>a</number>
            ...
            <apns></apns>
            <action>insert</action>
        </unit>
        <unit>
            <base>false</base>
            <number>b</number>
            ...
            <apns></apns>
            <action>insert</action>
        </unit>
    </address>

There is no ownership at the unit level.
The owner is specified at the base address level.

Example 3

What I think we want an instead of the example 2 (apt building) is something like this:

message 1

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>14</number>
            <street>MAPLE ST</street>
            <unit_number>100</unit_number>
            <block>1234</block>
            <lot>001</lot>
            <apn>1234001</apn>
        </address>
    </address_change>
</xml>

Example 4

And reworking example 1 (single family) to fit the model shown in example 3, we have this:

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>14</number>
            <street>MAPLE ST</street>
            <unit_number></unit_number>
            ...
            <block>1234</block>
            <lot>001</lot>
            <apn>1234001</apn>
        </address>
    </address_change>
</xml>

Example 5

Let's move on to a time share, which can be seen at 2655 Hyde St.
In a time share we have a single unit with multiple owners.

message 1

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>2655</number>
            <street>HYDE ST</street>
            <unit_number>1</unit_number>
            <disposition>provisional</disposition>
            <create_tms>2010-07-02 14:11:22.843000</create_tms>
            <retire_tms>None</retire_tms>
            <block>0026T</block>
            <lot>065A</lot>
            <apn>0026T065A</apn>
        </address>
    </address_change>
</xml>

message 2

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>2655</number>
            <street>HYDE ST</street>
            <unit_number>1</unit_number>
            <disposition>provisional</disposition>
            <create_tms>2010-07-02 14:11:22.843000</create_tms>
            <retire_tms>None</retire_tms>
            <block>0026T</block>
            <lot>066A</lot>
            <apn>0026T066A</apn>
        </address>
    </address_change>
</xml>

The only difference here is that the APN is different in message 2.
This much is straight forward.
If a unit address changes, and there are APNs assigned to the unit,
we have one address change message for each of these APNs.
But at 2655 Hyde, there are lots of APNs assigned to the base address.

Val, please take a look at this address and tell me if you think we need to
do anything special here.
http://174.37.80.164/

Example 6

Here is a condo or tenants in common example, where each unit has a single owner, and there
is a "common area" owned by the owners association.

message 1 (base unit or common area, APN is assigned)

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>100</number>
            <street>MAIN ST</street>
            <unit_number></unit_number>
            <disposition>provisional</disposition>
            <create_tms>2010-07-02 14:11:22.843000</create_tms>
            <retire_tms>None</retire_tms>
            <block>1234</block>
            <lot>001</lot>
            <apn>1234001</apn>
        </address>
    </address_change>
</xml>

message 2 (condo unit)

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>100</number>
            <street>MAIN ST</street>
            <unit_number>1</unit_number>
            <disposition>provisional</disposition>
            <create_tms>2010-07-02 14:11:22.843000</create_tms>
            <retire_tms>None</retire_tms>
            <block>1234</block>
            <lot>002</lot>
            <apn>1234002</apn>
        </address>
    </address_change>
</xml>

message 3 (condo unit)

<xml>
    <address_change>
        <key>12873</key>
        <action>insert</action>
        <address>
            <number>100</number>
            <street>MAIN ST</street>
            <unit_number>2</unit_number>
            <disposition>provisional</disposition>
            <create_tms>2010-07-02 14:11:22.843000</create_tms>
            <retire_tms>None</retire_tms>
            <block>1234</block>
            <lot>003</lot>
            <apn>1234003</apn>
        </address>
    </address_change>
<xml>

Example 7

    <address>
        <key>419692</key>
        <base_number_prefix></base_number_prefix>
        <base_number>1</base_number>
        <base_number_suffix></base_number_suffix>
        <street_name>S VAN NESS</street_name>
        <street_name_suffix>AVE</street_name_suffix>
        <unit_number_prefix></unit_number_prefix>
        <unit_number>600</unit_number>
        <unit_number_suffix></unit_number_suffix>
        <create_tms>2010-08-03 12:05:54.578000</create_tms>
        <retire_tms>2010-08-03 12:38:33</retire_tms>
        <disposition>provisional</disposition>
        <mailing>True</mailing>
        <longitude>-122.418862841</longitude>
        <latitude>37.7747051425</latitude>
        <change_tms>2010-08-03 14:38:33.467492</change_tms>
        <action>retire</action>
        <block>3506</block>
        <lot>001</lot>
        <apn>3506001</apn>
    </address>

Example 8

<addresses>
    <address>
        <key>419623</key>
        <base_number_prefix></base_number_prefix>
        <base_number>2655</base_number>
        <base_number_suffix></base_number_suffix>
        <street_name>HYDE</street_name>
        <street_name_suffix>ST</street_name_suffix>
        <unit_number_prefix></unit_number_prefix>
        <unit_number>308</unit_number>
        <unit_number_suffix></unit_number_suffix>
        <disposition>provisional</disposition>
        <create_tms>2010-07-02 14:11:22.843000</create_tms>
        <retire_tms></retire_tms>
        <block>0026</block>
        <lot>028</lot>
        <apn>0026028</apn>
    </address>
    ...
</addresses>

Example 9

This separates the "history" information,

<addressChangeNotification>
    <address>
        <key>419709</key>
        <base_number_prefix></base_number_prefix>
        <base_number>1</base_number>
        <base_number_suffix></base_number_suffix>
        <longitude>-122.418876252</longitude>
        <latitude>37.774694542</latitude>
        <street_name>S VAN NESS</street_name>
        <street_name_suffix>AVE</street_name_suffix>
        <unit_number_prefix>XXX</unit_number_prefix>
        <unit_number>300</unit_number>
        <unit_number_suffix>YYY</unit_number_suffix>
        <create_tms>2010-08-13 12:15:43.828000</create_tms>
        <retire_tms>2010-08-13 18:41:16.149255</retire_tms>
        <disposition>provisional</disposition>
        <mailing>True</mailing>
        <block>3506</block>
        <lot>001</lot>
        <apn>3506001</apn>
    </address>
    <timestamp>2010-08-13 18:41:16.149255</timestamp>
    <action>retire</action>
</addressChangeNotification>

Example 10

DBI may use different street names and street suffixes.
This version accommodates these variations.

<addressChangeNotification>
    <address>
        <key>419709</key>
        <base_number_prefix></base_number_prefix>
        <base_number>1</base_number>
        <base_number_suffix></base_number_suffix>
        <jurisdiction>PRESIDIO</jurisdiction>
        <longitude>-122.418876252</longitude>
        <latitude>37.774694542</latitude>
        <street_name>SOUTH VAN NESS</street_name>
        <street_name_suffix>
            <abbreviated>AVE</abbreviated>
            <unabbreviated>AVENUE</unabbreviated>
        </street_name_suffix>
        <unit_number_prefix>XXX</unit_number_prefix>
        <unit_number>300</unit_number>
        <unit_number_suffix>YYY</unit_number_suffix>
        <create_tms>2010-08-13 12:15:43.828000</create_tms>
        <retire_tms>2010-08-13 18:41:16.149255</retire_tms>
        <disposition>provisional</disposition>
        <mailing>True</mailing>
        <block>3506</block>
        <lot>001</lot>
        <apn>3506001</apn>
    </address>
    <timestamp>2010-08-13 18:41:16.149255</timestamp>
    <action>retire</action>
</addressChangeNotification>

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. By the way, I used a real editor to edit this table.

EAS Field: addresses.address_id                      int
XML Field: key
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: address_base.base_address_prefix     char(10)
XML Field: base_number_prefix
FGDC Field: address number prefix         text
AVS Field: 
Example: 
Comment: 


EAS Field: address_base.base_address_num             int
XML Field: base_number
FGDC Field: address number                 int
AVS Field: AVS_ADDRESSES.STREET_NUMBER       NUMBER(6)
Example: 
Comment: 


EAS Field: address_base.base_address_suffix     char(10)
XML Field: base_number_suffix
FGDC Field: address number suffix         text
AVS Field: 
Example: 
Comment: MAD-122


EAS Field: ?
XML Field: ?
FGDC Field: ?
AVS Field: AVS_ADDRESSES.STREET_NUMBER_SFX VARCHAR2(1)
Example: V, A, C, P
Comment: MAD-122


EAS Field: address_base.zone_id                      int
XML Field: jurisdiction
FGDC Field: community place name          text
AVS Field: 
Example: SFMAIN, TI, PRESIDIO
Comment: FGDC field is approx


EAS Field: address_base.geoemtry.longitude        double
XML Field: longitude
FGDC Field: address longitude           double
AVS Field: 
Example: 
Comment: 


EAS Field: address_base.geoemtry.latitude         double
XML Field: latitude
FGDC Field: address latitude            double
AVS Field: 
Example: 
Comment: 


EAS Field: address_base.street_segment.st_name  char(29)
XML Field: street_name
FGDC Field: street name                   text
AVS Field: AVS_STREETS.STREET_NAME        VARCHAR2(28)
Example: 
Comment: todo - problem with field width


EAS Field: address_base.street_segment.st_type   char(6)
XML Field: 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


EAS Field: addresses.unit_num_prefix             char(5)
XML Field: unit_number_prefix
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: addresses.unit_num                   char(20)
XML Field: unit_number
FGDC Field: unit identifier               text
AVS Field: AVS_ADDRESSES.UNIT                NUMBER(6)
Example: 
Comment: todo - is data type mismatch


EAS Field: addresses.unit_num_suffix            char(10)
XML Field: unit_number_suffix
FGDC Field: na
AVS Field: AVS_ADDRESSES.UNIT_SFX          VARCHAR2(10)
Example: 
Comment: 


EAS Field: addresses.unit_type_id                    int
XML Field: na
FGDC Field: unit type                     text
AVS Field: na
Example: suite, apartment
Comment: 


EAS Field: na
XML Field: na
FGDC Field: na
AVS Field: AVS_ADDRESSES.ADDRESS_TYPE     VARCHAR2(10)
Example: PRIMARY, ALTERNATE, ALIAS
Comment: 


EAS Field: addresses.create_tms                 datetime
XML Field: create_tms
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: addresses.retire_tms                 datetime
XML Field: retire_tms
FGDC Field: na
AVS Field: AVS_ADDRESSES.END_DATE                 DATE
Example: 
Comment: 


EAS Field: addresses.disposition_code                int
XML Field: disposition
FGDC Field: address lifecycle status      text
AVS Field: 
Example: provisional, offical
Comment: FGDC field is approx


EAS Field: na
XML Field: na
FGDC Field: address official status       text
AVS Field: AVS_ADDRESSES.VALID             VARCHAR2(1)
Example: 
Comment: EAS combines this with address life cycle status


EAS Field: addresses.mailable_flg                boolean
XML Field: mailing
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: parcel.block_num                      char(5)
XML Field: block
FGDC Field: na
AVS Field: AVS_STRUCTURES.BLOCK            VARCHAR2(5)
Example: 
Comment: 


EAS Field: parcel.lot_num                        char(5)
XML Field: lot
FGDC Field: na
AVS Field: AVS_STRUCTURES.LOT              VARCHAR2(4)
Example: 
Comment: todo - field with mismatch


EAS Field: parcel.blk_lot                        char(9)
XML Field: apn
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: na
XML Field: na
FGDC Field: building identifer            text
AVS Field: AVS_STRUCTURES.STRUCTURE_NUMBER VARCHAR2(2)
Example: 
Comment: 


EAS Field: addresses_history.last_change_tms    datetime
XML Field: timestamp
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: addresses_history.history_action     char(10)
XML Field: action
FGDC Field: na
AVS Field: 
Example: 
Comment: 


EAS Field: addresses.unq_adds_id                     int
XML Field: na
FGDC Field: na
AVS Field: AVS_ADDRESSES.ADDRESS_KIND VARCHAR2(10)
Example: DBI, ASSESSOR
Comment:

Example 11

The purpose of this version is to support the rework of the model precipitated by MAD-156.

<?xml version="1.0" encoding="utf-8"?>
<addressChangeNotification>
    <base_address_part>
        <base_address>
            <base_address_id>157611</base_address_id>
            <base_number_prefix></base_number_prefix>
            <base_number_prefix></base_number_prefix>
            <base_number>53</base_number>
            <base_number_suffix></base_number_suffix>
            <jurisdiction>SF MAIN</jurisdiction>
            <longitude>-122.433538793</longitude>
            <latitude>37.734081063</latitude>
            <street_name>WILDER</street_name>
            <street_name_suffix>
                <abbreviated>ST</abbreviated>
                <unabbreviated>STREET</unabbreviated>
            </street_name_suffix>
        </base_address>
        <action>no change</action>
    </base_address_part>

    <unit_address_part>
        <unit_address>
            <address_id>419583</address_id>
            <unit_number_prefix></unit_number_prefix>
            <unit_number>603</unit_number>
            <unit_number_suffix></unit_number_suffix>
            <base_unit_address_flag>False</base_unit_address_flag>
            <disposition>official</disposition>
            <mailing>True</mailing>
            <create_tms>2010-06-28 15:33:10.437000</create_tms>
            <last_change_tms>2010-10-25 17:05:15.093000</last_change_tms>
            <retire_tms></retire_tms>
        </unit_address>
        <action>no change</action>
    </unit_address_part>
    
    <address_parcel_link_part>
        <address_parcel_link>
            <id>68</id>
            <create_tms>2010-10-25 17:05:15.093000</create_tms>
            <last_change_tms>2010-10-25 17:05:15.093000</last_change_tms>
            <retire_tms></retire_tms>
            <parcel>
                <block>6745</block>
                <lot>089</lot>
                <apn>6745089</apn>
            </parcel>
        </address_parcel_link>
        <action>insert</action>
    </address_parcel_link_part>

</addressChangeNotification>