Discussion :

[justinedr71]

Bonjour

Mon projet consiste à mettre en place des chemins.
Pour cela je travaille sur les données suivantes :

=> Des links : ligne de points ayant une direction
=> Des segments : ligne droite reliant deux points

Les segments sont toujours rattachés à un link.
Par exemple, le Link 1 est composé de 5 segments, les segments 1, 2, 3, 4 et 5.

Je dois créer une fonction qui, à partir d'un segment, me renvoie la totalité de ses segments suivants (qu'ils soient sur le même link ou non. En effet, il peut y avoir des intersections entre plusieurs link)

J'ai la table Suivants qui est comme suit :
Le sens représente le fait que le segment suivant soit rattaché à la fin 'F' ou au début 'D' du segment précédent.

LINK_ID SEG_ID SENS LINK_ID_SUIVANT SEG_ID_SUIVANT
1 1 F 1 2
1 2 D 1 1
1 2 F 1 3
1 3 D 1 2
1 3 F 1 4

Mon but serait de pouvoir créer une fonction, qui à partir d'un segment donné, me trouve tous les segments suivants, de façon récursive.
Aussi, il existe une autre table, sur laquelle je peux faire une jointure sur le LINK_ID et le SEG_ID afin de pouvoir trouver les détails du segment (coordonnées géographiques pour l'affichage, longueur entre les deux points d'extrémité...) : table Segments. Je dois aussi me servir de cette table pour m'assurer que chaque hiérarchie ne dépasse pas une certaine longueur...

Voila, jusqu'ici je me suis pas trop mal débrouillée en suivant ce lien :
http://sqlt.tripod.com/recursivity.htm

Mais j'ai des doubles dans mon résultat, et je ne suis pas sur que la longueur à ne pas dépasser soit respectée...

Voici mon code :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
 
ALTER FUNCTION GetRecurs (@LinkID BIGINT, @SegmentID  BIGINT, @longueur FLOAT)
/* La fonction prend en paramètres :
=> @LinkID & @SegmentID : identifiants du segment dont on veut la descendance
=> @longueur : distance maximale sur laquelle doit s'étendre la liste des segments */
RETURNS @result TABLE (LINK_ID BIGINT, SEG_ID BIGINT, geom GEOGRAPHY, long FLOAT)
/* Dans la table de retour : 
=> LINK_ID & SEG_ID : identifiants du segment
=> geom : pour l'affichage du segment
=> long : pour le calcul de la longueur totale */
 
AS
 
BEGIN
	DECLARE @Lid BIGINT
	DECLARE @Sid BIGINT
	DECLARE @LidSEG BIGINT
	DECLARE @SidSEG BIGINT	
	DECLARE @row INT
	DECLARE @ligne INT	
	DECLARE @sommeLongueur FLOAT = @longueur	
	DECLARE @tbl_temp TABLE (i INT identity, LINK_ID BIGINT, SEG_ID BIGINT, geom GEOGRAPHY, long FLOAT)
 
	/* On insère dans la table le segment dont il est question 
	UNE SEULE LIGNE D'INSEREE */
	INSERT INTO @result
	SELECT LINK_ID, SEG_ID, geom, LONG
	FROM Test.dbo.Segments
	WHERE LINK_ID = @LinkID AND SEG_ID = @SegmentID
	/* A présent notre table contient les données du segment passé en paramètre.
	A ce stade aucun doublon car :
	=> Première insertion : table vide
	=> Insertion suivante : à partir de la table Temporaire, qui elle n'a pas de doublons
	SOURCE D'ERREUR POSSIBLE */  
 
	/* On met à jour la longueur des segments déjà comptabilisés
	en ajoutant la longueur du segment nouvellement ajouté */
	SELECT @sommeLongueur += LONG FROM @result WHERE LINK_ID = @LinkID AND SEG_ID = @SegmentID 
 
	/* On insère dans la table temporaire la liste des segments qui suivent ce segment,
	si ces segments ne sont pas déjà comptabilisés dans la table result =>
	empêche de boucler */
	INSERT INTO @tbl_temp
	SELECT su.LINK_ID_SEG as LINK_ID, su.SEG_ID_SEG as SEG_ID, se.geom, se.LONG
	FROM Test.dbo.Suivants su, Test.dbo.Segments se
	WHERE su.LINK_ID = @LinkID AND su.SEG_ID = @SegmentID
	AND se.LINK_ID = su.LINK_ID_SEG AND se.SEG_ID = su.SEG_ID_SEG
	AND NOT EXISTS (SELECT r.LINK_ID, r.SEG_ID 
					FROM @result r
					WHERE r.LINK_ID = su.LINK_ID_SEG
					AND r.SEG_ID = su.SEG_ID_SEG)  
 
	/* @row = nombre de ligne dans la table temporaire :
	CAD nombre de segments suivants le segment passé en paramètre */
	SET @row = @@ROWCOUNT
 
	/* On se base sur la première ligne de notre table temporaire */
	SET @ligne = 1
 
	/* Pour chaque élément de la table temporaire,
	CAD chaque segment */
	WHILE ( @ligne < = @row AND @sommeLongueur < 200  )
	BEGIN
		/* On récupère les données du segment qui nous intéresse
		pour effectuer la récursivité */
		SELECT @LID = LINK_ID FROM @tbl_temp WHERE i = @ligne
		SELECT @SID = SEG_ID FROM @tbl_temp WHERE i = @ligne
		INSERT INTO @result
		SELECT * FROM dbo.GetRecurs(@LID, @SID, @sommeLongueur)
 
		SET @ligne = @ligne + 1			
	END
	RETURN
