SpatiaLite Cookbook

	Recette #1: Creer une BDD spatiale propre
Février 2011

Table des matières

Modèle relationnel

Toute base de donnée bien faite est basé sur le modèle relationnel.
En simplifiant:

Chaque catégorie (aka class) de la BDD sera repérée par un identifiant unique
Identifier toutes les relations reliant les catégories.
Éviter la redondance des données.

Concernant le jeu de donnée ISTAT Census 2001; identifier les catégories et les relations est très simple:

En bout de chaîne, nous avons les Local Councils
Chaque Local Council appartient à une County : ainsi, il existe une relation liant Local Councils et Counties.
Plus précisément, il s'agit d'une relation de type one-to-many
(un seul County / plusieurs Local Councils: placer un Local Council dans plusieurs Counties est interdit).
La même chose opère entre County et Regions.
Il n'y a pas réellement besoin d'établir une relation entre Local Councils et Regions, car elle découle des deux relations précédentes.

Ainsi, il est assez facile d'identifier plusieurs défauts dans la présentation du Shapefile original:

La colonne POP2001 est commune à Local Councils, Counties et Regions:
Bon, c'est clairement un cas de redondance inutile.
Nous allons simplement garder l'information au sein de l’échelon le plus bas (Local Councils):
on pourra par la suite transmettre cette valeur aux Counties (or Regions).
Une deuxième redondance existe: nous n'avons pas réellement besoin de stocker les codes County et Region pour chaque Local Council.
Préserver le code County est suffisant, car il est possible de transférer cette valeur à la Region correspondante.
Une représentation géométrique est stockée pour chaque County et Region:
c'est aussi une redondance, car on peut obtenir ces géométrie en aggregeant celles stockées au niveau des Local Council.

Ensuite nous avons le jeu de donnée cities1000 : celui ci viens d'une source totalement différente( il n'y a donc pas d'identifiants permettant d'établir une relation avec d'autres entités).
Ce jeu de donnée est livré en projection 4326 SRID (WGS84), alors que le jeux de donnée ISTAT - Census 2001 est en projection 23032 SRID [ED50 UTM zone 32];
On laissera donc ce jeux de donné dans son état initial.
On verra plus tard comment lier ce jeu de donnée aux autres (relations spatiales)

CREATE TABLE regions (
region_id INTEGER NOT NULL PRIMARY KEY,
region_name TEXT NOT NULL);

Étape 1a) on va commencer par créer la table regions (i.e. 1er niveau hiérarchique).
note: on a définit une PRIMARY KEY (clé primaire), i.e. un identifiant unique et non ambigu pour identifier chaque région.

INSERT INTO regions (region_id, region_name)
SELECT COD_REG, REGIONE
FROM reg2001_s;

Étape 1b) On va peupler la table regions .
La requête INSERT INTO ... SELECT ... effectue une copie:
les lignes sont extraites de la table d'origine, puis insérées dans la table cible.
Comme vous pouvez le constater, les colonnes sont identifiées dans l'ordre .

CREATE TABLE counties (
  county_id INTEGER NOT NULL PRIMARY KEY,
  county_name TEXT NOT NULL,
  car_plate_code TEXT NOT NULL,
  region_id INTEGER NOT NULL,
  CONSTRAINT fk_county_region
    FOREIGN KEY (region_id)
    REFERENCES regions (region_id));

INSERT INTO counties (county_id, county_name,
car_plate_code, region_id)
SELECT cod_pro, provincia, sigla, cod_reg
FROM prov2001_s;

Étape 2a) on va maintenant créer et peupler la table counties.
note: une relation existe entre counties et regions.
On va définir une FOREIGN KEY (clé étrangère) afin d'expliciter cette relation une fois pour toute.

CREATE INDEX idx_county_region
ON counties (region_id);

Étape 2b) Pour des raisons de performance, on doit créer un INDEX correspondant à chaque FOREIGN KEY que l'on va définir

