Everyone has tables of business and operations data that contain an address, city, country or postal zone that can be easily geocoded in ArcGIS Maps for Office. Because Maps for Office uses the ArcGIS Online Geocoding Service you can geocode an address or place for more than 100 countries worldwide. What a lot of people don’t know is that you can map anything in your business data by creating a custom locator using any map or a feature service from ArcGIS.
You use your maps instead of the default location type and save the definition so that the next time you want to see related data it’s automatically available for you. For example, let’s say your organization has its own boundaries like sales territories or water districts, which need to be mapped. Every month you create a report of the sales in the territory or water leaks in the district. ArcGIS Maps for Office will let you turn those tables into powerful mapping visualizations using a new location type aka locator from the existing map services your organization has already created.
Better still, you can connect to public map services published by other organizations like parcels, zoning, land use, trade areas or school districts. You can create new location types/locators for all those location types as well. If you can map it, you can link your own data to it!
Connecting Map Services to ArcGIS Maps for Office
Let’s look at an example, using parcel data publicly shared by a local government.
In the above example, I want to link a table of parcel property numbers to the parcels published by Yucaipa City Council. I’ve made a table, called Parcels, which will become a layer in my map but there is no standard or existing location type for the parcel layer.
Using the More… option you can see the standard and user defined locators. The screenshot below shows my existing personal and standard locators. By selecting Add location type I can extend the list of locators to allow me to find or join feature names in the parcel map.
Searching For and Creating New Location Types Using Map Services
When adding a location type you can search for a specific feature service or use tags. Searching for “Yucaipa Parcels” finds the authoritative service provided by the city. Clicking Details provides full metadata on the service and publisher.
The next step is to select the attribute field(s) that you will use in the new location type. In my case I only need APN but I could have chosen any attribute which has a unique one-to-one link that defines the parcel outline. Clicking on the information icon (i) creates a small pop-up of expected values which highlights the format of each column. This can help ensure that you match the input format in your table with the expected values in the map service.
Once I give the new location type a name, it will appear in my list of locators/location types and I can use it every time with my parcel map.
Putting Your New Location Type to Work
Before you start to use your new location type ensure you have set up the table appropriately. In the example below I will use parcel APNs which begin with zeros in the map service so I need to format my “Parcel” column which contains the APN to text, otherwise the zero will be stripped off if the column is formatted as General or Number in Excel.
When adding data from the Excel table, I now have the option of using my Yucaipa Parcels location type. I’m prompted to use one of the fields in my table to match with the feature service in ArcGIS Online which I previously defined, in this case APN.
ArcGIS Maps for Office will show you the values in the match column and the expected format. From the example above you can see that formatting the column as text has preserved the leading zero and the input and expected values have the same format. Clicking OK will match every row in the table which has an APN with the appropriate parcel stored in the map service published by Yucaipa City Council.
Using a Location Type to Bring Back Additional Pop-up Attributes
I often want to bring back a set of attributes from the map and merge them with the data in my table but I only want to see it in the pop-up. Normally this is only if you add the map as a layer from ArcGIS Online and click on the pop-up from both layers. However, a custom location type will let you do this without having to load the actual map or share your Excel data with the cloud.
My old location type only contained the APN. If I select other attribute fields which are defined in the map, such as taxable and improved value, I can retrieve those additional fields and display them in the pop-up.
When using the location type I DO NOT define a column in my table for any field other than the APN number i.e. leave all other fields you want to see on the pop-up as “None” like in the screenshot below.
When I add the Excel table data to my ArcGIS Maps for Office map, the pop-up contains the information that is in my table (APN, Owner and Parking spaces) as well as the tax variable I want from the map service. With this technique you can create unique and custom pop-ups that merge data from two different sources without having to publish both datasets to ArcGIS Online or Portal for ArcGIS.
Tips for Creating New Location Types
Lastly, I’ve found that there are a few “gotchas” which can impact the use of your new location type. Here’s a few tips to ease frustration:
- Numbers that are Text Strings – Parcels numbers and other references that have a leading zero will be formatted as text not numbers. Use the information icon when creating the new location type to check the format of your match column in the map. If there are leading zeros, you must ensure that your match column in the Excel table is set to text
- Commas and Quotes – Usually the data you are using in Excel is automatically converted to the best format but that might hide how the data is really formatted. Numbers which have come from a text file can often have commas in them. Clean up and convert all your data before trying to link it to a custom location type
- Compound Addresses in a Single Field – Sometimes you get addresses which are all in the same field but the service expects multiple fields for the address like street and zip. If this is the case, save the files in Comma Separated Variable (CSV) format and use a good text editor or Notepad to break up the addresses into the desired format.
- Beware of Quotation Marks – Some text fields can contain hidden quote marks (“) which will impact how the text strings is sent to and interpreted by ArcGIS Online. If you can do without them remove them.
- Arcade Expressions – Arcade is a very popular way to add new dynamic attribute to a feature service. You can generate unique fields by concatenating other fields or use logic and mathematical operations on existing attributes. Arcade expression attributes cannot be used as a location type so check the attribute types in the map using the pop-up or table before creating the new location type.
By creating new location types in ArcGIS Maps for Office you can literally find and map anything and everything that’s in ArcGIS Online or your organizational portal. That’s a lot of information and some amazing insights that you can share with stakeholders all without leaving Excel!