* Creating CSV Spreadsheet for Importing into Google Earth

[ColinBeaver]

I have set up a CSV Spread sheet by running a Fact Query and the Map Life Facts Plug-in to provide the data

1. Latitude
Is entered manually from data provided by the Map Life Facts Plug-in.
2. Longitude
Is entered manually from data provided by the Map Life Facts Plug-in.
3. Name
Is listed as a record automatically in the Fact Query CSV Output
4. Description
Is a mail merge of the Relationship, Fact, Fact Date & Location (Address+Place) from the Fact Query CSV Output.
5. Icon
By sort the Facts it is relatively simple to allocate an event related place-maker from Google's mapfiles.
6. Event
Is listed as a Fact automatically in the Fact Query CSV Output.
7. Event Date
Is listed as a Fact Date automatically in the Fact Query CSV Output.
8. Location
Is listed as Address + Place automatically in the Fact Query CSV Output.

It is then very simple to convert to a KML file and import into Google Earth giving a very pictorial picture of where my ancestors lived.

I have also joined place-markers from myself to my most distance related relative which again is gives a very pictorial picture.

As anyone any suggestions on how to reduce the manual input/manipulations.

Thanks
Colin

[tatewise]

I presume you know the Plugin can create Google Maps with plot markers for all Facts of any Individual?

The Plugin options allow the Lati/Longitude to be saved in FH Source records, which could be exported to CSV via a Query,, and merged with your current CSV using Place/Address as the key.

Alternatively, a Plugin could add a Source Citation to each Fact for the matching Place/Address, then the Lat/Longitude could be included directly via your Fact Query.

[ColinBeaver]

Thank you for your response.

Since my original forum posting, I am able to run a query to give me all the information in a a format I require. This only leaves the Latitude & Longitude in a table form.

Yes I had Created the individual Google Maps.

I have also saved the data in Source Code which from my Query produced the following.

"Map Life Facts Address ~ Place Location Substitute = Latitude = 53.381129 Longitude = -1.470085 Plot Mode ="

To achieve my KML requirements, the Latitude & Longtitude need to be in separate columns, my aim now is to adapt the Map Life Facts to output the data not as a Map but the same data in a table - do you believe this is possible.

An advantage for having the data as a table listing, enable the Latitude & Longitude output data to be more easily checked for errors.

I am not clear on your suggestion of "Alternatively, a Plugin could add a Source Citation to each Fact for the matching Place/Address, then the Lat/Longitude could be included directly via your Fact Query."

Thanks

[tatewise]

Currently you are extracting the entire Note field of the Source Record into one Query Column.

You can use the =GetLabelledText() function to extract each value into a separate Query Column.
e.g.
=GetLabelledText(%...NOTE2%,"Latitude = ")
and
=GetLabelledText(%...NOTE2%,"Longitude = ")
where
%...NOTE2% is the data reference you currently use.

With that adjustment may be your other suggested techniques are unnecessary?

[ColinBeaver]

Thank yo again for your help.

This has enabled me to run
A Fact Query
provides the Name, Relationship, Fact, Fact Date & Location (Address + Place).

An Individual Query
provides the Name & Record Number

A Repository Query
Output from the Map Life Facts is used to provides the Latitude & Longitude.

These three spreadsheets are combined and converted to a KML file.

I can now import into Google Earth carrying out a wide variety of pictorial illustrations.

It is worth mentioning the the Map Life Fact Plugin is a great piece of programming.

Thanks
Colin

[Valkrider]

Colin

Can you show us what your Google Earth output looks like? You can always attach a screen shot or two to a post.

[tatewise]

Colin's request would indeed by very interesting, and could form the basis of a new Knowledge Base entry.

BTW: You can include the Record Id Number in the Fact Query if that helps. Just define a Column with =RecordId( GetRecord(%FACT%) ).

However, I am interested in how you handle Marriage Events that have two owner Names.
Perhaps you always use just the Fact Owner 1 husband column =FactOwner(%FACT%,1,MALES_FIRST)
In which case the Record Number Id is given by =RecordId( FactOwner(%FACT%,1,MALES_FIRST) )

