topic Re: Data Scrubbing Addresses in Data Management Questions

Data Scrubbing Addresses

BrandonPowell1 — Fri, 02 Sep 2016 15:45:50 GMT

This is my first ever post in GeoNet and I am new to the GIS field.

One of my first assignments is to geocode all of our customer locations in ArcMap. The task seems simple enough, but I need the data to be clean first. Are there any recommended tools to clean up the addresses? How do I know they are accurate? Etc..

Re: Data Scrubbing Addresses

ChrisDonohue__GISP — Fri, 02 Sep 2016 16:13:13 GMT

If you haven't already done so, I'd recommend looking at both the Geocoding process and your address data to get a feel for how it all fits together. In particular, pay attention to how the data is broken out into fields in your data.

Check the Help of the Version of the software you have. Here's some older Help information in the same vein:

Geocoding - ArcGIS Desktop 10.0

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&sqi=2&ved=0ahUKEwi7nKD-iPHOAhVIVWMKHdmuAUcQFgglMAI&url=h…

Hopefully folks can share some tools that may help.

Adding Addressing Address Points

Chris Donohue, GISP

Re: Data Scrubbing Addresses

ChrisDonohue__GISP — Fri, 02 Sep 2016 16:43:49 GMT

Some questions to better help the GeoNet community provide answers for you:

What GIS software are you using for geocoding? ArcGIS? ArcGISPro? ArcGIS Online? Another?
What version?
Does your organization use the Local Government Information Model (LGIM)?

Also, let me tag a specific addressing troublemaker expert jborgion . Joe does 911 addressing and I believe address scrubbing is a normal part of his work as he pulls together addressing data from several sources. If he is free and sees this, I bet he has some good leads. Though the Black SUV full of National Addressing Standards police may have finally caught up with him....

Chris Donohue, GISP

Re: Data Scrubbing Addresses

BrandonPowell1 — Fri, 02 Sep 2016 16:49:58 GMT

Thanks Chris.

I'm using ArcGIS and ArcGIS Online

Version 10.4.1

I have to be honest I'm not sure if we use LGIM or not, so I'm leaning towards a no. Like I said, I'm very new to this.

I appreciate your assistance!

Re: Data Scrubbing Addresses

JoeBorgione — Fri, 02 Sep 2016 17:31:35 GMT

Hey now... I resemble that comment...

... but I need the data to be clean first....

Which data are you talking about? Your (1) table of customers or the (2)data you are matching to?

#1: This is the obstacle all of us face when geocoding. The addresses need to be in a format compatible with the data you are matching against ie: 1234 S Main St may not get a 100% match if your data says MAINE STREET

#2 What are you matching against and what is the vintage or pedigree of the data? Is it current? Is it attributed properly? ie yesterday some sent me street data to be used in a geocoding project but only some of the streets have names, and NONE of them have address ranges. (Wow!)

Re: Data Scrubbing Addresses

ChrisDonohue__GISP — Fri, 02 Sep 2016 17:59:22 GMT

Just to provide a broad overview, Geocoding processes assume the address data will be fed to it in a certain way. Depending upon what type of address locator one employs (single field or multiple field), the data input to it will need to be in a certain format. So the cleanup process usually involves arranging the data into the needed format. This often involves creating new fields in the input table and then calculating over parts of the original data to come up with the combination(s) the address locator needs so one gets a relevant geocoded result. Or one may have to parse out pieces of data from fields to populate new fields. For example, parsing out "123 Main Street" into a StreetNumber field ("123"), a StreetName field ("Main"), and a StreetType ("Street") field.

Note - if you don't have an address locator available, you can build one. This will take some learning.

