Help - Geocoding (I think)

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!
 
Here is one that is supposed to use Longitude and Latitude.. but I'm still trying to figure it out

Integrating Excel and Google Maps to Geo-code Your Addresses | Microsoft Trends

Like where does he reference which cells to look up for starting and stoping points

VBA Macro Code
Option Explicit

' Subroutine to plug in Longitude and Latitude for a range of locations.
Public Sub LookupLongitudeAndLatitude()

' Declare variables
Dim CurrentSheet As Worksheet
Dim CurrentRange As Range
Dim row As Range
Dim Address As String
Dim Longitude As Double
Dim Latitude As Double
Dim Success As Boolean
Dim BlankValues As Boolean
Dim Status As String

' Get the current selected cells
Set CurrentSheet = ActiveSheet
Set CurrentRange = Selection


' check for current range size to see if three columns are available.
If CurrentRange.Columns.Count <> 3 Then
MsgBox ("Please select 3 columns, one with addresses in it and two blank to put the long and lat values")
Else

For Each row In CurrentRange.Rows
BlankValues = True
Address = row.Cells(1, 1)

' Check for existing values. We're expecting blank for columns 2 and 3 so if they have any content,
' we'll error out and send a warning isntead of writing over existing cells.
If IsEmpty(row.Cells(1, 2)) <> True Then
MsgBox ("Expected longitude column to be blank for cell " & row.Cells(1, 2).Address(False, False))
BlankValues = False
End If
If IsEmpty(row.Cells(1, 3)) <> True Then
MsgBox ("Expected latitude column to be blank for cell " & row.Cells(1, 3).Address(False, False))
BlankValues = False
End If

If BlankValues = True Then
Success = GetLongitudeAndLatitude(Address, Longitude, Latitude, Status)
If Success = True Then
row.Cells(1, 2) = Longitude
row.Cells(1, 3) = Latitude
Else
row.Cells(1, 2) = Status
row.Cells(1, 3) = Status
End If

End If
Next row
End If

' reset selection to original range
CurrentSheet.Select
CurrentRange.Select

End Sub

Private Function GetLongitudeAndLatitude(Address As String, Longitude As Double, Latitude As Double, Status As String) As Boolean

' Declare variables and set return value to false by default
GetLongitudeAndLatitude = False
Dim response As DOMDocument60
Dim http As XMLHTTP60
Dim node As IXMLDOMNode
Dim nodes As IXMLDOMNodeList
Set http = New XMLHTTP60

' Read the data from the website
On Error Resume Next
' Open an XML request from Google using their GeoCode API
http.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?address=" & URLEncode(Address), False
http.send
Set response = http.responseXML

' get the status node. This node tells you whether your search succeeded - OK means success. Any other status means some kind of error or address not found.
Set node = response.SelectSingleNode("/GeocodeResponse/status")
If node.nodeTypedValue <> "OK" Then
Status = node.nodeTypeString
Else
Set nodes = response.SelectNodes("/GeocodeResponse/result")
' check for multiple addresses if we found more than 1 result then error out.
If nodes.Length > 1 Then
MsgBox ("Found Multiple Matches for Address: " & Address)
Else
' grab the latitude and longitude from the XML response
Set node = response.SelectSingleNode("/GeocodeResponse/result/geometry/location/lat")
Latitude = node.nodeTypedValue
Set node = response.SelectSingleNode("/GeocodeResponse/result/geometry/location/lng")
Longitude = node.nodeTypedValue
GetLongitudeAndLatitude = True
End If

End If

Set http = Nothing
Set response = Nothing

End Function

' URL Encoding function courtesy of How can I URL encode a string in Excel VBA? - Stack Overflow
Private Function URLEncode( _
StringVal As String, _
Optional SpaceAsPlus As Boolean = False _
) As String

Dim StringLen As Long: StringLen = Len(StringVal)

If StringLen > 0 Then
ReDim result(StringLen) As String
Dim i As Long, CharCode As Integer
Dim Char As String, Space As String

If SpaceAsPlus Then Space = "+" Else Space = "%20"

