Improving Service Delivery with Trusted City-Wide Address Data:

Service Impact Messaging By Address (SIMBA) – How City of Charlotte Masters Data Quality

Dr. Leonard J. Huggins

Outline

• Overview

• Address Management Program – The Background

• Governance & Standards

• The Technical Implementation

• Benefits & Improved Data Quality

• Access & Openness

• Questions

AMP Definitions The Background

3

SIMBA High Level Structure 1. Address Repository: - MDM 2. Address Augmentation: GIS overlay (Council District, NPA, etc.) 3. Consumption: Enterprise Service Bus for data sourcing and consumption 4. Governance: Address Governance Committee & Standards 5. Funding: Cost Allocation Plan

The enterprise implementation of the City’s Address Management Program (AMP) to create a common set of address source data for City service delivery.

SIMBA (Service Impact Messaging By Address)

Address Governance Committee: Standard Practice for Address Data_v1.0 022014

MDM - Address Repository – the centralized, shared repository that provides a consistent view of the most accurate, “gold-standard” corporate address data in a standardized format allowing for validation and use of address information.

SIMBA Address Recommendations

4

Rec. # Address Recommendations Address Management Program Governance

Mailing Services

Address Validation Tools

Address Standard

SIMBA MDM-Address

Implementation

Address Governance Committee

#1 Mecklenburg County Partnership #2 Mailing Address Validation #3 Owner/ Occupant & Mailing Address

Change Content

#4 Address Business Processes (Future State)

#5 Change Management #6 Address Resource #7 USPS Partnership #8 Mail Service Vendors #9 Data QC/QA Processes

#10 Address Data Content Standard #11 Service Impact/Land Use Change

Management

#12 Error Reporting for Situs Addresses #13 Regional Data Acquisition #14 Communication/Training Program

Governance

Going Beyond Before Getting the Now

Data Governance Organization

6

Data Governance Capabilities

Initiation

Design

Development

Testing

Production

Master Address Data Project Phase

Capa

bilit

y/M

atur

ity

Time

Assessment

Cleansing

Validating

Monitoring

Automation

DataStandards

MetadataRepository

Master Data Design

Notifications

Structure

Roles & Responsibilities

Best Practices

Operationalization

Improvement

Business Process

CollaborationAutomation

Semantic Standards

Communications

Optimization

Reporting

Data Certification

The Technical Implementation

SIMBA: The NOW

The Architecture

Validation & Standardization

• Jurisdiction: 9

• Street Type: 26

• Street Suffix: 10

• Unit Type: 24

• Street Pre-direction: 8

Code_IN Name_IN Code_OUT Name_OUT EXT Extension EXT EXTENSION EX~ EXT EXT EXTENSION

E W N S NE NW SE SW

Code2_IN Code4_IN Name_IN Code2_OUT Code4_OUT Name_OUT AL ALY ALLEY AL ALY ALLEY AV AVE AVENUE AV AVE AVENUE

Source Addresses vs. Golden Records

11

Unique addresses Standardized Validated against: Block range Complete

street name Jurisdiction

County Physical

Locations (CMAT)

(n=508,013)

Tax Locations (CAMA)

(n=337,850)

Solid Waste Services Locations

(n=393,171)

City Master Addresses SIMBA Golden Records vs CMAT

12

CMAT + ~ 44,000 additional unique addresses

CMAT addresses

SIMBA Golden Records

Improving Data Quality

SIMBA: Benefits

SIMBA Benefits

14

• Golden Records (City Master Addresses) Common, repository of standardized ‘Golden’

record sets for use by City systems Accurate address data

• Separate issue resolution process for City – Data Quality ‘fire-wall’ in place to prevent inclusion of

questionable records – Ability to correct data for City use while source system

researching/processing updates • Improved issue resolution on County datasets

– Faster identification of chronic issues like duplicate records or inconsistent updates between CMAT and CAMA

– Ability to provide a listing of records with similar issues before a failure occurs versus sharing individual records when reacting to a service delivery problem

Golden Records: Example Complexes/buildings with units listed but not in County MAT

SITUSGoldenRecordID Street PreDirection STREET NUMBER STREET NAME Street Type Street Suffix Unit Value

1081807 NULL 3600 PARK RD NULL WP01 1430575 NULL 3600 PARK RD NULL WP20 1593495 NULL 3600 PARK RD NULL WP19 2028847 NULL 3600 PARK RD NULL W316 1503569 NULL 3600 PARK RD NULL W315

Source: Mecklenburg County POLARIS 3G Source: Google Maps, 2014

County MAT: 1 Situs Address (No Units)

Emerald Source System & Golden Records: 60 Situs Addresses (Unique Unit Values)

WP01

W210 W211

WP316

WP104

W212

W103 W102

W101 WP315

WP314

id master_id

Source System

ID

Source Record

IdentifierStreet

NumberStreet Name

Street Type X Coord Y Coord

Postal Code

Source ParcelID

eng_last_source_update_date

eng_ change_

typeeng_last_update

_date

7319021 1192243 11 14906119-3 4311 NULL NULL NULL 14906119 5/15/14 7:36 PM U 6/29/14 1:19 AM

7319021 1192243 11 14906119-3 4311 SOUTH BV NULL NULL NULL 14906119 6/29/14 1:19 AM U 6/29/14 1:19 AM

If Planning pulled raw CAMA data, they would have

