Remove From My Forums

Finding Latitude and Longitude from address in database field [MS Access 2010]

Question
0

Sign in to vote

Has anyone created a database that will retrieve Longitude and Latitude, storing that information in field of that same table?

Record for address: 1600 Pennsylvania Ave NW, Washington, DC 20006

On click, retrieve Latitude / Longitude

Save in Field

Any examples would be greatly appreciated!

Friday, August 5, 2011 2:30 PM

Answers

ryan: I had to recreate the code because I couln't find the my oringal code. I was very busy this weekend and my 1st attempt failed. the Latitude and longitude is burried inside the goolgle map where it isn't easy to grab. I had to tunnel through the page to get the data. Try this solution. I still lokking forward to a beer.

Sub Getlatitude()

Const READYSTATE_COMPLETE = 4

StreetAddress = InputBox("Enter Location : ")

URL = "http://www.google.com/maps"
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
 
    
  ie.Navigate URL

  Do While ie.readystate <> 4 Or ie.busy = True
   DoEvents
  Loop
 
  Set Input_box = ie.document.getelementbyid("q_d")

  Input_box.innertext = StreetAddress
    
    
  Set submit = ie.document.getelementbyid("q-sub")
  'submit.Select
  submit.form.submit
    
  Do While ie.readystate <> 4 Or ie.busy = True
   DoEvents
  Loop
  Set Script = ie.document.getelementsbytagname("script")
  
  For Each itm In Script
   Startchar = InStr(itm.outerHTML, "span:{lat:")
   If Startchar > 0 Then
   
     endchar = InStr(Startchar, itm.outerHTML, "}")
     If endchar > 0 Then
      Location = Mid(itm.outerHTML, Startchar, endchar - Startchar)
      'remove string before '{
      Location = Mid(Location, InStr(Location, "{") + 1)
      Location = Split(Location, ",")
      Location(0) = Replace(Location(0), "lat:", "")
      Location(1) = Replace(Location(1), "lng:", "")
      
      MsgBox ("Latitude : " & Location(0) & vbCrLf & _
          "Longitude : " & Location(1))
      Exit For
     End If
   End If
  Next itm

Set ie = Nothing

End Sub

jdweng

Proposed as answer by ryguy72 Monday, August 8, 2011 3:48 PM
Marked as answer by Mark Matzke Thursday, August 11, 2011 8:47 PM

Sunday, August 7, 2011 11:45 PM

All replies

0

Sign in to vote

Hi Mark,

Why storing the Latitude and Longitude, while these are available for download?

There are one's you pay for, and are up-to-date:

http://www.zipinfo.com/products/z5ll/z5ll.htm

And one's not up-to-date, but for free:
http://databases.about.com/od/access/a/zipcodedatabase.htm

So when you have the database, you can them do a search routine to find the corresponding Latitude and Longitude.

Btw, FMS has a nice database created, which does already something you are looking for:

http://www.fmsinc.com/microsoftaccess/zipcodedatabase.html

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Friday, August 5, 2011 3:38 PM
0

Sign in to vote

The latitude and longitude are in google map html file. You can easily view the text in the webpage after entering the address in the search box. I don't have the code with me, but can get you the VBA code later today.
jdweng

Friday, August 5, 2011 4:55 PM
0

Sign in to vote

Joel, That would be fantastic!

Friday, August 5, 2011 5:26 PM
0

Sign in to vote

I can send you a sample later, when I get home. I can't do it from the office (blocked).

Friday, August 5, 2011 9:00 PM
0

Sign in to vote

Maybe this will get you started:

http://www.mediafire.com/?z9qjna4kofotz11

Saturday, August 6, 2011 9:14 PM
0

Sign in to vote

http://www.mediafire.com/?z9qjna4kofotz11 was blocked by my firewall as well (or it might have been norton)

Sunday, August 7, 2011 9:40 PM

Sub Getlatitude()

Const READYSTATE_COMPLETE = 4

StreetAddress = InputBox("Enter Location : ")

URL = "http://www.google.com/maps"
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
 
    
  ie.Navigate URL

  Do While ie.readystate <> 4 Or ie.busy = True
   DoEvents
  Loop
 
  Set Input_box = ie.document.getelementbyid("q_d")

  Input_box.innertext = StreetAddress
    
    
  Set submit = ie.document.getelementbyid("q-sub")
  'submit.Select
  submit.form.submit
    
  Do While ie.readystate <> 4 Or ie.busy = True
   DoEvents
  Loop
  Set Script = ie.document.getelementsbytagname("script")
  
  For Each itm In Script
   Startchar = InStr(itm.outerHTML, "span:{lat:")
   If Startchar > 0 Then
   
     endchar = InStr(Startchar, itm.outerHTML, "}")
     If endchar > 0 Then
      Location = Mid(itm.outerHTML, Startchar, endchar - Startchar)
      'remove string before '{
      Location = Mid(Location, InStr(Location, "{") + 1)
      Location = Split(Location, ",")
      Location(0) = Replace(Location(0), "lat:", "")
      Location(1) = Replace(Location(1), "lng:", "")
      
      MsgBox ("Latitude : " & Location(0) & vbCrLf & _
          "Longitude : " & Location(1))
      Exit For
     End If
   End If
  Next itm

Set ie = Nothing

End Sub

jdweng

Proposed as answer by ryguy72 Monday, August 8, 2011 3:48 PM
Marked as answer by Mark Matzke Thursday, August 11, 2011 8:47 PM

Sunday, August 7, 2011 11:45 PM

1

Sign in to vote

Joel, I'm not the OP. However, I did test the code and, of course, it works excellent!!

If I tried to do this, I would have looked for this tag:

<body onload="onLoad()">

Or, I would have looked for this:

gmnoprint

Then, I would have done a little parsing to get the values. You used a totally different technique. I looked for 'itm.outerHTML' and I looked for 'outerHTML'; I couldn't find either.

When are you coming into NYC next? Send me an email. I'm available for that beer, or more likely several beers, anytime.

Monday, August 8, 2011 3:48 PM

Answered by:

Finding Latitude and Longitude from address in database field [MS Access 2010]

Question

Answers

All replies