This posted started as a question on the Alteryx community about finding the nearest underground (subway if you prefer!) station to an address. I don’t have much experience working with addresses and map data, but thought their was probably a way leveraging the APIs Google maps provide.

This feels very much like “standing on the shoulders of giants” that Scott Hanselman talks of in his blog post.

Geocoding

First task was to convert the address into a longitude and latitude. This process is called geocoding. Google Maps has a whole range of different APIs one of which is Geocoding. The geocoding API has a fairly straight forward syntax to call it using a standard http GET request:

https://maps.googleapis.com/maps/api/geocode/json?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&key=YOUR_API_KEY

The API requires an API_KEY. You can sign up for free here. There is a limit of 2,500 requests for free on the standard plan, but should you need more then you can enable pay and go paying $0.50 / 1000 extra request. If you don’t have a valid API key you will get a request denied message (although oddly not an HTTP error!):

Having got an API key, now wanted to experiment with API and see what format the data came back in. There are various different REST API testing tools out there (including just doing it in a browser), but my favourite is Postman – it allows straight forward testing of these kind of APIs and has a good JSON pretty printer. It also has a JavaScript testing engine inside it which allows you to do some simple scripting on the response. Finally, you can also use environment variables, so I can keep my API key in there and use it within the Uri by writing {{ApiKey}}.

To set up the environment and variable in Postman, click the environment drop down and select manage environment:

Inside the new window you can create a new environment (I chose to call it GoogleApi) and then add a new key called ApiKey with a value set to the ApiKey:

Having created the environment, it should be available in the drop down on the right. Google’s geocoding API has a few different options. In this case I wanted a JSON format return and would be specifying an address. The request URL (using this environment variable) looks like:

https://maps.googleapis.com/maps/api/geocode/json?address=<Address>&key={{ApiKey}}

Taking a simple test case of looking for the address “Scott Logic, Newcastle”. Running this query in postman results in a JSON result like:

{
“results”: [
{
“address_components”: [
{
“long_name”: “Newcastle upon Tyne”,
“short_name”: “Newcastle upon Tyne”,
“types”: [
“locality”,
“political”
]
},…
],
“formatted_address”: “Newcastle upon Tyne, UK”,
“geometry”: {
“bounds”: {
“northeast”: { “lat”: 55.0453044, “lng”: -1.5326051 },
“southwest”: { “lat”: 54.9594399, “lng”: -1.7810817 }
},
“location”: { “lat”: 54.978252, “lng”: -1.61778 },
“location_type”: “APPROXIMATE”,
“viewport”: {
“northeast”: { “lat”: 55.0453044, “lng”: -1.5326051 },
“southwest”: { “lat”: 54.9594399, “lng”: -1.7810817 }
}
},
“partial_match”: true,
“place_id”: “ChIJzWRvDH6FfUgRkWGncrBS4gs”,
“types”: [ “locality”, “political” ]
}
],
“status”: “OK”
}

 

 

Some of the address components have been removed from the above, the full JSON file can be downloaded from here. There is a lot of information which can be read out this response. The first block address components tells us about the matched address, described as a set of fields:

Field	Short Name	Long Name
locality, political	Newcastle upon Tyne	Newcastle upon Tyne
postal_town	Newcastle upon Tyne	Newcastle upon Tyne
administrative_area_level_3, political	Newcastle upon Tyne	Newcastle upon Tyne
administrative_area_level_2, political	Tyne and Wear	Tyne and Wear
administrative_area_level_1, political	England	England
country, political	GB	United Kingdom

For my purpose, I only need the latitude and the longitude. These are stored within the geometry object as the location. The additional properties of the geometry object specify the region that appears on a Google map search.

I uses Postman’s test framework to write a small block of JavaScript which stores these values as environment values inside I can then use later:

var jsonData = JSON.parse(responseBody);
postman.setEnvironmentVariable(“lat”, jsonData.results[0].geometry.location.lat);
postman.setEnvironmentVariable(“lng”, jsonData.results[0].geometry.location.lng);

