Places to Place Details Conversion #places #placedetails #update

Why?

Splitting Places into Place and Place Details is an onerous job in RootsMagic: you have to edit every fact/event that used the original Place. Until RootsMagic comes up with a utility to make it easier to do, we need a better way. With some judicious SQLite queries and editing of RM’s Place List, there is a faster, better way. The opposite direction is much easier.

Split Place into Place and Place Detail

Here’s an example of how SQLite can carry out the bulk conversion of Places having too detailed data to Place Details of another Master Place, e.g.:
Place: Mount Pleasant Cemetery, London, Ont.
to
Place: London, Middlesex Co., Ontario, Canada
Place Detail: Mount Pleasant Cemetery
and accordingly revise the 5, 50, 500 facts/events that used the original Place or any other Place having “London, Ont” in its name.

The example uses a series of SQLite queries run one at a time in sequence, but, first, make sure that you have your desired master Place in your Place List, e.g., “London, Middlesex Co., Ontario, Canada” and MAKE A BACKUP:

-- Pick out the PlaceID of the desired Master Place, i.e., "London, Middlesex Co., Ontario, Canada",
-- from the results of this query
 
SELECT * FROM PlaceTable
WHERE Name LIKE 'London,%';
 
-- This will be the MasterID value for the UPDATE - in this example PlaceID=519

Results from 1st query. Choose PlaceID=519 as the master Place.

-- Establish a suitable WHERE constraint that finds all the Places that need to be converted to Place Detail
-- The following worked well. Note the underscore character is a wildcard for any one character and
-- thus precludes a name beginning "London..." from the results.
-- The percent character is a wild card for any number of characters.
 
SELECT * FROM PlaceTable
WHERE Name LIKE '_%London, Ont%' AND PlaceType=0;
 
-- PlaceType of 0=Place, 2=Place Detail (1=Temple)

Results from 2nd query showing the Places to be converted to Place Details.

-- Use that WHERE constraint in the following
-- Convert Places in PlaceTable to Place Details of the Master Place (PlaceID=519 in this example)
 
UPDATE PlaceTable SET PlaceType=2, MasterID=519
WHERE Name LIKE '_%London, Ont%' AND PlaceType=0;

SQLite manager reports 4 records updated.

-- Convert Place and Place Detail cited in the EventTable
 
UPDATE EventTable SET SiteID=PlaceID, PlaceID=519
WHERE PlaceID IN
(
 SELECT PlaceID FROM PlaceTable
 WHERE Name LIKE '_%London, Ont%' AND PlaceType=2
 )
AND SiteID=0;

SQLite manager reports 5 records updated, i.e., five events used at least some of the four Places previously converted to Place Details and now use the master Place and the corresponding converted Place Details.

Now look up the Master Place (London, Ontario, Canada) in the RM4 Place List to confirm that the former Places are now its Place Details.

Place List immediately after SQLite queries. 90 Albert was already a Detail for the master Place.

1. Optionally merge variants of the same place with the target master Place.
2. Print > Place List > all events in a single place (our “London, Middlesex…” master), check the box “Print place details”.
3. Inspect the report for unused Place Details and delete them at your discretion.
4. You may have variants of the same Place Detail that you want to unify; can be done with further queries but for a few, just go to Edit Person and revise the fact. Reiterate from step 2.
5. Edit each remaining Place Detail to remove the now redundant Master Place info (i.e.”, London, Ont…”)

Final Place List after bulk SQLite conversions and RM4 edits.

Editing the Place Detail name in RootsMagic 4 rather than in SQLite is necessary because of the proprietary RMNOCASE collation sequence. It is possible to edit the Place Detail name of a RootsMagic 5 database with SQLiteSpy + the fake RMNOCASE extension and then use RM5’s File > Database tools > Rebuild indexes function to correct probable index errors.

Convert Place + Place Detail to Place

This is a much easier, global procedure but needs a SQLite manager that supports a fake RMNOCASE collation, such as SQLiteSpy with the fake RMNOCASE extension (see RMNOCASE – faking it in SQLiteSpy) or SQLite Expert (see RMNOCASE – faking it in SQLite Expert…).

Place_Details-Convert_to_Place.sql
Download this file and load it into SQLiteSpy, having opened your database with it first. Of course, you’ve made a backup! Run the script. The results should be a series of UPDATE statements. Copy these results into a new SQL page in the SQL editor, delete the first line which has the column heading ‘Statement’ and execute all the statements. Place Details are now Places.

You may think you are finished, but not quite. In RM5, do File > Database > Integrity Check. If you see errors reported, then choose Reindex from the same menu and that should clear them. Unfortunately for RM4 users, there is no equivalent tool so you are at greater risk of some form of index corruption due to the fake collation.

/* Place_Details-Convert_to_Place.sql
Converts Place Details + Places into Places; preserves apostrophes
 through some tricky substitutions but other characters may be problematic.
2012-01-22 ve3meo
 
Three steps:
1. Update EventTable SET PlaceID=SiteID, SiteID=0 WHERE SiteID>0
2. Auto generate a statement for each Place Detail of the form
    Update PlaceTable SET Name = SiteName, PlaceName, PlaceType = 0, MasterID = 0
3. Manually copy the resulting series of Update Statements to a SQLite editor and run them
*/
-- Step 1 Open database and run this script
UPDATE EventTable SET PlaceID=SiteID, SiteID=0 WHERE SiteID>0
;
-- Step 2 auatically generates the UPDATE statements for each Place Detail
SELECT 'UPDATE PlaceTable SET Name = '''
       || REPLACE(Site.Name || ', ' || Place.Name, '''', '''''') || ''''       || ', PlaceType = 0, MasterID = 0
       WHERE PlaceID = ' || Site.PlaceID || ';'
       AS Statement
FROM PlaceTable AS Site INNER JOIN PlaceTable AS Place ON Site.MasterID = Place.PlaceID
;
-- Step 3 Copy the resulting UPDATE statements to your SQLite Editor and run them against the database

Discussions & comments from Wikispaces site