SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

Ancestors Query – Recursive #recursive

Posted on by Tom Holden

The result of this query is a list of the Record Numbers of the birth or bloodline ancestors of a given person. Not very useful by itself, it is a first effort at a recursive query exploiting the WITH RECURSIVE syntax and common-table-expressions support introduced in SQLite 3.8.3 2014-02-03. The earlier RecursiveAncestors.sql

-- RecursiveAncestors.sql
/*
2014-04-13 Tom Holden ve3meo
 
Generates the list of RINs for the ancestors of a person.
 
Uses the WITH RECURSIVE syntax introduced in SQLite 3.8.3 2014-02-03
*/
 
/* modelled on http://www.sqlite.org/lang_with.html example:
 
The next example uses two common table expressions in a single WITH clause. The following table records a family tree:
 
CREATE TABLE family(
  name TEXT PRIMARY KEY,
  mom TEXT REFERENCES family,
  dad TEXT REFERENCES family,
  born DATETIME,
  died DATETIME, -- NULL if still alive
  -- other content
);
The "family" table is similar to the earlier "org" table except that now there are two parents to each member. We want to know all living ancestors of Alice, from oldest to youngest. An ordinary common table expression, "parent_of", is defined first. That ordinary CTE is a view that can be used to find all parents of any individual. That ordinary CTE is then used in the "ancestor_of_alice" recursive CTE. The recursive CTE is then used in the final query:
 
WITH RECURSIVE
  parent_of(name, parent) AS
    (SELECT name, mom FROM family UNION SELECT name, dad FROM family),
  ancestor_of_alice(name) AS
    (SELECT parent FROM parent_of WHERE name='Alice'
     UNION ALL
     SELECT parent FROM parent_of JOIN ancestor_of_alice USING(name))
SELECT family.name FROM ancestor_of_alice, family
 WHERE ancestor_of_alice.name=family.name
   AND died IS NULL
 ORDER BY born;
 
*/
 
