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

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.

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;