I would like to share with you simple hint – How to use Google Earth as a geocoding tool.
You should have list with addresses and names of the points in Microsoft Exel file.
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://earth.google.com/kml/2.1"> <Document> <Folder> <name> </name> <Placemark> <name> </name> <visibility>0</visibility> <address><![CDATA[ ]]></address> </Placemark> </Folder> </Document> </kml>
So we should take the name of the point and the address and we are putting it in this frame structure.
To do this we have to create simple macro which will create .KML file with the names and the addresses from our Microsoft Excel file.
Sub generateKML() ' ' GenerateKML Macro ' Macro recorded 27/02/2010 by ipt ' ' Set file details Set filePath = [File_details!C2] ' Set document name Set docName = [File_details!C3] Open filePath For Output As #1 'Write header to file outputText = [File_details!C5] & docName & [File_details!C6] Print #1, outputText 'Start to loop through stations For Each cell In [Data!A2.A50001] pmName = cell.Offset(0, 0) pmAddress = cell.Offset(0, 1) If pmName = "" Then Exit For End If 'Create a placemark outputText = [File_details!C8] & pmName & [File_details!C9] & pmAddress & [File_details!C10] Print #1, outputText Next 'Write footer to file outputText = [File_details!C12] Print #1, outputText Close #1 ' End Sub
We are creating two sheets – Sheet “data” – is where we have to paste the names and the addresses
The second Sheet is called “File_Details” and in this sheet we should describe the structure of the .KML file.
We are describing output file name and directory also.
The macro will Loop thru the record set from “Data” Sheet and it should Save data as .KML file.
When you open the file with Google Earth it will start geocoding. For approximately 2500 POI – raw addresses with removed fullstops it took 30 minutes to geocode
To get the geocoded data you have save your file as .KML from Google Earth in specific directory.
After that you opening saved file with Microsoft Excel.
Microsoft Excel will ask you to open this file as an .xml table(picture)
And opening the file you should see something like: