Help - Geocoding (I think)

S

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
So based on the research i've done I believe what I want to do is Geocode some data.

I have about 10k in addresses in an excel sheet that I want get the distance between them and a single location. From what I've found the best way to do that is by using the longitude and latitude of each address. <- There is where I need your help first. The data I'm dealing with is private data so I can't just upload a bunch of peoples addresses into some random website to get their L&L. Also, once i get the L&L, how do i make excel triangulate the distance between two coordinates.

So in short

1. Looking for California (Really just Orange County) database that contains Addresses & their perspective L&L
2. Some excel formula that can use the L&L and give me the distance to another L&L!!!

Thanks!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
L&L to L&L is a basic square route as the crow (quail) flies. There is some very complex maths allowing for earty curvature etc, and the stuff I have used is UK only I believe.

I think you can upload L&L into a Google Earth KLM file, but i'm not proficient at that and do a plot of each point
 
Upvote 0
The forum member called diddi kindly posted this code some time ago.
I assume your addresses contain post codes or in your case, zip codes. If so, you can use the following;

Start zip code in column A
Finish zip code in column B
Use this formula in column C and copy down
Code: 
=G_Distance(A1,B1)

(I have commented out some of the original code and replaced it with my ranges to suit my set-up)

Put this in module 1
Code: 
Function G_DISTANCE(Origin As String, Destination As String) As Double
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", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & 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
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

And this in module 2
Code: 
Sub GetMapsDistances()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim ixnlDistanceNodes As IXMLDOMNodeList
    Dim ixnNode As IXMLDOMNode
    Dim TtlDist As Long
   
    ' Donated to MrExcel users by diddi
    ' Read the data from the website
   
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & Sheet2.Range("A4").Value & "&destination=" & Sheet2.Range("A6").Value & "&sensor=false", False
    'xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & ComboBox1.Value & "&destination=" & ComboBox2.Value & "&sensor=false", False
    xhrRequest.send
 
    Set domDoc = New DOMDocument60
    domDoc.LoadXML xhrRequest.responseText
 
    Set ixnlDistanceNodes = domDoc.SelectNodes("//step/distance/value")
 
    '  Total up the distance from node to node
    TtlDist = 0
    For Each ixnNode In ixnlDistanceNodes
        TtlDist = TtlDist + Val(ixnNode.Text)
    Next ixnNode
 
    'Label1.Caption = "One way distance is about " & Int(TtlDist / 1000) & "km"
    'Label2.Caption = "Return trip is about " & Int(TtlDist * 2 / 1000) & "km"
    'Sheet2.Range("c8") = "One way distance is about " & Int(TtlDist / 1000) & " km"
   
    Set ixnNode = Nothing
    Set ixnlDistanceNodes = Nothing
    Set domDoc = Nothing
    Set xhrRequest = Nothing
End Sub

Edit - The code refers to Sheet1, adjust to suit
 
Last edited:
Upvote 0
Taul said:
The forum member called diddi kindly posted this code some time ago.
I assume your addresses contain post codes or in your case, zip codes. If so, you can use the following;

Start zip code in column A
Finish zip code in column B
Use this formula in column C and copy down
Code: 
=G_Distance(A1,B1)

(I have commented out some of the original code and replaced it with my ranges to suit my set-up)

Put this in module 1
Code: 
Function G_DISTANCE(Origin As String, Destination As String) As Double
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", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & 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
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

And this in module 2
Code: 
Sub GetMapsDistances()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim ixnlDistanceNodes As IXMLDOMNodeList
    Dim ixnNode As IXMLDOMNode
    Dim TtlDist As Long
   
    ' Donated to MrExcel users by diddi
    ' Read the data from the website
   
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & Sheet2.Range("A4").Value & "&destination=" & Sheet2.Range("A6").Value & "&sensor=false", False
    'xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & ComboBox1.Value & "&destination=" & ComboBox2.Value & "&sensor=false", False
    xhrRequest.send
 
    Set domDoc = New DOMDocument60
    domDoc.LoadXML xhrRequest.responseText
 
    Set ixnlDistanceNodes = domDoc.SelectNodes("//step/distance/value")
 
    '  Total up the distance from node to node
    TtlDist = 0
    For Each ixnNode In ixnlDistanceNodes
        TtlDist = TtlDist + Val(ixnNode.Text)
    Next ixnNode
 
    'Label1.Caption = "One way distance is about " & Int(TtlDist / 1000) & "km"
    'Label2.Caption = "Return trip is about " & Int(TtlDist * 2 / 1000) & "km"
    'Sheet2.Range("c8") = "One way distance is about " & Int(TtlDist / 1000) & " km"
   
    Set ixnNode = Nothing
    Set ixnlDistanceNodes = Nothing
    Set domDoc = Nothing
    Set xhrRequest = Nothing