WITH RECURSIVE
  parent_of(ChildID, ParentID) AS
    (SELECT PersonID, FatherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
       WHERE RelFather=0 --birth father (remove WHERE constraint to include all relationships)
     UNION
     SELECT PersonID, MotherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
       WHERE RelMother=0 --birth mother (remove WHERE constraint to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of
       WHERE ChildID=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ParentID FROM parent_of
       INNER JOIN ancestor_of_person ON ChildID = AncestorID)
SELECT AncestorID FROM ancestor_of_person, PersonTable
 WHERE ancestor_of_person.AncestorID=PersonTable.PersonID
;

Don’t ask me how it works – I’m not sure…

 

Ancestors Named Group #namedgroup #pedigree

Posted on by Tom Holden

Intro

RootsMagic 4 users have been seeking an improvement to Named Groups, namely "dynamic refresh" or, at least, a manual refresh button. The idea is that a group’s membership should be updated on demand following the rules that set it up in the first place. Currently, a group is static, requiring the user to edit the group, setting up the rules all over again.This query provides a manual refresh for a specific group, i.e., the ancestors of a specified person. While not the most challenging criterion for group membership and thus not a big time-saver, nonetheless it demonstrates the principle upon which more complex rules could be based. AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.

Download

AncestorsGroup.sql
NB- LabelID instead of LabelValue error corrected 2011-11-27 21:45 EST

Usage

  1. Backup your database before you do this every time until you are confident that it does no harm.
  2. In RM4, create a named group having the words "ancestors" and "#RIN" in it, where RIN is the number of the person whose ancestors are to be in the group, e.g., "Tom’s ancestors #269". Do not leave this group empty, add anyone immediately or do the next step before creating any more new groups (bug in RM4).
  3. Open your RM4 database with SQLite Expert Personal and load AncestorsGroup.sql into the SQL editor.
  4. Execute the query (F5)
  5. You will be prompted to enter a value for @StartRIN: enter the number of the person in the name of the group, in this example "269" (without quotes and the leading #).
  6. When the query has finished, you will need to refresh the RM4 sidebar view of your group by selecting another group and then re-selecting it.

Discussion

The RM4 GroupTable allows for group members to be defined in ranges of consecutive numbers between a StartID and EndID, thus shortening the table versus one row per member. This script makes no attempt to use this capability so it consumes one row per member with EndID=StartID. I don’t know if there is any significant efficiency gained as a result of ranges.

Discussions & comments from Wikispaces site


ve3meo

Comment: “AncestorsGroup.sql is built around the query described at Ancestors Query which is used virtually intact.‍”

ve3meo
03 September 2018 19:34:25

ve3meo Apr 13, 2014

This query would benefit from Ancestors+Query+-+Recursive . Unlimited number of generations and a much shorter, faster query.

Citations – Delete Duplicates #citations #sources #duplicates

Posted on by Tom Holden

In RootsMagic 4 (and maybe still in RM6), merging databases having common people with identical citations has resulted in duplicate citations for the merged person or couple and their events. These are counted in RM’s database properties report but not flagged as a problem and may not be detected until running a report or editing a person. An early SQLite query provides a more comprehensive report – see the page RMGC_Properties – Query and/or the RMtrix program. Another early query generates a spreadsheet report of all citations with a column counting duplicates – see All Citations & Dupes Count – Query, from which one could work to locate all instances and manually delete them one at a time. In March, 2014, RootsMagic Forums user Vyger posted under a 2010 topic on anomalies from merging that he was faced with a large number of duplicate citations and no easy way to get rid of them – pick up the the discussion here. He was sent the script below and reported that it successfully cleaned out all 916 duplicates with no apparent side effects. As this script may be useful to others, it is posted for the first time here, four years after its initial development.

Caveat Emptor: the script was written at the time of RM4 and does not check for differences between webtags for citations, a feature introduced later, and some other long-established fields, e.g. Quality. The Media system also changed from RM4 so the script may not handle differences in citation media tags correctly. Therefore, be sure to run this on a copy of your database, not your master, and review carefully before adopting the resulting database to go forward.

Citations-Dupes-Delete.sql

-- Citations-Dupes-Delete.sql
-- 2010-02-13 ve3meo
-- Deletes duplicate citations for each person, family and events
-- Uses LEFT OUTER JOINS to include the most orphaned citations
--     Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries
--     and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to
--     reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons.
--     Requires a temp table because of speed degradation when incorporated in main selects;
--     filtering can be done on screen in SQLiteDeveloper.
--
--     QUOTE() around BLOB type fields from CitationTable to display text where some SQLite
--      managers merely say BLOB.
-- rev 2010-12-11
--  align with RMGC_Properties procedure; prev version was too loose.
--
-- BEGIN
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations
;
CREATE TEMP TABLE tmpCitations (CITID INTEGER, SrcID INTEGER, RIN INTEGER, Uniq INTEGER, RinCitation TEXT);
 
INSERT INTO tmpCitations
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || 'Personal' || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)
  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.FIELDS AS TEXT)) || QUOTE(mm1.mediafile)  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.FIELDS AS TEXT)) || QUOTE(mm2.mediafile)
  AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- all Couple citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE c.ownertype=1 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
-- GROUP BY RinCitation
 
UNION ALL
-- Citations for Alternate Names
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq,
  n.OwnerID || 'Alternate Name' || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.FIELDS) || QUOTE(mm1.mediafile) ||
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.FIELDS) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID
WHERE  c.ownertype=7 AND +n.IsPrimary=0
-- GROUP BY RinCitation
ORDER BY rincitation
;
 
-- Delete Duplicates
DELETE FROM CitationTable
 WHERE CitationID IN
  (
   SELECT c.CitID
    FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
    ORDER BY c.CitID
   )
;
 
-- Count duplicates
--SELECT '- Duplicate Citations', COUNT(1), 'identical in most respects, cluttering reports'
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;
 
-- List Duplicates
--SELECT c.CitID
--  FROM tmpcitations C INNER JOIN tmpcitations C1 USING (rincitation) WHERE C.RowID = C1.ROWID+1
--ORDER BY c.CitID
--;

Group – Unconnected Persons #namedgroup

Posted on by Tom Holden

Problem

I have some experience with SQL but would like to create a query that would create a named group of Orphans that would contain the people in the file that have no parents, spouses, or children. These people are most likely remnants from deleting trees in my old program that I used. Another reason is that one of my gedcoms had family information missing so the entire file imported as individuals that were unconnected.

In the sample file there is only one orphaned person in the file. In my personal file there are actually hundreds of them. Since the file is large, I would like to be able to run the query that would update the named group as I work through merging two large files (over 300,000 people) so I can try to keep track of who is still orphaned in the tree.

sample data.rmgc

A Solution

This approach involves two steps of development:

  1. Script to store the RIN (PersonID) of these so-called “Orphans” in a temporary table
  2. Script to create/update a Named Group from the temp table

They are combined into a single script downloadable from the bottom of the page. It is efficient: in a database of 160,000 people, it created a group of the 1000 unconnected persons in ~3 seconds. It takes longer than that for RootsMagic to initially display the group in the sidebar; People View displays the group very quickly.

A message about this solution has been posted to the RootsMagic Forums at http://forums.rootsmagic.com/index.php?/topic/13326-dynamic-group-for-unconnected-persons/.

The “Orphans” Table

-- Persons-NoParentNoSpouseNoChild.sql
/*
2014-01-23 Tom Holden ve3meo
2014-01-24 rev changed table name to more generic
 
Creates a temporary table xGroupTempTable of PersonIDs (RINs)
of those who have neither a parent nor a spouse nor a child.
To be used with another procedure to update a named group.
*/
DROP TABLE
IF EXISTS xGroupTempTable;
 
CREATE TEMP TABLE
IF NOT EXISTS xGroupTempTable AS
    SELECT *
    FROM (
        --- Persons not in FamilyTable, either no spouse or no child
        SELECT PersonID
        FROM PersonTable
 
        EXCEPT
 
        SELECT *
        FROM (
            SELECT FatherID AS PersonID
            FROM FamilyTable
 
            UNION
 
            SELECT MotherID AS PersonID
            FROM FamilyTable
            )
        ) NATURAL
    INNER JOIN (
        -- persons with no parent
        SELECT PersonID
        FROM PersonTable
 
        EXCEPT
 
        SELECT ChildID
        FROM ChildTable
        );

Try the above script. Examine the temp table and look up some persons identified therein using RootsMagic to confirm that it is working as expected.

The “Orphans” Named Group

This script was extracted and adapted to work with the above script from Group – Persons with Text Dates. It creates and maintains a RM Group named “SQL: Unconnected Persons” from the temporary table xGroupTempTable.

After running this script with RootsMagic open simultaneously on the same database, it is necessary to refresh the Sidebar Group display. One does so by selecting a different group and re-selecting “SQL: Unconnected Persons”. Unfortunately, if this is the only group in the Groups list, you may have to close and reopen the database to refresh the list of members.

-- Group-UnconnectedPersons.sql
/*
2014-01-24 Tom Holden ve3meo
Creates and updates a named group of persons
having no parent, no spouse, nor a child in the database
from a temp table xGroupTempTable created by another script
*/
-- Create Named Group if it does not exist 'SQL: Unconnected Persons'
INSERT
    OR IGNORE
INTO LabelTable
VALUES (
    (
        SELECT LabelID
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        )
    ,0
    ,(
        SELECT ifnull(MAX(LabelValue), 0) + 1
        FROM LabelTable
        ) -- ifnull() needed if LabelTable is empty
    ,'SQL: Unconnected Persons'
    ,'SQLite query'
    );
 
-- Delete all members of the named group
DELETE
FROM GroupTable
WHERE GroupID = (
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        );
 
-- Add members to the named group
INSERT INTO GroupTable
SELECT NULL
    ,(
        SELECT LabelValue
        FROM LabelTable
        WHERE LabelName LIKE 'SQL: Unconnected Persons'
        )
    ,PersonID AS StartID
    ,PersonID AS EndID
FROM (
    SELECT DISTINCT PersonID
    FROM xGroupTempTable
    );

Download complete script

This file combines the two scripts into one so that a single pass takes care of updating the group. Group-UnconnectedPersons.sql

Discussions & comments from Wikispaces site


ve3meo

Comment: “keep track of who is still orphaned”

ve3meo
03 September 2018 21:24:57

ve3meo Jan 22, 2014

Have you tried RM’s Tools > Count Trees function? Each orphan will be listed as a tree with a Count of 1 and the Start Person will be the orphan.
mom2twinzz Jan 22, 2014

Yes. However, since I am dealing with potentially hundreds of these orphans, grouping them is more helpful than having the tree finder find each tree each and every time I am trying to connect a person.

