D 2017-06-18T00:11:17.255
L tuscany
U sandro
W 6244
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a><hr><br>
<h1>Creating the tuscany.sqlite sample database</h1>
This quick tutorial explains by practical step by step examples how the <a href="https://www.gaia-gis.it/gaia-sins/gui2-samples/tuscany.sqlite">tuscany.sqlite</a> sample database was actually created.<br>
It's mainly intended for didactic purposes: you are expected to replicate all single steps, and at the end of the process you'll surely master the new tools
and wizards supported by <b>spatialite_gui 2.1.0</b>.<br><br>
All <b>Vector</b> and <b>Raster Coverages</b> will be based on <a href="http://www502.regione.toscana.it/geoscopio/cartoteca.html">Tuscany Open Data</a> datasets.
<ol>
<li><b>ambiti_amministativi</b>: a collection of administative boundaries datasers (Vector).</li>
<li><b>grafo stradale Iter.Net</b>: roads and railways (we'll use the railways only).</li>
<li><b>Morfologia - dtmoro</b>: a <b>DTM</b> (Raster).</li>
<li><b>WMS - Web Map Services</b> published by Tuscany Region (
<a href="http://www.regione.toscana.it/-/geoscopio-wms">GEOscopio WMS</a>)</li>
</ol>
<h3>Step #1: populating the database by importing input Shapefiles</h3>
<table cellpadding="8" cellspacing="4" border="1">
<tr><td>This one is a pure SQL task, and we'll assume that you should be already well accustomed to this.</td>
<td><verbatim>
SELECT ImportShp('C:\vanuatu\toscana\ambiti_amministrativi\am_reg_region_am_reg', 'regione', 'CP1252', 3003);
SELECT ImportShp('C:\vanuatu\toscana\ambiti_amministrativi\am_prov_multipart', 'province', 'CP1252', 3003);
SELECT ImportShp('C:\vanuatu\toscana\ambiti_amministrativi\am_com_multipart', 'comuni', 'CP1252', 3003);
SELECT ImportShp('C:\vanuatu\toscana\Iternet\shp\\elem_ferr', 'elem_ferr', 'CP1252', 3003);
</verbatim></td></tr>
<tr><td>Now a <b>Spatial View</b> will be created and properly registered.</td>
<td><verbatim>
CREATE INDEX idx_codprov ON province (substr(codprov, 3));
CREATE VIEW municipalities AS
SELECT c.pk_uid AS rowid, c.nome AS municipality,
c.codcom AS census_code, c.codcatasto AS cadastral_code,
p.nome AS province, p.sigla AS car_plate,
c.geometry AS geom
FROM comuni AS c
JOIN province AS p ON (substr(p.codprov, 3) = c.codprov)
INSERT INTO views_geometry_columns
VALUES ('municipalities', 'geom', 'rowid', 'comuni', 'geometry', 1);
</verbatim></td></tr>
<tr><td>
The <b>tuscany.sqlite</b> sample contains a meainingless <b>mixed Geometry Types</b> Spatial Table just intended for testing purposes.<br>
You can easily create by yourself something similar, so just very skeletical
directions will be supplied here.<br>
Please check the SQL Snippets on the side.</td>
<td><verbatim>
CREATE TABLE mixed_geom_types (
id INTEGER PRIMARY KEY AUTOINCREMENT,
class TEXT NOT NULL,
nome TEXT NOT NULL);
SELECT AddGeometryColumn('mixed_geom_types', 'geom', 3003,
'GEOMETRY', 'XY');
INSERT INTO mixed_geom_types (id, class, nome, geom)
SELECT NULL, 'Points', name, point_geom
FROM point_table;
INSERT INTO mixed_geom_types (id, class, nome, geom)
SELECT NULL, 'Linestrins', name, linestring_geom
FROM linstring_table;
INSERT INTO mixed_geom_types (id, class, nome, geom)
SELECT NULL, 'Polygons', name, polygon_geom
FROM polygon_table;
</verbatim></td></tr>
</table><br>
<hr>
<h3>Step #2: creating Vector Coverages based on Spatial Tables and Spatial Views.</h3>
<table cellpadding="8" cellspacing="4" border="1">
<tr><td>Spatial Tables declaring a <b>GEOMETRYCOLLECTION</b> or <b>GEOMETRY</b> Type aren't considered at all exceptional in SpatiaLite.<br><br>
You just have to create a Vector Coverages as you've already seen in the previous tutorial about <b>natural_earth.sqlite</b>.</td>
<td align="center"><img alt="tuscany1" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany1.png"></td></tr>
<tr><td>Just select a Spatial Table from the list, then fill all dialog fields and finally confirm.</td>
<td align="center"><img alt="tuscany2" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany2.png"></td></tr>
<tr><td>Registering a Vector Coverage based on some <b>Spatial View</b> is more or less the same: you just have to select a different tool from the menu.</td>
<td align="center"><img alt="tuscany3" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany3.png"></td></tr>
<tr><td>Just select a Spatial Table from the list, then fill all dialog fields and finally confirm.</td>
<td align="center"><img alt="tuscany4" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany4.png"></td></tr>
<tr><td>SpatiaLite possess the capability to reproject on-the-fly a Vector Coverage, but you have to declare in advance all the supported SRIDs.<br><br>
When you have to configure several strictly related Vector Coverages it could be simpler and easier configuring all Coverages in a single final pass.</td>
<td align="center"><img alt="tuscany5" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany5.png"></td></tr>
<tr><td>You can add just a single SRID at each time, but you can easily recicle on the same step until all SRIDs have been added.</td>
<td align="center"><img alt="tuscany6" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany6.png"><br><img alt="tuscany7" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany7.png">
</td></tr>
<tr><td>Alternatively you can add or delete the associated SRIDs just for a single Vector Coverage.</td>
<td align="center"><img alt="tuscany50" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany50.png"></td></tr>
<tr><td>Just use the dialog shown on the side in order to add or remove SRID definitions.</td>
<td align="center"><img alt="tuscany51" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany51.png"></td></tr>
<tr><td><u>Remember</u>: you always have to update the Extents every time that you'll add a new Vector Coverare or that you'll change its associated SRIDs.</td>
<td align="center"><img alt="tuscany8" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany8.png"></td></tr>
</table>
<br><br><hr>
Back to <a href="https://www.gaia-gis.it/fossil/spatialite_gui/wiki?name=spatialite-gui">main Wiki page</a>
Z e283dbfbb47f507de61dc1300af76b28