back to first page..
back to RHT

-- 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;

OOOOOOAAAAAH ON DIRAIT DES HYPER GROSSES CIVELLES DIT THIBAULT

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.
Ci dessous la fonction utilise les expressions régulières ltree => vecteur => table

/*
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