Changes between Version 3 and Version 4 of Meuse clc

Timestamp:

May 26, 2014 3:17:47 PM (11 years ago)

Author:

cedric

Comment:

--

Meuse clc

@@ -6 +6 @@
  [[Image(source:data/Docs/trac/Meuse/clcmeusebelge.jpg)]] [[BR]]
 
+{{{
+#!sql
+select * from european_wise2008.rbd_f1v3 where name_engl like 'Meuse'
+
+
+-- selection of the meuse basin
+select * from european_wise2008.rbd_f1v3 where name_engl like 'Meuse'
+-- 5 lines
+
+
+select * from europe.wso limit 10;-- id, wso_id, area
+-- I just need the two second columns, below a request spatial joining riversegments and the wise layer
+select distinct on (wso_id)  wso_id, name_engl as area from ccm21.riversegments r
+    join (SELECT the_geom, name_engl FROM european_wise2008.rbd_f1v3 As f where name_engl like 'Meuse' ) as sub
+    ON ST_Intersects(sub.the_geom,r.the_geom) limit 10;
+
+    
+
+select * from ccm21.riversegments where wso1_id in 
+(291110,
+291112,
+291115,
+291130,
+291133,
+292843,
+292848,
+298657,
+324473,
+324479)
+--324473
+--324479
+--291130 --Meuse
+select *  from ccm21.riversegments where wso_id in 
+(291110,--rhin
+291112,-- Rhone
+291115,-- Seine
+291130,-- Meuse
+291133,--Scheldt
+292843,-- Meuse du bas
+292848,-- micro pipi près de l'esuaire
+298657)-- petit pipi près de l'esuaire
+
+
+-- i'll insert this in europe.wso
+
+insert into europe.wso(wso_id,area) VALUES (291130,'Meuse');-- Meuse
+insert into europe.wso(wso_id,area) VALUES (292848,'Meuse');-- micro pipi près de l'esuaire
+insert into europe.wso(wso_id,area) VALUES  (298657,'Meuse');-- petit pipi près de l'esuaire
+insert into europe.wso(wso_id,area) VALUES  (292843,'Meuse');--- Meuse du bas
+
+
+drop table if exists clc.meusebelge;
+create table clc.meusebelge as (
+        select distinct on (gid) cl.* from clc.clc00_v2_europe cl
+        join
+        (select the_geom from ccm21.catchments c join europe.wso e on e.wso_id=c.wso_id where e.area='Meuse' 
+        except
+        select the_geom from ccm21.catchments c join europe.wso1 e on e.wso1_id=c.wso1_id where e.area='France') as ccm
+        on st_intersects(cl.the_geom,ccm.the_geom)
+        
+);
+ alter table clc.meusebelge ADD CONSTRAINT meusebelge_pkey PRIMARY KEY (gid);
+  alter table clc.meusebelge ADD  CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
+  alter table clc.meusebelge ADD  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
+  alter table clc.meusebelge ADD  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
+  
+INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
+SELECT '', 'clc', 'meusebelge', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
+FROM clc.meusebelge  LIMIT 1;
+}}}