Changes between Version 28 and Version 29 of Noeud - parcours RHT

Timestamp:

Jan 5, 2012 1:29:40 PM (13 years ago)

Author:

celine

Comment:

--

Noeud - parcours RHT

@@ -260 +260 @@
 }}}
 
+== Distance source (Parcours source)
+{{{
+#!sql
+-- 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
+/*
+AJOUT D'UN INDEX
+ltree supports several types of indexes that can speed up the indicated operators:
+    B-tree index over ltree: <, <=, =, >=, >
+    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_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
+-- 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.*');
+--  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;
+-- 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(min integer, max integer) RETURNS integer AS $$
+DECLARE
+    ligne integer;
+    nbligne integer; 
+    id_drains record;
+BEGIN
+        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);
+
+-- 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
+
+-- 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  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 ==
+En pratique nous n'aurons probablement besoin que du noeud le plus amont des parcours comme programmé ci dessus. Mais les scripts ci-dessous pourraient s'avérer utiles. [[BR]]
+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}
+SELECT regexp_split_to_array('hello world', E'\\s+');
+SELECT regexp_split_to_array('hello.world', E'\\.+');
+*/
+SELECT regexp_split_to_array(ltree2text(chemin),E'\\.+') from rht.rht_topology where chemin <@ '212340';
+
+/*
+http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Array_to_table
+SELECT (ARRAY[1,3,4,6,7])[idx.n] FROM generate_series(1,7) idx(n);
+CREATE OR REPLACE FUNCTION unpack(anyarray)
+RETURNS SETOF anyelement AS $$ 
+SELECT $1[i] 
+   FROM generate_series(array_lower($1,1), 
+                        array_upper($1,1)) g(i);
+$$ LANGUAGE sql STRICT IMMUTABLE;
+*/
+CREATE OR REPLACE FUNCTION unpack(anyarray)
+RETURNS SETOF anyelement AS $$ 
+SELECT $1[i] 
+   FROM generate_series(array_lower($1,1), 
+                        array_upper($1,1)) g(i);
+$$ LANGUAGE sql STRICT IMMUTABLE;
+-- pour convertir un vecteur en table
+select unpack(vecteurchemin) from 
+        (SELECT regexp_split_to_array(ltree2text(chemin),E'\\.+') as vecteurchemin from rht.rht_topology where chemin <@ '212340' limit 1) as sub
+-- j'avais pas vu il y a déjà une fonction magique unest...
+select cast(unnest(vecteurchemin) as integer) as chemin_id_drain from
+        (SELECT regexp_split_to_array(ltree2text(chemin),E'\\.+') as vecteurchemin from rht.rht_topology where chemin <@ '212340' limit 1) as sub
+-- Fonction qui renvoit les identifiants uniques des tronçons en amont d'un ouvrage
+
+
+CREATE OR REPLACE FUNCTION troncons(integer)
+RETURNS SETOF integer AS $$
+select cast(unnest(vecteurchemin) as integer) as chemin_id_drain from
+        (SELECT regexp_split_to_array(ltree2text(chemin),E'\\.+') as vecteurchemin from rht.rht_topology where chemin <@ text2ltree(cast ($1 as text))  limit 1) as sub;
+$$ LANGUAGE sql STRICT IMMUTABLE;       
+
+SELECT troncons_amont(212340); -- OK
+}}}
+