En faisant simple: une PRIMARY KEY n'est pas une simple contrainte logique (logical constraint).
Dans SQLite, définir une PRIMARY KEY implique la création d'un index implicite supportant un accès direct rapide à chaque ligne.
En revanche, une FOREIGN KEY établit simplement une contrainte logique.
Ainsi, si vous voulez un support rapide à chaque ligne, vous devrez créer un index spatial.

CREATE TABLE local_councils (
  lc_id INTEGER NOT NULL PRIMARY KEY,
  lc_name TEXT NOT NULL,
  population INTEGER NOT NULL,
  county_id INTEGER NOT NULL,
  CONSTRAINT fk_lc_county
    FOREIGN KEY (county_id)
    REFERENCES counties (county_id));

CREATE INDEX idx_lc_county
ON local_councils (county_id);

Étape 3a) On va maintenant créer la table local_councils.
Une relation liant local_councils et counties existe également.
Ainsi, ici aussi, il faudra définir une FOREIGN KEY , puis créer l'index correspondant.
note: on a pour l'instant défini aucune colonne géométrique, bien que ce soit nécessaire pour local_councils;
ce n'est pas une erreur, c'est tout à fait intentionnel.

SELECT AddGeometryColumn(
'local_councils', 'geometry',
23032, 'MULTIPOLYGON', 'XY');

Étape 3b) créer une colonne Géométrique nécessite une procédure particulière.
On doit utiliser la fonction spatiale AddGeometryColumn(), en spécifiant:

nom de la table
nom de la colonne geometry
le SRID
le type de géométrie
la dimension
(dans notre cas, 2D simple )

INSERT INTO local_councils (lc_id,
lc_name, population, county_id, geometry)
SELECT PRO_COM, NOME_COM, POP2001,
COD_PRO, Geometry
FROM com2001_s;

Étape 3c) Enfin, nous pouvons peupler la table local_councils comme d'habitude.

CREATE TABLE populated_places (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL);

SELECT AddGeometryColumn(
'populated_places', 'geometry',
4326, 'POINT', 'XY');

INSERT INTO populated_places (id,
name, geometry)
SELECT NULL, COL002,
MakePoint(COL006, COL005, 4326)
FROM cities1000
WHERE COL009 = 'IT';

Étape 4) Dernière étape: créer (et peupler) la table populated_places.
Plusieurs points importants ici:

on a utilisé la clause AUTOINCREMENT pour PRIMARY KEY
ceci signifie que SQLite générer automatiquement un ID unique approprié pour cette PRIMARY KEY lorsque aucune valeur n'a été spécifiée.
d'après tout ceci, après INSERT INTO la valeur NULL a été explicitée pour peupler la PRIMARY KEY:
ceci indique explicitement à SQLite de remplir automatiquement ce champ.
le jeu de donnée original cities1000 contient deux colonnes longitude [COL006] et latitude [COL005]:
on a donc utilisé la fonction spatiale MakePoint() afin de créer une géométrie de type POINT.
en spécifiant le SRID 4326 on instancie la géométrie avec le SRS WGS84 [Geographic System].

Récapitulatif rapide:

Vous avez tout d'abord manipulé des Virtual Shapefiles (et Virtual CSV/TXT).
Ces Virtual Tables ne sont pas de réelles BDD: elles ne sont pas stockées en interne.
Ces tables ont été utiles afin de tester quelques requêtes SQL.
Afin de pouvoir effectuer des requêtes plus complexes, nous allons importer ces tables dans SpatiaLite
Cette étape nécessite de créer des tables internes.

DROP TABLE com2001_s;
DROP TABLE prov2001_s;
DROP TABLE reg2001_s;
DROP TABLE cities1000;

Étape 5) Finalement vous pouvez supprimer les Virtual Tables, nous ne les utiliserons plus.
Note: le fait de supprimer les tables virtuelles ne supprime en aucun cas les fichiers sources, mais juste le lien entre ces fichiers et votre BDD

Table des matières

	Author: Alessandro Furieri a.furieri@lqt.it Traduced from English by RIVIERE Romain
	This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

	Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.