Sum up values of neighbouring polygons – a GRASS GIS approach

How to sum up values of neighbouring polygons in QGIS? This question was asked on gis.stackexchange, with two interesting answers. One answer explains how to use Spatialite and SQL to achieve this. The other answer, explained in more detail here, presents a script using pyqgis and shapely. In this post I am using an alternative approach, using GRASS GIS. Continue reading “Sum up values of neighbouring polygons – a GRASS GIS approach”


Spatialite 4.0 is out

The release of Spatialite 4.0 was just announced on the Spatialite user group. You can download it from the Spatialite website. And while you are on the website, check out the page about switching to 4.0. Version 4.0 introduces several relevant changes, which may cause (severe) cross-version compatibility issues.


Find and select duplicate values in your vector attribute table

Suppose you have a vector layer in your GRASS GIS database and you want to find all duplicate values in one of the columns of the attribute table of that layer?

If you are using SQLite or PostgreSQL as the database back-end (and you should), this is easy using a SQLite statement. Suppose you have the table sp1th and the column value with duplicate values: Continue reading “Find and select duplicate values in your vector attribute table”

Extract X and Y coordinates from a spatial table in Spatialite

A quick note on adding two columns with X and Y values in a vector layer in spatialite. Spatialite stores the information about the geometry in binary format in the ‘Geometry’ field.

You can use the Spatialite function AsText() to convert and extract these values as a Well Known Text (WKT), which is an easy to read format. If you for example have a spatial table ‘Waypoints’ with two columns ‘PKUID’ and ‘Geometry’, you can extract a column with WKT values using: Continue reading “Extract X and Y coordinates from a spatial table in Spatialite”

Split strings in SQLite

I am just back from vegetation survey in Kenya and Uganda. Before starting with the analysis, I will have to go through the more boring data entry and organization. I will use a Spatialite database to store all the data as it plays nicely together with QGIS as well as R. For example, importing the waypoint data (gpx format) into a spatialite database is a breeze with the QSpatialite plugin for QGIS.

One problem is that data and time in the gpx file are given in one field, in the form ‘2011/11/01 5:49:41+00‘. There are various ways I could split this before importing in the SQLite database, e.g., using R or a spreadsheet. But how to do this after importing the data in the Spatialite database? The date + time are imported in the field ‘time‘. Continue reading “Split strings in SQLite”