back to first page ..

Extraction d'une sous selection des clc pour la meuse

les données ne sont pas utilisées ensuite, on va directement tapper dans la table de la clc européenne... Mais c'est pour voir.

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;