END

[mikedavem]

Bonjour,

Quelle version de SQL Server ?

Si 2005 il existe les CTE pour traiter la récursivité.

Voir l'article de SQL Pro à ce sujet : http://sqlpro.developpez.com/cours/s...te-recursives/

++

[justinedr71]

Microsoft SQL server 2008....

Les CTE sont-ils toujours d'actualité sur cette version?

[mikedavem]

Les CTE sont-ils toujours d'actualité sur cette version?

Oui

++

[SQLpro]

Étant l'auteur de l'article en question, je me permet de vous signaler que votre modélisation n'est pas très bonne. En effet il n'aurait pas fallu dédoubler vos liens, mais simplement prévoir le lien montant et le lien descendant séparément si vous gérez un graphe orienté.
Dans ce cas, deux méthodes sont possible :
1) table des trajets avec sens de A vers B et indication booléenne d'un chemin possible ou non de retour + vue dédoublant les chemins (avantage, pas de redondance)
2) table des trajets de A vers B et ajout des trajets de B vers A s'il y en as

***

Exemple 1 :

La table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE T_TRAJET_TRJ 
(DE        VARCHAR(32),
 A         VARCHAR(32),
 DISTANCE  INT,
 RETOUR    BIT)

Les données de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
-- double sens : 
INSERT INTO T_TRAJET_TRJ VALUES ('PARIS', 'LYON', 450, 1)
-- sens unique :
INSERT INTO T_TRAJET_TRJ VALUES ('MARSEILLE', 'LYON', 320, 0)

La vue de travail :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
-- vue de tous les chemins :
CREATE VIEW V_TRAJET_TRJ 
AS
SELECT DE, A, DISTANCE
FROM   T_TRAJET_TRJ
UNION ALL
SELECT A, DE, DISTANCE
FROM   T_TRAJET_TRJ
WHERE  RETOUR = 1;

***

Solution 2

La table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE TABLE T_TRAJET_TRJ 
(DE        VARCHAR(32),
 A         VARCHAR(32),
 DISTANCE  INT)

Les données de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
-- double sens : 
INSERT INTO T_TRAJET_TRJ VALUES ('PARIS', 'LYON', 450)
INSERT INTO T_TRAJET_TRJ VALUES ('LYON', 'PARIS', 450)
-- sens unique :
INSERT INTO T_TRAJET_TRJ VALUES ('MARSEILLE', 'LYON', 320)

La solution 1 est à préférer dans tous les cas (économie de volume de données, aucun incohérence possible...)

A +

[justinedr71]

Je préfèrerai pouvoir utiliser la deuxième solution... Car mes segments doivent pouvoir avoir accès aux liens, à la fois précédent et suivant.
Ce sera au moment de l'analyse d'une position qu'on regardera si l'on souhaite prendre les liens suivants ou précédents (accordés au début ou à la fin)...

Avec votre solution 2, la vue de travail que vous proposez n'est plus valide?

[SQLpro]

Elle ne sert a rien puisque toutes les données sont dans la même table au prix du doublement du volume de la base qui sera donc au minimum deux fois moins performante !

A +

[justinedr71]

J'ai la procédure suivante (qui prend en paramètre @linkId, @segId et @distance) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH listeSuivants (linkId, segId, long) 
	AS
	   (SELECT DISTINCT link_id, seg_id, long_seg 
		FROM   Test.dbo.[next]
		WHERE LINK_ID = @linkId
		   AND SEG_ID = @segId
		UNION  ALL
		SELECT link_id_seg, seg_id_seg, precedent.long+long_seg
		FROM   Test.dbo.[next] AS suivant
			   INNER JOIN listeSuivants AS precedent
					 ON precedent.linkId = suivant.link_id
					 AND precedent.segId = suivant.seg_id)
	SELECT linkId, segId, SUM(long) as distance
	FROM   listeSuivants
	group by linkId, segId
	having SUM(long) <= @distance

J'ai regroupé mes données dans deux tables :
Next qui contient les segments suivants
Previous qui contient les segments précédents

Les deux tables ont le même schéma et pourtant cette procédure ne fonctionne pas sur la deuxième (previous), il me dit : "L'instruction a été terminée. La récursivité maximale 100 a été épuisée avant la fin de l'instruction."
???

[SQLpro]

Pourquoi avez vous besoin de deux tables ???? Cela ne sert à rien !

A +