Geographical Data in Excel 2013

fellovertoday · Mar 14, 2015

Hello all,

I am trying to create a tool in excel, where I can take the following data types below as an input, identify the distance between the data points entered and represent these points on a map.

I have looked at using power view for plotting the points and I have found a UDF that uses the gmaps API to calculate the difference between two points.

The problem I am having is that these solutions only work with post codes or place names, I can't find anything that would convert/accept Ordnance Survey Map Reference and Grid Coordinates.

If anyone has experience in this area please could you suggest an approach/solution for this issue?

Many Thanks

Data Field	Format
Postcode	n/a
Ordnance Survey Map Reference	1st Letter will be N, T, S or H 2nd Letter one of A (North West corner) to Z (South East corner) except I 6 Numbers (3 digits for each Eastings and Northings - i.e. within each square how far from the SW corner of the square)
Grid Coordinates	3 decimal places – WGS84

oldbrewer · Mar 14, 2015

are we working on only one sheet or will one ref be one sheet and the second ref another sheet ?

I thought the grid along the bottom was 49 - 50 - 51 etc with third part estimated ie 497 likewise the northings

once you have 2 map references it is simple geometry to calculate the straight line distance between them

ParamRay · Mar 14, 2015

If you have the easting and northing for each postcode unit, then you can use Pythagoras's theorem (a^2 + b^2 = c^2) to work out the distance between two postcodes.

See here:
Calculating distances | Resources | Ordnance Survey

fellovertoday · Mar 14, 2015

Hi thanks for the replies,

My main issue is how to convert one of the data types so that I can calculate the distance between the two points and then show them on a map.

For example, if I have a grid reference and a post code I need to convert both to the same format, calculate the distance between and then show it on a map. The issue is the conversion and representing the points on a map as I believe I can only search by location names and post codes on power viewer. So I guess in short is there a method where I could convert any data point to the nearest postcode for representation on a map?

Many Thanks

SpillerBD · Jun 15, 2017

For relatively short distances it is simple geometry. Long distances are significantly affected by the curvature of the Earth.
If you can convert to the decimal GPS coordinates, which should be easy, you can use this UDF that returns miles:

Code:

Public Function G_DISTANCE(Origin As String, Destination As String) As Double
'This FUNCTION must be copied to actually Workbook modules
'Also the Reference (under Tools) "Microsoft XML. v6.0" must be activated
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    On Error GoTo exitRoute
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "[URL]http://maps.googleapis.com/maps/api/directions/xml?origin[/URL]=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then G_DISTANCE = (distanceNode.Text / 1000) * 0.62137119 'convert from km to miles
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

Geographical Data in Excel 2013

fellovertoday

New Member

Excel Facts

oldbrewer

Well-known Member

ParamRay

Well-known Member

fellovertoday

New Member

SpillerBD

Well-known Member

Similar threads

Forum statistics

Share this page

Geographical Data in Excel 2013

fellovertoday

New Member

Excel Facts

oldbrewer

Well-known Member

ParamRay

Well-known Member

fellovertoday

New Member

SpillerBD

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock