<a
<h2>Affine Transformations</h2>
Starting since version <b>4.3.0</b> SpatiaLite supports several new SQL functions based on <a transformation</a>; understanding the underlaying mathematics could easily be rather difficult if you have absolutely no familiarity with this kind of operazions.<br>
So we'll start smoothly by introducing first a very simple practical example based on kind of a joke about the geography of Italy.
<h2>Fancy Geograpy and amusing Math</h2>
<table border="1" cellspacing="4" cellpadding="8">
<tr><td>
<h3>Preface</h3>
Looking to a map of Italy it appears obvious at first glance that Sicily is located in a very inconvenient position.<br>
It's dangerously close to north african shores and the <a of Messina</a> is exaggeratedly narrow, thus causing many troubles and severe security risks to the navigation of capital ships.<br>
Even worst, from time to time some crazy politician strongly advocates the very stupid idea to build an incredibly costly <a crossing the Strait</a> overlooking the very high seismical risk of this district and purposely ignoring that in 1908 both towns of <a and Reggio Calabria</a> were completely destroyed by an earthquake followed by a tsunami killing more than 100,000 peoples, one of the most frightening natural disasters registered in Europe during modern times.<br><br>
So we'll immediately start a case study intended to identify a possible alternative location for Sicily.<br>
Incidentally we'll use the new SQL functions based on Affine Transformations for this task.<br>
Let's go on.
</td><td>
<img alt="wfs-1" border="1">

<tr><td>
<h3>Step #1: translating Sicily into a more convenient position</h3>
There is plenty of unused free room in the lower Tyrrhenian Sea, so we start by applying a <a this practically means adding (or subctracting) a constant value to the coordinates on both <b>x</b> and <b>y</b> axes.<br>
After a very quick examination going <b>150 km</b> westewards and <b>150 km</b> northwards seems to be an absolutely reasonable choice.<br>
So using the Affine Transformation SQL functions we'll write the following SQL statement:
<verbatim>
CREATE TABLE sicilia_1 AS
SELECT cod_reg,
   ATM_Transform(geometry,
      ATM_CreateTranslate(-150000, 150000)) AS geom
FROM sicilia_0;
SELECT RecoverGeometryColumn('sicilia_1', 'geom', 32632, 'MULTIPOLYGON', 'XY');
</verbatim>
</td><td>
<img alt="wfs-1" border="1">

<tr><td>
<h3>Step #2: rotating Sicily so to get a nice horizontal alignement</h3>
Aligning the southern shores of Sicily to an almost horizontal line will surely lead to a more nicely ordered layout: so we have now to apply a <a rotation</a> of about <b>25.0 degrees</b>.<br>
Thanks to Affine Transformations, we can combine both the previous translation and the current rotation into a single movemente (a so called <b>rototranslation</b>).<br>
We simply have to execute the following SQL statement:
<verbatim>CREATE TABLE sicilia_2 AS
SELECT cod_reg,
   ATM_Transform(geometry,
      ATM_Translate(-150000, 150000,
         ATM_Translate(cx, cy,
            ATM_Rotate(25,
               ATM_CreateTranslate(-cx, -cy))))) AS geom
FROM (SELECT cod_reg, ST_X(centroid) AS cx, ST_Y(centroid) AS cy, geometry
      FROM (SELECT cod_reg, ST_Centroid(geometry) AS centroid, geometry
            FROM sicilia_0) AS g1
) AS g2;
SELECT RecoverGeometryColumn('sicilia_2', 'geom', 32632, 'MULTIPOLYGON', 'XY');
</verbatim>
</td><td>
<img alt="wfs-1" border="1">

<tr><td>
<h3>Step #3: inflating and reshaping Sicily</h3>
An increased surface is surely welcome, because will automatically imply more cultivable lands, thus certanly impelling a strong productivity boost to the sicilian agricolture: on the other hand shortening a little bit the length of the southern coastline will surely facilitate mobility and communications.<br>
So we'll now apply a <a transformation using two different values: <b>sx=0.9</b> and <b>sy=1.3</b>.<br>
Once again, Affine Transformations enable us to combine altogether both translate, rotate and scale into a single transformation.<br>
This is the corresponding SQL statement:
<verbatim>CREATE TABLE sicilia_2 AS
CREATE TABLE sicilia_3 AS
SELECT cod_reg,
   ATM_Transform(geometry,
      ATM_Translate(-150000, 150000,
         ATM_Translate(cx, cy,
            ATM_Scale(0.9, 1.3,
               ATM_Rotate(25,
                  ATM_CreateTranslate(-cx, -cy)))))) AS geom
FROM (SELECT cod_reg, ST_X(centroid) AS cx, ST_Y(centroid) AS cy, geometry
      FROM (SELECT cod_reg, ST_Centroid(geometry) AS centroid, geometry
            FROM sicilia_0) AS g1
) AS g2;
SELECT RecoverGeometryColumn('sicilia_3', 'geom', 32632, 'MULTIPOLYGON', 'XY');
</verbatim>
</td><td>
<img alt="wfs-1" border="1">

<tr><td>
<h3>Step #4: final touch: reflecting Sicily</h3>
An increased surface is surely welcome, because will automatically imply more cultivable lands, thus certanly impelling a strong productivity boost to the sicilian agricolture: on the other hand shortening a little bit the length of the southern coastline will surely facilitate mobility and communications.<br>
So we'll now apply a <a transformation using two different values: <b>sx=0.9</b> and <b>sy=1.3</b>.<br>
Once again, Affine Transformations enable us to combine altogether both translate, rotate and scale into a single transformation.<br>
This is the corresponding SQL statement:
<verbatim>CREATE TABLE sicilia_2 AS
CREATE TABLE sicilia_4 AS
SELECT cod_reg,
   ATM_Transform(geometry,
      ATM_Translate(-150000, 150000,
         ATM_Translate(cx, cy,
            ATM_YRoll(180,
               ATM_Scale(0.9, 1.3,
                  ATM_Rotate(25,
                     ATM_CreateTranslate(-cx, -cy))))))) AS geom
FROM (SELECT cod_reg, ST_X(centroid) AS cx, ST_Y(centroid) AS cy, geometry
      FROM (SELECT cod_reg, ST_Centroid(geometry) AS centroid, geometry
            FROM sicilia_0) AS g1
) AS g2;
SELECT RecoverGeometryColumn('sicilia_4', 'geom', 32632, 'MULTIPOLYGON', 'XY');
</verbatim>
</td><td>
<img alt="wfs-1" border="1">

<tr><td>
<h3>Conclusion and final considerations</h3>
</td><td>
<img alt="wfs-1" border="1">

</table>

<hr><br>
<a