Skip To Content ArcGIS for Developers Sign In Dashboard

ArcGIS Runtime SDK for Qt

Statistical query (group & sort)

Sample Viewer View Sample on GitHub

Query a feature table to get statistics for one or more specified fields. The sample queries a service feature table of US states to get the selected statistics. The results can be grouped and sorted using one or several fields in the table.

Use case

You can use statistical queries, grouping and sorting to process large amounts of data saved in feature tables. This is helpful for identifying trends and relationships within the data, which can be used to support further interpretations and decisions. For example, a health agency can use information on medical conditions occurring throughout a country to identify at-risk areas or demographics, and decide on further action and preventive measures.

How to use the sample

The sample will start with some default options selected. You can immediately click the "Get Statistics" button to see the results for these options. There are several ways to customize your queries:

  • You can add statistic definitions to the top-left table using the combo boxes and "Add" button. Select a table row and click "Remove" to remove a definition.

  • To change the Group-by fields, check the box by the field you want to group by in the bottom-left list view.

  • To change the Order-by fields, select a Group-by field (it must be checked) and click the ">>" button to add it to the Order-by table. To remove a field from the Order-by table, select it and click the "<<" button. To change the sort order of the Order-by field, the cells of the "Sort Order" column are combo-boxes that may be either ASCENDING or DESCENDING.

How it works

  1. Create a ServiceFeatureTable using the URL of a feature service and load the table.
  2. Get the feature tables field names list with featureTable.fields.
  3. Create StatisticDefinitions specifying the field to compute statistics on and the StatisticType to compute.
  4. Create StatisticsQueryParameters passing in the list of statistic definitions.
  5. To have the results grouped by fields, add the field names to the query parameters' groupByFieldNames collection.
  6. To have the results ordered by fields, create OrderBys, specifying the field name and SortOrder. Pass these OrderBys to the parameters' orderByFields collection.
  7. To execute the query, call featureTable.queryStatistics(queryParameters).
  8. Get the StatisticQueryResult. From this, you can get an iterator of StatisticRecords to loop through and display.

About the data

This sample uses a Diabetes, Obesity, and Inactivity by US County feature layer hosted on ArcGIS Online.

Relevant API

  • Field
  • OrderBy
  • QueryParameters
  • ServiceFeatureTable
  • StatisticDefinition
  • StatisticRecord
  • StatisticsQueryParameters
  • StatisticsQueryResult
  • StatisticType

Tags

correlation, data, fields, filter, group, sort, statistics, table

Sample Code

import QtQuick 2.6
import QtQuick.Controls 2.2
import Esri.ArcGISRuntime 100.9

