Category Archives: Spatialite

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

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

New spatialite plugin for QGIS

For some time now it is possible to load SpatiaLite data layers in QGIS. It is read only though, any changes will need to be saved as shapefile.

There is a Spatialite manager plugin, which offers an interface to the database, with table view, table information, preview of the map, import of dbf files and a very basic SQL editor. Unfortunately, it doesn’t offer what I would consider a key function, the option to upload layers to the Spatialite database.

This gap has now been filled by the QGIS plugin QSpatialite. Continue reading