I have a vector layer ‘nursery data’ in a Spatialite database. The coordinate reference system of the layer is WGS84 geodetic (EPSG 4326). What if I need to reproject the layer in WGS84/UTM37N (EPSG 32637)?
The geometry attributes of a vector layer is stored in a column, e.g., in the column Geometry. In the example below, I will add a geometry column ‘geom_utm’ with geometries in EPGS 32637. First, I need to create the column ‘geom_utm’, with data type ‘BLOB’. We then update (fill) this column with geometry attributes from the original Geometry column, applying a coordinate reprojection. This is done using the SQL/Spatialite function TRANSFORM().
ALTER TABLE 'nursery data' ADD COLUMN geom_utm BLOB; UPDATE 'nursery data' SET geom_utm = TRANSFORM(Geometry, 32637)
To use the SpatiaLite extension geometry functions, the newly created geometry field (geom_utm) need to be registred in a separate table managed by SpatiaLite. This can be done with the following SQL statement.
SELECT RECovergeometrycolumn('nursery data', 'utm',32637,'MULTIPOINT',2)
Note that the geometry type of the data in the table ‘nursery data’ is ‘multipoint’. Adapt the SQL statement above according to the geometry type of your data. Now, if you open your spatialite database in QGIS (there are various ways to open your database, including the QGIS browser and the Qspatialite plugin), you will see the same spatial layer twice (Figure 1). You should realize that these are both based on the same table though (open the database in a normal (non-spatial ) sqlite database manager and you will see only one table ‘nursery data’).
The last step can also be done using the GUI provided by the Qspatialite plugin for QGIS, as shown in Figure 2.
For more information, you definitely should check out the Spatialite cookbook (I got most of the above from there).