utilisation Table building

But

Constitution d’une table regroupant les ways décrivant les contours d’un building et l’élément LineString correspondant.

Création de la table “building”

Dans cette table on se limitera aux trois champs utiles ou pré-calculés, à savoir :

• L’id du way ;
• Le champ value du tag building
• La LineString correspondant à la suite de nœuds formant le way.

-- Création de la table.
CREATE TABLE building (
	id INTEGER PRIMARY KEY,
	value TEXT,
	line BLOB); -- sans type géométrique initialement.

Remplissage de la table

-- Index d'optimisation
CREATE INDEX IF NOT EXISTS way_tags_id_tag ON way_tags (id_tag);

-- Remplissage
INSERT INTO building 
SELECT
	w.id AS id,
	t.value AS value,
	LineFromText('LINESTRING(' || (SELECT group_concat(X(n.coord) || ' ' || Y(n.coord))
		FROM way_nodes AS wn
		JOIN node AS n ON n.id=wn.id_node
		WHERE wn.id_way=w.id
		ORDER BY wn.rang) || ')', 4326) AS line
FROM way AS w
JOIN way_tags AS wt ON w.id=wt.id_way
JOIN tag AS t ON wt.id_tag=t.id
WHERE t.key='building';

-- Suppression des enregistrements avec colonne @line@ NULL
DELETE FROM building WHERE line IS NULL;

-- Caractérisation de la colonne géométrique contenant les LineString.
SELECT RecoverGeometryColumn('building', 'line', 4326, 'LINESTRING', 2);

Ajouts de différents indexes

-- Création des indexes géographiques
SELECT CreateMbrCache('building', 'line');		-- ou 
SELECT CreateSpatialIndex('building', 'line');	-- suivant le besoin.
CREATE INDEX building_value ON building (value);

Exemple d’utilisation

Recherche des polygones identiques :
Après RTFM (Spatialite Tutorial), j’ai compris que les indexes de Spatialite ne sont pas automatiques, mais qu’il faut les utiliser “à la main”.

Donc après la création d’un “SpatialIndex”, on obtient de nouvelles tables, en particulier Idx_building_line qui contient les MBR des LineString et les identifiants tirés de building.id (en fait, ce sont les ROWID, mais comme j’ai le bon gout d’avoir choisi un type INTEGER PRIMARY KEY pour cette colonne, ils se confondent tous les deux).

1. Commençons simplement par rechercher les buildings avec les mêmes MBR (BBox)
   

   SELECT *
   	FROM Idx_building_line AS ib1
   	JOIN Idx_building_line AS ib2 ON
   		ib1.xmin = ib2.xmin AND ib1.xmax = ib2.xmax AND
   		ib1.ymin = ib2.ymin AND ib1.ymax = ib2.ymax AND
   		ib1.pkid < ib2.pkid;	-- pour éviter l'égalité du même enregistrement et la double affichage.
   

2. Ajoutons le test d’égalité des contours des buildings : il faut rechercher le building à partir de la table des indexes.
   

   SELECT *
   	FROM Idx_building_line AS ib1
   	JOIN Idx_building_line AS ib2 ON
   		ib1.xmin = ib2.xmin AND ib1.xmax = ib2.xmax AND
   		ib1.ymin = ib2.ymin AND ib1.ymax = ib2.ymax AND
   		ib1.pkid < ib2.pkid;	-- pour éviter l'égalité du même enregistrement et la double affichage.
   	LEFT JOIN building as b1 ON b1.id=ib1.pkid		-- force le sens de la jointure
   	LEFT JOIN building as b2 ON b2.id=ib2.pkid		-- idem.
   	WHERE Equals(b1.line, b2.line);
   

   
   La preuve (avec un EXPLAIN QUERY PLAN sur la requête précédente) :
   orderfromdetail00TABLE Idx_building_line AS ib1 VIRTUAL TABLE INDEX 2:11TABLE Idx_building_line AS ib2 VIRTUAL TABLE INDEX 2:AdAcAbAa
3. Après ajoutons le test des tags tous identiques :
   

   SELECT
   	ib1.pkid AS way1, 
   	ib2.pkid AS way2,
   	group_concat(t1.key || '="' || t1.value || '"') AS tags
   	FROM idx_building_line AS ib1
   	JOIN idx_building_line AS ib2 ON
   		ib1.xmin = ib2.xmin AND ib1.xmax = ib2.xmax AND
   		ib1.ymin = ib2.ymin AND ib1.ymax = ib2.ymax AND
   		ib1.pkid < ib2.pkid	-- pour éviter l'égalité du même enregistrement et la double affichage.
   	LEFT JOIN building AS b1 ON b1.id = ib1.pkid		-- force le sens de la jointure
   	LEFT JOIN building AS b2 ON b2.id = ib2.pkid		-- idem.
   	LEFT JOIN way_tags AS wt1 ON wt1.id_way = ib1.pkid
   	JOIN tag AS t1 ON wt1.id_tag = t1.id
   	LEFT JOIN way_tags AS wt2 ON wt2.id_way = ib2.pkid
   	JOIN tag AS t2 ON wt2.id_tag = t2.id
   	WHERE Equals(b1.line, b2.line)
   	GROUP BY ib1.pkid, ib2.pkid
   	HAVING group_concat(t1.key || '="' || t1.value || '"') = group_concat(t2.key || '="' || t2.value || '"')
   

   
   Mais visiblement le GROUP BY n’est pas très performant lorsque je complète ma requête par un LIMIT. Voici une autre écriture, qui donne un résultat comparable (des colonnes de “travail” restent visibles dans le résultat).
   

   SELECT
   	ib1.pkid AS way1, 
   	ib2.pkid AS way2,
   	(SELECT
   		group_concat(t1.key || '="' || t1.value || '"')
   		FROM way_tags AS wt1
   		JOIN tag AS t1 ON wt1.id_tag = t1.id
   		WHERE wt1.id_way = ib1.pkid) AS tags1,
   	(SELECT
   		group_concat(t2.key || '="' || t2.value || '"')
   		FROM way_tags AS wt2
   		JOIN tag AS t2 ON wt2.id_tag = t2.id
   		WHERE wt2.id_way = ib2.pkid) AS tags2
   	FROM idx_building_line AS ib1
   	JOIN idx_building_line AS ib2 ON
   		ib1.xmin = ib2.xmin AND ib1.xmax = ib2.xmax AND
   		ib1.ymin = ib2.ymin AND ib1.ymax = ib2.ymax AND
   		ib1.pkid < ib2.pkid	-- pour éviter l'égalité du même enregistrement et la double affichage.
   	LEFT JOIN building AS b1 ON b1.id = ib1.pkid		-- force le sens de la jointure
   	LEFT JOIN building AS b2 ON b2.id = ib2.pkid		-- idem.
   	WHERE Equals(b1.line, b2.line) AND tags1 = tags2