13 Dec 2022

QGIS3: Virtual Layers & Spatialite/SQLite/SQL Queries - Intersecting Lines With Polygons

SQL and Spatialite make super effective table and geometry operations possible. With the implemantation of Virtual Layers, QGIS has now a built in functionality to run such queries very easily and without the need for preparation of databases. The below SQL snippet, i.e., can be used to intersect features of a line and a polygon layer, and calculate the summed segment lengths for each single polygon. The usecase here was, that I needed to know the cummulated lengths of trail crossing single parcels.

  t.Name AS Trail_Name,
  g.GNR AS Parcel_NR,
  SUM(LENGTH(INTERSECTION(t.geometry, g.geometry))) AS SUM_L_inP,
  COLLECT(INTERSECTION(t.geometry, g.geometry)) as geom
FROM Trails AS t JOIN Parcels AS g ON INTERSECTS(t.geometry, g.geometry)

9 Sept 2022

QGIS REGEX Expression to Find Last Vaue of an XML/Html Image Source of the Description Field of a KML File

regexp_substr(description, 'img src="!?.*img src="(.*)"') = '',
regexp_substr(description, 'img src="(.*)"'), 
regexp_substr(description, 'img src="!?.*img src="(.*)"') 

7 Jul 2022

GDAL: Bash Script for Converting All GPKG-Files From Directory To GPX-Format

Since lately QGIS (QGIS 3.12) comes equipped with a nice tool ("Split Vector Layers"), that will split your layers based on an attribute and ame your files accordingly. Th only drawback is, that you can not choose different output format - so you'll need to go with the only possibility, namely the GPKG-format. Now, I rather needed GPX files and had to find a way of converting the gpkg-files in batch mode. This I achieved by writing a small bash script looping over the files with GDAL's ogr2ogr command, and convert each file to GPX.

#!/bin/bash ## first two lines and last line prevent whitspace problems in filenames (see: https://unix.stackexchange.com/questions/9496/looping-through-files-with-spaces-in-the-names ## then loop over all files with gpkg extension an convert to gpx format preserving the old filename! ## the script would throw an error and discard attribute fields for all fieldnames that do not match the GPX XML definition (name, cmt, etc…). ## if you dont put the GPX_USE_EXTENSION=YES paramter.. ## with <-t_srs epsg:4326=""> the target CRS, to which the input coordinates will be transformed, is given.. OIFS="$IFS" IFS=$'\n' for f in *.gpkg do ogr2ogr -f GPX -dsco GPX_USE_EXTENSIONS=YES -t_srs EPSG:4326 -overwrite ${f%.*}.gpx $f done IFS="$OIFS" read -p "Hit [Enter] to exit..."