-- MapEvents-KML.sql 2 Sep 2010 by ve3meo
-- Produces both a useful on-screen display like Geo-Lifelines
--  AND XML-coded KML placemarks that can be imported into 
--  Google Maps (best), Google Earth and Bing Maps.
-- Works best with a SQLite manager that can sort columns such as SQLiteSpy and SQLite Developer.
-- Requires selected results to be copied and pasted into a KML file shell. 
--  This file is then imported into the map. 
-- For details vist http://sqlitetoolsforrootsmagic4.wikispaces.com/MapEvents+query
 
DROP TABLE IF EXISTS Parms;
CREATE TEMP TABLE IF NOT EXISTS Parms (RefLat FLOAT, RefLong FLOAT, RadiusMiles FLOAT, StartSortdate INTEGER, EndSortDate INTEGER);
-- 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 (x,x,0,0,0) will return all facts.
INSERT INTO Parms VALUES (43.9005556,-78.8572222,10, 0, 0); -- CHANGE THESE VALUES TO SUIT YOUR NEEDS   

SELECT 
  '<!--' AS '<!--',
  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,
  MRIN,  
  "Sharer Name",
  Dup,
  SortDate,

  '-->' AS '-->',
  '
  <Placemark>
    <name><![CDATA[' ||
  Person || '-' || 
  RIN || ', ' || 
  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
  || ']]></name>' ||
  '
    <description><![CDATA[' ||
  CASE  Role 
  WHEN '' THEN '' 
  ELSE '(' || Role ||') '
  END || 
  CASE Detail
  WHEN '' THEN ''
  ELSE Detail || '. '
  END ||
  ifnull("Sharer Name" || '. ','') ||
  CASE Geo
  WHEN 'S' THEN '<u>'
  ELSE ''
  END ||
  ifnull(Site ||', ' , '') ||
  CASE Geo
  WHEN 'S' THEN '</u>'
  WHEN 'P' THEN '<u>'
  ELSE ''
  END ||
  Place ||
  CASE Geo
  WHEN 'P' THEN '</u>'
  ELSE ''
  END ||
  ']]></description>
    <Point><coordinates>' ||
  Long || ',' || Lat || ',0' ||
  '</coordinates></Point>
  </Placemark>' 
AS '<!--XML PlaceMark-->'  
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 
  CASE RadiusMiles
  WHEN 0 THEN 1
  ELSE 
    ABS(RefLat - Lat) < 360.0*RadiusMiles/24900
    AND
    ABS(RefLong - Long) < 360.0*RadiusMiles/24900/cos(RefLat/180*3.1416)
  END

  AND

  CASE StartSortDate
  WHEN 0 THEN 1
  ELSE  
    SortDate >= StartSortDate 
  END

  AND

  CASE EndSortDate
  WHEN 0 THEN 1
  ELSE  
    SortDate <= EndSortDate
  END
--LIMIT 200 
-- Google Maps and Bing Maps both have a limit of about 200 points that can be imported. 
;