copy

Thursday, 22 August 2013



Last updated: 09/08/2018, 2 min read (without the code)

Custom Elevation Function (VBA & Google API)


Problem description



About a month ago I faced the following problem: I had around 100 coordinates – in format “latitude, longitude” – and I had to find the elevation of each point in order to proceed with some calculations. The input data came from a GPS, but the question was how to use those data in order to find the required elevation data.

Google Earth Elevation

Google Maps Find Altitude - Daft Logic

I found two possible “manual” ways that could be used: 
  1. Open Google Earth and search point by point in order to find the elevation. The elevation of the address will be displayed at the bottom right corner of the screen. Then, write each elevation value to an empty cell next to latitude and longitude data. 
  2. Use the following page: http://www.daftlogic.com/sandbox-google-maps-find-altitude.htm and again write each elevation value to Excel.
Although both ways worked well, the problem was that I had to find too many points (i.e. 100) in order to process them manually. That’s why I tried to find something easier.



Solution



VBA saved me once again from spending a lot of time on this tedious issue. The solution I found is a custom VBA function that uses the Google Elevation API. What is this API? Copying from Google’s page: “The Elevation API provides elevation data for all locations on the surface of the earth, including depth locations on the ocean floor (which return negative values). In those cases where Google does not possess exact elevation measurements at the precise location you request, the service will interpolate and return an averaged value using the four nearest locations. With the Elevation API, you can develop hiking and biking applications, mobile positioning applications, or low-resolution surveying applications”.

I will not copy the entire Google page here, so for anyone who is interested to learn more about Google APIs he/she can find more information on the pages below. The full description of all APIs is given there.
One of the two possible output formats from the Google Elevation API is “/xml”, which returns results in XML, wrapped within an <ElevationResponse> node. The other is a “/json”, which returns results in JavaScript Object Notation (JSON). I decided to go with the first format, so the tricky part about the VBA function was the use of XML, v3.0 library in the code.



VBA code



Here is the VBA code for the custom Elevation function. Have in mind that the use of the Google Elevation API is subject to a limit of 40,000 requests per month, so be careful not to exceed this limit.

2018 Update: the function has been updated to reflect the changes in Google API. To use this VBA function you will need a valid API key. Check this link that presents a step-by-step guide on how to acquire one for free.

Option Explicit

Function GetElevation(Latitude As Double, Longitude As Double) As Variant
    
    '-----------------------------------------------------------------------------------------
    'The function returns the elevation from a given location using the Google Elevation API.
    
    'NOTE: As Google points out, the use of the Google Elevation API is subject to a limit
    'of 40,000 requests per month, so be careful not to exceed this limit. For more info check:
    'https://cloud.google.com/maps-platform/pricing/sheet

    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    
    '2018 Update: In order to use this function you will now need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html

    'Written By:    Christos Samaras
    'Date:          22/08/2013
    'Last Updated:  09/08/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim ApiKey          As String
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim ElevationNode   As IXMLDOMNode
               
    'Set your API key in this variable. Check this link for more info:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    ApiKey = "Your API Key goes here!"
    
    'Check that an API key has been provided.
    If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
        GetElevation = "Invalid API Key"
        Exit Function
    End If
    
    'Checking if the latitude value is within the acceptable limits [-90, 90].
    If Latitude < -90 Or Latitude > 90 Then
        GetElevation = "Incorrect latitude value"
        Exit Function
    End If
    
    'Checking if the longitude value is within the acceptable limits [-180, 180].
    If Longitude < -180 Or Longitude > 180 Then
        GetElevation = "Incorrect longitude value"
        Exit Function
    End If
        
    'Generic error handling.
    On Error GoTo errorHandler
    
    'Create the request based on Google Elevation API. Parameters (from Google page).
    '- Locations: defines the location(s) on the earth from which to return elevation data.
    'This parameter takes either a single location as a comma-separated {latitude,longitude} pair
    '(e.g. "40.714728,-73.998672") or multiple latitude/longitude pairs passed as an array or as an encoded polyline.
    'Here we use it with a "single location" format.
    'Since we don't require a path, (but a single location) the parameter "path" is omitted here.
    
    Request.Open "GET", "https://maps.googleapis.com/maps/api/elevation/xml?" _
    & "&locations=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
            
    'Send the request to the Google server.
    Request.send
    
    'Read the results from the request.
    Results.LoadXML Request.responseText
    
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case StatusNode.Text
            
        Case "OK"   'The API request was successful.
            'Get the elevation node value.
            Set ElevationNode = Results.SelectSingleNode("//result/elevation")
            GetElevation = CDbl(ElevationNode.Text)
    
        Case "INVALID_REQUEST"  'The API request was malformed.
            GetElevation = "Request was malformed"
        
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded limit.
            GetElevation = "Requestor has exceeded limit"
            
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetElevation = "Request was denied"
        
        Case "UNKNOWN_ERROR"    'An unknown error occured.
            GetElevation = "Unknown error"
        
        Case Else   'Just in case...
            GetElevation = "Error"
        
    End Select
        
    'In case of error, release the objects.
errorHandler:
    Set StatusNode = Nothing
    Set ElevationNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also



Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info