-- Geo-LifeLines.sql 31 Aug 2010
-- adapted from Lifelines.sql by ve3meo to include Geocoding and 
--  filtering by distance from a geo-coordinate.
-- Lists a summary of all events and facts for each person including
--  personal, couple, parental, child, shared, alternate name.
-- Reports persons not in a database tree who shared an event.
-- Lists RIN, Person, Role, Fact, Date (decoded), Detail, Site, Place, 
--  Geo type (S-Site, P-Place), Latitude, Longitude, 
--  MRIN, Sharer(s) and RIN(s), Duplicate count and Sort Date (undecoded).
-- Works best with a SQLite manager that can sort columns such as SQLiteSpy and SQLite Developer. 
DROP TABLE IF EXISTS Parms;
CREATE TEMP TABLE IF NOT EXISTS Parms (RefLat FLOAT, RefLong FLOAT, RadiusMiles FLOAT);
-- Parms table contains the reference latitude and longitude in decimal notation and the
--  range in miles therefrom that will select only the facts for Places and Place Details 
--  that fit within the (rectangular) boundary. The values (0,0,12450) will return all facts.
INSERT INTO Parms VALUES (43.9005556,-78.8572222,10); -- change these values to suit your needs   
SELECT 
  RIN, 
  Person, 
  Role, 
  Fact, 
  CASE Substr( rawDate , 1 , 1 )
	WHEN 'Q' THEN rawDate 
	WHEN 'T' THEN Substr( rawDate , 2 , 20 ) 
	WHEN 'D' THEN 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'A' THEN 'aft ' 
			WHEN 'B' THEN 'bef ' 
			WHEN 'F' THEN 'from ' 
			WHEN 'I' THEN 'since ' 
			WHEN 'R' THEN 'bet ' 
			WHEN 'S' THEN 'from ' 
			WHEN 'T' THEN 'to ' 
			WHEN 'U' THEN 'until ' 
			WHEN 'Y' THEN 'by ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 13 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 3 , 1 ) = '-' THEN 'BC' ELSE '' END  
		|| 
		CASE WHEN Substr( rawDate , 4 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 4 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 3 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' AND ( Substr( rawDate , 4 , 4 ) <> '0000' AND Substr( rawDate , 10 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 8 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 8 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 10 , 2 ) , '-00' ) , '' ) 
		|| 
		CASE Substr( rawDate , 2 , 1 ) 
			WHEN 'R' THEN ' and ' 
			WHEN 'S' THEN ' to ' 
			WHEN 'O' THEN ' or ' 
			WHEN '-' THEN ' - ' 
		ELSE '' 
		END 
		|| 
		CASE Substr( rawDate , 24 , 1 ) 
			WHEN 'A' THEN 'abt ' 
			WHEN 'E' THEN 'est ' 
			WHEN 'L' THEN 'calc ' 
			WHEN 'C' THEN 'ca ' 
			WHEN 'S' THEN 'say ' 
			WHEN '6' THEN 'cert ' 
			WHEN '5' THEN 'prob ' 
			WHEN '4' THEN 'poss ' 
			WHEN '3' THEN 'lkly ' 
			WHEN '2' THEN 'appar ' 
			WHEN '1' THEN 'prhps ' 
			WHEN '?' THEN 'maybe ' 
		ELSE '' 
		END 
		|| 
		CASE WHEN Substr( rawDate , 14 , 1 ) = '-' THEN 'BC' ELSE '' END 
		|| 
		CASE WHEN Substr( rawDate , 15 , 4 ) = '0000' THEN '' ELSE Substr( rawDate , 15 , 4 ) END 
		|| 
		CASE WHEN Substr( rawDate , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( rawDate , 14 , 5 ) ) ELSE '' END 
		|| 
		CASE 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) <> '00' ) THEN '-??' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' AND ( Substr( rawDate , 15 , 4 ) <> '0000' AND Substr( rawDate , 21 , 2 ) == '00' ) THEN '' 
			WHEN Substr( rawDate , 19 , 2 ) = '00' THEN '' 
		ELSE '-' || Substr( rawDate , 19 , 2 ) 
		END 
		|| 
		Coalesce( Nullif( '-' || Substr( rawDate , 21 , 2 ) , '-00' ) , '' ) 
	ELSE '' 
  END AS Date ,
  Detail,
  Site,
  Place,
  Geo,
  Lat ||',' || Long AS LatLong,
  MRIN,  
  "Sharer Name",
  Dup,
  SortDate 