What happened is that I had a computer malfunction that apparently truncated my gedcom from Legacy family tree. I no longer have the original LFT file, so direct importing the file into RM is impossible. Looking at a tree count, I have over 11,000 trees more than 10000 are orphaned trees, but that have information that I still want to merge in. Automatic merging has failed since the information was conflicting (middle names exist in one file but not the other, etc.) and failed to merge most of the people. So I am forced to manually merge people. Having a named group of orphans helps me to keep track of where I am in the merge process.

Facts – Reference Numbers for person, spouse and parents. #facttypes #events #refno

Posted on by Tom Holden

Purpose

This page responds to a request posted in the RootsMagic Forums by Forum member JoyceAE5 on 21 Jan 2014.

Basically, what I want is a list of everyone in my database with the following information:
 
Person's RefNo, Person's Name, Spouse's RefNo, Spouse's Name, Father's RefNo, Father's Name, Mother's RefNo & Mother's Name

Building Blocks

List of RINs and Names

It’s easy enough to get every person’s RIN and Name:

SELECT Per.PersonID AS "RIN"
    ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.Given AS
    "Name"
FROM PersonTable Per
INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
    AND +Nam.IsPrimary -- excludes Alternate Names;

Sample results: 

RIN Name
...
286 CLENDENON , Magdalena
287 HOLDEN , Abigal
288 HOLDEN , Barbery
289 MCDONALD , Samuel Charles
...

List of RINs and RefNos

What about each Person’s RefNo value?

SELECT Per.PersonID AS "RIN"
    ,CAST(Evt.Details AS TEXT) AS "RefNo"
FROM PersonTable Per
LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
    AND Evt.EventType = 35 -- the FactTypeID for the Reference Number fact is 35
    AND Evt.OwnerType = 0
    -- restricts to events for individuals; redundant in this case because the FactType is so restricted
    ;

which gives:

RIN    RefNo
...
328    Groves340
329    Groves284
329    HLAF139
330
...

Note that RIN 329 has two RefNo and 330 has none. If many persons have two or more RefNos, the number of combinations reported will go up exponentially; if a person, her spouse and her parents all have two RefNos, that person will be listed 16 times (2^4).

Combine two lists into a lookup table

Since we want the RefNo and Name for each Person, his spouse and parents, it would be most efficient to combine these results in a single lookup table so that the processing need not be repeated for each person and these relatives. That’s achieved by JOINing the two queries on the common field, RIN and storing a query of the results to a temporary table:

DROP TABLE IF EXISTS xNamesRefNoTable;
 
CREATE TEMP TABLE IF NOT EXISTS xNamesRefNoTable AS
    SELECT *
    FROM (
        SELECT Per.PersonID AS RIN
            ,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.
            Given || ' -' || Per.PersonID AS NAME
        FROM PersonTable Per
        INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
            AND + Nam.IsPrimary -- excludes Alternate Names;
        ) NATURAL
    INNER JOIN (
        SELECT Per.PersonID AS RIN
            ,CAST(Evt.Details AS TEXT) AS RefNo
        FROM PersonTable Per
        LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
            AND Evt.EventType = 35
            -- the FactTypeID for the Reference Number fact is 35
            AND Evt.OwnerType = 0
            -- restricts to events for individuals; redundant in this case because the FactType is so restricted
        );

Sample from this table, displayed as tab-delimited:

RIN    NAME    RefNo
66    FITCHETT Sr., U.E.,  Joseph -66    Groves073
66    FITCHETT Sr., U.E.,  Joseph -66    HLAF337
67    MCARTHUR ,  Nancy Ann -67    Groves074
68    FITCHETT ,  Dennis -68    Groves075
68    FITCHETT ,  Dennis -68    HLAF361

Note that the RIN has also been appended to the Name for convenience.

Report Query

Now to put together the Person, Spouse and Parents. I’m leaping to a complete report query that uses the temporary table created above, rather than building it up piece-meal. If you examine it from the inside out, you will see how it builds up a query listing the RINs of the person, spouse, father and mother. Then the temporary table is looked up from each RIN to retrieve the corresponding RefNo and Name. 

SELECT RINS.RIN
    ,Person.[RefNo]
    ,Person.[NAME]
    ,Spouse.[RefNo]
    ,Spouse.[NAME]
    ,Father.[RefNo]
    ,Father.[Name]
    ,Mother.[RefNo]
    ,Mother.NAME
