1 Dec 2014

QspatiaLite Use Case: SpatiaLite Aggregation over Points within Polygons using the QspatiaLite Plugin

Here's a nice example for aggregation of points per polygon areas, which I grabbed from an Answer on SO, by user @Micha. The polygons could be regions of interest, a sampling grid, etc.
Say you want to do maximum, minimum, averages, etc. per polygon using the spatial database SpatiaLite.

  • You'll first need to import both of your layers into a spatialite DB, called "sensors" (the point layer) here, with a "pollution" column and "SHAPE1" (the polygons) with a "plgnID" column. You can do this easily with the QspatiaLite-plugin "Import" button (example data is HERE).

  • Now this query will give you various statistics from the sensors for each polygon:

    SELECT g.plgnID AS "plgn_ID",
       AVG(s.pollution) AS "Average Pollution", 
       MAX(s.pollution) AS "Maximum Pollution",
       COUNT(*) AS "Number of Sensors"
    FROM sensors AS s JOIN SHAPE1 AS g 
    ON contains(g.geometry, s.geometry)
    GROUP BY g.plgnID

  • No comments :

    Post a Comment