FROM
(
SELECT 
-- Invented Parents fact to show Birth parents regardless of existence of Birth Fact
  Ch.ChildID AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Child' AS Role , 
  'Parents' AS Fact , 
  NULL AS rawDate ,
  NULL AS Detail,  
  NULL AS Site,
  NULL AS Place,
  NULL AS Geo,
  'Nil' AS Lat,
  'Nil' AS Long,
  'M'|| parent.FamilyID AS MRIN, 
  IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
    Nfath.Suffix COLLATE Nocase ||', '|| 
    Nfath.Prefix COLLATE Nocase ||' '|| 
    Nfath.Given COLLATE Nocase ||'-'||      
    Nfath.OwnerID , '' )          
  ||
  IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
    Nmoth.Suffix COLLATE Nocase ||', '|| 
    Nmoth.Prefix COLLATE Nocase ||' '|| 
    Nmoth.Given COLLATE Nocase ||'-'||      
    Nmoth.OwnerID , '' )
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  NULL AS SortDate
FROM 
  Childtable Ch 
  LEFT JOIN Nametable Npers ON Ch.ChildID = Npers.Ownerid AND +Npers.Isprimary = 1
  INNER JOIN familytable AS parent ON parent.Familyid = Ch.Familyid AND Ch.RelMother IN (0, NULL) AND Ch.RelFather IN( 0 , NULL)  
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
   
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12

UNION ALL

SELECT 
-- Facts for Wives in Couple events
  Nwife.OwnerID AS RIN, 
  UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
  Nwife.Suffix COLLATE Nocase ||', '||  
  Nwife.Prefix COLLATE Nocase ||' '||  
  Nwife.Given COLLATE Nocase AS Person,  
  'Partner' AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN 'S'    
    WHEN Place.latitude <> 0 OR Place.Longitude <> 0
    THEN 'P'
    ELSE null    
  END AS Geo,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.latitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.latitude*1.0e-7, 'Nil')    
  END AS Lat,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.longitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.longitude*1.0e-7, 'Nil')    
  END  AS Long,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
  Nhusb.Suffix COLLATE Nocase ||', '|| 
  Nhusb.Prefix COLLATE Nocase ||' '|| 
  Nhusb.Given COLLATE Nocase ||'-'||      
  Nhusb.OwnerID AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate 
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN PersonTable AS Person ON E.OWNERID = person.PersonID  
WHERE E.OwnerType = 1 

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL

SELECT 
-- Facts for Individuals and Husbands
  CASE E.OwnerType   
    WHEN 0 THEN E.OwnerID 
    WHEN 1 THEN Nhusb.OwnerID
  END
    AS RIN, 
  CASE E.OwnerType   
    WHEN 0 THEN
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  
    WHEN 1 THEN
      UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase
  END AS Person,  
  CASE E.OwnerType   
    WHEN 0 THEN 'Principal'    
    ELSE 'Partner'    
  END
    AS Role, 
  Fact.Name COLLATE Nocase AS Fact, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN 'S'    
    WHEN Place.latitude <> 0 OR Place.Longitude <> 0
    THEN 'P'
    ELSE null    
  END AS Geo,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.latitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.latitude*1.0e-7, 'Nil')    
  END AS Lat,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.longitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.longitude*1.0e-7, 'Nil')    
  END  AS Long,
  CASE E.OwnerType  
    WHEN 0 THEN     
      CASE 
        WHEN E.EventType IN (1,33) THEN
          'M'|| Parent.FamilyID
        WHEN E.EventType = 6 THEN
          'M'|| AdoptParent.FamilyID
      END      
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN
      CASE 
        WHEN E.EventType IN(1,33) THEN
          IFNULL( UPPER(Nfath.Surname) COLLATE Nocase ||' '|| 
          Nfath.Suffix COLLATE Nocase ||', '|| 
          Nfath.Prefix COLLATE Nocase ||' '|| 
          Nfath.Given COLLATE Nocase ||'-'||      
          Nfath.OwnerID , '' )          
          ||
          IFNULL( ' & '|| UPPER(Nmoth.Surname) COLLATE Nocase ||' '|| 
          Nmoth.Suffix COLLATE Nocase ||', '|| 
          Nmoth.Prefix COLLATE Nocase ||' '|| 
          Nmoth.Given COLLATE Nocase ||'-'||      
          Nmoth.OwnerID , '' )
        WHEN E.EventType = 6 THEN
          IFNULL( UPPER(Nafath.Surname) COLLATE Nocase ||' '|| 
          Nafath.Suffix COLLATE Nocase ||', '|| 
          Nafath.Prefix COLLATE Nocase ||' '|| 
          Nafath.Given COLLATE Nocase ||'-'||      
          Nafath.OwnerID , '')          
          ||
          IFNULL(' & '|| UPPER(Namoth.Surname) COLLATE Nocase ||' '|| 
          Namoth.Suffix COLLATE Nocase ||', '|| 
          Namoth.Prefix COLLATE Nocase ||' '|| 
          Namoth.Given COLLATE Nocase ||'-'||      
          Namoth.OwnerID , '')
        ELSE NULL        
      END     
    WHEN 1 THEN
      UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate AS SortDate 
