Knowing the distance between your staff and students’ locations and your institution has innumerable uses. From timetable planning to calculating the CO2 impact of your organisation. Given a person’s postcode calculating their straight line distance from an address is straightforward but not that useful. Actual driving, cycling or walking distances would be better. Here the Google Maps APIs can help.
The API you need to get distances between points is the Google Distance Matrix API. In order to use it you’ll need an API key which you can set up by following these directions. When you first set up an account you’ll get a free trial but when this expires you’ll have to enable billing. Fortunately, even after the free trial expires, you get $200 of credit every month. The distance matrix API usage is priced at $5 per thousand elements so this gets you 40,000 searches a month for free which is enough for a lot of uses.
Once you’ve got an API key you can start to get some data. First I’ll set up a SQL Server table to hold some postcodes that I want to process.
create table dbo.postcodes
(
postcode varchar(10)
, distance_to_origin_meters int
, distance_search_status varchar(50)
)
insert into dbo.postcodes values ('SW1V 3JD', null, null)
Distance Matrix API Response Structure
Next I’ll create a new C# console app in visual studio. The Distance Matrix API returns its results in JSON (or XML but JSON is recommended so that’s what I’ll use). In order to parse the JSON string I’ll use Newtonsoft JSON so install this NUGET package.
The API lets you supply multiple origin and destination locations. It will then return the distance between every origin and every destination. The output supplies a rows array containing one element array for each origin supplied. Each element array contains one record per destination and the distance details between the origin and that destination. Here’s how the JSON is structured:
{
"status": "OK",
"origin_addresses": [ ],
"destination_addresses": [ ],
"rows": [
{
"elements": [
{
"status": "OK",
"duration": {
"value": 0,
"text": ""
},
"distance": {
"value": 0,
"text": ""
}
}
]
}
]
}
For my example I’ll just be supplying a single origin and destination and so will get a single element back. Note that pricing is per element so if you send 3 origins and 4 destinations you’ll pay for 12 usages on the API elements. My next job is to create a class to mirror the JSON structure so I can de-serialise the response into it.
//define a class to hold the JSON response from the Google Distance API
public class distanceResponse
{
public class distance
{
public string text { get; set; }
public int value { get; set; }
}
public class duration
{
public string text { get; set; }
public int value { get; set; }
}
public class element
{
public distance distance { get; set; }
public duration duration { get; set; }
public string status { get; set; }
}
public class row
{
public element[] elements { get; set; }
}
public string[] destination_addresses { get; set; }
public string[] origin_addresses { get; set; }
public row[] rows { get; set; }
public string status { get; set; }
}
Calling the Distance Matrix API
Then define a few strings to hold the parameters for the API. In the URL I’ll specify the return format (JSON), an origin postcode to get distances from, travel mode (walking), units (imperial – this only affects the distance text, the value is always returned in metres), region (UK – since I’m just using UK postcodes this helps google narrow the search and should provide better results) and finally the API key.
// set up the parameters for the HTTP request static HttpClient client = new HttpClient(); static string base_Url = "https://maps.googleapis.com/maps/api/distancematrix/json?"; static string origins = "origins=B6 6JD"; static string mode = "mode=walking"; static string units = "units=imperial"; static string region = "region=uk"; static string apiKey = "key=APIKEYGOESHERE";
I’ve also declared the HttpClient that I’ll use to make the request here as static. Next I’ll define an async method to make an API call and return the response.
static async Task<string> GetDistance(string postCode)
{
//call the API for distance between a postCode and and the origin postcode
string URL = base_Url + origins + "&destinations=" + postCode + "&" + mode + "&" + units + "&" + region + "&" + apiKey;
HttpResponseMessage response = await client.GetAsync(URL);
String responseContent = await response.Content.ReadAsStringAsync();
return responseContent;
}
Connecting to the Destination Postcodes
Now in the Main method I’ll do the plumbing to use the SQL server postcodes table and retrieve all postcodes that don’t already have a distance calculated.
//connect to datasource with postcodes
string connectionString = "Server=DESKTOP\\SQLEXPRESS;Database=ROI;Trusted_Connection=True;";
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
//get records with a postcode and no distance
string selectSql = "select postcode, distance_to_origin_meters, distance_search_status from dbo.postcodes" +
" where postcode is not null and distance_search_status is null";
//set up a data adapter and datatable
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectSql, sqlConnection);
DataTable dataTable = new DataTable();
sqlDataAdapter.UpdateCommand = new SqlCommand(
"UPDATE dbo.postcodes " +
"SET distance_to_origin_meters = @distance_to_origin_meters " +
", distance_search_status = @distance_search_status " +
"WHERE postcode = @postcode", sqlConnection);
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@distance_to_origin_meters", SqlDbType.Int, 4, "distance_to_origin_meters");
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@distance_search_status", SqlDbType.VarChar, 20, "distance_search_status");
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@postcode", SqlDbType.Char, 10, "postcode");
//get the postcode data
sqlDataAdapter.Fill(dataTable);
Get Distances Between Postcodes and Origin
Then loop through all the postcodes and call the API to get the distance to the origin postcode. De-serialise the result into distanceResponse and extract the distance to update the postcodes data table. The statuses NOT_FOUND and ZERO_RESULTS indicate failure to find the either the origin or destination and failure to find a route between them and are ignored.
//loop through the postcodes
foreach (DataRow rw in dataTable.Rows){
//call the API and get the JSON string response
string postcode = (string) rw["postcode"];
string response = GetDistance(postcode).GetAwaiter().GetResult();
//using Json converted deserialise the JSON response into distanceResponse object
distanceResponse dr = JsonConvert.DeserializeObject<distanceResponse>(response);
//update the table with the distance in metres
rw["distance_search_status"] = dr.rows[0].elements[0].status;
if (dr.rows[0].elements[0].status != "NOT_FOUND" && dr.rows[0].elements[0].status != "ZERO_RESULTS")
{
rw["distance_to_origin_meters"] = dr.rows[0].elements[0].distance.value;
}
}
Finally write back the data table to SQL server.
//write back the changes to SQL Server
sqlDataAdapter.Update(dataTable);
Console.WriteLine("Done");
Console.ReadLine();
Below you can see the results recorded from the API and directly in Google Maps. As you can see the results are the same – 112 miles.
Finally below is the full source code for the console app.
using System;
using System.Net.Http;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Data;
namespace GetDistances
{
class Program
{
//define a class to hold the JSON response from the Google Distance API
public class distanceResponse
{
public class distance
{
public string text { get; set; }
public int value { get; set; }
}
public class duration
{
public string text { get; set; }
public int value { get; set; }
}
public class element
{
public distance distance { get; set; }
public duration duration { get; set; }
public string status { get; set; }
}
public class row
{
public element[] elements { get; set; }
}
public string[] destination_addresses { get; set; }
public string[] origin_addresses { get; set; }
public row[] rows { get; set; }
public string status { get; set; }
}
// set up the paramters for the HTTP request
static HttpClient client = new HttpClient();
static string base_Url = "https://maps.googleapis.com/maps/api/distancematrix/json?";
static string origins = "origins=B6 6JD";
static string mode = "mode=walking";
static string units = "units=imperial";
static string region = "region=uk";
static string apiKey = "key=APIKEYGOESHERE";
static async Task<string> GetDistance(string postCode)
{
//call the API for distance between postCode and WR2 6AJ
string URL = base_Url + origins + "&destinations=" + postCode + "&" + mode + "&" + units + "&" + region + "&" + apiKey;
HttpResponseMessage response = await client.GetAsync(URL);
String responseContent = await response.Content.ReadAsStringAsync();
return responseContent;
}
static void Main(string[] args)
{
//connect to datasource with postcodes
string connectionString = "Server=DESKTOP\\SQLEXPRESS;Database=ROI;Trusted_Connection=True;";
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
//get records with a postcode and no distance
string selectSql = "select postcode, distance_to_origin_meters, distance_search_status from dbo.postcodes" +
" where postcode is not null and distance_search_status is null";
//set up a data adapter and datatable
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectSql, sqlConnection);
DataTable dataTable = new DataTable();
sqlDataAdapter.UpdateCommand = new SqlCommand(
"UPDATE dbo.postcodes " +
"SET distance_to_origin_meters = @distance_to_origin_meters " +
", distance_search_status = @distance_search_status " +
"WHERE postcode = @postcode", sqlConnection);
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@distance_to_origin_meters", SqlDbType.Int, 4, "distance_to_origin_meters");
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@distance_search_status", SqlDbType.VarChar, 20, "distance_search_status");
sqlDataAdapter.UpdateCommand.Parameters.Add(
"@postcode", SqlDbType.Char, 10, "postcode");
//get the postcode data
sqlDataAdapter.Fill(dataTable);
//loop through the postcodes
foreach (DataRow rw in dataTable.Rows){
//call the API and get the JSON string response
string postcode = (string) rw["postcode"];
string response = GetDistance(postcode).GetAwaiter().GetResult();
//using Json converted deserialise the JSON response into distanceResponse object
distanceResponse dr = JsonConvert.DeserializeObject<distanceResponse>(response);
//update the table with the distance in metres
rw["distance_search_status"] = dr.rows[0].elements[0].status;
if (dr.rows[0].elements[0].status != "NOT_FOUND" && dr.rows[0].elements[0].status != "ZERO_RESULTS")
{
rw["distance_to_origin_meters"] = dr.rows[0].elements[0].distance.value;
}
}
//write back the changes to SQL Server
sqlDataAdapter.Update(dataTable);
Console.WriteLine("Done");
Console.ReadLine();
}
}
}