FROM (
    SELECT Pert.PersonID AS RIN
        ,Spouses.SpouseID
        ,Parents.[FatherID]
        ,Parents.MotherID
    FROM PersonTable Pert
    LEFT JOIN (
        -- Get RIN of Spouse (MotherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[MotherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[FatherID]
 
        UNION
 
        -- Get RIN of Spouse (FatherID)
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID] AS SpouseID
        FROM PersonTable Per
        INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[MotherID]
        ) AS Spouses ON Pert.[PersonID] = Spouses.RIN
    LEFT JOIN (
        -- Get RINs of Parents
        SELECT Per.PersonID AS RIN
            ,Fam.[FatherID]
            ,Fam.[MotherID]
        FROM PersonTable Per
        LEFT JOIN ChildTable Child ON Per.PersonID = Child.[ChildID]
        INNER JOIN FamilyTable Fam USING (FamilyID)
        ) AS Parents ON Pert.[PersonID] = Parents.RIN
    ) AS RINS NATURAL
INNER JOIN xNamesRefNoTable AS Person
LEFT JOIN xNamesRefNoTable AS Spouse ON RINS.SpouseID = Spouse.[RIN]
LEFT JOIN xNamesRefNoTable AS Father ON FatherID = Father.[RIN]
LEFT JOIN xNamesRefNoTable AS Mother ON MotherID = Mother.[RIN];

Sample output

RINS.RINPerson.[RefNo]Person.[NAME]Spouse.[RefNo]Spouse.[NAME]Father.[RefNo]Father.[Name]Mother.[RefNo]Mother.Name
917HLAF061HARTLEY , Florence -917HLAF102BLAKESTON , Sidney -918HLAF039HARTLEY , Rev. George -875HLAF040COWIESON , Annetta Jane -876
918HLAF102BLAKESTON , Sidney -918HLAF061HARTLEY , Florence -917
919HLAF103BLAKESTON , Zella -919HLAF102BLAKESTON , Sidney -918HLAF061HARTLEY , Florence -917

Download Final Script

The whole script combining all the queries can be run in one fell swoop. Download it here: Facts-RefNos_person_spouse_parents.sql

Privatize Living #private #reports #gedcom

Posted on by Tom Holden

This script sets private the events, alternate name facts, and personal notes for persons whose Living flag is set. Thus, reports and GEDCOM can take full advantage of the options:

  • Privatize living people (GEDCOM only)
  • Include private facts
  • Include {private} notes
  • Strip { } brackets

thus providing many more combinations by which these outputs may be tailored.

This is a first pass script, lightly tested so use the usual precautions and feedback any problems. There is a known problem with privatizing notes that are already privatized – RM may output a closing brace character “}”.

PrivatizeLiving.sql

-- PrivatizeLiving.sql
/* 2013-12-14 Tom Holden ve3meo
Privatizes events, alt names, personal notes for persons with Living flag set
so that reports can be made with names of living persons but no details while
full details are outputted for the deceased.
 
To Do: privatize family note when one spouse is Living.
 
N.B.: There is a series of queries in a comment block at the end which can be run
to UNDO the privatization, it is nevertheless advisable to run this script against
a copy of your database in case the process is not perfectlt reversible. Of course,
you may wish to keep the resulting privatization if it suits your purposes so that
reversal is unnecessary.
*/
 
-- Make a table of persons marked Living
DROP TABLE IF EXISTS xLivingTable
;
CREATE TEMP TABLE IF NOT EXISTS xLivingTable
AS
SELECT PersonID, Note, TRIM(CAST(Note AS TEXT)) AS NoteTxt FROM PersonTable WHERE Living
;
 
-- Make a backup table of EventIDs and their IsPrivate setting
-- for all persons marked Living; must cover individual and family events
DROP TABLE IF EXISTS xLivingEventsBak
;
CREATE TABLE IF NOT EXISTS xLivingEventsBak
AS
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Individual events
INNER JOIN xLivingTable Lv ON E.OwnerID = Lv.PersonID AND E.OwnerType = 0
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living husband
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.FatherID = Lv.PersonID
UNION
SELECT E.EventID, E.IsPrivate FROM EventTable E  -- Spousal events for living wife
INNER JOIN FamilyTable Fm ON E.OwnerID = Fm.FamilyID AND E.OwnerType = 1
INNER JOIN xLivingTable Lv ON Fm.MotherID = Lv.PersonID
;
 
-- SET EventTable.IsPrivate = 1 for all events in xLivingEventsBak
UPDATE EventTable
SET IsPrivate = 1
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
 
