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

 

[gaj104]

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.

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.

 

[Teak McGlukes]

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.

 

[Teak McGlukes]

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.

 

[ocguy106]

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

 

[Teak McGlukes]

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.

 

[gaj104]

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.

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

 

[Taul]

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/library/ms762244(VS.85).aspx

 

[lawboy]

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.calcxy.gps.mappoint.html

Thanks

 

[lawboy]

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