Assigning the modal average based on points within a polygon

Question

I have two layers:

• Survey Points
• Habitat Multi polygon

The Survey points contain the field sensitivity

I want to assign a number to each polygon based on the modal average sensitivity.

This thread has a similar theme

STIntersects - Update field to give me count of points in polygon

There is no way to do this inside QGIS so I've had to resort to using PostgreSQL. I've found an example to experiment with but it do not know how to incorporate the spatial aspect between datasets.

http://www.udel.edu/evelyn/SQL-Class3/SQL3_Stat.html

In summary I'm looking for help on how to script SQL to find the modal point value per polygon and attach it to the polygons attribute table.

Answer 1

I understand you want mode and not mean, median or count?

The following query should work:

SELECT polygon_id, val AS mode, MAX(cval) AS max_val_count, geometry 
FROM 
(
    SELECT 
    polygon_id,
    val,
    count(*) AS cval,
    geometry
    FROM 
    (
        SELECT
        polygon_layer.id AS polygon_id, 
        point_layer.id AS point_id , 
        point_layer.value as val,
        polygon_layer.geometry
        FROM polygon_layer JOIN point_layer ON ST_Intersects(polygon_layer.geometry, point_layer.geometry) = 1
        ) t 
    GROUP BY polygon_id, val
    ) t2
GROUP BY polygon_id

Change the above to your appropriate layers and field names.

This works in QGIS with a virtual layer (no need for PostGIS) but this can work with a PostGIS view too.