Changes between Initial Version and Version 1 of CookBook BDCarthage_UGA

Timestamp:

Oct 3, 2011 11:14:22 AM (14 years ago)

Author:

celine

Comment:

--

CookBook BDCarthage_UGA

@@ +1 @@
+back to first page[..][[BR]]
+back to ["RHT"][[BR]]
+
+{{{
+drop table  if exists uga2010.id_bdcarthage;
+     CREATE TABLE uga2010.id_bdcarthage (
+     id serial PRIMARY KEY,
+     id_bdcarth integer,
+     uga varchar(25)
+     );
+
+ --Bretagne
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Bretagne') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Bretagne' where uga IS NULL -- 27250 lignes
+
+ --Adour
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Adour') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Adour' where uga IS NULL -- 27250 lignes
+
+ --Garonne
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Garonne') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Garonne' where uga IS NULL -- 27250 lignes
+
+ --Loire
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Loire') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Loire' where uga IS NULL -- 27250 lignes
+
+ --Seine-Normandie
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Seine-Normandie') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Seine-Normandie' where uga IS NULL -- 27250 lignes
+
+
+ --Corse
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Corse') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Corse' where uga IS NULL -- 27250 lignes
+
+ --Artois-Picardie
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Artois-Picardie') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Artois-Picardie' where uga IS NULL -- 27250 lignes
+
+ --Rhone-Mediterranee
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Rhone-Mediterranee') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Rhone-Mediterranee' where uga IS NULL -- 27250 lignes
+
+ --Rhin
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Rhin') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Rhin' where uga IS NULL -- 27250 lignes
+
+--Meuse
+    insert into uga2010.id_bdcarthage(id_bdcarth) 
+    select distinct on (id_bdcarth) id_bdcarth from bd_carthage2011.troncon_hydrographique r
+    join (SELECT ST_Union(f.the_geom) as singlegeom
+        FROM uga2010.uga As f where libelle='Meuse') as sub
+    ON ST_Intersects(sub.singlegeom,r.the_geom);
+
+    UPDATE uga2010.id_bdcarthage set uga='Meuse' where uga IS NULL -- 27250 lignes
+}}}