/*
-- Alternate Names can also be set private and perhaps desirably so so we repeat
the pattern above
*/
-- Make a backup table of non-primary NameIDs and their IsPrivate setting
-- for all persons marked Living;
DROP TABLE IF EXISTS xLivingNamesBak
;
CREATE TABLE IF NOT EXISTS xLivingNamesBak
AS
SELECT NameID, IsPrivate FROM NameTable
WHERE OwnerID IN
(SELECT PersonID FROM xLivingTable)
ORDER BY NameID
;
 
-- Privatize Alternate Names for Persons marked Living in the database
UPDATE NameTable SET IsPrivate = 1
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
 
-- PRIVATIZE Personal Notes for persons marked Living by enclosing in braces {}
UPDATE PersonTable
SET Note =
--SELECT
CAST(REPLACE(CAST(Note AS TEXT), CAST(Note AS TEXT), '{' || CAST(Note AS TEXT) || '}') AS BLOB)
--FROM PersonTable
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
 
/*
-- BLOCK OF UNDO queries to reverse the above changes.
 
-- UNDO Privatize events by SET EventTable.IsPrivate = 0 for all events in
  xLivingEventsBak that have IsPrivate = 0
UPDATE EventTable
SET IsPrivate = 0
WHERE EventID IN
(SELECT DISTINCT EventID FROM xLivingEventsBak WHERE NOT IsPrivate ORDER BY EventID)
;
 
-- UNDO privatization of Alt Names for Living persons
UPDATE NameTable SET IsPrivate = 0
WHERE NameID IN
(
 SELECT NameID FROM xLivingNamesBak WHERE NOT IsPrivate
)
;
 
-- UNDO Privatize Personal Notes by updating from xLivingTable
UPDATE PersonTable
SET Note =
(SELECT Note FROM xLivingTable Lv WHERE PersonTable.PersonID = Lv.PersonID)
WHERE PersonID
IN (SELECT PersonID FROM xLivingTable WHERE NoteTxt NOT LIKE '{%}')
;
*/
-- END of script

An Example of Using SQLite Views #views #colorcoding

Posted on by thejerrybryan

I thought I would post some results from a little project I’ve recently been working on. Consider a descendant narrative report in RM in any of the supported formats: outline, NEHGS, etc. In any of these reports, the parents of the spouses will be listed as a part of the birth sentence for the spouse, viz. “Jane Doe, daughter of John Doe and Sarah Smith, was born in 1848″. My project has been to assure that I have all the appropriate data and documentation for the parents, even though the data itself will not appear in this particular report except for the names of the parents.

My strategy is to color code such parents of spouses using navy as the color. Navy is not otherwise used as a color in my database. I can then do searches for individuals who are colored navy or make a Named Group out of them or whatever. In other words, I use my set of navy people as sort of a “to do list” for my research.I should explain that I have a Named Group in my database called Active_Research consisting of all the individuals who I am most actively working on. Everybody in my Active_Research group is color coded green and everybody who is color coded green is in my Active_Research group.

So suppose I want to color code as navy all the parents of the spouses of the descendants of John Doe, and by the way John Doe and all his descendants and their spouses are already color coded green and are already in the Active_Research group. So in RM, I do the following.

  1. Color code John Doe and all his descendants and spouses as teal. This temporarily disturbs their normal green color, but I will restore the green before I’m done. And just like navy, teal normally is never used anywhere in my database.
  2. Color code John Doe and all his descendants but not their spouses as green.

At this point, the direct descendants of John Doe have been restored to green, and the spouses have been colored as teal. So I need to color code the parents of all the teal people as navy, and then restore the teal people to green. I will then have restored all my green people to green, and will have color coded all the parents of the spouses of John Doe’s descendants as navy.

It’s trivial to restore the teal people to green from within RM, but before I do I need to color the parents of all the teal people as navy using SQLite scripts. The script to do so is included below. In order to accomplish the desired processing, I experimented with the idea of using views – something I had never done before in SQLite although I had used views before in other SQL environments.

My SQL tends to use lots of subqueries – probably many more than most people would use. It’s just my style, I guess. And I was thinking that all my subqueries might be a lot easier to develop, debug, and manage if they were views than if they were true subqueries.

