Geocoding in Google Sheets

readme.md

Geocoding in Google Sheets

The NYS GIS Program Office geocoding service can geocode structured addresses (with street number, city, zipcode, etc. in different fields) as well as freeform addresses (with everything in a single field).

This geocoder is available as an ArcGIS REST GeocodeServer, which is great if you are using Esri software, but what if you are not? Below, we'll see how to use the geocoder within Google Sheets, for an easy way to geocode addresses across New York State, without having to use any special software.

Understanding the GeocodeServer request URL

Calls to an ArcGIS GeocodeServer can be written in the form of a URL with parameters for the input address and output settings. Here is an example, broken into separate lines for readability:

https://gisservices.its.ny.gov/arcgis/rest/services/Locators/Street_and_Address_Composite/GeocodeServer
/findAddressCandidates
?SingleLine=108+E+Green+St+Ithaca+NY+14850
&maxLocations=1
&outSR=4326
&f=pjson

The first line is the base URL for the NYS GIS Geocoder, and /findAddressCandidates is the command we are running.

The SingleLine parameter contains the full address. (Although I've found that it does pretty well even without the city, as long as you have the correct zip code. And you can safely omit the state, because this particular geocoder only does NY.) Notice that spaces have been replaced by +, which is a part of proper URL encoding. (URLs are not supposed to contain certain characters like spaces.)

The maxLocations parameter limits the response to just one (best) result. This worked well for the addresses I had, which were well-formed street addresses. If you need to evaluate multiple candidate matches, you'd be better of using something like OpenRefine's reconciliation feature.

The outSR parameter specifies the EPSG code for the coordinates in the response. 4326 is good old WGS 84 latitude/longitude, but you could ask for other coordinates like State Plane or UTM.

The f=pjson sets the output format to pretty JSON (which is easier to read in a browser), but f=json would work just as well.

Understanding the GeocodeServer response JSON

Here's what the JSON response looks like:

{
 "spatialReference": {
  "wkid": 4326,
  "latestWkid": 4326
 },
 "candidates": [
  {
   "address": "108 E Green St, Ithaca, NY, 14850",
   "location": {
    "x": -76.498483039002124,
    "y": 42.438894671895063
   },
   "score": 100,
   "attributes": {
    
   }
  }
 ]
}

Adding the magic to Google Sheets

So now we just need a way to take an address from a Google Sheet, create the proper URL, send it off to the geocoder, pull the x and y values out of the response, and put them into the sheet.

Google Sheets has some built-in functions for fetching data from URLs, but these only support formats like CSV and XML. However, using another Google product, I found this post about ImportJSON that explains how to set up a script within Google Sheets that will add a new function for fetching and parsing JSON. It even takes care of the URL encoding. You'll want to use the latest version of the script, which is available at https://raw.githubusercontent.com/bradjasper/ImportJSON/master/ImportJSON.gs

Once the script has been added, you can use the =ImportJSON() function within your Gooogle Sheet. Here is an example, broken into separate lines for readability:

=ImportJSON(
    concatenate(
        "https://gisservices.its.ny.gov/arcgis/rest/services/Locators/Street_and_Address_Composite/GeocodeServer/findAddressCandidates?SingleLine=",
        A2,
        "&maxLocations=1&outSR=4326&f=pjson"
    ),
    "/candidates/location/x,/candidates/location/y",
    "noHeaders"
)

The ImportJSON() function takes three arguments: the URL, the path to the bits of JSON we want to extract, and some configuration options.

We use the concatenate() function to construct the full URL containing the address string found in cell A2 of our sheet.

The comma-delimited list of JSON paths /candidates/location/x,/candidates/location/y specifies where to pull the x and y coordinates from the response.

The option noHeaders says to just put the x and y values into the cells, rather than putting labels for x and y in other cells.

Here's how it works:

Now that we have the x and y coordinates, we can save the sheet to a local CSV file, and import into QGIS or whatever other software we are using.

very cool! for folks that like to write JavaScript, we've got a helpful open source lib for that too:

<script src="https://unpkg.com/@esri/arcgis-rest-geocoding"></script>

const endpoint = "https://gisservices.its.ny.gov/arcgis/rest/services/Locators/Street_and_Address_Composite/GeocodeServer/";

geocode({
  singleLine: "108 E Green St Ithaca NY 14850",
  outSR: 4326,
  endpoint
})
  .then(response => {
    response.candidates[0].location; // { x: -76.498483, y: 42.438894, spatialReference: ...  }
  });