returned mail with address = 4311 ≡ $

SIMBA Benefits

16

• Benefit: Ability to correct data for SIMBA users before source system updates (improve latency issues)

Source Systems: 10 = County Master Address Table (CMAT); 11 = CAMA Locations; 20 = SWS Locations

SITUS Golden

Record IDStreet

NumberStreet Name

Street Type Juris X Coord Y Coord

Postal Code

Geo ParcelID

Master Maximum

Tax ParcelID

eng_ change_

typeeng_last_update

_dategroup_ count

2166057 501 OAK GROVE RD CHAR 1463778 531796 28205 15904805 15904805 U 11/14/14 1:17 AM 3

SIMBA Benefits

17

• Benefit: Ability to have a new SWS Locations inserted for SIMBA users before County systems officially add said address

Source Systems: 10 = County Master Address Table (CMAT); 11 = CAMA Locations; 20 = SWS Locations

id master_id

Source System

ID

Source Record

IdentifierStreet

NumberStreet Name

Street Type X Coord Y Coord

Postal Code

Source ParcelID

eng_last_source_update_date

eng_ change_ type

eng_last_update_date

143087 2166057 20 11102281 501 OAK GROVE RD NULL NULL 28205 NULL 5/15/14 7:36 PM I 5/15/14 7:36 PM

id master_id

Source System

ID

Source Record

IdentifierStreet

NumberStreet Name

Street Type X Coord Y Coord

Postal Code

Source ParcelID

eng_last_source_update_date

eng_ change_ type

eng_last_update_date

143087 2166057 20 11102281 501 OAK GROVE RD NULL NULL 28205 NULL 5/15/14 7:36 PM U 11/14/14 1:17 AM338757 2166057 10 305037 501 OAK GROVE RD 1463778 531796 28205 15904805 11/11/14 1:20 AM U 11/14/14 1:17 AM

9084073 2166057 11 15904805-1 501 OAK GROVE RD NULL NULL NULL 15904805 11/14/14 1:17 AM I 11/14/14 1:17 AM

SIMBA Benefits

18

• Benefit: Faster identification of chronic source system issues

Source: Mecklenburg County POLARIS 3G

19

• Benefit: Faster identification of chronic source system issues

SIMBA Benefits

20

• Benefit: Ability to provide a listing of records with similar issues before a failure occurs versus sharing individual records

SIMBA Benefits

21

• Benefit: Ability to provide a listing of records with similar issues before a failure occurs versus sharing individual records

SIMBA Benefit: Department Example

22

• Address Services Solid Waste Services Department

• Supports more productive and accurate routing – More addresses with geocoding elements (Lat/Long) – Reduced geocoding time

• Case: – 18,501 addresses without geocoding elements

» 7,238 w/o Zip – All w/o Lat/Long – Non-SIMBA solution - bulk, iterative trial and error process

for all Mecklenburg zips (60 – 80 hours) – SIMBA – 8 hours (single zip as plan input)

» Results: 18,475 addresses with geocode elements – 26 needed re-evaluation.

SIMBA Geocoding Time Savings

SIMBA Access WebFOCUS Address & Parcel Search

23

SIMBA Golden Address Utility

24

DEMO 1:

Address and Parcel Search

Address SIMBA Elements 1105 Otts Drill downs and events 3600 Park Rd Addition from multiple sources 15904805 Parcel Lookup 00338102 Multiple addresses on a parcel

SIMBA Access & Utility Address Verification Drop & Pickup

25

• Address Verification – User drops a formatted Excel file in a Public folder – Listener on folder initiates job – Job verifies address list versus SIMBA Golden Records

and Loqate addresses – Returns verified address list with deliverability computed

Address1 City State ZipCode Data1 Data2 Data3 Data4 Address_Out City_Out

State_Out Zip_Out

County_Out

AVC_Out

Geoaccuracy_Out

Latitude_Out

Longitude_Out

Deliverable

ROBINHOOD CHARLOTTE NC 28227 10920109

DURGA DHAKAL

BISHNU DHAKAL Robinhood Cir Charlotte NC 28227

P32-I33-P6-094 A3

35.198583

-80.710518 N

1000 TIMBERLAKE DR CHARLOTTE NC 28227

10925110

WENDY SHANAHAN

1000 Timber Lake Dr Charlotte NC

28227-4039

V44-I44-P7-100 I4

35.199320

-80.704070 Y

1040 DOOLEY DR CHARLOTTE NC 28227

10919119

TAMARA L JOHNSON 1040 Dooley Dr Charlotte NC

28227-8141

V44-I44-P7-100 I4

35.202799

-80.712946 Y

1051 DOOLEY DR CHARLOTTE NC 28212

10922112

JOAN C UNDERWOOD 1051 Dooley Dr Charlotte NC

28227-8142

V44-I44-P4-092 I4

35.202330

-80.712540 Y

SIMBA Golden Address Utility

26

DEMO 2:

Mailing Address Verification

SIMBA Golden Address Utility

27

DEMO 3:

GIS Buffering & Address Extraction

The Wrap

Leonard J. Huggins, PhD GIS Coordinator|Address Coordinator City of Charlotte | Solid Waste Services 1105 Otts Street Charlotte, NC 28205

704-336-2563 | 704-564-4841 | lhuggins@charlottenc.gov

Questions …. Questions …. Questions …..