Places+without+Geo-codes+for+Named+Groups

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.

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.

code format="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; code