Running ArcGIS Functions using Function Builder User Interface
ArcGIS for Excel includes ArcGIS Functions that enable you to perform geospatial operations in Excel. With these Functions you can geocode, enrich your data with additional data variables, and perform routing operations, without leaving your familiar Excel environment. ArcGIS for Excel includes an intuitive interface for writing, managing, and running ArcGIS Functions.
Let's dive into exploring each ArcGIS Function using the Function Builder user interface to discover what there is and, how they can help in workflows.
Note - Before using the ArcGIS for Excel function, you must Sign-in to ArcGIS with an Organizational account. Geocoding, Geoenrichment, and Routing functions consume credits. To learn about the credits, please refer to this link.
GETADDRESS
The GETADDRESS function allows you to determine the address at longitude and latitude location through reverse geocoding. You can select your preferred geocoder from the geocoder drop-down menu, including an option to choose a custom geocoder. The drop-down menu will display all geocoders that have been set up within your ArcGIS Organization.
In the following video, we'll explore a scenario where survey data sites are represented in the UTM coordinate system. Utilizing the GETADDRESS function, we'll convert these coordinates into addresses, write and run the function using the Function Builder user interface. Finally, we’ll add those addresses to the ArcGIS for Excel map.
GETCOORDINATES
The GETCOORDINATES function geocodes a location, returning latitude and longitude, and optionally address match score that indicates the quality of match when geocoding addresses. You can select your preferred geocoder from the geocoder drop-down menu, including the option to choose a custom geocoder. The drop-down menu will display all geocoders that have been set up within your ArcGIS Organization.
The 'GETCOORDINATES' function not only converts addresses to coordinates in the Web Mercator coordinate system but also allows you to convert them to many other coordinate systems. In the below video, we’ll see a scenario where Netherlands addresses are converted to Dutch national coordinate system (RD New wkid 28992) using the ArcGIS Function Builder. Eventually, the coordinates are rendered on a Dutch coordinate system topographic basemap.
ENRICHBYPOINT
ENRICHBYPOINT function is a Geoenrichment function. Using ENRICHBYPOINT Function, you can enrich your point location within a certain buffer area or drive time with additional demographics and location-based data variables and get to know more about your location. The additional information can drive better understanding, analysis, and decision making.
In the following video will showcase how the point locations for California cities are enriched with data variables. We’ll enrich these locations with Total population and Travel index. Finally, we’ll visualize the enriched data on the ArcGIS for Excel map, this analysis will help us to understand how much people in different places tend to spend on travel.
ENRICHBYGEOGRAPHY
ENRICHBYGEOGRAPHY function you can enrich levels of geography such as districts, states, block groups, census tracts, municipalities, emirates, departements, provinces, regions, etc. with additional demographics and location-based data variables. Basically, you are adding demographic, business, health, crime, education, landscape, spending, and policy data to the new columns as contextual data in your datasheet.
In the below video showcases New York area zip codes on the map and enriching these zip codes with Gen Z data variables so that the map highlights the highest concentration of Gen Z residents.
ENRICHBYADDRESS
Like the ENRICHBYPOINT function, utilizing ENRICHBYADDRESS function you can enrich your address location within a certain buffer area or drive time with additional demographics and location-based data variables.
FINDROUTE & FINDROUTEBYADDRESS
Using FINDROUTE and FINDROUTEBYADDRESS functions allows users to calculate the total travel distance and time between two-point locations or addresses. These functions offer valuable routing information.
HELPER FUNCTIONS
We also provide Helper functions. Helper functions in ArcGIS for Excel are formulas that assist with various operations such as geocoding, geoenrichment, and routing. They include functions like LISTDATACOLLECTIONS, LISTGEOGRAPHIES, SUGGESTADDRESS, LISTTRAVELMODES, and ADDHEADERS. These functions help you manage and manipulate data collections, suggest addresses based on input, list available travel modes for routing, and add headers to your data sets in Excel.
Note – Helper functions do not consume credit. The SUGGESTADDRESS function consumes credits when you request more than 15 suggestions at once.
For example, the LISTGEOGRAPHIES function provides a selection of geographies available for data analysis. By utilizing the function, you can list various geography levels (such as districts, provinces, zip codes, regions, etc.) within a country or list area IDs for a specific geography level and search for geographies. You can also copy and paste the generated geography names or IDs from the function into a separate dataset or append them to another dataset for further analysis. The below video showcases the fetching of Census tracts for the state of Ohio in the United States and municipalities of the Veneto region of Italy using the function.
SUGGESTADDRESS function based on user input suggests possible matches. The video shows the address matches that the function can fetch when searching for the text Starbucks Riverside Downtown CA.
LIMITATION and WORKAROUND
Running the ArcGIS function formula within the Excel tables or in the column directly to the right side of the Excel table causes the #SPILL! Error. The cause of this behavior is a limitation within Microsoft Excel where running the formulas are not fully supported in the Excel tables, please check the link for the workaround.
We hope you find the article informative. Try ArcGIS for Excel function and see how ArcGIS for Excel function benefits you in your work. If you have any questions or ideas for enhancements, please share them in the comments section below.