-- Place_Frequency_GPSVisualizer.sql
/*
2016-01-29 Tom Holden ve3meo
adapted from Place_Frequency.sql

Produces a comma-delimited list that can be copied and pasted into a text editor,
saved as a .csv file and uploaded to GPSVisualizer.com to produce a proportional
marker map for the the number of events in each place.

Temp View vPlaceFreq is the same result set as produced by Place_Frequency.sql
 Returns frequency of use for each Place in the database
 - total events for each place, including family events. 
 - max number of events for any person and a person having that max number
  (family events not counted)
 - Unused places also listed.
 Useful for finding places in need of Abbreviations or Geocoding or Unused
 
Temp View vGPSvisualizer builds a result set for the final query to transform 
into comma-delimited format.
*/
DROP VIEW IF EXISTS vPlaceFreq
;
CREATE TEMP VIEW vPlaceFreq
AS
SELECT Places.PlaceID
	,EventsByPlace AS TotEvents -- total events for place
	,Events AS MaxEvents -- Max Events for a place by Person (Indiv facts)
	,PersonID -- Person having the max events for that place
	,NAME AS Place -- place name used by Place or Place:original in sentence template
	,Abbrev -- value used by Place:short in sentence template
	,Latitude / 10000000.0 AS Latitude -- in decimal degrees, North+
	,Longitude / 10000000.0 Longitude -- in decimal degrees, East+
	,Normalized AS Standardized -- the Standardized value in the Edit Place screen
FROM PlaceTable Places
LEFT JOIN (
	SELECT PlaceID
		,PersonID
		,Max(Events) AS Events
	FROM (
		-- table of Places for which Persons have events and the number of events for each combinatio
		SELECT PlaceID
			,OwnerID AS PersonID
			,COUNT() AS Events
		FROM EventTable
		WHERE OwnerType = 0 -- Individual, not Family, events
		GROUP BY PlaceID
			,OwnerID -- to aggregate number of events by Place-Person combo
		ORDER BY Events ASC -- to order so that the next GROUP BY PlaceID will extract the highest value of Events for a Place
		)
	GROUP BY PlaceID
	) AS PersonEvents ON Places.PlaceID = PersonEvents.PlaceID
LEFT JOIN (
	-- table of total events per place
	SELECT PlaceID
		,COUNT() AS EventsByPlace
	FROM EventTable
	GROUP BY PlaceID
	) AS AllEvents ON Places.PlaceID = AllEvents.PlaceID
WHERE PlaceType = 0 -- user defined Place; excludes Place Details and Temples
	--GROUP BY Places.PlaceID -- to aggregate TotEvents and extract highest value of MaxEvents for Place-Person combo
ORDER BY MaxEvents DESC -- initial view puts the highest max events first as priority for attention
	;

DROP VIEW IF EXISTS vGPSvisualizer
;
CREATE TEMP VIEW vGPSvisualizer
AS
SELECT 
  ABBREV AS Name
  ,'Events: '|| TotEvents || ' in ' || Place AS Desc
  ,Latitude
  ,Longitude
  ,TotEvents AS N
FROM vPlaceFreq
WHERE TotEvents -- no point plotting places with no events
;

SELECT '"' || Name || '","' || Desc || '",' || Latitude || ',' || Longitude || ',' || N
AS [Name,Desc,Latitude,Longitude,N]
FROM vGPSvisualizer
;
-- end of script