Changes between Version 3 and Version 4 of Cookbook CCM21_UGA

Timestamp:

Jul 20, 2010 5:28:23 PM (15 years ago)

Author:

celine

Comment:

--

Cookbook CCM21_UGA

@@ -2 +2 @@
 back to ["CookBook Eda"][[BR]]
 back to ["UGA"][[BR]]
+
+
+{{{
+     drop table  if exists uga2010.wso;
+     CREATE TABLE uga2010.wso (
+     id serial PRIMARY KEY,
+     wso_id integer,
+     uga varchar(25)
+     )
+
+--Loire
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='1') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Loire' where uga IS NULL --40 sea nodes
+
+--Garonne
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='2') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Garonne' where uga IS NULL --60 sea nodes
+
+--Adour
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='3') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Adour' where uga IS NULL --19 sea nodes
+
+--Artois-Picardie
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='4') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='ArtoisPicardie' where uga IS NULL -- sea nodes
+
+--Rhône-M‚diterranée
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='5') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='RhoneMediterranee' where uga IS NULL -- sea nodes
+
+--Corse
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='6') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Corse' where uga IS NULL -- sea nodes
+
+--Seine-Normandie
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='7') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Seine-Normandie' where uga IS NULL -- sea nodes
+
+--Bretagne
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='8') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Bretagne' where uga IS NULL -- sea nodes
+
+--Rhin
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='9') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Rhin' where uga IS NULL -- sea nodes
+
+--Meuse
+
+    insert into uga2010.wso(wso_id) 
+    select distinct on (wso_id)  wso_id from ccm21.riversegments r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where gid='10') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.wso set uga='Meuse' where uga IS NULL -- sea nodes
+}}}