Having run this request the environment variables look like:

Finding The Nearest Station

So far we have converted the address into a latitude and longitude. The next step is to fund the nearest points of interest on the map. Google has another REST API to return information round a GPS co-ordinate – their places API. Again it requires the same API key as above. For this case, the Place Search will allow you to fin a list of the nearest stations.

The base URI is:

https://maps.googleapis.com/maps/api/place/nearbysearch/output?parameters

You can choose between JSON and XML as the output format (JSON is simpler to deal with so would recommend that). Likewise as before you must provide an API as the key argument. Unlike the geocoding API, it only takes a location as longitude, latitude. Finally, you must specify to either rank the results by distance (rankby=distance) or to search within a specify radius (radius=10000 to search within 10km). If you are using the ranking mode, you are also then required to provide either a type, keyword or name to search for.

To find the nearest station, I want to rank the results by distance from the GPS co-ordinate and specify that I am looking for a station. The type filter allows you specify one of the types listed here – for this case I wanted subway_station. The Uri using Postman’s variables is hence:

https://maps.googleapis.com/maps/api/place/nearbysearch/json?key={{ApiKey}}&location={{lat}},{{lng}}&rankby=distance&type=subway_station

You will receive back a JSON structure like:

{
“html_attributions”: [],
“next_page_token”: “CpQCBQEAAHI5uyVLuCCs3K37OvmGVgCNtscQ8uxa3xKJPNQ9mrFMxxwH0OxejgPfOyqZCNhrc6lidxbNyOjklu7fp71rBvbjqWazd2wfKG19ktEp6_QWuCaJPbYLRzpsJNNdiELV73BBw0oYWtHioJbNjNqPC6YsUrP_ByQXPBhiYN8VNI54ZJfHb5bwLSYuVBDwiJ9j4Vjf1vEqnPYnjyLr0Fmo0HDfJrDODQW2R7tgVau58EhpThj23Y-EYaC0ssTjsg3lltqgce1ujxb3Z6lf-TCXJAM9xs8tsBSA-Q8InQjxTbMHqUa5DvDUSi4l0-uHejdm1H6pEgYjdTJmRSnGhX-Z_H3ROBsGuWRPA7dDZ9LFxVZcEhBGeaTGsXmvk17sfFidGPCdGhTUCE975QsCFCutIUZJHXLkAZavdA”,
“results”: [
{
“geometry”: {
“location”: {
“lat”: 54.977481,
“lng”: -1.6138929
}
},
“icon”: “https://maps.gstatic.com/mapfiles/place_api/icons/generic_business-71.png”,
“id”: “d5b6ffa31de95679ef807796a9778a8a410a0418”,
“name”: “Haymarket”,
“photos”: [
{
“height”: 3085,
“html_attributions”: [
“<a href=\”https://maps.google.com/maps/contrib/107056009756478253671/photos\”>Bocaj Nesnaj</a>”
],
“photo_reference”: “CoQBcwAAABS6JL5Gh0S6dRdHx01dkPO4OoxXwS2hBRD58yQjYrNubcroTz4x40mY32hEY0nrP1iBZowoP5FhHDM45WdNQiswcUoxE2mkXYxBoXnC99wxHaGJPQ4-pvMc-jCkH18JnjXJfXzv5dAoNtgWav9Pq0_OO3xFTTl8nQg75Ch_SI2oEhCXxxcDj98Bun0BMW2FJGy_GhSHErdUum1PWUpazHkeUJUBfKYrFQ”,
“width”: 4896
}
],
“place_id”: “ChIJDyP2_MtwfkgRDqPu-wmHBMA”,
“reference”: “CmRdAAAA2nyuhb9XS9zptApVcc8D88IWc_zdZ-RuunDdkaVYC42U8n2hEuEF7nO4Ol0CWqzI13Nnt2iCn5Opgr9iHjGsOFCSbURd573RKgJWNaNw2VdKgdKIKXKWk8FTIFy2eFaAEhD76A2tt6zeX9bxOKOPUdw1GhSaGzIqZRA0-ZBQJ8lICKRFhlWYPQ”,
“scope”: “GOOGLE”,
“types”: [
“subway_station”,
“transit_station”,
“point_of_interest”,
“establishment”
],
“vicinity”: “United Kingdom”
}, …
],
“status”: “OK”
}

