29 Nov 2014

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!
  • 4 Nov 2014

    VBA Spreadsheet Function for Substring Inbetween Strings

    Function Substring2(theString As String, str1 As String, repstr1 As Integer, Optional str2 As Variant, Optional repStr2 As Variant) As String
    
        '****************************************************************************************************************
        'author:    kay cichini
        'date:      04112014
        'purpose:   find substring deligned by the x-th repition of one string at the left side
        '           and anothers string x-th repition at the right side
        'str1:      first string to be matched
        'str2:      second string to be matched, optional
        'repstr1:   nth repition of str1 to be matched
        'repstr2:   nth repition of str2 to be matched, optional
        '           with optional arguments ommited function will return substring ending with the last character of the
        '           searchstring
        '----------------------------------------------------------------------------------------------------------------
        'example:   Substring2("1234 678 101214 xxxx"; " "; 2; "x"; 3)
        '           will match position 10 after the second repition of str1, find position 20 after the third "x"
        '           then apply a mid-function with signature 'mid(string, start, length)',
        '           where the position 10 is the start and length is position 20 - len("x") - 10 = 9
        '           and the result is "101214 xx"
        '****************************************************************************************************************
        
        Dim start1, start2, lenStr1, lenStr2, length As Integer
        
        If IsMissing(str2) And IsMissing(repStr2) Then
        
            'case when last char in string should be matched
            '-----------------------------------------------
            
            start1 = 1
            lenStr1 = Len(str1)
            
            If InStr(start1, theString, str1) = 0 Then
                '0 -> String couldn't be matched!
                Exit Function
            End If
            
            For i = 0 To repstr1 - 1
                start1 = InStr(start1, theString, str1) + lenStr1
            Next i
            
            length = Len(theString) - start1 + 1
            Substring2 = Mid(theString, start1, length)
    
        Else
        
            'other cases
            '-----------
            start1 = 1
            lenStr1 = Len(str1)
            start2 = 1
            lenStr2 = Len(str2)
            
            If InStr(start1, theString, str1) = 0 Or InStr(start2, theString, str2) = 0 Then
                '0 -> String couldn't be matched!
                Exit Function
            End If
            
            For i = 0 To repstr1 - 1
                start1 = InStr(start1, theString, str1) + lenStr1
            Next i
            
            For i = 0 To repStr2 - 1
                start2 = InStr(start2, theString, str2) + lenStr2
            Next i
    
            length = start2 - lenStr2 - start1
            Substring2 = Mid(theString, start1, length)
            
        End If
        
    End Function