Discussion :

[DOUM555]

Bonjour,

Je dois faire une requête afin de trouver les chevauchements d'adresses dans des bouts de rues.

Voici la requête de base sur toute la table

Code :

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

select * from voi_troncon_ln_v where n_voie is not null

(Voir fichier attaché requeteBase.jpg)


En considérant les adresses par rue (distinct t_voie, l_voie, n_voie,d_voie ou group by t_voie, l_voie, n_voie,d_voie ), on doit déterminer si dans l'ensemble des enregistrements, il y a des adresses (champs D_GCH, F_GCH, D_DRT, F_DRT) qui sont incluses dans d'autres enregistrements.

D_GCH=> Début gauche
F_GCH=> Fin gauche
D_DRT=> Début droit
F_DRT=> Fin droit

on n'a pas à distinguer la gauche de la droite mais on doit distinguer les pairs des impairs (mod(b.d_drt,2) = mod(a.d_drt,2) ou mod(b.d_gch,2) = mod(a.d_gch,2)).

Par exemple, la requête

Code :

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

1
2
3
4
 
select * 
from voi_troncon_ln_v 
where id_trc in (520774,503975,508874, 503261, 503264, 503971, 503216)

montre les enregistrements dans l'image suivante, sur une rue en particulier.


Ce qu'il faut considérer dans la requête:
-Le ID_TRC=503216 n'entre pas dans la comparaison car il n'y a pas d'adresse associé à ce bout de rue (les champs D_GCH, F_GCH, D_DRT, F_DRT sont tous à 0). On peut donc mettre dans la requête and not(d_gch=0 and d_drt=0). Si les deux sont à 0, impossible qi'il y ait chevauchement.
-Le ID_TRC=503971 côté droit (411 à 411) n'entre pas en conflit (pas de chevauchement) entre le tronçon ID_TRC=508874 (368 à 508) car le premier est impair alors que le second est pair, de même que les autres de l'exemple. Mais le côté gauche (400 à 400) chevauche le 508874 (368 à 508).
-Finalement, on voit que le tronçon ID_TRC=508874 entre en conflit avec les tronçons ID_TRC=520774, 503975, 503261 et 503264. L'adresse 380 entre sur les tronçons 520774, 503975 et 508874. C'est ce qu'il faut trouver pour l'ensemble de la table. De même, l'adresse 420 entre sur les tronçons 508874 ET 503261. Et finalement, l'adresse 460 entre sur les tronçons 503264 ET 508874.

Comment peut-on constituer la requête qui va faire en sorte qu'on va trouver les chevauchements pour tous les bouts de rues de cette table?

Jusqu'à maintenant, j'ai trouvé ça comme requête mais ça ne semble pas fonctionner:

Code :

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
 
select a.*
from voi_troncon_ln_v a
where a.objectid > (select min(b.objectid)
from voi_troncon_ln_v b
where b.t_voie = a.t_voie
and b.l_voie = a.l_voie
and b.n_voie = a.n_voie
and b.d_voie = a.d_voie
and (( 
( b.d_drt>0
and mod(b.d_drt,2) = mod(a.d_drt,2) 
and (b.d_drt between a.d_drt 
and nvl(a.f_drt,a.d_drt) 
or b.f_drt between a.d_drt 
and nvl(a.f_drt,a.d_drt)) 
))
OR
( 
( b.d_gch>0
and mod(b.d_gch,2) = mod(a.d_gch,2) 
and (b.d_gch between a.d_gch 
and nvl(a.f_gch,a.d_gch) 
or b.f_gch between a.d_gch 
and nvl(a.f_gch,a.d_gch)) 
))
)
)
and not(a.d_gch=0 and a.d_drt=0)

[SQLpro]

MERCI de respecter la charte de postage en :

1) Donnez les ordres SQL de création de vos tables (DDL SQL) (CREATE TABLE) ainsi que les INSERT d'un jeu de données basique afin que tout un chacun puisse reproduire ce que vous voulez faire sur son SGBDR afin de mieux vous aider.
Exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 CREATE TABLE MaTable
(Col1 VARCHAR(128),
 Col2 INTEGER,
 Col3 DATE,
 Col4 CHAR(32),
 CONSTRAINT PK_MaTable PRIMARY KEY (COL1, COL2))
 
INSERT INTO MaTable (COL1, COL2, COL3) 
VALUES ('Un mot', 32, CURRENT_TIMESTAMP)
INSERT INTO MaTable (COL1, COL2, COL3) 
VALUES ('Deux maux', 0, NULL)

Tous les SGBDR sont dotés d'un outil permettant d'obtenir au moins le DDL (CREATE...) de vos tables. Exemple avec MS SQL Server 2000 : Entreprise manager, clic droit sur la base, menue toutes les tâches/Générer un script SQL...

2) Présentez le résultat que vous voulez voir apparaître sous la forme d'une table
Exemple :

Code :

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

