Reprojecting your layer in Spatialite

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’).

Figure 1: My ‘nurseries’ database opened in the QGIS Qspatialite plugin. Note the two point layers ‘nursery data’. Both are based on the same table, but representing the point data in different projections

The last step can also be done using the GUI provided by the Qspatialite plugin for QGIS, as shown in Figure 2.

Figure 2: Creating a new spatial table ‘test’ based on the table ‘nursery data’, applying a coordinate reprojection.

For more information, you definitely should check out the Spatialite cookbook (I got most of the above from there).


One thought on “Reprojecting your layer in Spatialite

  1. Pingback: From xy table to spatial table in spatialite | Ecostudies

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s