Gregory A. Newkirk
Steve Long

Using Map Objects to Link United States Postal Service Address Data to Assessor's Parcel Data

This paper discusses techniques used in linking United States Postal Service (USPS) address data with the Clark County Assessor's GIS parcel data within the City of Vancouver, Washington. Data from these two sources were imported into Microsoft Access, where a one-to-many relate was established using fields common to both data sets. This resulted in 50,000 matched USPS records. To assist in linking the remaining 30,000 unmatched USPS addresses to the Assessor's GIS parcel data, a Map Objects application was developed.

Introduction

The Clark County Office of Assessment and GIS maintains, as part of its cadastral data, a 12-digit geocode and a situs address. Prior to GIS, the geocode was useful for locating parcels on the old quarter-section maps while the situs address was useful for parcel location on larger maps and in the field.

Since the introduction of GIS, situs addresses have become increasingly valuable. Unfortunately, while GIS allows for spatial queries that can generate mailing labels, situs addresses are not maintained for that purpose and do not always match USPS delivery records.

Prompted by the US Census Bureau, the City of Vancouver undertook a project to update parcel situs addresses within the City limits (including future annexation areas), thereby allowing the City to participate in the Partnership for Address List Supplementation (PALS) program. Though PALS was subsequently discontinued, the project retained its value for participating in the US Census Bureau's Local Update Of Census Addresses (LUCA) program. The project also retains its value for occupant notification of government actions and the generation of neighborhood mailing lists.

Initially, the address matching functionality of Arcview was tested to determine if the USPS addresses could be linked to parcels by creating spatial locations for each of them. An initial test using 216 postal addresses resulted in a match of 150 addresses. However, the matched addresses where clustered along road lines and could not be associated with individual parcels. As a result, a different methodology was needed. The City chose a methodology that required application development using Microsoft Access, Visual Basic and Map Objects. A GIS intern was hired with programming skills in Visual Basic and database management. The project began with data acquisition and preparation. Next, a Map Objects application was constructed to assist with matching USPS addresses to parcel data.

Data Acquisition

The project began by contacting the United States Postal Service (USPS) in Portland, Oregon to obtain a copy of all mail delivery addresses for zip codes within the City of Vancouver. A recently adopted federal law allows federal agencies to share certain data for public purposes. In return for the address data provided by the USPS, the City of Vancouver agreed to provide information on street name and address corrections for data that did not agree with City records. The specified data format was for nine separate fields: house number, street direction (i.e. NE), street name, street type (i.e. AVE), unit type (i.e. APT), unit number, zip code, zip+4 and occupancy type (i.e. commercial or residential). The occupancy type data were critical to the PALS and LUCA programs as the US Census Bureau is only interested in residential addresses for the 2000 Census.

The City had access to the assessor's parcel data and associated situs addresses which were formatted into six separate fields: assessor's parcel number, house number, street direction, street name, street type and zip code. The two data sets were then combined into one database to begin the project.

Database Construction

First, USPS data were received as 10 individual files (one for each zip code) in ascii, comma-delimited format. These files were imported into a Microsoft Access database and concatenated into one table (see Table 1). The USPS data field for street type is four characters long, while the Assessor's data is only two characters. To facilitate matching, the Assessor's data in the street-type data field were converted to USPS format and abbreviation standards.

Second, ArcInfo software was used to perform a spatial query of all parcels within the City of Vancouver (and future annexation areas) which produced a data set of approximately 58,000 records with assessor's parcel numbers and situs addresses. These data were then imported into another table within the database (see Table 2).

Third, a one-to-many relate was established between the four common fields of house number, street direction, street name and street type within the two tables. This produced a match of approximately 50,000 USPS records. A third table was created into which the matched USPS records were placed along with the assessor's parcel number from the other table (see Table 3). This new table constituted the City of Vancouver's new Master Address File that provides a link between spatial coordinates, casdastral data and USPS delivery addresses.

Table 1: Postal
house number	street direction	street name	street type	unit type	unit number	zip code	ocupancy type
7020	NE	100^th	CT	APT	A1	98662	residential
7020	NE	100^th	CT	APT	A2	98662	residential

Table 2: Assessor
assessor's parcel number	house number	street direction	street name	street type	zipcode
108491126	3813	NE	100^th	CT	98662
104522000	7020	NE	100^th	CT	98662

Table 3: Master Address File
apn	house number	street dir	street name	street type	unit type	unit number	zip code	occupancy type
108491126	3813	NE	100^th	CT	\|	\|	98662	R
104522000	7020	NE	100^th	CT	APT	A1	98662	R
104522000	7020	NE	100^th	CT	APT	A2	98662	R

