---Création du schéma oria sous eda2.0 puis :

{{{
#!sql
-- Chargement des noeuds mer qui sont en espagne mais n'ont pas été calculés pour la France
select wso_id from europe.wso where area='Spain'
except (select  wso_id  from europe.wso where area='France') --2339
-- chargement des noeuds de la Façade Atlantique
select wso_id from ccm21.seaoutlets where area_cd='A1'; --6361
-- intersection
(select wso_id from europe.wso where area='Spain'
except (select  wso_id  from europe.wso where area='France')) --2339
intersect (select wso_id from ccm21.seaoutlets where area_cd='A1'); --348
-- Une table pour les vues dans Qgis
create table ccm21.riversegments_spain as (
        select * from ccm21.riversegments where wso_id in ((select wso_id from europe.wso where area='Spain'
except (select  wso_id  from europe.wso where area='France')) --2339
intersect (select wso_id from ccm21.seaoutlets where area_cd='A1'))); --348);

-- insertion de nouveaux bassins
insert into europe.wso(wso_id) 
(select wso_id from europe.wso where area='Spain'
except (select  wso_id  from europe.wso where area='France')) 
intersect (select wso_id from ccm21.seaoutlets where area_cd='A1'); 
UPDATE europe.wso set area='Spain_Atl' where area IS NULL;

/* 
creation des tables des stations et des barrages 
*/
-- Schema: "oria"

-- DROP SCHEMA oria;

--CREATE SCHEMA oria  AUTHORIZATION postgres;

drop table if exists oria.estaciones_st;
create table oria.estaciones_st (  
        st_id character varying(10),
        st_ecoregion character varying(10),
        st_river character varying(40),
        st_name text,
        st_x numeric,
        st_y numeric,
        st_z numeric,
        st_pk_des numeric,
        st_date date,
        st_21fpcomun_num integer,
        st_nha numeric);
alter table oria.estaciones_st add constraint  pk_st_id primary key (st_id);
set client_encoding to 'latin1';
copy oria.estaciones_st  from 'c:/base/estaciones.csv' with csv delimiter as ';' header; 

drop table if exists oria.operation_op;
create table oria.operation_op (
        op_date date,
        op_st_id character varying(10),
        op_op_id character varying(10) primary key,
        st_x numeric,
        st_y numeric,
        st_z numeric,
        st_pk_des numeric,
        op_surf numeric,
        op_methodest character varying(30),
        op_fishingobj character varying(30),
        op_nbpas integer,
        op_effort numeric,
        op_densite numeric,
        op_nbtot numeric,
        op_nbp1 numeric,
        op_nbp2 numeric,
        op_nbp3 numeric,
        op_nb150 numeric,
        op_nb150_300 numeric,
        op_nb300_450 numeric,
        op_nb_450_600 numeric,
        op_nb_600 numeric,
        op_glass_eel_transport boolean);
alter table oria.operation_op add constraint  fk_op_st_id foreign key (op_st_id) references oria.estaciones_st(st_id);
copy oria.operation_op  from 'c:/base/operation.csv' with csv delimiter as ';' header; 
alter table oria.operation_op drop column  st_x;
alter table oria.operation_op drop column         st_y ;
alter table oria.operation_op drop column         st_z ;
alter table oria.operation_op drop column         st_pk_des ;
-- projection sur le SRID 3035
SELECT AddGeometryColumn('oria', 'estaciones_st','the_geom', 3035,'POINT',2); 
UPDATE oria.estaciones_st   SET the_geom=ST_Transform(PointFromText('POINT(' || st_x || ' ' || st_y || ')',23030),3035);
ALTER TABLE oria.estaciones_st SET WITH OIDS;

CREATE INDEX indexestaciones_st ON oria.estaciones_st
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

-- capture des stations de pêche
DROP TABLE IF EXISTS oria.oria_ccm_500;
CREATE TABLE oria.oria_ccm_500 as (
        SELECT distinct on (st_id ) st_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
               SELECT st_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM oria.estaciones_st As s
               INNER JOIN  ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,500)
               WHERE s.the_geom IS NOT NULL
               ORDER BY st_id) AS sub 
        GROUP BY st_id, gid, wso1_id,  distance,the_geom
);
alter table oria.oria_ccm_500 add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'oria', 'oria_ccm_500', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM oria.oria_ccm_500 LIMIT 1;

-- creation d'index, clé primaire, et constraintes qui vont bien
alter table oria.oria_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table oria.oria_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table oria.oria_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table oria.oria_ccm_500 ADD CONSTRAINT pk_id PRIMARY KEY(id);
CREATE INDEX indexoria_ccm_500 ON oria.oria_ccm_500
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

-- joining operations and stations
drop table if exists oria.operation_station;
create table oria.operation_station as (
	select * from oria.estaciones_st join oria.operation_op on op_st_id=st_id
	where st_id in (select st_id from oria.oria_ccm_500));
alter table oria.operation_station add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table oria.operation_station add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table oria.operation_station add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);

-- Obstacles

create table oria.obstacles  (
ob_river character varying(3),
ob_id character varying(20) primary key, --'IDCODIGO
ob_x numeric,
ob_y numeric,
ob_height numeric,  --'CFCOALTURA
ob_type text, -- PUTIPO identifies the type of use
ob_use text --UAUSUSO true (verdadero) falso, indicates, wether this turbine,dam or whatever is being used now
);
set client_encoding to 'latin1';

copy oria.obstacles  from 'c:/base/obstacles.csv' with csv delimiter as ';' header;
}}}