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><br><hr>
Back to <a Wiki page</a>