Logo Home    Page list

MapEvents-KML query #googlemaps #places #placedetails #geocoding #events

Table of Contents

Google Maps
Google Earth
Bing Maps
This query helps you plot events from your RootsMagic database on Google Maps, Google Earth, and Bing Maps, provided there are geo-coded Places and Place Details (sites) in your database. It generates results in the Keyhole Markup Language (KML) that can be pasted into a KML shell file and then imported into these mapping services. Google Maps provides the most useful display but the KML file must be stored on a network server (could be a HTTP server on your local computer). Using their respective API or SDK interface would provide superior results from all three but that is beyond my capability.

Google Maps

Example showing Placemark callout for the event selected in the sidebar, corresponding to the record highlighted in the SQLiteSpy screenshot below. The underline signifies the coordinates are for the Place Detail (Site).

Example of Google Maps callout when a placemark is clicked that has multiple events with the same coordinates.

Click on this link to view the file that populated the Google Map above: MapEvents.kml
Copy and paste its URL into the Google Maps Search form to view on Google Maps: http://sqlitetoolsforrootsmagic.wikispaces.com/file/view/MapEvents.kml
Right-click and Save (download) this file to your local drive to import into Bing Maps and Google Earth.

Download this query to run on your data: MapEvents-KML.sql
Be sure to change the parameters in the query to suit your requirements. If your data has no events in the Oshawa area before about 1902, my parameters will find nothing!

Copy and paste the results of the query into this shell where instructed using a text editor and save as a .kml file: MapEvents-shell.kml
N.B.: you must restrict the number of Placemarks to 200 or fewer in your KML file.
Screenshot of SQLiteSpy after running query that produced the Placemarks for the KML file on this page.

Google Earth

If the Wikispaces server issued the proper mime type for a KML file and Google Earth is installed on your machine, clicking on: MapEvents.kml would launch Google Earth with these Placemarks imported. As it does not, you have to download the file to your local drive, open Google Earth and import the file from your drive.

Bing Maps

Since this page was first authored, I have learned that one can pass the KML file to Bing Maps without having to download and import. In fact, the Import function has disappeared from where it was. Now (and maybe then, too), one can pass the URL of the KML file to Bing Maps in this fashion:
For example, this URL should open Bing Maps with the above referenced KML file that we have seen in Google Maps and Google Earth and result in a display similar to this screenshot:

Each RootsMagic event is numbered uniquely. The number inside the place mark is, I think, that of the highest numbered event in that location. Hovering over an event in the list pops up a small title label beside its location on the map. While the Google products enable you to browse all the events in a place, Bing does not directly although one could sort them by place and scroll through while hovering. However, that hover/scroll feature might also be used to advantage with other sort orders - e.g., by person and date; with Google, it would be very clicky traversing a person's events but with Bing, just hover down the list, clicking only on those where the description is needed.


This was an earlier discovery that I never explored very far, perhaps because it did not support KML at the time. Now it does and one need simply place the URL of the KML file in a form. While I am disappointed with its labelling and listing capabilities, it offers other useful features including conversion between data formats, e.g., upload CSV and download KML or vice versa. And it also does geocoding worldwide with finer resolution for USA, down to street address level. Here's an example of the same KML file rendered in geocommons:
You can explore the same map online at http://geocommons.com/overlays/406864. Unfortunately, the values in the place list are truncated to a useless state and the HTML underlining for Place Details is mishandled.