Reverse Geocoding

[Teak McGlukes]

Hey guys, just want to let you know first of all, I don’t post a lot on the forums- but I sure do use a TON of the information I find here, it’s so helpful. It’s great to know there is a community of people out there so ready to assist others for free and put in hours of hard work just because they’re nice. So, thank you.

I have a bit of a dilemma. I am trying to do some reverse geocoding in Excel. Reverse geocoding is the process of taking a set of GPS coordinates and turning it into the closest available ‘human readable’ address.

For example, our local sheriff’s office is at 45.520585° latitude by -122.993693° longitude.

There is a service at geonames.org that offers reverse geocoding by a URL API. But, the results come back in XML format, and I just can’t get it to do what I want. I am familiar with web queries, at least ones that come back in standard HTML format. I can write them and insert variables, which I need to do- but I can’t get that working on the XML. You can see what it returns here:

http://ws.geonames.org/findNearestAddress?lat=45.520585&lng=-122.991466

Is there a way I can work with this and get it to accept variables? I would like to enter the coordinates into fields in the worksheet and have it go out and look up the address. When I treat it like a regular web query it doesn’t return all of the information.
Something else interesting I found is a guy that created an excel function out of a similar function from the geonames.org site (under the Latitude and Longitude from Geonames section).

http://www.codeforexcelandoutlook.com/excel-vba/latitude-longitude-functions/

So something like that would be great, but he hasn’t built one for the reverse geocoding, and I don’t know VBA well enough to adapt it.
I guess I’m just looking for some ideas.

Anything helps,
Thanks-
Teak

 

[shg]

That's about 100,000 points ... What would you do with them?

 

[shg]

Sorry, 1200-odd points, but same question.

 

[lawboy]

Hi shg,
I would like these in order to do a reverse geocode. I have a few hundred thousand coords that I would like to take and search for the closest street address. Alot of my lat longs return a blank on the geonames server. So I figured that I could do a calc on the lat longs and change them to represent a circle from the original lat long and query the server at each calc.

Thanks

 

[shg]

A grid rather than a circle:

       --A-- ----B---- ----C-----
   1   Point    Lat       Lon    
   2         30.274643 -97.740338
   3      1  30.257976 -97.759637
   4      2  30.257976 -97.749987
   5      3  30.257976 -97.740338
   6      4  30.257976 -97.730689
   7      5  30.257976 -97.721039
   8      6  30.266310 -97.759637
   9      7  30.266310 -97.749987
  10      8  30.266310 -97.740338
  11      9  30.266310 -97.730689
  12     10  30.266310 -97.721039
  13     11  30.274643 -97.759637
  14     12  30.274643 -97.749987
  15     13  30.274643 -97.740338
  16     14  30.274643 -97.730689
  17     15  30.274643 -97.721039
  18     16  30.282976 -97.759637
  19     17  30.282976 -97.749987
  20     18  30.282976 -97.740338
  21     19  30.282976 -97.730689
  22     20  30.282976 -97.721039
  23     21  30.291310 -97.759637
  24     22  30.291310 -97.749987
  25     23  30.291310 -97.740338
  26     24  30.291310 -97.730689
  27     25  30.291310 -97.721039
  28     26       #N/A       #N/A
  29     27       #N/A       #N/A
  30     28       #N/A       #N/A
  31     29       #N/A       #N/A
  32     30       #N/A       #N/A

The array formula in B3:C36 is

=LLGRID(B2,C2, 1, 0.5)

