Changes between Version 10 and Version 11 of Parcours source RHT

Timestamp:

Oct 26, 2011 10:55:02 PM (14 years ago)

Author:

cedric

Comment:

--

Parcours source RHT

@@ -20 +20 @@
 -- 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 cast(ltree2text(subpath(chemin, -1, 1))as integer) as chenin_id_drain from rht.rht_topology where chemin <@ '212340');
+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é.
 
---  fonction pour renvoyer les distances source
+alter table rht.rht_topology 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
+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
+
+
 DROP FUNCTION IF EXISTS rht.distance_source();
-CREATE OR REPLACE FUNCTION rht.distance_source() RETURNS integer AS $$
+
+CREATE OR REPLACE FUNCTION rht.distance_source(min integer, max integer) RETURNS integer AS $$
 DECLARE
-    id_drains RECORD;
-    nbligne integer;
+    ligne integer;
+    nbligne integer; 
+    id_drains record;
 BEGIN
-          nbligne:=1;
-          DROP TABLE IF EXISTS rht.distance_mer;
-          CREATE TABLE rht.distance_mer (id_drain integer,distance_mer  numeric);
-          FOR id_drains IN SELECT id_drain FROM rht.rht_topology ORDER BY id_drain LOOP
-                  -- pour vérification décommenter ensuite
-                  RAISE NOTICE 'distance_source for %', quote_ident(CAST(id_drains.id_drain as text));
-                  EXECUTE 'INSERT INTO rht.distance_mer SELECT MAX(dmer) FROM rht.rht_topology WHERE id_drain IN (
-                        SELECT CAST(ltree2text(subpath(chemin, -1, 1))as integer) FROM rht.rht_topology where chemin <@  text2ltree(cast ('||
-                        id_drains.id_drain
-                        || 'as text)) )';  
-                nbligne=nbligne+1;      
-        END LOOP;
-    RETURN nbligne;
+        ligne:=1;
+        DROP TABLE IF EXISTS sousselection;
+        DROP sequence IF EXISTS  seq;
+        CREATE sequence seq;
+        EXECUTE 'create temporary table sousselection as SELECT id_drain, nextval(''seq'') as num FROM (SELECT * FROM rht.rht_topology WHERE noeudsource=FALSE ORDER BY id_drain) as sub WHERE id_drain >='|| min || ' AND id_drain<='||max ;
+        SELECT COUNT(*) FROM sousselection INTO nbligne;
+        WHILE ligne <=nbligne LOOP
+                  -- pour vérification décommenter ensuite
+                  SELECT * from sousselection where num=ligne INTO id_drains;
+                  RAISE NOTICE 'distance_source for %', quote_ident(CAST(id_drains.id_drain as text));
+                  EXECUTE 'INSERT INTO rht.distance_source SELECT '||id_drains.id_drain||', 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 ~  ''*.'||
+                        id_drains.id_drain
+                        || '.*'' )';  
+                ligne=ligne+1;      
+        END LOOP;
+    RETURN ligne;
 END;
 $$ LANGUAGE plpgsql;
+-- lancement de la requête
+DROP TABLE IF EXISTS rht.distance_source;
+CREATE TABLE rht.distance_source (id_drain integer,distance  numeric);
+select rht.distance_source(1,50000);
 
-select rht.distance_source();
+-- 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.*' )
+-- 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;
 }}}
 == utilisation des donnees de parcours ==
@@ -51 +84 @@
 Ci dessous la fonction utilise les expressions régulières ltree => vecteur => table
 {{{
-
+#!sql
 /*
 regexp_split_to_array(string text, pattern text [, flags text ])        regexp_split_to_array('hello world', E'\\s+')   {hello,world}