Changes between Version 45 and Version 46 of Noeud - parcours RHT

Timestamp:

Jan 6, 2012 11:55:26 AM (13 years ago)

Author:

celine

Comment:

--

Noeud - parcours RHT

@@ -286 +286 @@
 -- script de calcul des distances sources
 set search_path to rht,public;
-select cast(ltree2text(subltree(chemin,0,1)) as integer) from rht_topology limit 10
+select cast(ltree2text(subltree(chemin,0,1)) as integer) from rhtvs2 limit 10
 /*
 AJOUT D'UN INDEX
@@ -293 +293 @@
     GiST index over ltree: <, <=, =, >=, >, @>, <@, @, ~, ? 
 */
-CREATE INDEX chemin_btee_index ON rht.rht_topology USING btree(chemin); --27688 ms
-VACUUM ANALYSE rht.rht_topology;
+CREATE INDEX chemin_btee_indexvs2 ON rht.rhtvs2 USING btree(chemin); --27688 ms
+VACUUM ANALYSE rht.rhtvs2;
 CREATE INDEX chemin_gist_index ON rht.rht_topology USING GIST (chemin); -- fait planter mon serveur....
-SELECT * from rht.rht_topology where chemin <@ '212340'; -- tronçon source de la Vilaine -- 2360
-SELECT chemin from rht.rht_topology where chemin <@ '212340' limit 1
+SELECT * from rht.rhtvs2 where chemin <@ '212340'; -- tronçon source de la Vilaine -- 35641
+SELECT chemin from rht.rhtvs2 where chemin <@ '212340' limit 1
 -- La distance source est la distance mer maximale de tous les chemins possibles en ne prenant que le dernier tronçon du chemin.
-Select max(dmer) from rht.rht_topology where id_drain in (
+Select max(dmer) from rht.rhtvs2 where id_drain in (
 Select cast(ltree2text(subpath(chemin, -1, 1))as integer) as chenin_id_drain from rht.rht_topology where chemin ~ '*.212340.*');
 --  travail sur les noeuds source et le recalcul des distances mer et source (voir fichier doc joint)
 -- en pratique les distances sont calculées a partir du milieu de chaque tronçon, elles prennent par contre toute la distance des tronçons autre que le tronçon considéré.
 
-alter table rht.rht_topology add column noeudsource boolean default FALSE;
+--si noeudsource non intégrés les intégrer  (ici déjà intégrés)
+alter table rht.rhtvs2 add column noeudsource boolean default FALSE;
 -- identification des noeuds source
-update rht.rht_topology set noeudsource=TRUE where id_drain in (
-select id_drain from rht.rht_topology except (
-select rt2.id_drain from rht.rht_topology rt1 join rht.rht_topology rt2 on rt1.nextdownid=rt2.id_drain)
-);-- 57533
-update rht.rht_topology set dmer=dmer+length/2;-- 114600
+update rht.rhtvs2 set noeudsource=TRUE where id_drain in (
+select id_drain from rht.rhtvs2 except (
+select rt2.id_drain from rht.rhtvs2 rt1 join rht.rhtvs2 rt2 on rt1.nextdownid=rt2.id_drain)
+);
+
 alter table rht.rht_topology add column dsource numeric;
-update rht.rht_topology set dsource=length/2 where noeudsource;-- 57533
 -- voir schéma pour explications