Changes between Version 9 and Version 10 of Parcours source RHT

Timestamp:

Oct 26, 2011 12:15:20 PM (14 years ago)

Author:

cedric

Comment:

--

Parcours source RHT

@@ -16 +16 @@
 VACUUM ANALYSE rht.rht_topology;
 CREATE INDEX chemin_gist_index ON rht.rht_topology USING GIST (chemin); -- fait planter mon serveur....
-SELECT chemin from rht.rht_topology where chemin <@ '212340'; -- tronçon source de la Vilaine -- 2360
+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');
+
+--  fonction pour renvoyer les distances source
+DROP FUNCTION IF EXISTS rht.distance_source();
+CREATE OR REPLACE FUNCTION rht.distance_source() RETURNS integer AS $$
+DECLARE
+    id_drains RECORD;
+    nbligne integer;
+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;
+END;
+$$ LANGUAGE plpgsql;
+
+select rht.distance_source();
 }}}
 == utilisation des donnees de parcours ==