[ColinBeaver]

Hi Mike

Unfortunately I am unable to send a screen shot of my Google Earth Output as it is larger than the allowed 256KB size attachment

Regards
Colin

[tatewise]

Can you use an image editor such IrfanView to Resize/Resample the resolution and thus reduce its size?

BTW: Did my suggested =RecordId(...) function work?

[tatewise]

Colin has Emailed his screenshots to me in a PDF, and I have cropped them and attached them here.

Google Earth Overall View.jpg (59.07 KiB) Viewed 8508 times

Google Earth Clicking on a Location Expands to Show all at That Location.jpg (56.08 KiB) Viewed 8508 times

Google Earth Clicking on One of the Place-marks Reveals the Description.jpg (47.11 KiB) Viewed 8508 times

Colin said:

To arrive at the KML spreadsheet (as previously posted) involve combining the 3 CSV spreadsheet outputs. This is a mixture of copy and paste supplement by mail-merge. The use of a LUA Plugin would probable ease this process.

The attachment is my first attempt which is very much in the early stages. I am trying to balance information on screen under the "Name" heading with that included in the "Description".

For ease, I am also using the place-marks as available in the Google Library.
For Census 4=1841, 5=1851, 6=1861, 7=1871, 8=1881, 9=1891, 0=1901 & 1=1911
Other Events are A=Birth, B=Baptism, M=Marriage, R=Resident & D=Death/Burial/Cremated.

Once I am happy with the method of data generation, I will start to explore bespoke place-marks eg using different colours.

I now feel as if I am getting somewhere.

BTW:
Did my suggested =RecordId(...) function reduce the 3 CSV to 2 CSV?
My earlier suggestion of using Source records instead of Repository records, and citing each Source from the Facts with a matching Address, should reduce the 2 CSV to one. A quick-fix Plugin would simplify the creation of the initial citations.

[ColinBeaver]

Hi Mike

Thanks for your Record ID suggestion which works a treat.

By using - https://mapicons.mapsmarker.com/numbers-letters/ - I am able to generate Placemarkers of different colours. This provides a way of differentiating the different branches.

I have used GE-Path 1.4.6 (free shareware programme) to connect my pedigree Placemarkers.

One thing to note is Google Earth will only accept a maximum of 200 Placemarkers at one time, eg my list of 560 had to be split into three separate KML uploads.

I find the main advantage of having all my ancestors on one map is that Google Earth enables you to turn off individual Placemarkers at will.

Also it shows quite dramatically when you have a few of your Ancestors baptised, married etc at the same church.

I still feel a Plugin is the way forward to simplify this process.

Thanks

ps A few of my Family History friends are looking at purchasing Family Historian with a view to creating their own KML upload.

[ColinBeaver]

A couple of things to add to my previous post.

I immediately save the csv spreadsheet as an Excel Workbook. It is much easier to work with and it saves the alterations such as column width.

With regards to Marriages, I remove the ..of by the excel search an replace function. I have then manually added the relationship for the person who is direct relation, ie on my Nixon branch it would be the person with a Nixon birth surname. Subsequently using the FACTOWNER Query Function simplifies this process.
ie =FactOwner(%FACT%,1,MALES_FIRST) & =Relationship(FileRoot(),FactOwner(%FACT%,1,MALES_FIRST),TEXT,1).

I now have a Query which gives all the information to create a spreadsheet for importing into Google Earth with the exception of Latitude and Longitude.

The other rider is that information has to be combined using mail merge to provide the "Name" & "Description" - this is relatively simple and not very time consuming.

Colin

[tatewise]

Colin, you are correct that a Plugin should be able to automate the process you describe.
I suggest you use my Map Life Facts Plugin as it is to put Lat/Longitude in Repository records.
Your Plugin would extract the data from your Project and compose the KML uploads using the rules you mention, then may be able to upload to Google Earth.
If you need Plugin design advice then post in the Plugin Discussion Forum.

[ColinBeaver]

Mike

I have taken your advice on posted on the Plugin Forum
Colin