Logo Home    Page list

Places - Frequency of Use #places #geocoding

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:
-- 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
    SELECT PlaceID
        ,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
    -- 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