Map Locations in Google Earth

From time to time I will be adding items to these pages giving details of how to achieve certain objectives, usually related to genealogy and the use of Legacy software, often in conjunction with other software.

I am not suggesting that the solutions offered are the only, or indeed the best, options to solving problems but simply that they are ways which I have found to work.

* 'V' indicates a video is available

The icons below are links to any other software mentioned.

Create Google Earth Location Layers from Legacy Genealogy Databases

Carlisle Birth Explosion!

Two articles by Howard Mathieson in the Journal of One-Name Studies, ¹ describing how to map locations on Google Earth using Earth Plot, caused me to wonder if this could be done using the Latitude and Longitude data stored in the Legacy .fdb files. I decided that it should be possible, and this tutorial shows how this can be done.

My first project was the plotting of birth locations for the deceased from my Ferguson One-Name Study and the main details of the procedure are given below.

Software Required:

A working knowledge of PHP, MySQL, and of either Access SQL of HSQL (OO.o) is required to use this method. I have used PHP because I am more comfortable with it, and with MySQL rather than with HSQL. I recommend "How to do everything with PHP & SQL" by Vaswani - see (My Review)

Joomla!StandAlone Server is optional as many web hosts have Joomla! installed and this can be used instead. If you have Access available then OO.o is not required; the method of opening the Legacy.fdb file is given in a separate tutoral (see left panel).

Data Selection

Following the pattern detailed by Howard, I wanted the final display attached to the pin in GE. to comprise Name, ID, date of birth and location. As in some cases I had no location for a birth, only for a christening, I decided, for this purpose, to use this data as though it was the birth data. For compactness, the Short Location Name was also used, although the Long Location Name is used by Legacy to identify the co-ordinates.

This naturally leads to the selection of the required data, viz. the date and place of birth or christening, Latitude, Longitude, Given Name and Surname, and ID; for people who are deceased and whose surname sounds like "Ferguson" ("F622").

Legacy stores the information required in two tables; "tblIR" contains the personal details, and table "tblLR" the locations. In OO.o the basic SQL required to create the new table is:

SELECT `tblIR`.`IDIR`, `tblIR`.`Surname`, `tblIR`.`GivenName`, `tblIR`.`BirthSD`, `tblLR`.`ShortName`, `tblLR`.`Latitude`, `tblLR`.`Longitude` FROM `tblIR`, `tblLR` WHERE ( `tblLR`.`Latitude` <> 0 AND `tblLR`.`Longitude` <> 0 AND `tblIR`.`IDLRBirth` = `tblLR`.`IDLR` AND `tblIR`.`SoundsLike` = 'F622' AND `tblIR`.`Living` = 1 AND `tblIR`.`IDLRBirth` <> 1 OR `tblLR`.`Latitude` <> 0 AND `tblLR`.`Longitude` <> 0 AND `tblIR`.`SoundsLike` = 'F622' AND `tblIR`.`Living` = 1 AND `tblIR`.`IDLRBirth` = 1 AND `tblIR`.`IDLRChris` = `tblLR`.`IDLR` AND `tblIR`.`IDLRChris` <> 1 )

The resulting table after running this query I converted to CSV (click for OO.o CSV creation) and imported into Joomla's DB. There are two points to note, a comma cannot be used as an end of record identifier because it is used in the location fields, and if you wish to retain nicknames a single quote must be used to identify the fields, otherwise remove the nicknames in the CSV.

Import into Joomla!

After setting up Joomla and creating the DB the next task is to create the the table. The one which I used is shown on the right and note that I have used the IDIR field as the Primary Key, as by definition this must be unique. This table is only temporary, in the sense that I only need it to hold the basic data which I will manipulate as follows:

The year needs to be extracted from "BirthSD"
Lat. and Long. need to be converted to decimal
The GivenName and Surname need to be concatenated, the output from 1. added together with the IDIR, birth year and the ShortName (location)

Legacy stores the cordinates as a number comprising degrees, minutes, seconds, and Earth Plot requires that it be sumitted in the decimal format. The year comprises the first four digits of the BirthSD field.

Putting the whole together will give another table which you need to create, again shown on the right with three columns such that a row will read eg. " -2.936783 | 54.895366 | Carlisle, Cumberland | John Ferguson [85] b. 1881 Carlisle, Cumberland", where "[85]" is the ID number which ensure this column will be unique and can be used as the primary key.

The php script truncates (empties) the second table, it then takes the data from the first table and performs the manipulations, before filling the second with it.

Extracts from the PHP scrip

Truncate the second table and extract from the first

$query="TRUNCATE MapData"; $result=mysql_query($query) or die ('Cannot truncate: $query. ' . mysql_error()); $query="SELECT * FROM fergysmap"; $result=mysql_query($query) or die ('Error in query: $query. ' . mysql_error());

Convert the Co-ordinates to Decimal Format

function convertDegreesToDecimal($cord)
{
$absCord = Abs($cord);
$min = fmod($absCord, 10000);
$deg = ($absCord - $min)/10000;
$sec = fmod($min, 100);
$NewMin = ($min - $sec)/6000;
$NewSec = $sec/3600;
global $NewCord;
$NewCord = $deg + $NewMin + $NewSec;
if ($cord < 0)
{
$NewCord = $NewCord * -1;
}
}

Set up the Data

if (mysql_num_rows($result) >0)
{
while ($row = mysql_fetch_object($result))
{
$Lon = $row ->Longitude;
$Lat = $row ->Latitude;
$Place = $row -> ShortName;
$combine = $row ->GivenName . ' ' . $row -> Surname . ' '. '['
 . $row->IDIR . ']: ' . 'b.' . ' ' . substr($row ->BirthSD, 0, 4);

NB. The $combine equation is all on one line.

Call the function

// Convert Longitude to Decimal
convertDegreesToDecimal($Lon);
$Lon = $NewCord;

// Convert Latitude to Decimal
convertDegreesToDecimal($Lat);
$Lat = $NewCord;

Insert into the Second Table

$queryInsert ="INSERT INTO MapData (Longitude, Latitude, Place, Data) VALUES ('$Lon', '$Lat', '$Place', '$combine' )"; $resultInsert = mysql_query($queryInsert) or die ("Error in query: $queryInsert . " .mysql_error());

Import into Earth Plot

Earth Plot requires the CSV file which is to be imported to be in the Excel Format, so Export the second DB in that format before importing.

In the Data section of Earth Plot insert the headings: Longitude, Latitude, Place, and Event for the four columns, then select these on the Options screen, before clicking the GE Globe to export. On the right is shown an individual label. The end result can be opened here (GE Needed).

Acknowledgement

Grateful acknowledgement is made to Howard Mathieson for his articles in the Vol 10 Issue 5 and Issue 6 editions of the Journal of One-Name Studies, the quarterly publication of the Guild of One-Name Studies (non-members: £2.00/copy) which stimulated my interest in this subject and introduced me to Earth Plot.

Printer friendly version