Changes between Version 6 and Version 7 of Noeuds mer RHT

Timestamp:

Oct 19, 2011 9:14:17 PM (14 years ago)

Author:

cedric

Comment:

--

Noeuds mer RHT

@@ -37 +37 @@
 );
 
+-- je vais chercher dans la ccm les informations sur les distances mer et les cumuls de barrages
+
+select distinct on (zonegeo) zonegeo from rht.noeudmer;
+
+alter table rht.noeudmer add column cumnbbar integer;
+alter table rht.noeudmer add column dmer numeric;
+alter table rht.noeudmer add column wso1_id integer;
+
+
+update rht.noeudmer set (cumnbbar,dmer,wso1_id)= (jointure.cs_nbdams,jointure.cum_len_sea,jointure.wso1_id) from
+        -- resultat final de la jointure spatiale entre ccm et noeudfrontiere
+        (select distinct on (id_drain) id_drain, min(distance) as distance, wso1_id,cs_nbdams, cum_len_sea from (
+                -- requete intermédiaire dont on prend le min (sub)
+                select id_drain, ST_distance(noeudfrontiere.the_geom,ccmrs.the_geom) as distance,ccmrs.*
+                FROM ccm.riversegments_france ccmrs join
+                (select id_drain, the_geom from rht.noeudmer where noeudmer and zonegeo in ('Est','pyrénées')) as noeudfrontiere
+                on ST_DWithin(noeudfrontiere.the_geom,ccmrs.the_geom, 500)) as sub
+        group by id_drain, distance, wso1_id,cs_nbdams, cum_len_sea) as jointure
+where noeudmer.id_drain=jointure.id_drain; --100 lignes modifiées
 }}}