Rectangle {
    id: rootRectangle
    clip: true
    width: 800
    height: 600

    

    ServiceFeatureTable {
        id: censusTable
        url: "https://services.arcgis.com/jIL9msH9OI208GCb/arcgis/rest/services/Counties_Obesity_Inactivity_Diabetes_2013/FeatureServer/0"

        Component.onCompleted: load();

        onLoadStatusChanged: {
            if (loadStatus !== Enums.LoadStatusLoaded)
                return;

            const fieldModel = [];
            for (let i = 0; i < fields.length; i++) {
                fieldModel.push(fields[i].name);
            }
            statisticOptionsPage.fields = fieldModel;
        }

        onQueryStatisticsStatusChanged: {
            if (queryStatisticsStatus === Enums.TaskStatusErrored) {
                resultsModel.clear();
                resultsModel.append({"section": "", "statistic": "Error. %1".arg(error.message)});
                return;
            }

            if (queryStatisticsStatus !== Enums.TaskStatusCompleted)
                return;

            // reset the model and check the results
            resultsModel.clear();
            if (!queryStatisticsResult) {
                resultsModel.append({"section": "", "statistic": "Error. %1".arg(error.message)});
                return;
            }

            // iterate the results and add to a model
            const iter = queryStatisticsResult.iterator;
            while (iter.hasNext) {
                const record = iter.next();
                const sectionString = JSON.stringify(record.group).replace("{","").replace("}","");

                for (let statKey in record.statistics) {
                    if (record.statistics.hasOwnProperty(statKey)) {
                        const result = {
                            "section" : sectionString,
                            "statistic" : statKey + ": " + record.statistics[statKey]
                        };
                        resultsModel.append(result)
                    }
                }
            }
        }
    }

    StackView {
        id: stackView
        anchors.fill: parent

        // Initial page is the OptionsPage
        initialItem: OptionsPage {
            id: statisticOptionsPage
            width: parent.width
            height: parent.height
            onStatisticButtonClicked: {
                // create the parameter object
                const params = ArcGISRuntimeEnvironment.createObject("StatisticsQueryParameters");

                // add the statistic definition objects
                const statisticDefinitions = [];
                for (let i = 0; i < statisticsModel.count; i++) {
                    const statistic = statisticsModel.get(i);
                    const definition = ArcGISRuntimeEnvironment.createObject("StatisticDefinition", {
                                                                                 onFieldName: statistic.field,
                                                                                 statisticType: statisticStringToEnum(statistic.statistic)
                                                                             });
                    statisticDefinitions.push(definition);
                }
                params.statisticDefinitions = statisticDefinitions;

                // set the grouping fields
                params.groupByFieldNames = groupingFields;

                // add the order by objects
                const orderBys = [];
                for (let j = 0; j < orderByModel.count; j++) {
                    const group = orderByModel.get(j);
                    const orderBy = ArcGISRuntimeEnvironment.createObject("OrderBy", {
                                                                              fieldName: group.field,
                                                                              sortOrder: orderStringToEnum(group.order)
                                                                          });
                    orderBys.push(orderBy);
                }
                params.orderByFields = orderBys;

                // ignore counties with missing data
                params.whereClause = "\"State\" IS NOT NULL";

                // execute the query
                censusTable.queryStatistics(params);

                // show the results page
                stackView.push(resultsPage);
            }
        }

        // The ResultsPage is shown when a query is executed
        ResultsPage {
            id: resultsPage
            width: parent.width
            height: parent.height
            statisticResult: resultsModel
            onBackClicked: stackView.pop();
        }
    }

    // helper to convert from statistic type string to enum
    function statisticStringToEnum(statString) {
        switch(statString) {
        default:
        case "Average":
            return Enums.StatisticTypeAverage;
        case "Count":
            return Enums.StatisticTypeCount;
        case "Maximum":
            return Enums.StatisticTypeMaximum;
        case "Minimum":
            return Enums.StatisticTypeMinimum;
        case "Standard Deviation":
            return Enums.StatisticTypeStandardDeviation;
        case "Sum":
            return Enums.StatisticTypeSum;
        case "Variance":
            return Enums.StatisticTypeVariance;
        }
    }

    // helper to convert from sort order string to enum
    function orderStringToEnum(orderString) {
        switch(orderString) {
        default:
        case "Ascending":
            return Enums.SortOrderAscending;
        case "Descending":
            return Enums.SortOrderDescending;
        }
    }

    ListModel {
        id: resultsModel
    }
}
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// http://www.apache.org/licenses/LICENSE-2.0

// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
// [Legal]

import QtQuick 2.6
import QtQuick.Controls 2.2