1
2
3
4
5
6
-- Le résultat attendu :
NOM     VILLE       NOMBRE 
------  ----------  ------ 
MARTIN  PARIS            2 
DUPOND  STRASBOURG       3
...

A +

[Antoun]

Puisqu'on compare, non pas des tronçons, mais des côtés pairs ou impairs de tronçons, il nous faut constituer une liste de ces côtés de troncons qui précise quatre infos : une id de côté de voie (qui semble être ton ID_REG/ID_RED), le type de côté (REG ou RED), le n° de début et le n° de fin.

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select ID_TRC, ID_REG as id_cotevoie, 'G' as typecote, D_GCH as deb, F_GCH as fin
from voi_troncon_ln_v
where n_voie is not null
  and D_GCH != 0 
union all
select ID_TRC, ID_RED, 'D', D_DRT, F_DRT
from voi_troncon_ln_v
where n_voie is not null
  and D_DRT != 0

Pour exploiter cette liste, il y a plusieurs solutions (table dérivée, vue, table temporaire, CTE). Sur les copies écrans, je crois reconnaître la version pour Oracle de Toad, je vais donc supposer qu'il s'agit d'un Oracle récent, et utiliser une CTE :

Code :

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
with demitroncons as
(
select ID_TRC, ID_REG as id_cotevoie, 'G' as typecote, D_GCH as deb, F_GCH as fin
from voi_troncon_ln_v
where n_voie is not null
  and D_GCH != 0 
union all
select ID_TRC, ID_RED, 'D', D_DRT, F_DRT
from voi_troncon_ln_v
where n_voie is not null
  and D_DRT != 0
)
select T1.ID_TRC, 'recouvre', T2.ID_TRC
from demitroncons T1
  inner join demitroncons T2 
    on T1.id_cotevoie = T2.id_cotevoie and T1.typecote = T2.typecote
      and T1.deb <= T2.fin and T1.fin >= T2.deb 
where T1.ID_TRC < T2.ID_TRC

A noter que le dernier WHERE permet à la fois d'éliminer les doublons (donc "b recouvre a" pour chaque "a recouvre b") et les auto-recouvrements ("a recouvre a").

[DOUM555]

Wow, la requête... pour un novice comme moi

Un très gros merci Antoun ta précisieuse aide. J'ai ajouté une autre condition afin de vérifier les pairs/impairs (... and mod(T1.deb,2)=mod(T2.deb,2)).

Dominic Lavoie

Code :

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
  with demitroncons as
(
select ID_TRC, ID_REG as id_cotevoie, 'G' as typecote, D_GCH as deb, F_GCH as fin
from voi_troncon_ln_v
where n_voie is not null
  and D_GCH != 0 
union all
select ID_TRC, ID_RED, 'D', D_DRT, F_DRT
from voi_troncon_ln_v
where n_voie is not null
  and D_DRT != 0
)
select T1.ID_TRC, 'recouvre', T2.ID_TRC
from demitroncons T1
  inner join demitroncons T2 
    on T1.id_cotevoie = T2.id_cotevoie and T1.typecote = T2.typecote
      and T1.deb <= T2.fin and T1.fin >= T2.deb 
	  and mod(T1.deb,2)=mod(T2.deb,2)
where T1.ID_TRC < T2.ID_TRC

[Antoun]

Wow, la requête... pour un novice comme moi

Un très gros merci Antoun ta précisieuse aide. J'ai ajouté une autre condition afin de vérifier les pairs/impairs (... and mod(T1.deb,2)=mod(T2.deb,2)).

Normalement tu n'as pas besoin, parce que si c'est le même côté gauche ou droit, c'est forcément le même côté pair ou impair... mais je t'invite à tester si ça fait une différence ou pas dans le nombre de lignes.

[DOUM555]

ouen, non ici, il peut y avoir autant des pairs à gauche ou à droite et vice versa. C'est pourquoi il faut que je vérifie la parité

As-tu déjà eu à dealer avec des adresses sur des tronçons, et donc que t'es familié avec ça ou bien t'es juste très à l'aise avec le sql?

En tout cas, un gros merci

[Antoun]

ouen, non ici, il peut y avoir autant des pairs à gauche ou à droite et vice versa. C'est pourquoi il faut que je vérifie la parité

Sur une la même voie, le côté gauche est toujours pair ou toujours impair. Donc si deux demi-tronçons de la même voie ont la même latéralité, ils ont forcément la même parité (même si on ne sait pas laquelle).

As-tu déjà eu à dealer avec des adresses sur des tronçons, et donc que t'es familié avec ça ou bien t'es juste très à l'aise avec le sql?

En fait, j'ai pas mal travaillé sur des recouvrements de périodes. Donc quand j'ai compris qu'il suffisait de ramener ton problème à une simple question de recouvrement (avec l'UNION), j'avais trouvé la solution.

Sinon, ça va, je ne suis pas trop manche avec le SQL

En tout cas ton problème était très intéressant !