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:

<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="">
<address><![CDATA[ ]]></address>

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


‘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
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”.

Google Maps Mashups 1

Climate Hot Map

The Union of Concerned Scientists has created this Google Map to show the probable effects of global warming around the world. The map is accompanied by a Climate Hot Map Scavenger Hunt, which if you complete successfully gives you a chance to win a trip for two to the Rio Cachoeira Natural Reserve in Brazil.

The map explores the effect of climate change on people, the environment, the oceans, ecosystems and the temperature. You can select to explore any of these categories on the map using the menu at the bottom of the map.

The ash cloud created by the eruption of the Puyehue volcano in Chile continues to cause huge disruption to plane flights in Australia. Real-time flight tracking website is using Google Maps to show the location of the ash cloud as it drifts around the southern hemisphere.

The ash cloud is predicted to linger over south-east Australia for some time, causing widespread disruption to flights in and out of Sydney and Melbourne. At the time of writing the map shows a few flights in and out of south-east Australian airports but nowhere near the flight activity that can be seen in and around Perth.

Mibazaar has created a Google Maps based application to explore where people are searching for a given keyword in Google Search.

In this demo of the application you can view where people are searching for different makes of Ford car around the world. For each make of car you can view the ten locations where the most people are searching for that Ford.

The map includes historical data so you can view how searches have changed over the years for each make of car.

Mibazaar – Google Trends – Ford

SailorsMap is a Google Map designed to help boat owners find useful places nearby.

Marinas and local stores that may be useful are added to the map on the fly. If your browsing device supports GPS then SailorsMap is automatically positioned at your current location.

As well as displaying nearby points of information, found via Google Maps Search the map, displays the nautical anchorages of Croatia.


The Instituto Brasileiro de Geografia e Estatística has created an application that allows you to browse the results of the 2010 Brazilian census on a Google Map.

Using the application you can click on a census tract on the map and view demographic information collected in the census. The information includes the population, the percentage of men and women and the percentage of different age groups in the population.

Dotter Example – San Francisco Crime Map

This Google Map displays 5000 crimes in San Francisco almost instantly on a Google Map. The map was created with the Google Maps API and the Dotter.js, a javascript class that generates data URIs.

The crimes displayed were committed in San Francisco between the 25th April and 25th May 2011. What is really impressive about the map is how quickly the 5000 data points load on the map.

If you want to create your own super-fast map with thousands of data points then the Dotter.js class is available on Github

Quake-Catcher Network

Many laptops these days are built with accelerometers that are designed to protect your hard drive from damage. The accelerometer detects sudden movement and can switch the hard drive off so that the heads don’t crash on the platters.

The Quake-Catcher Network realised that they could create the world’s largest and densest earthquake monitoring system simply by using the data from accelerometers in the world’s laptop computers. The Quake-Catcher Network links participating laptops into a single coordinated network that can detect and analyze earthquakes faster and better than ever before.

QCN uses Google Maps to show the data collected from participating laptops and from participating desktop computers with USB sensors. The map also shows the latest USGS reported earthquakes.

Live Call Map – OnSIP

OnSIP, a provider of Voice over IP calls has released a real-time Google Map of calls made using its service.

The map makes nice use of the drop marker animation in the Google Maps v3 API. Each time an OnSIP customer makes or receives a call, a map marker is dropped on the live map, openly displaying call volume peak and trend information. A marker is dropped on the map every time an OnSIP customer makes or receives a call.

Map Channels

Map Channels, the popular Google Maps creation tool, now lets you add data to a map from Google Fusion Tables.

In the four years that Map Channels has been running over 20,000 maps have been created by its users. It has proved popular with casual map makers and with major news organisations., including Fox News and CBC.

You can create a Google Map with Map Channels using data from a KML, a Google Spreadsheet, a GeoRSS feed, tab delimited text and now with a Google Fusion Table. You can see an example of a Map Channels created map with data provided by a Fusion Table in this Wikipedia Events Map. has created a Google Map of some of America’s most interesting trails. The map includes the route of the Appalachian Trail, the Pacific Crest Rail, the Continental Divide Trail and many more.

When you select a trail its route is displayed on the map. You can then select to view waypoints, points of interest, parking, camping spots and photos along the trail from a drop-down menu.

As well as providing a Google Map of the trails also provides a full set of printable maps for each trail.

Ofcom – Broadband Speeds Map

Ofcom, the regulator of the UK communications industries, has created a Google Map to show the speed of broadband throughout the UK.

Each county in the UK has been ranked on how they score on four broadband metrics; average sync speed, percentage getting less than 2Mbit/s, superfast broadband availability and broadband take up. The map displays as a basic heat map with each county coloured to show how they perform overall on each metric.

The map confirms what you already probably suspected. If you want superfast broadband then you are more likely to be lucky if you live in a big city. If you live in the Outer Hebrides then you are probably going to have make do with superslow broadband.



Cross from Google Maps Mania

Resources for the latest Christchurch earthquake

It was just last September that we were discussing the earthquake that struck Christchurch, New Zealand, and now they’ve been hit by another powerful quake.

As you might expect, and as reported by Google Maps Mania, there are already a variety of resources available to assist those in the area.

The easiest is the Google Person Finder for Christchurch, to help track down anyone that has been affected by the earthquake. You can use the site if you’re looking for someone, or if you know the condition of someone that others may be looking for. As of now, the site already has nearly 5000 records.

Along those lines, I’m very happy to report that Frank is doing well. Him and Karen were in Christchurch when the quake struck, and while they saw a lot of damage around them, they are unharmed. You can read about it on the Tahina Expedition blog, which includes some photos he has just taken in Christchurch.

cathedral.jpgAnother great resource for the quake is this very useful Ushahidi-powered site, which provides information about hospitals, road conditions, hazard zones and other related information. It’s an excellent resource to have available if you’re on the ground in the area.

ushahidi.pngFinally, I encourage you to check out the Christchurch Quake Map, which shows all of the quakes and aftershocks in the area for the past year (nearly 5,000 of them). You can filter the map to show only the 55 quakes that have struck in the past 24 hours.

quake-map.pngIf you’re aware of any other great resources, please leave a comment and we’ll update this post as needed.