This article will show you how to transform addresses into latitude/longitude coordinates using an on-prem ESRI geocoder. If you haven’t read the article about transforming and validating data here, it will help give the background required for this article.
Many customers have ESRI ArcGis geocode servers that they run and allow different departments to access in order to geocode addresses. These geocoding servers are generally built by the customer’s GIS department and are often more accurate, complete and/or up to date than general geocoders offered by Google and MapQuest.
Start by uploading or importing a dataset from a URL.
Click the “Add Column” button on the left sidebar. Type a column name, and then hit save.
After the column has been saved, click back on the “Preview Data Table” button on the left sidebar. Scroll all the way to the right side of your dataset, and you’ll see your new column, which has no data in it yet. Click the column options button and select “Data Transforms” from the drop-down.
The data transform screen will show a text editor, where the current expression is just a null value.
We can use the “geocode_esri” function to do the geocoding. It takes a street address, city, state, zip, and an ESRI ArcRest server URL. It optionally takes a final argument which is a rate limit in requests per minute. The default rate limit is 30 requests per minute (where each request has 100 addresses, meaning 3000 addresses get geocoded per minute, by default). If you want the process to go faster, and your geocode server can keep up, you may raise this limit. Conversely, if you find your geocode server cannot keep up with the request rate, consider lowering this number.
The following image shows the function being used against New York’s geocode server.
The source dataset has the `house` column and `street_name` in separate columns, so we concatenate them into a single string with a space in the middle to pass to the “geocode_esri” function as a single string.
When you click run, you will see results start to come in.
If the ESRI server fails to geocode an address, it will be returned as an error and highlighted in red.