back to first page [..] [[BR]]
[[PageOutline]]

= Source des données de pêche =
Les données en vert sont dans la table stations, système de projection 31370
 [[Image(source:data/Docs/trac/Meuse/pechefrancebelgique.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;
}}}