How to use Google Earth as a source of geocoding!

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.


.KML format have couple of structures and one of them is:

[php]
<?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>
[/php]

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.

[php]
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
[/php]

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
1875 POI’s

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:


Copy POI and coordinates and bring them back in Microsoft Excel by “Paste special as value”.