Polling the Google Maps API from VBA - possible?

[Banista]

Hi all,

I want to integrate Geocoding into an Access file, but I'm not sure if this is even possible. I honestly do not know much about Web programming, so I might be missing something fundamental.

Here's a document detailing how to get geocoding information from Google Maps using HTTP:
http://www.developer.com/db/article.php/3621981

Any pointers you can give me on how to approach this would be great. Or just telling me that there's no way to accomplish this from within VBA

Bani

 

[Banista]

anybody with some input? Any thoughts on this matter would really be helpful for me at this point

 

[Oldsoftboss]

Maybe a bit more info might help. Sound like a fun project we could get involved in. What do you want to do with the data? How do want the data displayed?

Dave

 

[Banista]

Hello Dave,

thanks for your interest, and my apologies for answering so late. I was convinced I'd be notified by email about a new reply

Anyway, the basic idea is to visualize a supplier / customer network in Google Earth.

The way I do this is to have a client table that looks like this as my starting point:

Client name
Street Address
ZIP
City
Country
CustomParameter 1-n (orders / year, or similar criteria).
Geocoding Latitude
Geocoding Longitude

Now, what my program does is to create a Google Earth .kml file with VBA, based on said Access table (it's a small form + module within Access)

The KML language is very similar to XML, and .kml files can be opened directly with google earth.

Here's an example output file of the VBA routine:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://earth.google.com/kml/2.1">
<Document>
<name>Input table Template.kml</name>
<Style id="Supplier_Icon_Normal_1">
<IconStyle>
<color>fffff0000</color>
<scale>2.2500000000</scale>
<Icon><href>http://maps.google.com/mapfiles/kml/shapes/donut.png</href></Icon>
</IconStyle>
<LabelStyle>
<color>77ffffff</color>
<scale>1</scale>
</LabelStyle>
</Style>

<Style id="Supplier_Icon_Highlight_1">
<IconStyle>
<color>fffff0000</color>
<scale>2.75</scale>
<Icon><href>http://maps.google.com/mapfiles/kml/shapes/donut.png</href></Icon>
</IconStyle>
<LabelStyle>
<color>ffffffff</color>
<scale>1</scale>
</LabelStyle>
</Style>

<StyleMap id="Nat_Supplier_Style_1">
<Pair>
<key>normal</key>       <styleUrl>#Supplier_Icon_Normal_1</styleUrl>
</Pair>
<Pair>
<key>highlight</key>        <styleUrl>#Supplier_Icon_Highlight_1</styleUrl>
</Pair>
</StyleMap>


<Folder><name>Input table Template</name>
<open>0</open>
<Placemark><name>Test LLC</name><styleUrl>#Nat_Supplier_Style_1</styleUrl>
<description>Supplier Nr: AVC12068
Nr of sub-clients: 217
really awesome?: False
Supplier Volume: 2,21 orders per year
Address:
Kaiserring 6, 68161 Mannheim, GERMANY
</description>
<Point><coordinates>8.4707210000,49.4805660000,0</coordinates></Point></Placemark>
<Placemark><name>MHello Access World Forum Company</name><styleUrl>#Nat_Supplier_Style_1</styleUrl>
<description>Supplier Nr: VFC0889051
Nr of sub-clients: 80
really awesome?: True
Supplier Volume: 5.3 orders per year
Address:
Untermainanlage 15, 60313 Frankfurt, Germany
</description>
<Point><coordinates>8.6727070000,50.1083540000,0</coordinates></Point></Placemark>
</Folder>
</Document>
</kml>

Feel free to copy/paste this into a new file, name it xy.kml, and then open it with Google Earth

Okay. Now, those geocoding coordinates are typically not available in a company's database, whereas address data is. So currently, you have to take the address data from the access table mentioned above, and go to a batch geocoding website such as http://www.wotton.org/map/batchgeo.html
to obtain the latitude and longitude.

This is a really painstaking manual process, as imperfect or strangely formatted address data won't lead to correct results, requiring several passes, trying only the ZIP code etc. Plus it relies on some obscure free website

Therefore, I want to automize this, and do the geocoding directly from Access, and write the results in the table. Based on the return codes from Google Maps, you can know whether you found the right match, and do all kinds of CASE, IF/WHEN or similar routines to make sure the best possible coordinates are obtained.

I hope this gives you a better picture, and maybe you can even come up with some pointers on how to approach this.
Bani

 

[gemma-the-husky]

banista

i know this is an old thread

i am trying to do a similar thing

I've got the google url string set up - easy to explain how to do this

but instead of displaying the results i need to scrape the required info to get the coords - any idea how to do this?