Places+-+Frequency+of+Use

See Maps - markers proportional to number of events for an extension of this script to produce results suitable for mapping.

This script produces a list that I think is useful for finding the places that lack an abbreviated name or geographical coordinates and for tackling first the ones having the biggest payoff. For example, a place that is only used once in total or only once for a person does not need a shortened name. On the other hand, a place that is greatly used will add much to a report such as the Place List option "Print events near a place" once it is geo-coded and narratives will sound less repetitive using the abbreviated name. So the list provides two counts: The list includes: code format="sql" -- Place_Frequency.sql /* 2013-03-25 Tom Holden ve3meo rev 2013-03-25 TotEvents error corrected rev 2013-03-27 Place:Abbrev corrected to Place:short in comments. TotEvents extended to include family events. MaxEvents remains for Indiv only. Unused places also listed. Returns frequency of use for each Place in the database - total events for each place - max number of events for any person and a person having that max number (family events not counted) Useful for finding places in need of Abbreviations or Geocoding or Unused 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 ; code
 * the total number of events in the database that use each place (TotEvents),
 * the highest number of events that any one individual's events use each place (MaxEvents)
 * PlaceID: the record number in the PlaceTable for the Place in case you want to find it there
 * PersonID: the record number of a Person having the highest number of uses for the Place so that you can inspect the person using RM Explorer; there may be other persons with the same number or less - you could run the inner query to see them all.
 * Place: the full name that is the default output for reports and for the default Place:original variable in sentence templates.
 * Abbrev: the shortened name that is output by the Place:short variable in sentence templates
 * Latitude, Longitude: the geographical coordinates displayed in decimal degrees with North and East being positive values, South and West as negative.
 * Standardized: the name that the Geo-coding function assigns if the place is found in the Gazetteer or the name you input into the Standardized field which might be the contemporary name while the historical name might be entered in the Name field.