Preparing to start

1. download the sample dataset
   (it simply corresponds to a slightly modified vector map of Italy's Regions as originally released by ISTAT under CC-BY license terms).
2. unzip the downloaded dataset and import the Shapefile into a SpatiaLite DB file. The appropriate import settings are shown in the side figure:
   SRID=32632, charset=CP1252.
3. Extract Sicily into a separate database table by executing the following SQL statements:

   CREATE TABLE sicilia AS
   SELECT * FROM italy WHERE cod_reg = 19;
   
   SELECT RecoverGeometryColumn('sicilia', 'geometry', 32632, 'MULTIPOLYGON', 'XY');
   
   DELETE FROM italy WHERE cod_reg = 19;
   

4. that's all: you are now ready to start this funny (and certainly not serious) tutorial about Affine Transformations.

Preface

1. Sicily is placed too far South and too much close to North Africa; this implies an unpleasant torrid summer, and this later poses in turn many severe limitations to agricultural activities.
2. The Strait of Messina is exaggeratedly narrow, thus posing severe safety risks to the navigation of capital ships, big container ships, cruise liners and supertankers.
   Even worse, from time to time some crazy politician starts strongly advocating the very stupid idea to build an incredibly costly bridge crossing the Strait, completely overlooking the very high seismic risk of this district and deliberately forgetting that in 1908 both towns of Messina and Reggio Calabria were completely destroyed by an earthquake followed by a tsunami.
   More than 120,000 peoples lost their lives, and it was one of the most frightening natural disasters registered in Europe during modern times.

Step #1: translating Sicily into a more convenient position

CREATE TABLE sicilia_1 AS
SELECT cod_reg, 
   ATM_Transform(geometry,
      ATM_CreateTranslate(-150000, 150000)) AS geom
FROM sicilia;

SELECT RecoverGeometryColumn('sicilia_1', 'geom', 32632, 'MULTIPOLYGON', 'XY');

• all affine transformation related SQL function names start with an ATM_ prefix: this simply stands for Affine Transformation Matrix.
• ATM_Transform() is very similar to ST_Transform(); the most obvious difference is in that all transformation arguments are now expected to be passed in the form of an appropriate BLOB-serialized Affine Transformation Matrix.
• ATM_CreateTranslate() simply is an SQL function returning a BLOB-serialized Affine Transformation Matrix initialized in such a way to represent a simple 2D Translate accordingly to (tx, ty) arguments.
  You could eventually define a complete 3D Translate by passing (tx, ty, tz) arguments, but this isn't strictly required in our current example.

Step #2: rotating Sicily so to get a nice horizontal alignment

CREATE TABLE sicilia_2 AS
SELECT cod_reg, 
   ATM_Transform(geometry,
      ATM_Translate(
         ATM_Translate(
            ATM_Rotate(
               ATM_CreateTranslate(-cx, -cy),
            25),
         cx, cy),
      -150000, 150000)
   ) 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) AS g1
) AS g2;

SELECT RecoverGeometryColumn('sicilia_2', 'geom', 32632, 'MULTIPOLYGON', 'XY');

• correctly handling Rotate is a little bit more difficult. Any rotation will always imply a fixed center point, and by default this is exactly placed at the coordinates origin: (0, 0).
• so a very naive attempt to directly invoke ATM_Rotate(25) will simply relocate Sicily in Southern Spain, and this absolutely isn't our intention.
• what we really need is a more complex sequence of chained transformations:
  1. we'll start first by creating a new BLOB-Matrix intended to relocate Sicily's centroid exactly on the coordinates origin: ATM_CreateTranslate(-cx, -cy)
  2. now we can safely chain the intended Rotate: ATM_Rotate(atm-blob, 25)
  3. after applying Rotate we have now to restore the initial position: ATM_Translate(atm-blob, cx, cy)
  4. and finally we'll apply the Translation intended to reposition Sicily westward and northward: ATM_Translate(atm-blob, -150000, 150000)
     we've used two separate translations simply for didactic clarity: we could easily merge both them into a single translation:
     ATM_Translate(atm-blob, cx - 150000, cy + 150000)
  5. this way ATM_Transform() will receive the final BLOB-Matrix resulting by chaining all the above transformation steps in the correct sequence.
• Very important notice: Affine Transformations are not commutative: the relative order of subsequent operations in a complex transformation chain should always be very carefully considered.
• all the FROM (SELECT ... FROM (SELECT ...) AS g1) AS g2 stuff simply is a rather trivial SQL trick based on two nested sub-queries.
  It's intended scope is simply to avoid multiple calls to ST_Centroid(), ST_X() and ST_Y() in order to get the centroid coordinates.

Step #3: inflating and reshaping Sicily

CREATE TABLE sicilia_3 AS
SELECT cod_reg, 
   ATM_Transform(geometry,
      ATM_Translate(
         ATM_Translate(
            ATM_Scale(
               ATM_Rotate(
                  ATM_CreateTranslate(-cx, -cy),
               25),
            0.9, 1.3),
         cx, cy),
      -150000, 150000)
   ) 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) AS g1
) AS g2;

SELECT RecoverGeometryColumn('sicilia_3', 'geom', 32632, 'MULTIPOLYGON', 'XY');

