Place Details without a Place #placedetails #phantom

Orphaned Place Details

On integrating these queries into the RMtrix bundled utilities, I found it necessary to revise them extensively, partly because they do not safely or efficiently address those orphaned Place Details that are actually used by a fact/event in the database. Moreover, without the foreknowledge of the names of the Place Details, it is impossible to find them using RootsMagic tools. My revised queries provide both an efficient way of finding where the orphaned sites are used, so that we can get at them with the Edit Person screen, and a speedy deletion of only those that are unused.

Screenshot from RMtrix of results from the OrphanedPlaceDetails query. The unused ones have blanks for Person and Fact; the used ones can be readily fixed by using RM5 to edit the fact for the person shown.

FAQ

1. What is an Orphaned Place Detail? It’s a Place Detail for which there is no parent Place in the PlaceTable.
2. What’s the problem with them? Unused ones merely clutter up one table. While ones that are used do appear to come out alright in narrative reports, they do not show in the Place List, they cannot be: edited, have images tagged to them, geotagged nor commented.
3. How do they arise in the first place? One way for certain is that a Place Detail can be added through the Edit Person screen with the Place field left empty (as of RM5021). Other possibilities are from past deletion or merging of the parent.
4. How do I fix the ones I want to keep? Use the OrphanedPlaceDetails query in RMtrix or download and run it in SQLiteSpy to get the report. Using RootsMagic, select the persons listed and find the fact in their Edit Person screen; assign the Place and then add the Place Detail.
5. How do I get rid of the unused ones? Orphaned Place Details – Delete Unused in RMtrix or download and run it in SQliteSpy. WARNING – there is risk of database corruption of the sort that RM5 can repair but RM4 cannot. After the deletion, use RM5’s Database Tools > Rebuild Indexes. Best to leave the unused Place Details alone in RM4.

Downloads

OrphanedPlaceDetails.sql

OrphanedPlaceDetails_DeleteUnused.sql

Original Post from Jerry Bryan

I’ve discovered that I have four Place Details in my database without a Place.RMGC_Properties – Query The situation can be identified with the following extremely trivial query.

SELECT *
    FROM PlaceTable
    WHERE PlaceType = 2
                 AND
          MasterID = 0

PlaceType = 2 identifies PlaceTable entries that are for Place Details, and if PlaceType = 2 then MasterID identifies the associated Place with which the Place Details are associated. MasterID=0 indicates that there is no Place associated with the Place Details.
[Inline comment:

ve3meo Jan 30, 2012

RMGC_Properties – Query flags all unused Place Details, including those with no master Place, in the row “-Unused Place Details”. However, it does not report a Place Detail used in a fact but lacking a master Place as that may be a legitimate use. For example, the Occupation fact – we might know that he worked at General Motors but not in which municipality. I suggest you run this query as you might discover some other surprises. It needs work to update the Media reporting to RM5.
]

For each of the four Place Details in question, I did a search within RM5 itself for “Any Fact Place Details contains (the Place Details in question)”. In three cases, nothing was found. In the fourth case, RM5 itself found the Place Details in question associated with a blank Place (basically, an impossible situation).

I have not run any of Tom’s Place/Place Details queries against my database. Whatever strange thing happened to my database just happened between me doing data entry with the keyboard and mouse and then with whatever processing RM4/RM5 did behind the scenes. I include both RM4 and RM5 in this scenario because I don’t know when this little glitch in my database happened – before RM5 or after RM5.

I decided to fix my database with the following and equally trivial query.

DELETE
    FROM PlaceTable
    WHERE PlaceType = 2
                 AND
          MasterID = 0

But the DELETE query will not run. It gets the infamous error message: SQLite Error 1 – no such collation sequence: RMNOCASE. I don’t understand why there is an RMNOCASE error when both of the data elements I’m testing are numeric.
[inline comment: “why there is an RMNOCASE error when both of the data elements I’m testing are numeric”

ve3meo Jan 30, 2012

Because the query tries to delete a record with a field (PlaceTable.Name) that is so collated and that field is used in an index (idxPlaceName) for that table. Hence the index must be updated and cannot be. If you use SQLiteSpy with the fake RMNOCASE extension, you will succeed in deleting these records. However, you should follow up in RM5 with the Database Integrity Check and, if errors reported in an index, then the Reindex tool.

Tom

]
Jerry

Added by Jerry Bryan 3/6/2012

I have concluded that my original description of orphaned Place Details was just a symptom of a larger problem. Namely, somewhere in the process of using the RM 5.0.2.1 feature to split a Place into Place + Place Details and merging any resultant duplicate places, there appears to be a bug whereby one of the duplicate places being merged is deleted without the all the pointers in the EventsTable for that place being adjusted to point to the entry in the PlaceTable that is being kept. The following query will identify all such orphaned place pointers in the Events Table.

/*
     This query identifies all events in the EventTable which contain a PlaceID value that does
     not appear as a PlaceID value in the PlaceTable.  This is an "impossible" situation which
     should never occur.  However, due to a possible bug in RM 5.0.2.1 or other unknown cause,
     this "impossible" situation has occurred one time in my database.
 
     This "impossible" situation occurred after numerous repetitions of splitting a Place
     into Place + Place Details and subseqently merging the resultant duplicate Place values.
 
     If the database against which this query is run does not have the problem, then the query
     will return no results.  So "no results" is the sign of success.
*/
 
 
 
SELECT L.EventID, L.EventType, L.OwnerType, L.OwnerID, L.FamilyID, L.PlaceID, P.PlaceID
   FROM
       (
        SELECT EventID, EventType, OwnerType, OwnerID, FamilyID, PlaceID
        FROM EventTable
        WHERE PlaceID != 0          /* Eliminate events without places in the sub-query  */
        ORDER BY PlaceID, EventID
       ) AS L
 
             LEFT JOIN
 
        PlaceTable AS P ON L.PlaceID = P.PlaceID
 
    WHERE P.PlaceID IS NULL;     /* Events with a place, but the place is not in the PlaceTable  */

Discussions & comments from Wikispaces site

Inline comments