FROM 
  Eventtable AS E  
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1 AND E.OwnerType = 0
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID AND E.OwnerType = 1
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID  
  LEFT JOIN ChildTable AS Child ON E.OwnerID = Child.ChildID AND Child.RelMother = 0 AND Child.RelFather = 0 
  LEFT JOIN familytable AS Parent ON Child.Familyid = Parent.Familyid 
  LEFT JOIN NameTable AS Nfath ON parent.FATHERID = Nfath.OwnerID AND +Nfath.IsPrimary = 1 
  LEFT JOIN NameTable AS Nmoth ON parent.MotherID = Nmoth.OwnerID AND +Nmoth.IsPrimary = 1
  LEFT JOIN familytable AS AdoptParent ON E.FamilyID = AdoptParent.FamilyID  
  LEFT JOIN NameTable AS Nafath ON AdoptParent.FATHERID = Nafath.OwnerID AND +Nafath.IsPrimary = 1 
  LEFT JOIN NameTable AS Namoth ON AdoptParent.MotherID = Namoth.OwnerID AND +Namoth.IsPrimary = 1  

GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13


UNION ALL

SELECT  
-- Invented Fact for Father having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Fathered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN 'S'    
    WHEN Place.latitude <> 0 OR Place.Longitude <> 0
    THEN 'P'
    ELSE null    
  END AS Geo,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.latitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.latitude*1.0e-7, 'Nil')    
  END AS Lat,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.longitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.longitude*1.0e-7, 'Nil')    
  END  AS Long,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Fatherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid  AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EVENTTYPE = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelFather = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL

SELECT 
-- Invented fact for Mother having child
  Npers.Ownerid AS RIN , 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '|| 
  Npers.Prefix COLLATE Nocase ||' '|| 
  Npers.Given COLLATE Nocase AS Person, 
  'Parent' AS Role , 
  'Mothered' AS Fact , 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN 'S'    
    WHEN Place.latitude <> 0 OR Place.Longitude <> 0
    THEN 'P'
    ELSE null    
  END AS Geo,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.latitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.latitude*1.0e-7, 'Nil')    
  END AS Lat,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.longitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.longitude*1.0e-7, 'Nil')    
  END  AS Long,
  'M'|| Fam.FamilyID AS MRIN, 
  UPPER(Nch.Surname) COLLATE Nocase ||' '|| 
  Nch.Suffix COLLATE Nocase ||', '|| 
  Nch.Prefix COLLATE Nocase ||' '|| 
  Nch.Given COLLATE Nocase ||'-'||
  Ch.ChildID
   AS 'Sharer Name' , 
  Count( 1 ) -1 AS Dup,
  E.SortDate AS SortDate
FROM 
  Familytable Fam  
  LEFT JOIN Nametable Npers ON Fam.Motherid = Npers.Ownerid AND +Npers.Isprimary = 1 
  LEFT JOIN Childtable Ch ON Fam.Familyid = Ch.Familyid
  LEFT JOIN Nametable Nch ON Ch.Childid = Nch.Ownerid AND +Nch.Isprimary = 1
  LEFT JOIN Eventtable E ON Ch.Childid = E.Ownerid AND E.EventType = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SiteID = Site.PlaceID  
WHERE 
  Ch.RelMother = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL

SELECT
-- Alternate Name as a Fact
  Npers.Ownerid AS RIN, 
  UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
  Npers.Suffix COLLATE Nocase ||', '||  
  Npers.Prefix COLLATE Nocase ||' '||  
  Npers.Given COLLATE Nocase AS Person , 
  'Principal' AS Role, 
  'Alternate name' AS Event,
  Nalt.Date AS rawDate ,
  CASE Nalt.NameType
    WHEN 1 THEN 'AKA'
    WHEN 2 THEN 'Birth'
    WHEN 3 THEN 'Immigrant'
    WHEN 4 THEN 'Maiden'
    WHEN 5 THEN 'Married'
    WHEN 6 THEN 'Nickname'
    WHEN 7 THEN 'Other spelling'
    ELSE NULL
  END AS Detail, 
  NULL AS Site, 
  NULL AS Place, 
  NULL AS Geo,
  'Nil' AS Lat,
  'Nil' AS Long,
  NULL AS MRIN,
  UPPER(Nalt.Surname) COLLATE Nocase ||' '|| 
    Nalt.Suffix COLLATE Nocase ||', '|| 
    Nalt.Prefix COLLATE Nocase ||' '|| 
    Nalt.Given COLLATE Nocase 
    AS 'Alternate Name' ,          
  Count( 1 ) - 1 AS Dup,
  Nalt.SortDate AS SortDate
 
