D 2015-05-02T01:11:07.682
L Affine\sTransform
U sandro
W 7442
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0-doc">back</a>
<h2>Affine Transformations</h2>
Starting since version <b>4.3.0</b> SpatiaLite supports several new SQL functions based on <a href="http://en.wikipedia.org/wiki/Affine_transformation">affine 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 href="http://en.wikipedia.org/wiki/Strait_of_Messina">Strait 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 href="http://en.wikipedia.org/wiki/Strait_of_Messina_Bridge">bridge crossing the Strait</a> overlooking the very high seismical risk of this district and purposely ignoring that in 1908 both towns of <a href="http://en.wikipedia.org/wiki/1908_Messina_earthquake">Messina 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 src="https://www.gaia-gis.it/gaia-sins/affine-pics/sicily_0.png" alt="wfs-1" border="1">
</td></tr>
<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 href="http://en.wikipedia.org/wiki/Translation_%28geometry%29">translation</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 src="https://www.gaia-gis.it/gaia-sins/affine-pics/sicily_1.png" alt="wfs-1" border="1">
</td></tr>
<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 href="http://en.wikipedia.org/wiki/Rotation_%28mathematics%29">counterclockwise 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 src="https://www.gaia-gis.it/gaia-sins/affine-pics/sicily_2.png" alt="wfs-1" border="1">
</td></tr>
<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 href="http://en.wikipedia.org/wiki/Scaling_%28geometry%29">scaling</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 src="https://www.gaia-gis.it/gaia-sins/affine-pics/sicily_3.png" alt="wfs-1" border="1">
</td></tr>
<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 href="http://en.wikipedia.org/wiki/Scaling_%28geometry%29">scaling</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 src="https://www.gaia-gis.it/gaia-sins/affine-pics/sicily_4.png" alt="wfs-1" border="1">
</td></tr>
<tr><td>
<h3>Conclusion and final considerations</h3>
</td><td>
<img src="https://www.gaia-gis.it/gaia-sins/affine-pics/italy-2.0.png" alt="wfs-1" border="1">
</td></tr>
</table>
<hr><br>
<a href="https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0-doc">back</a>
Z 93f7b7581c9a495c31147239d1a5e429