D 2017-06-18T06:48:47.214
L tuscany
P 86e47dd2bfb4d9fc0bb4dccad1917687613f7d45
U sandro
W 11406
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>
<hr>
<h3>Step #3: registering WMS Layers.</h3>
<table cellpadding="8" cellspacing="4" border="1">
<tr><td>SpatialLite possess the capability to permanently register WMS Layers into a database file.</td>
<td align="center"><img alt="tuscany9" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany9.png"></td></tr>
<tr><td>You are required first to manually insert the URL corresponding to the <b>WMS GetCapabilitis</b>, then pressing the <b>Load</b> button.<br><br>
May well be that your local network configuration could require defining an <b>HTTP Proxy Server</b>.<br><br>
If the above GetCapability URL do effectively match an existing WMS server a list of WMS layers will appear: just select the one you intend to register, adjust the WMS configuration then confirm.<br><br>
In this specific example you'll register a WMS layer based on the most recent (2013) version of Aerual Ortophoto fully covering whole Tuscany.</td>
<td align="center"><img alt="tuscany10" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany10.png"></td></tr>
<tr><td>Now you'll register a second WMS layer based on <a href="https://www.openstreetmap.org">Open Street Map</a> vector datasets styled like the very popular <b>GeooleMaps</b>.<br><br>
<u>Note</u>: in this case you'll not select any specific child layer; you'll select instead the parent WMS super-layer, so to get a complete OSM Map in a single WMS request.</td>
<td align="center"><img alt="tuscany11" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany11.png"></td></tr>
<tr><td>As you have already seen about <b>Raster</b> and <b>Vector Coverages</b>, explicitly setting <b>Copyright</b> and <b>License</b> informations always is a best practice; this applies to <b>WMS Layers</b> too.</td>
<td align="center"><img alt="tuscany13" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany13.png"></td></tr>
<tr><td>This is the dialog box corresponding to the <b>Aerial Ortophotos</b> WMS Layer.</td>
<td align="center"><img alt="tuscany12" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany12.png"></td></tr>
<tr><td>And this is the dialog box corresponding to the <b>OSM Google</b> WMS Layer.</td>
<td align="center"><img alt="tuscany14" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany14.png"></td></tr>
</table><br>
<hr>
<h3>Step #4: Creating, populating and configuring the DTM Coverage.</h3>
<table cellpadding="8" cellspacing="4" border="1">
<tr><td>Final task: now you are requested to set up a Raster Coverage based on the <b>dtmoro.asc</b> DTM.<br><br>
This is practically identical to what you've previously seen about the <b>ETOPO1</b> dataset, and you'll apply once again the same styling rules.<br><br>
So we'll just explain the few relevant differences.</td></tr>
<tr><td><h4>Creating the Raster Coverage</h4>
<b>dtmoro</b> is a <b>Numeric Data Grid</b> exactly as <b>ETOPO1</b>, anyway a difference exists:<br><br>
<ul>
<li>ETOPO1 is based on <b>16 bit signed integer</b> values.</li>
<li>dtmoro.asc is based on <b>floating point single precision</b> values.
</ul></td>
<td align="center"><img alt="tuscany18" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany18.png"></td></tr>
<tr><td><h4>Important notice</h4>
If you are working on any recent version of <b>MS Windows</b> you strictly require to executed <b>spatialite_gui</b> under administator's privileges in order to succesfully import an <b>ASCII Grid</b> (as <b>dtmoro.asc</b> is).<br><br>
This is because Windows 7, Windows 8 and Windows 10 for security reasons forbid to create <b>temporary files</b> on the local file systems to all unauthorized users.<br>
The same limitation obviously applies to <b>rl2tool.exe</b> and to <b>mod_rasterlite2.dll</b> because is dictated by the underlying operating system.<br><br>
This odd limitation is Windows-specific, and never applies to sane minded Linux, Mac OS x or Unix-like platforms.</td>
<td align="center"><img alt="tuscany19" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany19.png"></td></tr>
<tr><td><h4>Loading an ASCII Grid</h4>
<u>Note</u>: you have to explicitly set an appropriate SRID value, because the ASCII Grid format does not support any internal SRID declaration.</td>
<td align="center"><img alt="tuscany21" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany21.png"></td></tr>
<tr><td><h4>Styling the DTM</h4>
Just follow the same procedure already explained about <b>ETOPO1</b>.</td>
<td align="center"><img alt="tuscany27" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany27.png"></td></tr>
<tr><td><h4>Defining Copyright and License informations</h4>
<u>Remember</u>: this always is a warmly suggested best practice.</td>
<td align="center"><img alt="tuscany29" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany29.png"></td></tr>
<tr><td>Final step: you have now to compute the full Extent (BBOX) for the <b>dtmoro</b> Raster Coverage.
<h3>All right; the <b>tuscany.sqlite</b> sample database is now ready to be published !</h3>
</td>
<td align="center"><img alt="tuscany30" src="https://www.gaia-gis.it/gaia-sins/gui2-pics/tuscany30.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 73ee5c38f904d78dd1e337b0c2d283e2