Function LLGrid(ByVal lat As Double, ByVal lon As Double, _
                ByVal rad As Double, step As Double) As Variant
    ' returns a lat/lon grid of points with spacing step (NM)
    ' within a specified radius rad (NM) of lat/lon
    Const pi        As Double = 3.14159265359
    Const D2R       As Double = pi / 180#       ' degrees to radians
    Const NM2D      As Double = 1852 / 111120   ' nautical miles to degrees
    Dim dLat        As Double
    Dim stepx       As Double
    Dim stepy       As Double
    Dim adOut()     As Double
    Dim n           As Long
    Dim i           As Long
    Dim j           As Long
    Dim iOut        As Long
 
    lon = lon * D2R             ' degrees to radians
    lat = lat * D2R             ' degrees to radians
    rad = rad * NM2D * D2R      ' NM to radians
    stepy = step * NM2D * D2R   ' NM to radians
    stepx = stepy / Cos(lat)    ' NM to radians at middle lat

    n = Int(rad / stepy)
    ReDim adOut(1 To (2 * n + 1) ^ 2, 1 To 2)
 
    For i = -n To n
        dLat = lat + i * stepy
        For j = -n To n
            iOut = iOut + 1
            adOut(iOut, 1) = dLat / D2R
            adOut(iOut, 2) = (lon + j * stepx) / D2R
        Next j
    Next i
 
    LLGrid = adOut
End Function

 

[lawboy]

shg, thank you....

 

[shg]

Y'er welcome.

 

[Liquidgenius]

Hi folks,

This thread helped me get reverse geocoding up and running in Excel. I figured I would share what I came up with in the hopes that it helps others. The code below will check the number of rows in your table and reverse geocode the lat and lng columns to an address using geonames.org's nearest address api. Note that you will have to set the column you want addresses in and the lat and lng must proceed it. It would be great if anyone could give pointers on how to select the lat and lng columns with a mouse and then select the column you want the address to show up in so you don't have to edit the code. Also, you will have to set up a free account with geonames. You must have Microsoft XML 6.0 turned on too.

Public Function geoRevGeocode(lat As String, lng As String) As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  'Dim address As String
   
  'Assemble the query string
  strQuery = "[URL]http://api.geonames.org/findNearestAddress?[/URL]"
  strQuery = strQuery & "lat=" & lat
  strQuery = strQuery & "&lng=" & lng
  strQuery = strQuery & "&username=demo" ' <---- your account username here
  'MsgBox (strQuery)
 
  'define XML and HTTP components
  Dim geonamesResult As New MSXML2.DOMDocument
  Dim geonamesService As New MSXML2.XMLHTTP
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode
 
  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  geonamesService.Open "GET", strQuery, False
  geonamesService.send
  geonamesResult.LoadXML (geonamesService.responseText)
 
  'target the "address" XML node
  Set oNodes = geonamesResult.getElementsByTagName("address")
 
  'Grab the individual nodes and merge into a readable string
  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strStreetNum = oNode.ChildNodes(2).Text
      strStreet = oNode.ChildNodes(0).Text
      strCity = oNode.ChildNodes(9).Text
      strState = oNode.ChildNodes(10).Text
      strZip = oNode.ChildNodes(6).Text
      geoRevGeocode = strStreetNum & " " & strStreet & ", " & strCity & ", " & strState & " " & strZip
    Next oNode
  Else
    'If there is no data then report
    geoRevGeocode = "Not Found"
  End If
End Function
 
Sub RevGeocode_Column()
    'Run a Geonames Reverse Geocode on the selected columns containing addresses.
    'Enter a new address down the column 1 each second to avoid slamming the API.
    'Keyboard Shortcut: Ctrl+3
   
    Dim rng As Range
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range(Cells(1, 3), Cells(FinalRow, 3))
   
    Dim i As Integer
    Dim a As Integer
    'Set a to column of Address, Latitude and Longitude must proceed Address column
    a = 12
    For i = 2 To rng.Count
     Cells(i, a).Value = "=geoRevGeocode(RC[-2],RC[-1])"
     Application.Wait (Now + TimeValue("0:00:01"))
     ActiveWindow.SmallScroll down:=1
    Next i
End Sub

 

[ilieradum]

Hy,
For my it doesn't work:
Compile Error
User defined type not defined

Wat i need to do to get the adress

Thanks

 

[Br0ck]

Did you reference the Microsoft XML library as mentioned above?

 

[ilieradum]

Pls tell me, i dont know how to do it!