Changes between Version 11 and Version 12 of Parcours source RHT

Timestamp:

Oct 28, 2011 12:04:01 PM (14 years ago)

Author:

cedric

Comment:

--

Parcours source RHT

@@ -74 +74 @@
 -- il faudra enlever la dmer de 212340
 
+-- POUR LES LANCEMENTS FINAUX IL A FALLU LANCER UN INDEX ET RAJOUTER UNE CLE PRIMAIRE
+Alter table rht.rht_topology add constraint c_pk_id_drain primary key (id_drain);
+CREATE INDEX id_drain_index  ON rht.rht_topology
+  USING btree
+  (id_drain);
+
+select rht.distance_source(100000,200000);
+select rht.distance_source(200001,300000);
+select rht.distance_source(300001,400000);
+select rht.distance_source(400001,600000);
+
+-- pour essai pour voir ce qui se passe dans la requête
+
+SELECT 212340,  MAX(dmer+length/2) as dmer FROM rht.rht_topology WHERE id_drain IN (
+                        SELECT CAST(ltree2text(subpath(chemin, -1, 1))as integer) FROM rht.rht_topology where chemin ~  '*.212340.*' )
+
+
+select * from rht.rht_topology t join rht.distance_source s on s.id_drain=t.id_drain where distance is null; --323
+
+-- il faudra enlever la dmer de 212340
+
 -- requête finale pour calculer les distances source (à lancer)
-alter table rht.distance_source d add column distance source numeric;
-update rht.distance_source set distance source = sub.distance_source from (
-select id_drain, distance-dmer as distance_source from rht.distance_source s joint rht.rht_topology t on s.id_drain=t.id_drain) as source
-where sub.id_drain=d.id_drain;
+alter table rht.distance_source  add column distance_source numeric;
+select count(*) from rht.distance_source; --57067 que les noeuds non source
+
+-- mise à jour des distances source dans la table distance source (il faut encore enlever dmer)
+update rht.distance_source d set distance_source = sub.distance_source from (
+select t.id_drain, distance-dmer as distance_source from rht.distance_source s join rht.rht_topology t on s.id_drain=t.id_drain) as sub
+where sub.id_drain=d.id_drain; --57067
+
+-- copie des valeurs de distance_source dans dsource de la table rht.rht_topology
+update rht.rht_topology t set dsource=distance_source
+from rht.distance_source d where d.id_drain=t.id_drain; --57067
+
+-- mise à jour des distances_source (pour l'instant valeurs = null) pour les noeuds source (pour les derniers c'est la moitié de la longueur du segment)
+update rht.rht_topology t set dsource=length/2 where noeudsource;
+-- nombre de 
+select count(*)as count, noeudsource from rht.rht_topology where dsource is null group by noeudsource;
+
+
 }}}
 == utilisation des donnees de parcours ==