The list of the nearest 20 subway stations is inside the results array with a name property and a geometry location property. The locations are in increasing distance from the original search location. The table below shows the results for Scott Logic, Newcastle:

name	geometry.location.lat	geometry.location.lng
Haymarket	54.977481	-1.6138929
Monument Metro Station	54.9736621	-1.6134493
St James	54.97408	-1.62099
Manors	54.97403	-1.60484
Jesmond	54.98308	-1.60548
Central Station	54.96912	-1.61615
West Jesmond	54.9936284	-1.6097669
Gateshead	54.9614035	-1.6038992
Byker	54.97615	-1.57957
Ilford Road	55.00017	-1.61085
Gateshead Stadium	54.95782	-1.58808
Chillingham Road	54.98283	-1.57169
South Gosforth	55.00614	-1.60815
Walkergate	54.98549	-1.55908
Longbenton	55.00881	-1.59158
Regent Centre	55.01172	-1.62204
Felling	54.95298	-1.5713
Four Lane Ends	55.01013	-1.57862
Wansbeck Road	55.0142	-1.63489
Fawdon	55.01374	-1.64462

You can download the Postman collection file I used to run these request here.

Using HERE or Bing Maps

If you prefer not to use Google Maps API then both HERE or Bing Maps provide REST APIs.

HERE has a set developer Rest APIs, including a Geocoding API. In this case you must provide and app_id and an app_code. You can register for those here.  There is a basic plan, which is free, that includes this API thought limited amount per month. The geocode request looks like:

https://geocoder.cit.api.here.com/6.2/geocode.json?app_id={{app_id}}&app_code={{app_code}}&searchtext=Newcastle,UK

Again you can choose between JSON and XML. The JSON response is more complicated than in Google’s case but the matches are found as an array of objects at Response.View.Result. Each object contains a Location with a NavigationPosition with a Latitude and Longitude. I found the search was not as good as interpreting my input as Google’s.

I didn’t go any further with HERE, but there is a Places API which I am guessing would have the ability to find places of interest, such as a subway station. The most interesting feature that I saw within the HERE was the ability to do a batch geocoding job.

Bing has a massive set of APIs you can use. Geocoding is done by the Location service. As with both the other services, you need to obtain an API key. The instructions to get a key are here. Again there is a Basic key, which is free, that will cover the usage need for geocoding within annual limts (125k requests!). The location service can yet again return either JSON (default) or XML (use the output (o) query argument to specify, &o=xml). You can provide what you want to find in the query (q) argument:

http://dev.virtualearth.net/REST/v1/Locations?q=Newcastle,UK&key={{ApiKey}}

The structure of the JSON is pretty similar to the results from HERE. The matching location GPS co-ordinates are listed in the resourceSets array inside resources array with a point objects containing a pair of co-ordinates. Again the structure feels more complicated than Google’s and the search results seemed less in line with what I expected.

Reproducing In Alteryx

As this all started as a question on the community, back to Alteryx. Fortunately, downloading and parsing JSON data is part of it’s bread and butter.

As I don’t like having API keys littering workflows (any more than I do have them in code), I like to put them in as workflow constants. If you are sharing the workflow even this can be a problem, in which case I would put them in a flat file or in the windows registry. The constant can be access by going to the properties tool of the workflow:

We can use a text input tool to contain the locations we want to search for, and then use a formula tool to construct the URL we need to request to get the GPS co-ordinates. After this the download tool and JSON parser tool can be used to download and read the JSON response. Finally some filtering on parsed result will give you the Latitude and Longitude.

Basically, we then repeat the above process feeding in the latitude and longitude to create the required URL in another formula tool and then download and parse the result.

You can download the workflow from here.