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.
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.
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.
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": {
}
}
]
}
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 probably want to use the latest version of the script, which is available at https://github.com/bradjasper/ImportJSON
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.