Geoprocess geometries within your Database: the Spatialite example

There are a number of good reasons to store your data in a real Database instead of using shapefiles and other file based formats. One of the most importants, and often ignored, is that the Databases do not only store your data, but also give the user a huge set of tools to allow geoprocess and analyze data. Take for instance Spatialite and its pretty impressive list of internal functions.

Assume we want to rotate a vector, we can use the function

RotateCoordinates( geom Geometry , angleInDegrees Double precision ) : Geometry

that will effectively rotate the vector, but as its done around the 0,0 point it may be not that useful. What we usuallly want to do is to rotate around the centroid of the vector or around any other point of our choice. Spatialite has obviously also a function to shift geometries

ShiftCoordinates( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry

so just calculate the x,y of the point you want to use to rotate your vector, do first a shift (to 0,0), do the rotation and another shift to place the vector back were it was originaly, example:

CREATE TABLE vector_rotated AS SELECT columnA, columnB, ShiftCoordinates( RotateCoords( ShiftCoordinates( geom,9.14549264013939,-38.7292415317569),45), -9.14549264013939,38.7292415317569) AS the_geom FROM vector

Everything can be done very easily within Quantum GIS, the DB Manager tool and its SQL console

Screenshot from 2012-05-27 21:19:44

After that and when using Spatialite don’t forget to issue

SELECT RecoverGeometryColumn(‘tablename’, ‘the_geom’, 4326 , ‘LINESTRING’, ‘XY’);

to “register” the column that stores the geometries. Now you can add the new vector layer

Screenshot from 2012-05-27 21:24:40