What address locator to use for geocoding addresses in Excel?

Question

I've been struggling to geocode the following addresses from an Excel table both in ArcMap and ArcGIS Pro:

As you can imagine, I'm trying to geocode the addresses in the "StreetAddress" field.

When I try to just simply add the table to my document and geocode it, I'm prompted to use an "address locator", of which none of the defaults work. So I believe I have to create an address locator. I'm uncertain of the style that I should use. I've tried streets, home address, and several others and I continue to get error messages.

Can anyone identify what style I need just by looking at the above example of the table?

Is there something else that I'm not doing correctly?

e.g. are commas invalid characters?

Should I be using a different table type that isn't Excel?

Are there address locators that I can just download and use this dataset for (instead of having to create one)?

I can tell I'm missing something fundamental here.

Answer 1

To create an address locator is simple in that you just need a street centerline shapefile of streets in Portland. This can be obtained from the Portland open data portal at this URL http://gis-pdx.opendata.arcgis.com/datasets/streets. Once you have obtained the street centerline shapefile you should use the Project Tool to project the shapefile in a proper projection so that the geocoded addresses are represented properly.

Figure 1: Finding the Create Address Locator Tool.

After that, click on the tools icon in the above ribbon and type in create address locator (Figure 1). Once the tool pops up select it and you should be presented a layout of the parameters that need to be filled in to execute the tool. In the case of your address data you want to select a dual range address locator style. This dual ranges address locator style will enable for searching of addresses on both sides of a street to get a higher hit rate. In the reference data field you want to add the Street Centerline shapefile and change the role to a primary table. This will allow ArcGIS Pro to automatically fill in the necessary fields below. Once you’re done filling out those parameters then just click run and your Address locator will be created (Figure 2).

Figure 2: Create Address Locator Tool Parameters

From here you can now geocode your addresses but you should adjust the format of your address field in excel table and get rid of the commas/suite numbers. Those will cause the addresses not to be geocoded due to those suite numbers not being in the reference street centerline shapefile (Figure 3).

Figure 3: Address Data Format.

Following that, now you can geocode your address data by simply using the Geocode Address tool(Figure 4). For the input table parameter add your address data and for the input address locator field just add the address locator created in the earlier step.

Figure 5: Geocode Address Tool.

Also once the address locator is inputted the subsequent fields will be autofilled, so the last thing to do would be to give your geocoded data a proper file name (Figure 5). Lastly, once you run the tool you should see an output with your geocoded points similar to figure 6.

Figure 6: Output with Geocoded Addresses.