• there is nothing really new in this: we'll simply chain yet another transformation in the appropriate sequence order.
• ATM_Scale(atm-blob, sx, sy) is intended for the simpler 2D case.
• in the more general 3D case you can invoke ATM_Scale(atm-blob, sx, sy, sz)

Step #4: final touch: reflecting Sicily

CREATE TABLE sicilia_4 AS
SELECT cod_reg, 
   ATM_Transform(geometry,
      ATM_Translate(
         ATM_Translate(
            ATM_YRoll(
               ATM_Scale(
                  ATM_Rotate(
                     ATM_CreateTranslate(-cx, -cy),
                  25),
               0.9, 1.3),
            180),
         cx, cy),
      -150000, 150000)
   ) 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) AS g1
) AS g2;

SELECT RecoverGeometryColumn('sicilia_4', 'geom', 32632, 'MULTIPOLYGON', 'XY');

• there are several possible rotations supported by Affine Transformation:
  1. ATM_Rotate() always intends a 2D rotation, i.e. a rotation centered around the Z axis
  2. In the more general 3D case there are three possible rotations, one for each axis.
  3. the corresponding SQL functions are: ATM_XRoll(), ATM_YRoll() and ATM_ZRoll()
  4. Note: ATM_ZRoll() and ATM_Rotate() simply are two different alias-names for the same identical SQL function.

Final considerations and conclusions

1. Agriculture: after applying the suggested relocation Sicily will gain an increased agricultural surface and will enjoy a more favourable climate: still sunny and warm but much less torrid and arid. This will certainly sustain a noticeable development of many economic activities based on agri-food industries.
2. Transportation systems: the suggested new layout for the Lower Tyrrhenian Sea strongly facilitates the development of maritime transports. Sicily could now become the central hub of an efficient network of high-speed and high-frequency ferry connections:
   • Palermo will now directly face Naples; Civitavecchia (Rome) seems to be a second obvious terminal for direct connections leading to Central Italy.
   • Messina will acquire a decisive strategic role, and will become the terminal for ferry connections leading to Central and Northern Italy: Civitavecchia, Leghorn and Genoa are the obvious destinations.
   • A less relevant (but anyway interesting) ferry link will join Trapani and Reggio Calabria.
   • Sardinia as well will strongly benefit from the new layout; Cagliari will be directly connected to Syracuse (or may be Augusta), and Olbia to Messina.
     This means definitely breaking the secular insulation of Sardinia, that will now start enjoying a stronger and more effective integration with Southern Italy.
   • Last but not least: at a more strategic level it's absolutely obvious that now supertankers, big container ships and cruise liners can freely circumnavigate Sicily in any direction under uncompromised safety conditions.
     And consequently all Tyrrhenian harbors will now be directly connected both to Eastern and Western Mediterranean: this will surely induce a remarkable growth in the volumes of international traffics they could potentially attract.
3. Heavy industry: the new transportation system strongly centered around maritime communications will surely induce an active rebirth of shipyards, a flourishing traditional excellence of many southern regions in past times but nowadays a sadly declining activity.
4. Tourism: a rearranged Lower Tyrrhenian Sea will certainly become a very attractive destination for international tourists.
   It's worth noting that the proposed layout will give birth to a wonderful island group extending between Sicily and southern Lazio - Campania; the Pontine, Aeolian and Gulf of Naples islands will be practically merged into a single archipelago.
   Several of these islands actually are active volcanoes, and the new island chain will directly join Mount Etna and Mount Vesuvius.
   So this area will represent the most impressive volcanic field of Europe and will certainly become a major tourist attraction thanks to its nicely sunny weather and pleasant climate.
5. Internal commerce: we can easily forecast a strong growth in volume of internal exchanges thanks to the better connectivity based on maritime transports.
   Just a single example: Sardinia should now be able to export its finest sheep cheese on Calabrian markets whilst Calabria could freely export its renown red hot chilly peppers to Sardinia; not only both regions will widely benefit from increased exchange volumes, but Sicily as well will take profit from flourishing logistic and by other activities based on commercial intermediation services.
6. Practical realization: the present study clearly demonstrates that there is nothing in Mathematics, Geometry or Geography forbidding the practical realization of the suggested idea.
   Unhappily the current state of the art in Geology still poses many puzzling problems not yet fully resolved; anyway we are hopefully expecting that future advancements in Tectonics will possibly allow to overcome any remaining issue.
   More specifically a better knowledge of deep interactions between the crust and the upper mantle along the Moho and a more detailed comprehension of the micro-plaques mechanics will certainly help; we are rather confident in future research achievements on these fields.

SELECT ATM_Multiply(ATM_CreateRotate(15), 
                    ATM_Multiply(ATM_CreateScale(1.1, 1.2, 1.3), 
                                 ATM_CreateTranslate(10, 20, 30)));

SELECT ATM_Rotate(
          ATM_Scale(
             ATM_CreateTranslate(10, 20, 30), 
          1.1, 1.2, 1.3), 
       15);

SELECT ATM_Rotate(
          ATM_Scale(
             ATM_Translate(
                ATM_Create(), 
             10, 20, 30),
          1.1, 1.2, 1.3),
       15);