To tell you the truth, I sort of went overboard. Because I usually code with lots of subqueries, when I replaced the subqueries with views I ended up with lots of views. Some of my views could have been combined, but splitting the views down into as many small pieces as I did made them really easy to develop.

The process of using views proved to be extremely easy. For each view, I would develop it and debug it as a standard query (not as a subquery). Once it was working, all I had to do to convert the query to a view was to precede it with a CREATE VIEW statement. I used CREATE TEMP VIEW to avoid storing the views themselves in my RM database.

If you look at my script, the first SQL statement that really does any processing of my RM database is the UPDATE statement itself. Within the UPDATE statement, there is a subquery which says “SELECT ParentID AS PersonID from ParentView”. ParentView is one of the views I created, and the reference to ParentView kicks off a whole chain of references to all the other views I created. A possible alternative coding style might have been an UPDATE statement that was 15 or 20 lines long with nested subquery on top of nested subquery. All that nested subquery stuff is still really happening, but it’s hidden in all the nested views.

By the way, I really wanted my UPDATE statement simply to say UPDATE ParentView SET COLOR = 10. Such a statement makes perfectly good sense to me, but you can’t update a view. So I had to update an RM table and move the reference to ParentView into a subquery.

Jerry

DROP VIEW IF EXISTS ChildView;
DROP VIEW IF EXISTS TealView;
DROP VIEW IF EXISTS FamilyView;
DROP VIEW IF EXISTS MotherView;
DROP VIEW IF EXISTS FatherView;
DROP VIEW IF EXISTS ParentView;
 
CREATE TEMP VIEW ChildView AS   -- View to produce the RIN and FamilyID of everyone who has parents
SELECT C.ChildID,
       C.FamilyID
FROM ChildTable AS C
ORDER BY C.ChildID;
 
CREATE TEMP VIEW TealView AS    -- View to produce the RIN of everyone who is colored Teal
SELECT P.PersonID
FROM PersonTable AS P
WHERE Color = 13                -- color is teal
ORDER BY P.PersonID;
 
CREATE TEMP VIEW FamilyView AS  -- View to produce the RIN and FamilyID of everyone who has parents and is colored teal
SELECT N.PersonID, C.FamilyID
FROM TealView AS N
       INNER JOIN
     ChildView AS C ON C.ChildID = N.PersonID
ORDER BY FamilyID;
 
CREATE TEMP VIEW MotherView AS  -- View to produce the RIN of the mother of everyone who has parents and is colored teal
SELECT F.MotherID AS ParentID
FROM FamilyTable AS F
        INNER JOIN
     FamilyView AS G ON F.FamilyID = G.FamilyID
ORDER BY F.MotherID;
 
CREATE TEMP VIEW FatherView AS  -- View to produce the RIN of the father of everyone who has parents and is colored Teal
SELECT F.FatherID AS ParentID
FROM FamilyTable AS F
        INNER JOIN
     FamilyView AS G ON F.FamilyID = G.FamilyID
ORDER BY F.FatherID;
 
CREATE TEMP VIEW ParentView AS  -- View to produce the RIN of the parents of everyone who has parents and is colored Teal
SELECT ParentID FROM MotherView AS ParentID
    UNION
SELECT ParentID FROM FatherView AS ParentID
ORDER BY ParentID;
 
UPDATE PersonTable SET Color = 10 WHERE PersonTable.PersonID IN (SELECT ParentID AS PersonID FROM ParentView); -- Update parents of Teal people to be Navy people
 
SELECT * FROM PersonTable WHERE Color = 10;   -- Display the Navy people, Teal people will be restored to Green back in RM

Places without Geo-codes for Named Groups #places #placedetails #geocoding #namedgroup

Posted on by Tom Holden

It is daunting to work through the Place List, finding Places and Place Details (sites) that need to be geo-coded (i.e., have latitude and longitude values entered) so that they can be readily mapped and their events included in the Place List report option “Print events near a place”. The larger the database of persons and families, invariably the more places and sites and the bigger the challenge. RootsMagic features such as Color Coding and Named Groups help narrow the focus when working on the facts for people and families of primary interest but, as of version 6, do nothing for the Place List. A desirable enhancement would provide the option to filter the Place List to include only those places and sites used by events for persons and families in a given color code or named group. Moreover, other filters would be helpful, such as showing only those lacking coordinates, or containing some string in any of the fields, etc., i.e., something paralleling RM Explorer for people.