End Sub
Click to expand...

I notice that you're accessing a outside website to gather information. What website is this? Again, I have private information (think Ashley Madison) but not...that i can't have floating through the internet.
 
Upvote 0
Hi,
Ok, I understand but its there as an option.
I'm not familiar with zip codes or how large an area they cover, I felt distances between zip codes would be accurate enough and still avoid uploading the actual address.
cheers
Paul.
 
Upvote 0
Taul said:
Hi,
Ok, I understand but its there as an option.
I'm not familiar with zip codes or how large an area they cover, I felt distances between zip codes would be accurate enough and still avoid uploading the actual address.
cheers
Paul.
Click to expand...

Thanks, but sadly zip codes can vary greatly in actual location (in socal) and most of my zip codes are tied to a very small radius so 5 miles is a big deal.
 
Upvote 0
mole999 said:
L&L to L&L is a basic square route as the crow (quail) flies. There is some very complex maths allowing for earty curvature etc, and the stuff I have used is UK only I believe.

I think you can upload L&L into a Google Earth KLM file, but i'm not proficient at that and do a plot of each point
Click to expand...

Sorry meant to respond earlier.. I'm not exactly sure what Google Earth KLM is... but I now realize I need as the crow drives not flies :) and I just need the ability to show how many miles Place A is away from Place B (but I have 3k Place A's) So pretty pictures aren't needed.

Also I have the longitude and Latitude of each location now (census.gov)
 
Upvote 0
drive distances are bespoke software generally, you either install a complete package locally or find a site that will do the work for you, as for the theft of data, if all you are using is L&L i would defy most to work out what it relates to, (unless its all the stars of Hollywood homes)
 
Upvote 0
Taul said:
The forum member called diddi kindly posted this code some time ago.
I assume your addresses contain post codes or in your case, zip codes. If so, you can use the following;

Start zip code in column A
Finish zip code in column B
Use this formula in column C and copy down
Code: 
=G_Distance(A1,B1)

(I have commented out some of the original code and replaced it with my ranges to suit my set-up)

Put this in module 1
Code: 
Function G_DISTANCE(Origin As String, Destination As String) As Double
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", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & 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
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

And this in module 2
Code: 
Sub GetMapsDistances()
    Dim xhrRequest As XMLHTTP60
    Dim domDoc As DOMDocument60
    Dim ixnlDistanceNodes As IXMLDOMNodeList
    Dim ixnNode As IXMLDOMNode
    Dim TtlDist As Long
   
    ' Donated to MrExcel users by diddi
    ' Read the data from the website
   
    Set xhrRequest = New XMLHTTP60
    xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & Sheet2.Range("A4").Value & "&destination=" & Sheet2.Range("A6").Value & "&sensor=false", False
    'xhrRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" & ComboBox1.Value & "&destination=" & ComboBox2.Value & "&sensor=false", False
    xhrRequest.send
 
    Set domDoc = New DOMDocument60
    domDoc.LoadXML xhrRequest.responseText
 
    Set ixnlDistanceNodes = domDoc.SelectNodes("//step/distance/value")
 
    '  Total up the distance from node to node
    TtlDist = 0
    For Each ixnNode In ixnlDistanceNodes
        TtlDist = TtlDist + Val(ixnNode.Text)
    Next ixnNode
 
    'Label1.Caption = "One way distance is about " & Int(TtlDist / 1000) & "km"
    'Label2.Caption = "Return trip is about " & Int(TtlDist * 2 / 1000) & "km"
    'Sheet2.Range("c8") = "One way distance is about " & Int(TtlDist / 1000) & " km"
   
    Set ixnNode = Nothing
    Set ixnlDistanceNodes = Nothing
    Set domDoc = Nothing
    Set xhrRequest = Nothing
End Sub

Edit - The code refers to Sheet1, adjust to suit
Click to expand...

Ok, so I tried the code and i get the error "Compile error: User-defined type not defined with this part being called out: myRequest As XMLHTTP60
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Named Cell stayed put when I Cut and Paste the cell into another cell
Replies
4
Views
220
My Aswer Is This
My Aswer Is This
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
943
MoonDancer
M
R
  • Question Question
VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1
2
Replies
13
Views
2K
dermie_72
D
N
  • Locked
  • Question Question
Is there a more efficient formula to do the same?
Replies
1
Views
316
Newbie73
N
M
  • Question Question
Moving rows from one sheet/table to multiple sheets/tables
Replies
1
Views
305
mybossmademecomehere
M

Forum statistics

Threads
1,224,109
Messages
6,176,415
Members
452,728
Latest member
mihael546

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top