Back to <a 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 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 Open Data</a> datasets.
<ol>
<li><b>ambiti_amministativi</b>: a collection of administative boundaries datasers (Vector).</li>
<li><b>grafo stradale 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
</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 'regione', 'CP1252', 3003);
SELECT 'province', 'CP1252', 3003);
SELECT 'comuni', 'CP1252', 3003);
SELECT 'elem_ferr', 'CP1252', 3003);

<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);

<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;

</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
<td align="center"><img alt="tuscany1"
<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"
<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"
<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"
<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"
<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" alt="tuscany7"

<tr><td>Alternatively you can add or delete the associated SRIDs just for a single Vector Coverage.</td>
<td align="center"><img alt="tuscany50"
<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"
<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"
</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"
<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"
<tr><td>Now you'll register a second WMS layer based on <a 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"
<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"
<tr><td>This is the dialog box corresponding to the <b>Aerial Ortophotos</b> WMS Layer.</td>
<td align="center"><img alt="tuscany12"
<tr><td>And this is the dialog box corresponding to the <b>OSM Google</b> WMS Layer.</td>
<td align="center"><img alt="tuscany14"
</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
<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.

<td align="center"><img alt="tuscany18"
<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"
<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"
<tr><td><h4>Styling the DTM</h4>
Just follow the same procedure already explained about
<td align="center"><img alt="tuscany27"
<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"
<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"
</table>

<br><br><hr>
Back to <a Wiki page</a>