In terms of accuracy, that can be a major issue. As Joe Borgione mentioned, a typo where "Maine" is entered instead of "Main" will will probably not produce a match when geocoding. So you may need to do some checking to look for typos, missing data components, data in the wrong sequence (example "Main Street 123" instead of the expected "123 Main Street", etc.

In general, I would say a one-shot perfect geocoding run is not possible outside of a classroom. You can get many of them to work by geocoding in the first try, but there will always be some addresses that will elude the process. Then one has investigate them and maybe correct the data, and do several followup geocoding runs with different settings to finally get them to work. Expect the ones that don't work the first time to take a bit of effort to resolve.

Geocode Addresses—Help | ArcGIS for Desktop

Chris Donohue, GISP

Re: Data Scrubbing Addresses

ChrisDonohue__GISP — Fri, 02 Sep 2016 18:03:18 GMT

No address ranges? Awesome data for geocoding.

"WE DON'T NEED NO STINKIN' ADDRESS RANGES!"

(Sorry, couldn't help but channel Cheech and Chong there.....)

Chris Donohue, GISP

Re: Data Scrubbing Addresses

BrandonPowell1 — Fri, 02 Sep 2016 19:14:29 GMT

Yep, sorry for being too vague.

1. We exported a list of our customers into Excel from our SQL database and this is what I need to be clean.

2. What are we matching against...I suppose I have to figure this out. As a test I have already imported the .csv file into an ArcGIS Online map as a layer and it generally did what I expected it to do, namely I got a bunch of points on the map of where our customers are located. In my excel sheet I have a column for (street address, street address 2(P.O. box, etc.), city, state, zip, zip+4).

I think Chris was helpful by saying:

"In general, I would say a one-shot perfect geocoding run is not possible outside of a classroom. You can get many of them to work by geocoding in the first try, but there will always be some addresses that will elude the process. Then one has investigate them and maybe correct the data, and do several followup geocoding runs with different settings to finally get them to work. Expect the ones that don't work the first time to take a bit of effort to resolve."

I admit to some degree I was hoping it would just work especially since it's over 5000 addresses!

Re: Data Scrubbing Addresses

JoeBorgione — Fri, 02 Sep 2016 19:42:16 GMT

I re-read your original post and noticed you are using ArcGIS Online as your geocoding source; I don't use that and never have so I can't comment on its accuracy or latency. Sounds like you are well on your way with it though; what is your hit ratio to non hits? That is, what percentage of your 5,000 records came back with a 85% or better score ? How many came back un matched? Look at the unmatched ones or the ones that scored lower that 85 and see how the address information is formatted. Those are the problem children.

BTW: 5,000 address is a good first start for you. I've done batches with 400 K +. Pretty sexy business, eh?!

Re: Data Scrubbing Addresses

ChrisDonohue__GISP — Fri, 02 Sep 2016 22:27:05 GMT

Joe - did you get paid by the address match? Hmmmm, maybe I should go back to working in the Private Sector......

Chris Donohue, GISP

Re: Data Scrubbing Addresses

JoeBorgione — Sat, 03 Sep 2016 13:16:19 GMT

I may need to re-negotiate that part of my contract. That's brilliant Chris!

Re: Data Scrubbing Addresses

BrandonPowell1 — Fri, 09 Sep 2016 17:25:50 GMT

Thanks Chris and Joe for your help. I've figured out a lot in the last 2 weeks and am starting to feel more comfortable with the ArcGIS suite. You're responses were very helpful to me. I am actually not going to be using the ArcGIS Online as a geocoding source, but rather a geocoder we purchased through BA.

Re: Data Scrubbing Addresses

BrandonPowell1 — Thu, 22 Sep 2016 15:41:00 GMT

Hey cdspatial‌ and jborgion‌ could I ask ya'll one more question about #geocoding postcodes‌? I've been doing some tests on geocoding our customer data again. I realized just yesterday that if I geocode an address for ex: 123 main street, rochester, IN 46975 and then I also goecode that same address but include the zip+4 46975-8008 that I'm still getting the same XY Coordinates. It's like my geocoder doesn't care about zip+4 which is really messing with my location accuracy. Any thoughts on that?

Re: Data Scrubbing Addresses

JoeBorgione — Thu, 22 Sep 2016 16:00:10 GMT

As I understand zip + 4, it's used by the USPS as more or less a mail sorting tool to get mail into the correct leather bag for delivery. If you don't use any zip, you may get several 1234 S Main St hits, but the actual location of all of them won't change by adding a zip or a zip + 4. Using them will just limit your choices.

Re: Data Scrubbing Addresses

BrandonPowell1 — Fri, 23 Sep 2016 13:59:46 GMT

I gave a really bad example above. I should have put PO Box 111, rochester, IN 46975 vs PO Box 111, rochester, IN 46975-8008. Because the geocoder doesn't care about the PO Box # it then just looks at the city/state/zip. Because we are doing location analytics here it really makes a difference whether or not we use the Zip or the Zip+4 because the +4 is much more accurate. I actually found yesterday that my Business Analyst data came with a USA_ZIP4_LocalComposite locator that I think is going to fix this. I was using the USA_LocalComposite before.

Re: Data Scrubbing Addresses

JoeBorgione — Sun, 25 Sep 2016 15:51:40 GMT

Geocoding PO boxes? Not sure I see the point in that.

Re: Data Scrubbing Addresses

ShanaBritt — Thu, 29 Dec 2016 16:49:43 GMT

Brandon:

The USA_ZIP4_LocalComposite locator included with Business Analyst will return a match for just 46975-8008 in the address " PO Box 111, rochester, IN 46975-8008". There is no zip4 information associated with the PointAddress and StreetAddress locators that participate in the USA_ZIP4_LocalComposite locator. The possible candidates you would get back would be from the Zip4, PostalCode and AdminPlaces locators.

-Shana