This page is intended to collect some scripts in support of calculating and displaying relationships between people in a RootsMagic database.

Kinship List

This first script produces a table similar to the RootsMagic Kinship List report but it does so markedly faster for a large database. RootsMagic 7.0.11.0 took almost five minutes (290s) to calculate and display a 1900 page report for 75,000 people in a 160,000 population. SQLiteSpy 1.9.10 executed this script in 2 seconds! That’s a 145:1 improvement in speed. Moreover, an SQLite manager can sort on the columns and filter the results (features vary depending on the product) and the SQLite Views created by the script can be exploited in further ad hoc queries the user may write.

Screenshot of results from KinshipList.sql in SQLiteSpy sorted on the RIN column.

The script does not calculate relationships so the comparison with the RootsMagic Kinship List report is not apples to apples. Rather, it lists the relationships calculated by the RootsMagic “Set Relationships” function which added a further 50s execution time which, if included, degrades the speed advantage to a mere 5.5:1. However, it is often the case that the Kinship List is set to recompute relationships to the same person as was already done by Set Relationships. And the query can remain open in the SQLite Manager while working on the database in RootsMagic.

The columns Relate1 and Relate2 represent the distance to the common ancestor from the reference person and their relative and are calculated by RootsMagic. The script uses these to generate the Relationship phrase. They may also be used in SQLite Expert Personal as filters on the results set, e.g., limiting the people listed to those having a total distance between them of less than, say, 10. A custom query in any SQLite manager could likewise limit results.
KinshipList.sql rev 2016-07-29 sample query calculates consanguinity degree of relationship
The part of the script that generates the Views should function with any SQLite Manager. The final query that displays results with the names of people requires one with a RMNOCASE collation.

Click image for Wikipedia page on Consanguinity.