WiGLE is a popular platform which can be used for finding the location of a device using the names of WiFi networks in its vicinity. I’ve written about this before, and wrote some Python code to interact with their API. This API has since been retired and replaced with a new one, as of December […]
This post is a nifty tool for tracking the distance travelled by building materials from source to site. I’ve been writing a few bits of code lately which have required using Google tools, so I thought I’d write up some notes here. This is for my information than anything else but may come in handy for others too. Used in conjunction with the carbon factors per tonne km from Defra, this allows a decent estimate for the transport stage of a lifecycle carbon footprint assessment.
The custom function for Excel works by entering two place names – or postcodes – and finding the shortest route between them using Gmaps route planner. I’ve called the UDF
G_DISTANCE and the syntax for using it is
=G_DISTANCE(Origin, Destination). It returns a value of the distance between the two locations in
miles kilometres. If any kind of error occurs then the function returns 0.
Using the function
This table shows the various ways you can use the function. You can also mix and match the types of input parameter you use. For example you may the site location precisely – either by latitude and longitude or postcode, but only know the city or even the country of origin of the materials. That’s fine – the function should still give you the right answer, or at worst return zero.
Two note of warning:
1) The Gmaps terms of service state that you are only allowed to use the API when displaying a map, so using this without doing so is at your own risk (hence the map above…).
2) Gmaps will generally give you a road-based route which may not be the most appropriate. For example cork from Portugal would probably be shipped rather than delivered all the way by HGV. If anyone has a better function for these cases please get in touch.
Function G_DISTANCE(Origin As String, Destination As String) As Double
' Requires a reference to Microsoft XML, v6.0
' Draws on the stackoverflow answer at bit.ly/parseXML
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
G_DISTANCE = 0
' Check and clean inputs
On Error GoTo exitRoute
Origin = WorksheetFunction.EncodeURL(Origin)
Destination = WorksheetFunction.EncodeURL(Destination)
' Read the XML data from the Google Maps API
Set myRequest = New XMLHTTP60
myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false", False
' Make the XML readable usign XPath
Set myDomDoc = New DOMDocument60
' Get the distance node value
Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
' Tidy up
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
As ever, you need to install this as a code module in the workbook you want to use it in. It also requires that you set a reference to Microsoft XML, v6.0 (Tools > References > Microsoft XML, v6.0 ).