Rectangle {
    id: rootOptionsPage
    color: "#F4F4F4"

    readonly property real pageWidth: rootOptionsPage.width - flickable.anchors.margins * 2
    readonly property real labelTextSize: 12
    readonly property var statisticTypes: ["Average", "Count", "Maximum", "Minimum", "Standard Deviation", "Sum", "Variance"]
    property var fields: null
    property var groupingFields: []
    property alias statisticsModel: statisticsModel
    property alias orderByModel: orderByModel
    signal statisticButtonClicked()

    // Setup a list model with some defaults pre-set
    ListModel {
        id: statisticsModel

        ListElement {
            field: "Diabetes_Percent"
            statistic: "Average"
        }
        ListElement {
            field: "Diabetes_Percent"
            statistic: "Count"
        }
        ListElement {
            field: "Diabetes_Percent"
            statistic: "Minimum"
        }
    }

    // Setup a list model with some defaults pre-set
    ListModel {
        id: orderByModel

        ListElement {
            field: "State"
            order: "Ascending"
        }
    }

    // Create a Title Bar for the Options Page
    Rectangle {
        id: titleBar
        anchors {
            left: parent.left
            top: parent.top
            right: parent.right
        }
        color: "#005e95"
        height: 40
        clip: true

        Text {
            anchors.centerIn: parent
            text: "Statistics: US States"
            color: "white"
            font.pixelSize: 28
        }
    }

    // Create a Flickable Column to hold the various options for configuring statistics
    Flickable {
        id: flickable
        anchors {
            top: titleBar.bottom
            left: parent.left
            right: parent.right
            bottom: parent.bottom
            margins: 10
        }
        contentWidth: column.width
        contentHeight: column.height
        flickableDirection: Flickable.VerticalFlick
        clip: true

        Column {
            id: column
            width: pageWidth
            spacing: 10

            Rectangle {
                width: pageWidth
                height: 225
                color: "transparent"
                clip: true
                border {
                    width: 1
                    color: "black"
                }

                Column {
                    anchors {
                        fill: parent
                        margins: 10
                    }
                    spacing: 5

                    Row {
                        anchors.horizontalCenter: parent.horizontalCenter
                        spacing: 5

                        Text {
                            anchors.verticalCenter: parent.verticalCenter
                            text: "Field"
                            font.pixelSize: labelTextSize
                        }

                        ComboBox {
                            id: fieldComboBox
                            anchors.verticalCenter: parent.verticalCenter
                            property int modelWidth: 0
                            width: modelWidth + leftPadding + rightPadding + indicator.width
                            model: fields
                            onModelChanged: {
                                if (!fields)
                                    return;
                                for (let i = 0; i < model.length; ++i) {
                                    metricsFieldComboBox.text = model[i];
                                    modelWidth = Math.max(modelWidth, metricsFieldComboBox.width);
                                }
                            }
                            TextMetrics {
                                id: metricsFieldComboBox
                                font: fieldComboBox.font
                            }
                        }

                        Text {
                            anchors.verticalCenter: parent.verticalCenter
                            text: "Type"
                            font.pixelSize: labelTextSize
                        }

                        ComboBox {
                            id: statisticComboBox
                            anchors.verticalCenter: parent.verticalCenter
                            property int modelWidth: 0
                            width: modelWidth + leftPadding + rightPadding + indicator.width
                            model: statisticTypes
                            Component.onCompleted : {
                                for (let i = 0; i < model.length; ++i) {
                                    metricsStatisticComboBox.text = model[i];
                                    modelWidth = Math.max(modelWidth, metricsStatisticComboBox.width);
                                }
                            }
                            TextMetrics {
                                id: metricsStatisticComboBox
                                font: statisticComboBox.font
                            }
                        }

                        Button {
                            text: "+"
                            width: 30
                            height: width
                            onClicked: {
                                for (let i = 0; i < statisticsModel.count; i++) {
                                    if (statisticsModel.get(i).field === fieldComboBox.currentText) {
                                        if (statisticsModel.get(i).statistic === statisticComboBox.currentText)
                                            return;
                                    }
                                }

                                statisticsModel.append({field: fieldComboBox.currentText, statistic: statisticComboBox.currentText});
                            }
                        }
                    }

                    Rectangle {
                        width: parent.width
                        height: 125
                        color: "transparent"
                        clip: true
                        border {
                            color: "black"
                            width:  1
                        }

                        ListView {
                            id: statisticView
                            anchors {
                                fill: parent
                                margins: 5
                            }
                            highlightFollowsCurrentItem: true
                            model: statisticsModel
                            clip: true
                            highlight: highlightRectangle
                            highlightResizeVelocity: 1000000
                            highlightMoveVelocity: 1000000

                            delegate: Item {
                                width: parent.width
                                height: 25

                                Row {
                                    anchors.verticalCenter: parent.verticalCenter
                                    spacing: 10
                                    Text {
                                        text: field
                                        font.pixelSize: labelTextSize
                                    }
                                    Text {
                                        text: "(%1)".arg(statistic)
                                        font.pixelSize: labelTextSize
                                    }
                                }

                                MouseArea {
                                    anchors.fill: parent
                                    onClicked: statisticView.currentIndex = index;
                                }
                            }
                        }
                    }

                    Button {
                        anchors.horizontalCenter: parent.horizontalCenter
                        text: "Remove Statistic"
                        onClicked: statisticsModel.remove(statisticView.currentIndex, 1);
                    }
                }
            }

            Row {
                anchors.horizontalCenter: parent.horizontalCenter
                width: parent.width
                height: 175
                spacing: 5

                Column {
                    width: parent.width * 0.45 - parent.spacing
                    height: parent.height
                    spacing: 5

                    Text {
                        text: "Group Field(s):"
                        font.pixelSize: labelTextSize
                    }

                    Rectangle {
                        width: parent.width
                        height: 125
                        color: "transparent"
                        border {
                            width: 1
                            color: "black"
                        }
                        clip: true

                        ListView {
                            id: fieldView
                            anchors {
                                fill: parent
                                margins: 5
                            }
                            highlight: highlightRectangle
                            highlightResizeVelocity: 1000000
                            highlightMoveVelocity: 1000000
                            clip: true
                            model: fields
                            delegate: Item {
                                width: parent.width
                                height: 25

                                CheckBox {
                                    anchors.verticalCenter: parent.verticalCenter
                                    text: modelData
                                    checked: text === "State"
                                    onCheckedChanged: {
                                        if (checked) {
                                            groupingFields.push(text);
                                        } else {
                                            // remove the item from the selected list
                                            const i = groupingFields.indexOf(text);
                                            if (i !== -1)
                                                groupingFields.splice(i, 1);

                                            // remove the item from the orderBy list
                                            for (let j = 0; j < orderByModel.count; j++) {
                                                if (orderByModel.get(j).field === text)
                                                    orderByModel.remove(j, 1);
                                            }
                                        }
                                    }

                                    onClicked: fieldView.currentIndex = index;
                                }
                            }
                        }
                    }
                }

                Column {
                    anchors.verticalCenter: parent.verticalCenter
                    width: parent.width * 0.1 - parent.spacing
                    spacing: 5

                    Button {
                        anchors.horizontalCenter: parent.horizontalCenter
                        width: 30
                        height: width
                        text: ">"
                        onClicked: {
                            // return if the field is not selected
                            if (groupingFields.indexOf(fields[fieldView.currentIndex]) === -1)
                                return;

                            // return if the field is already added
                            for (let i =  0; i < orderByModel.count; i++) {
                                if (orderByModel.get(i).field === fields[fieldView.currentIndex])
                                    return;
                            };

                            // add the data to the model
                            orderByModel.append({field: fields[fieldView.currentIndex], order: "Ascending"});
                        }
                    }

                    Button {
                        anchors.horizontalCenter: parent.horizontalCenter
                        width: 30
                        height: width
                        text: "<"
                        onClicked: orderByModel.remove(groupingView.currentIndex, 1);
                    }
                }

                Column {
                    width: parent.width * 0.45
                    height: parent.height
                    spacing: 5

                    Text {
                        text: "Order by Field:"
                        font.pixelSize: labelTextSize
                    }

                    Rectangle {
                        width: parent.width
                        height: 100
                        color: "transparent"
                        border {
                            width: 1
                            color: "black"
                        }
                        clip: true

                        ListView {
                            id: groupingView
                            anchors {
                                fill: parent
                                margins: 5
                            }
                            highlight: highlightRectangle
                            highlightResizeVelocity: 1000000
                            highlightMoveVelocity: 1000000

                            clip: true
                            model: orderByModel
                            delegate: Item {
                                width: parent.width
                                height: 25

                                Row {
                                    anchors.verticalCenter: parent.verticalCenter
                                    spacing: 10
                                    Text {
                                        text: field
                                        font.pixelSize: labelTextSize
                                    }
                                    Text {
                                        text: "(%1)".arg(order)
                                        font.pixelSize: labelTextSize
                                    }
                                }

                                MouseArea {
                                    anchors.fill: parent
                                    onClicked: groupingView.currentIndex = index;
                                }
                            }
                        }
                    }

                    Button {
                        anchors.horizontalCenter: parent.horizontalCenter
                        text: "Change Sort Order"
                        onClicked: {
                            const i = groupingView.currentIndex;
                            if (orderByModel.get(i).order === "Ascending")
                                orderByModel.get(i).order = "Descending";
                            else
                                orderByModel.get(i).order = "Ascending";
                        }
                    }
                }
            }

            Button {
                anchors.horizontalCenter: parent.horizontalCenter
                width: 250
                height: 50
                text: "<u><b>Get Statistics</b></u>"
                onClicked: statisticButtonClicked()
            }
        }
    }

    Component {
        id: highlightRectangle
        Rectangle {
            color: "cyan"
            opacity: 0.4
        }
    }
}
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
// http://www.apache.org/licenses/LICENSE-2.0

// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
// [Legal]

import QtQuick 2.6
import QtQuick.Controls 2.2

Rectangle {
    color: "#F4F4F4"
    signal backClicked()

    property var statisticResult

    Column {
        anchors.fill: parent
        clip: true

        Rectangle {
            id: titleBar
            width: parent.width
            height: 40
            color: "#005e95"
            clip: true

            Item {
                anchors {
                    left: parent.left
                    margins: 10
                    verticalCenter: parent.verticalCenter
                }
                height: 30
                width: 100

                MouseArea {
                    anchors.fill: parent
                    onClicked: backClicked()
                }

                Text {
                    anchors {
                        left: parent.left
                        verticalCenter: parent.verticalCenter
                    }

                    text: "< Options"
                    color: "white"
                    font {
                        pixelSize: 18
                        bold: true
                    }
                }
            }

            Text {
                anchors {
                    centerIn: parent
                    margins: 10
                }
                text: "Results"
                color: "white"
                font.pixelSize: 28
            }
        }

        Item {
            width: parent.width
            height: parent.height - titleBar.height

            ListView {
                id: resultView
                anchors {
                    fill: parent
                    margins: 10
                }
                model: statisticResult
                clip: true

                section {
                    property: "section"
                    delegate: Rectangle {
                        width: resultView.width
                        height: 25
                        color: "lightsteelblue"

                        Text {
                            anchors.verticalCenter: parent.verticalCenter
                            text: section
                            font.bold: true
                            font.pixelSize: 12
                        }
                    }
                }

                delegate: Item {
                    width: parent.width
                    height: 25

                    Text {
                        anchors.verticalCenter: parent.verticalCenter
                        text: statistic
                        font.pixelSize: 12
                    }
                }
            }
        }
    }
}