Places – Frequency of Use #places #geocoding

2021-11-22 Compatible with #RM8

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 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)

The list includes:

• 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.

Place_Frequency.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
    ;