MapInfo Pro

 View Only
Back to discussions
Expand all | Collapse all

MapInfo Monday: Statistics from Selected Records

  • 1.  MapInfo Monday: Statistics from Selected Records

    Employee
    Posted 08-28-2023 03:34

    Happy #MapInfoMonday!

    In today's post, I will cover a topic that I was asked about by @Ruchi Mishra, a colleague of mine from Australia. Ruchi is working with a retail client who is trying the aggregate demographics or spend values from selected polygons.

    The obvious solution would be to use the Statistics Window in MapInfo Pro. It will always reflect the currently selected records and show a couple of aggregated values for all the numerical columns.

    You open the Statistics window from the Tool Windows dropdown on the Home tab.

    When you select records, the Statistics window will automatically show the summarized and average values for all the numerical columns for the selected records.
    This works out of the box with any datasets loaded. However, it is limited to the summarized and average values. It doesn't show you the minimum and Maximum values. It also counts the statistics for numerical values that shouldn't be aggregated, values like zip codes for example.
    The alternative solution is to build your own query to aggregate values from the selection. I'll show you how to do this, and how you easily can use this without having to open up a SQL dialog or window to run it.
    I open up the SQL window from the SQL dropdown. Make sure you have selected a couple of records from your table. This makes the SQL window capable of showing you the columns.
    I add a Count(*) aggregate to give me the number of records selected and I aggregate values from the Inhabitants column of my Purchasing Power dataset. I use these aggregates for my column: Sum(), Avg(), Min(), and Max(). You can of course use as many types of aggregates on as many columns as you want.
    You can see a complete list of aggregates in the Aggregates list. Be aware that the first four aggregates are spatial aggregates that work on the spatial data.
    I query the result into a named query and I use the NoSelect keyword to avoid this query from changing my current selection. Here's the query, I built:
    Select Count(*) "Number_of_areas"
    , Sum(q.Inhabitants) "Inhabitants_Sum"
    , Avg(q.Inhabitants) "Inhabitants_Avg"
    , Min(q.Inhabitants) "Inhabitants_Min"
    , Max(q.Inhabitants) "Inhabitants_Max"
    From Selection As "q"
    Into __Stats NoSelect
    I want to browse the result in a floating window so that I still can see my map underneath. I use the Browse statement with the Floating keyword to achieve this:
    Browse * From __Stats Floating
    Finally, I give my query a name and mark it as a favorite. Use the icons on the right in the Scripts lists to do this.
    The benefit of marking a query - or script in general - as a favorite is that you can find these in the Favorites section of the SQL dropdown. From here you can run the script without showing it first in the SQL Select dialog or in the SQL window.
    When you hold your mouse on a favorite script in the Favorites section, you will see a popup with the content of the script.
    Click on the script to run it and see the result in a floating browser window.
    If you think the floating browser or browser windows in generally too annoying, you can change the script to show the result in the Info window.
    Change the Browse statement in the script to this Set Window statement instead:
    Set Window Info Table __Stats Rec 1 Front
    __Stats is the name of the query result and the Front keyword ensures that the Info window will be brought to the front so that you can see the result.
    Make sure to save the changes to your script to the script file using the Save icon in the Scripts list.
    When you run your script, the result will be shown in the Info window.
    I hope this gives you a good idea of how you can use the scripts in the SQL window to automate your workflow.


    ------------------------------
    Peter Horsbøll Møller
    Principal Presales Consultant | Distinguished Engineer
    Precisely | Trust in Data
    ------------------------------