Contents
Description
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. 2020-05-18 there no longer seems to be a way to convey or overlay a collection of points in a XML/XMZ file to Google Maps and Bing Maps; however, ArcGis and OpenStreetMaps are possibilities.
Google Maps
Preserved here to appreciate what once was possible…
 |
| 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.com/wp-content/uploads/2019/01/MapEvents.kml
Right-click and Save (download) this file to your local drive to import into Bing Maps and Google Earth.
Download Query
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
Preserved here to appreciate what once was possible…
The ability to use with Bing Maps a KML or KMZ file or any collection of points in a file has disappeared completely. That the URL reported below worked when the Import control had already disappeared must have been during the transition from full support to none. Tom Holden, 2020-05-18.
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:
http://www.bing.com/maps/default.aspx?mapurl=URLofKMLfile
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.
GeoCommons now ArcGis.com
It appears that the GeoCommons online mapping service was discontinued in or around 2014. There is a possibly related service at ArcGis.com with a free, public tier. (2019-01-27 TH) ArcGis can only import a zipped shapefile (ZIP), a table (CSV), a GPS Exchange Format (GPX), or a GeoJSON file. Support for the CSV format should be a fairly easy adaptation of the script MapEvents-KML.sql.
Preserved here to appreciate what once was possible…
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.
OpenStreetMaps
There are a number of ways to overlay KML data onto OpenStreetMaps. One of them is uMap. Using the English version from https://umap.openstreetmap.fr/en/ (there are others), I was able to import the XML file to a layer with this result:
Because the labels for events at the same place stack on top of each other, I would restrict my query to one fact type. Further exploration and some other overlay service may offer better display for co-sited events.