QspatiaLite Use Case: SpatiaLite Aggregation over Intersections of Polygons with QspatiaLite Plugin

This applies to several usecases: Imagine you have a grid or polygon-layer of sampling areas and want to know the dominant feature of another polygon layer under each grid cell / sampling polygon - this could be soiltypes, landuse classes, etc. Other than the dominant feature you might be interested in the diversity of features (i.e. number of soils, etc.) per grid cell / sampling area.

QGIS alone does not provide handy tools for aggregation of features of one layer combined with other layers, but the spatiaLite engine is tailored for this! Since QGIS now makes import/export and querying of spatial data easy, it seems very worthy to dive into spatiaLite and utilize its powerful tools!


For the presented example I'll use:

SHAPE1, which is a polygon layer with a sampling grid/areas

Soils, which is a polygon layer with soiltypes

I tested this on

QGIS 2.6 Brighton

with the QspatiaLite Plugin installed

Import the above layers to a spatiaLite DB with the Import function of the plugin (example data: HERE)

Run the query and choose "create spatial table and load in QGIS" and put geom as geometry column! (I chose SHAPE2 as name for the newly created layer..)

SELECT t.geom AS geom, 
    t.plgnID AS plgnID, 
    t.soiltype AS soiltype, 
    max(t.soil_area) AS MaxArea, count () AS n_soiltypes
       FROM (SELECT 
          g.Geometry AS geom, g.plgnID AS plgnID, s.Soiltype AS soiltype,
          AREA(INTERSECTION(g.geometryO, s.geometry)) AS soil_area
          FROM SHAPE1 AS g JOIN Soils AS s 
          ON INTERSECTS(g.Geometry,s.Geometry)
       ) AS t
GROUP BY t.plgnID
ORDER BY t.plgnID

That's it!