Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Reverse Geocoding

This is a discussion on Reverse Geocoding within the Excel Questions forums, part of the Question Forums category; Hey guys, just want to let you know first of all, I don’t post a lot on the forums- but ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    14

    Default Reverse Geocoding

    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

  2. #2
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Posts
    846

    Default Re: Reverse Geocoding

    Took a bit of research but the codes fairly simple.

    Firstly add the reference Microsoft XML, v6.0

    Put in your lat in A1 lng in A2 and when run will populate all the details in A4.

    Code:
    Sub GeoCoding()
    
    Dim XMLDoc As New DOMDocument
    Dim XMLNode As IXMLDOMNode
    Dim i As Long
    Dim lat As Double, lng As Double
    
    lat = Range("A1")
    lng = Range("B1")
    
    XMLDoc.Load "http://ws.geonames.org/findNearestAddress?lat=" & lat & "&lng=" & lng
    
    Do Until XMLDoc.readyState = 4
    DoEvents
    Loop
    
    If Len(XMLDoc.Text) = 0 Then
        Call MsgBox("No data!")
        Exit Sub
    End If
    
    Set XMLNode = XMLDoc.selectSingleNode("//geonames/address")
    
    For i = 0 To XMLNode.childNodes.Length - 1
    
        Cells(i + 4, 1).Value = XMLNode.childNodes(i).baseName & ": " & XMLNode.childNodes(i).Text
    
    Next i
    
    End Sub
    Change it round to your needs but this effectively gives you what you need.

    Hope it helps.
    graemejones.co.uk My website!

  3. #3
    New Member
    Join Date
    Jan 2010
    Posts
    14

    Default Re: Reverse Geocoding

    gaj104,

    Thanks so much for putting the time into helping me on this one!

    I am not super familiar with VBA, but I opened a new module and placed the code you gave me into that module.

    I then hit 'Compile VBA Project', and it gave the following error:

    Compile error:
    User-defined type not defined.

    Do you have any ideas why it would be doing that? Thanks again.

    Teak.

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    14

    Default Re: Reverse Geocoding

    gaj104,

    Nevermind, I got it. I didn't have the reference in place. I had to figure out how to stop the objects, then I referenced the XML, it works perfect.

    I cannot thank you enough, you're the best- thank you thank you thank you.

    Teak.

  5. #5
    New Member
    Join Date
    Aug 2007
    Posts
    41

    Default Re: Reverse Geocoding

    This would be very helpful for me as well. I copied the code and I get a compile error. What am I doing wrong

  6. #6
    New Member
    Join Date
    Jan 2010
    Posts
    14

    Default Re: Reverse Geocoding

    gaj104,

    I know I am asking a lot after you have already helped me so much, but I was wondering if you could teach me how to have this keep running until it has a result to display. I am running this in a loop that repeats, and when it errors out and shows the ‘No Data!’ error, it stops the macro, is there a way to keep it running until it has something to show?

    Thanks a million.

  7. #7
    Board Regular gaj104's Avatar
    Join Date
    Nov 2002
    Posts
    846

    Default Re: Reverse Geocoding

    Glad to help. Good to see you got it working.

    ocguy106, I'm imagining you didn't add in the reference.

    Teak, as for you loop, i'm unsure how how you are running it. But, if its something pseudo like:

    run GeoCode with lat and long. If no result, change lat, long. Check again and loop until there is a result. So in that case get the GeoCode to return whether it was successful or not. If not then loop else stop. i.e.

    Code:
    Sub MainLoop()
    
    Do While GeoCoding(Range("A1").Value, Range("B1").Value) = False
    
        Range("A1").Value = Range("A1").Value + 1
        Range("B1").Value = Range("B1").Value - 1
    
    Loop
    
    End Sub
    
    Function GeoCoding(lat As Double, lng As Double) As Boolean
    
    Dim XMLDoc As New DOMDocument
    Dim XMLNode As IXMLDOMNode
    Dim i As Long
    
    GeoCoding = False
    
    XMLDoc.Load "http://ws.geonames.org/findNearestAddress?lat=" & lat & "&lng=" & lng
    
    Do Until XMLDoc.readyState = 4
    DoEvents
    Loop
    
    If Len(XMLDoc.Text) = 0 Then
        GeoCoding = False
        Exit Function
    End If
    
    Set XMLNode = XMLDoc.selectSingleNode("//geonames/address")
    
    For i = 0 To XMLNode.childNodes.Length - 1
    
        Cells(i + 4, 1).Value = XMLNode.childNodes(i).baseName & ": " & XMLNode.childNodes(i).Text
    
    Next i
    
    GeoCoding = True
    
    End Function
    graemejones.co.uk My website!

  8. #8
    Board Regular Taul's Avatar
    Join Date
    Oct 2004
    Location
    Uxbridge
    Posts
    382

    Default Re: Reverse Geocoding

    Quote Originally Posted by ocguy106 View Post
    This would be very helpful for me as well. I copied the code and I get a compile error. What am I doing wrong
    This may help

    http://msdn.microsoft.com/en-us/libr...44(VS.85).aspx
    Getting old is not an achievement, you just have to wait.

    Excel 2010 on Win7 at home - 2007 on win7 at work

  9. #9
    New Member
    Join Date
    May 2011
    Posts
    4

    Default Re: Reverse Geocoding

    Hello,
    I know this is an old thread, but I would like to know if this method can somehow be better by doing a radius search around a lat long value so instead of just adding a value to increment a lat and long like this:

    Range("A1").Value = Range("A1").Value + 1 'lat
    Range("B1").Value = Range("B1").Value - 1 'long

    I would like the ability in VBA to specify a radius and then generate all lat long values belonging inside the radius in order to reverse geocode as specified:
    http://www.mp2kmag.com/a130--snap.ca....mappoint.html

    Thanks
    Last edited by lawboy; Sep 11th, 2011 at 04:25 AM.

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    4

    Default Re: Reverse Geocoding

    What I am looking for is the code and equation that creates a specified circumference(.25 mi.) around a given lat long point and then generates all lat long coordinates at a specified distance (every 15Ft.) inside the parameter of the specified circumference.

    Thanks

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com