FROM Nametable AS Nalt
  LEFT JOIN NameTable AS Npers ON Nalt.OwnerID = Npers.OwnerID AND +Npers.IsPrimary = 1
WHERE +Nalt.Isprimary = 0 
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

UNION ALL

SELECT 
-- shared events from WitnessTable as Facts for Sharers 
  W.PersonID AS RIN, 
-- to bring out Treeless witnesses
  CASE W.PersonID   
  WHEN 0 THEN
  UPPER(W.Surname) COLLATE Nocase ||' '|| 
    W.Suffix COLLATE Nocase ||', '||  
    W.Prefix COLLATE Nocase ||' '||  
    W.Given COLLATE Nocase  
  ELSE
  UPPER(Nwitn.Surname) COLLATE Nocase ||' '|| 
    Nwitn.Suffix COLLATE Nocase ||', '||  
    Nwitn.Prefix COLLATE Nocase ||' '||  
    Nwitn.Given COLLATE Nocase
  END AS Person,  
  Rolename COLLATE Nocase AS Role, 
  Fact.Name COLLATE Nocase AS Event, 
  E.Date AS rawDate ,
  CAST( E.Details AS TEXT ) AS Detail,  
  Site.Name COLLATE Nocase AS Site,
  Place.Name COLLATE Nocase AS Place,  
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN 'S'    
    WHEN Place.latitude <> 0 OR Place.Longitude <> 0
    THEN 'P'
    ELSE null    
  END AS Geo,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.latitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.latitude*1.0e-7, 'Nil')    
  END AS Lat,
  CASE 
    WHEN site.Latitude <> 0 OR site.longitude <> 0
    THEN site.longitude*1.0e-7    
    WHEN Place.latitude = 0 AND Place.Longitude = 0
    THEN 'Nil'
    ELSE IFNULL(Place.longitude*1.0e-7, 'Nil')    
  END  AS Long,
  CASE E.OwnerType  
    WHEN 1 THEN
      'M'|| Fam.FamilyID      
    ELSE NULL
  END
     AS MRIN, 
  CASE E.OwnerType  
    WHEN 0 THEN    
      UPPER(Npers.Surname) COLLATE Nocase ||' '|| 
      Npers.Suffix COLLATE Nocase ||', '||  
      Npers.Prefix COLLATE Nocase ||' '||  
      Npers.Given COLLATE Nocase  ||'-'||      
      E.OwnerID
    WHEN 1 THEN
      IFNULL( UPPER(Nhusb.Surname) COLLATE Nocase ||' '|| 
      Nhusb.Suffix COLLATE Nocase ||', '||  
      Nhusb.Prefix COLLATE Nocase ||' '||  
      Nhusb.Given COLLATE Nocase ||'-'||      
      Nhusb.OwnerID , '' )
      ||
      IFNULL( ' & '|| UPPER(Nwife.Surname) COLLATE Nocase ||' '|| 
      Nwife.Suffix COLLATE Nocase ||', '|| 
      Nwife.Prefix COLLATE Nocase ||' '|| 
      Nwife.Given COLLATE Nocase ||'-'||      
      Nwife.OwnerID , '' )
  END    
    AS 'Sharer Name',
  Count( 1 )-1 AS Dup,
  E.SortDate 
FROM 
  Witnesstable AS W
  LEFT JOIN Eventtable AS E ON W.EventID = E.EventID 
  LEFT JOIN Facttypetable AS Fact ON E.Eventtype = Fact.Facttypeid    
  LEFT JOIN Roletable AS Role ON   W.Role = Role.Roleid    
  LEFT JOIN NameTable AS Nwitn ON W.Personid = Nwitn.Ownerid AND +Nwitn.IsPrimary = 1
  LEFT JOIN NameTable AS Npers ON E.OwnerID = Npers.Ownerid AND +Npers.IsPrimary = 1
  LEFT JOIN FamilyTable AS Fam ON E.OwnerID = Fam.FamilyID
  LEFT JOIN NameTable AS Nhusb ON Fam.FatherID = Nhusb.OwnerID AND +Nhusb.Isprimary =1 
  LEFT JOIN NameTable AS Nwife ON Fam.MotherID = Nwife.OwnerID AND +Nwife.Isprimary = 1
  LEFT JOIN PlaceTable AS Place ON E.PlaceID = Place.PlaceID
  LEFT JOIN PlaceTable AS Site ON E.SITEID = Site.PlaceID
GROUP BY  1 ,  2 ,  3 ,  4 ,  5 ,  6 ,  7 ,  8 ,  9 ,  10 ,  11 ,  12 , 13

ORDER BY RIN, SortDate
), Parms
WHERE 
  ABS(RefLat - Lat) < 360.0*RadiusMiles/24900
  AND
  ABS(RefLong - Long) < 360.0*RadiusMiles/24900/cos(RefLat/180*3.1416)
;