For i = 1 To StringLen
Char = Mid$(StringVal, i, 1)
CharCode = Asc(Char)
Select Case CharCode
Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
result(i) = Char
Case 32
result(i) = Space
Case 0 To 15
result(i) = "%0" & Hex(CharCode)
Case Else
result(i) = "%" & Hex(CharCode)
End Select
Next i
URLEncode = Join(result, "")
End If
End Function

' Subroutine to plug in Distance between two addresses.
Public Sub LookupDistance()

' Declare variables
Dim CurrentSheet As Worksheet
Dim CurrentRange As Range
Dim row As Range
Dim SourceAddress As String
Dim DestinationAddress As String
Dim Distance As Double

Dim Success As Boolean
Dim BlankValues As Boolean
Dim Status As String

' select the currently selected range
Set CurrentSheet = ActiveSheet
Set CurrentRange = Selection

' check for current range size to see if three columns are available.
If CurrentRange.Columns.Count <> 3 Then
MsgBox ("Please select 3 columns, one with source address, one with destination address and one for distance.")
Else

For Each row In CurrentRange.Rows
BlankValues = True
SourceAddress = row.Cells(1, 1)
DestinationAddress = row.Cells(1, 2)
' Check for existing values. We're expecting blank for column 3 so if it contains any content,
' we'll error out and send a warning isntead of writing over existing cells.
If IsEmpty(row.Cells(1, 3)) <> True Then
MsgBox ("Expected distance column to be blank for cell " & row.Cells(1, 3).Address(False, False))
BlankValues = False
End If

If BlankValues = True Then
Success = GetDistance(SourceAddress, DestinationAddress, Distance, Status)
If Success = True Then
row.Cells(1, 3) = Distance
Else
row.Cells(1, 3) = Status
End If

End If
Next row
End If

' reset selection to original range
CurrentSheet.Select
CurrentRange.Select

End Sub

Private Function GetDistance(SourceAddress As String, DestinationAddress As String, Distance As Double, Status As String) As Boolean

' Declare variables and set return value to false by default
GetDistance = False
Dim response As DOMDocument60
Dim http As XMLHTTP60
Dim node As IXMLDOMNode
Dim nodes As IXMLDOMNodeList
Set http = New XMLHTTP60

' Read the data from the website
On Error Resume Next

' Open an XML request from Google using their Directions API
http.Open "GET", "https://maps.googleapis.com/maps/api/directions/xml?origin=" & URLEncode(SourceAddress) & "&destination=" & URLEncode(DestinationAddress), False
http.send
Set response = http.responseXML

' get the status node. If it isn't OK then we have either an error or no address found.
Set node = response.SelectSingleNode("/DirectionsResponse/status")
If node.nodeTypedValue <> "OK" Then
Distance = 0
Status = node.nodeTypedValue
Else
Set nodes = response.SelectNodes("/DirectionsResponse/route")
If nodes.Length > 1 Then
' this should never happen unless alternatives=true is added on to the URL above.
MsgBox ("Found Multiple Routes for Source Address " & SourceAddress & " and Destination Address " & DestinationAddress & ".")
Else
' Grab the distance value from the XML - it's in meters so we'll divide by 1000 to convert to KM
Set node = response.SelectSingleNode("/DirectionsResponse/route/leg/distance/value")
If node Is Not Null Then
Distance = node.nodeTypedValue
Distance = Distance / 1000
GetDistance = True
End If

End If

End If

Set http = Nothing
Set response = Nothing

End Function
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Ok looks like the above code goes into Marco box

and then you select the cells that contain the address and destination and a blank cell to right for the distance to be calculated

but now my problem is i'm getting a over_query_limit every once and awhile. I looked it up and it's because of how many queries i've sent in under a second (total of 2800 queries in all)

So where do i implement this into the above macro?

url = "MAPS_API_WEBSERVICE_URL"
attempts = 0
success = False

while success != True and attempts < 3:
raw_result = urllib.urlopen(url).read()
attempts += 1
# The GetStatus function parses the answer and returns the status code
# This function is out of the scope of this example (you can use a SDK).
status = GetStatus(raw_result)
if status == "OVER_QUERY_LIMIT":
time.sleep(2)
# retry
continue
success = True

if attempts == 3:
# send an alert as this means that the daily limit has been reached
print "Daily limit has been reached"
 
Upvote 0
No matter which zip codes I enter I am always getting 0. Is there anything else that needs to be done?
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

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