Reverse Geocoding

Teak McGlukes

New Member
Joined
Jan 16, 2010
Messages
14
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
This would be very helpful for me as well. I copied the code and I get a compile error. What am I doing wrong
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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