This initial matching procedure allowed the City of Vancouver to link two-thirds of the USPS addresses. A second procedure was needed to link the remaining 30,000 records. This procedure required a means for spatially viewing the parcels along with pertinent data such as situs addresses, owner addresses and street names.

Map Objects Application Design and Development

A random selection of Assessor's data was tested to determine the most common types of error. The first and most common type of error discovered was an incorrect street type. For example, an Assessor's situs address was coded as an avenue (AVE) when it should have been coded as a court (CT). The second most common type of error discovered was an incorrect direction. For example, an Assessor's situs address was coded as Northwest (NW) when it should have been coded as Northeast (NE). The third most common type of error was an incorrect or misspelled street name. Based upon these findings, the application design was begun. Visual Basic 5.0 and Map Objects 1.1 were used to develop the application. Two forms were developed for interacting with the database; one for data manipulation entitled 'Assessor Still to Match' and the other for spatially displaying the data entitled 'Census 2000 Project'.

Assessor Still to Match

The Assessor Still to Match form (see Figure 1) lists the first unmatched record in Table 1. It also lists all unmatched USPS addresses in the Table 2 that have an identical street direction, street name and street type. This form contains the functionality that updates the database. In particular, two tools were developed that allow street direction and street type data to be changed on the screen since these were most common typographical errors were found (see the Change Direction and Change Type buttons on on the Figure 1).By changing these fields, a new list of USPS records appears. The user can then click on the USPS record(s) that match the four street address fields in the Assessor record and use the Update Assessor button to copy the serial number from Table 1 into Table 2. An Update Undo button allows the user to reverse the action taken with the Update Assessor button. The Matched and Unmatchable buttons place a code in Table 1 which remove that assessor record from further consideration.

Census 2000 Project

The Map It button (see Figure 1) allows the user to call the Census 2000 Project form (see Figure 2). This form brings up a map of assessor's parcels and street names, while highlighting the parcel listed in the other form. A tool bar allows the user to zoom, pan, and identify adjacent parcels. The functionality of this form is for visualization and address determination. In addition to viewing parcel location, this form also lists owner and situs addresses. Between the street names, parcel layout and addressing information, the user can determine the nature of the addressing error, determine the correct address and search for it in the list of USPS addresses on the Assessor Still to Match form (Figure1).

Figure 1

Figure 2

Project Implementation

The project required approximately 400 hours of staff time to process the remaining 30,000 USPS addresses. Initially, the project ran smoothly when the errors were simple, allowing a rate of over 100 corrections per hour. Eventually, the errors become increasingly complex and more tools were needed beyond the application to complete the project. Instead of revising the application, these tools were used independently. The most useful tool was using Arcview to view orthophotos overlaid with parcel lines. This allowed the user to determine which parcels contained multiple buildings and should be assigned multiple addresses when more addresses existed than parcels. Another tool used was a Cole Street Address Directory for Vancouver, Washington. This provided an address sequencing reference since only unmatched USPS addresses appeared on the Assessor Still to Match form. Unfortunately, using tools outside of the application required greater user judgment and management of multiple applications. This, in turn, increased the amount project management that was needed.

Since the project's completion, the data generated have been used to create mailing labels based upon neighborhood boundaries and areas impacted by public works projects. The data has also been shared with the Clark County Department of Assessment and GIS for use in updating the County's cadastral database. The most important aspect of this project will be in using the now spatially-linked USPS addresses for evaluating U.S. CENSUS data as the City prepares for the 2000 Census.

Conclusion

If the project were to be undertaken again, the application would be modified to combine both forms unto one. It would also be modified to include a tool allowing the street name to be changed based upon a list of names generated from the USPS data. It would also include a tool allowing the user to view all USPS addresses within a user-specified range. Lastly, a tool allowing orthophotos to be displayed within the Census 200 Project form would also be added. And, while these modifications would have required additional time and expense, they would have proven cost effective and contributed toward greater efficiency .

Gregory A. Newkirk, AICP
Senior Planner, Geographic Information Systems
City of Vancouver
P.O. Box 1995
Vancouver, WA 98668-1995
Telephone: (360) 696-8012
Fax: (360) 696-8029
Email: gnewkirk@ci.vancouver.wa.us

Steve Long
Intern, Geographic Information Systems
City of Vancouver
P.O. Box 1995
Vancouver, WA 98668-1995
Telephone: (360) 735-8873 ext. 8120
Fax: (360) 696-8029
Email: slong@ci.vancouver.wa.us