This query provides an interim aid for the specific task of finding non-geo-coded places and sites used by the facts for a subset of the people and families in the database. It lists those places and sites for persons in one or more named groups whose group name contains the string “focus”. One would work within the RootsMagic Place List to go directly to these and assign coordinates using its Geo-code and Online Map functions or other resources such as Wikipedia, Google Maps and Google Earth.

PlacesWithoutGeocodesForGroup.PNG
Screenshot from SQLite Expert of query results showing places and sites, either or both of which lack geographical coordinates.

In the above screenshot, there are 10 different places listed from RecNo 24 to 33. In the RM Place List, 10 places, starting at Govan, Lanarkshire, all begin with “G”. Thus we have skipped those that are not of primary interest.

PlacesWithoutGeocodesForGroup.sql

-- PlacesWithoutGeocodesForGroup.sql
-- 2013-10-21 Tom Holden ve3meo
/*
Lists those places and place details (sites) and their geographical coordinates (geocodes)
for events of persons who are members of named groups having a groupname containing the string
"focus" and for which one of the sets of coordinates is 0 (i.e., not geocoded).
*/
SELECT P.NAME AS Place
    ,P.Latitude * 0.0000001 AS PlaceLat
    ,P.Longitude * 0.0000001 AS PlaceLong
    ,PD.NAME AS Detail
    ,PD.[Latitude] * 0.0000001 AS DetLat
    ,PD.[Longitude] * 0.0000001 AS DetLong
FROM PlaceTable P
LEFT JOIN PlaceTable PD ON P.PlaceID = PD.MasterID
WHERE ifnull(PD.PlaceType, 2) = 2
    AND P.PlaceType = 0
    AND (
        (
            P.Latitude = 0
            AND P.Longitude = 0
            )
        OR (
            PD.Latitude = 0
            AND PD.Longitude = 0
            )
        )
    AND P.PlaceID -- restrict to places used by events for persons in named group
    IN (
        SELECT DISTINCT PlaceID
        FROM (
            SELECT OwnerID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            WHERE OwnerType = 0
 
            UNION
 
            SELECT FatherID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
            WHERE OwnerType = 1
 
            UNION
 
            SELECT MotherID AS PersonID
                ,EventID
                ,PlaceID
                ,SiteID
            FROM EventTable E
            INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
            WHERE OwnerType = 1
            )
        WHERE PersonID IN (
                SELECT DISTINCT P.PersonID
                FROM PersonTable P
                    ,GroupTable
                WHERE P.PersonID BETWEEN StartID
                        AND EndID
                    AND GroupID IN (
                        SELECT DISTINCT LabelValue
                        FROM LabelTable
                        WHERE LabelType = 0
                            AND LabelName LIKE '%focus%'
                        ) -- persons in groups with name containing "focus"
                )
        )
    AND (
        PD.PlaceID IsNull -- Places with no PlaceDetail
        OR PD.PlaceID -- OR restrict to sites used by events by persons in named group
        IN (
            SELECT DISTINCT SiteID
            FROM (
                SELECT OwnerID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                WHERE OwnerType = 0
 
                UNION
 
                SELECT FatherID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
                WHERE OwnerType = 1
 
                UNION
 
                SELECT MotherID AS PersonID
                    ,EventID
                    ,PlaceID
                    ,SiteID
                FROM EventTable E
                INNER JOIN FamilyTable FM ON E.OwnerID = FM.FamilyID
                WHERE OwnerType = 1
                )
            WHERE PersonID IN (
                    SELECT DISTINCT P.PersonID
                    FROM PersonTable P
                        ,GroupTable
                    WHERE P.PersonID BETWEEN StartID
                            AND EndID
                        AND GroupID IN (
                            SELECT DISTINCT LabelValue
                            FROM LabelTable
                            WHERE LabelType = 0
                                AND LabelName LIKE '%focus%'
                            ) -- persons in groups with name containing "focus"
                    )
            )
        )
ORDER BY Place
    ,Detail;

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

Posted on by Tom Holden

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…

MapEvents-Example-GoogleMaps.png
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).
MapEvents-ExampleNearbyResults-GoogleMaps.png
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.

MapEvents-KML_screenshot.png
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.
MapEvents-Example-GoogleEarth.png

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:

MapEventsKML-BingMaps.PNG
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:
MapEventsKML-GeoCommons.PNG
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.