---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 ==
{{{
-- Obstacles
drop table if exists oria.obstacles;
create table oria.obstacles  (
ob_river character varying(3),
ob_id character varying(20), --'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;
---pour supprimer les doublons dans ob_id
create table oria.obstacles1 as (
	select distinct on (ob_id) * from oria.obstacles
); 
drop table oria.obstacles;
alter table oria.obstacles1 rename to obstacles;
alter table oria.obstacles add constraint prk_ob_id primary key (ob_id);
ALTER TABLE oria.obstacles SET WITH OIDS;

SELECT AddGeometryColumn('oria', 'obstacles','the_geom', 3035,'POINT',2); 
UPDATE oria.obstacles SET the_geom=ST_Transform(PointFromText('POINT(' || ob_x || ' ' || ob_y || ')',23030),3035);

-- capture des obstacles
DROP TABLE IF EXISTS oria.obstacles_ccm_500;
CREATE TABLE oria.obstacles_ccm_500 as (
        SELECT distinct on (ob_id ) ob_id , gid, wso1_id, min(distance) as distance, the_geom FROM (
               SELECT ob_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM oria.obstacles 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 ob_id) AS sub 
        GROUP BY ob_id, gid, wso1_id,  distance,the_geom
);
alter table oria.obstacles_ccm_500 add column id serial;
-- creation d'index, clé primaire, et constraintes qui vont bien
alter table oria.obstacles_ccm_500 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table oria.obstacles_ccm_500 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table oria.obstacles_ccm_500 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);

}}}

= CLC =
{{{
#!sql
--------------------------------------------
---------------------------------------------
-- Corinne Landcover
---------------------------------------------
---------------------------------------------
--------------------------------------
--------------------------------------
--SURFACE CUT
--------------------------------------
--------------------------------------
DROP TABLE IF EXISTS clc.clipped_spain;
CREATE TABLE clc.clipped_spain AS
SELECT intersected.clcgid, intersected.gid, code_00,the_geom
FROM (SELECT clc.gid as clcgid, c.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, c.the_geom)) the_geom
        FROM  clc.clc00_v2_europe clc INNER JOIN 
        (select * from ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Spain_Atl')) as c
        ON  ST_Intersects (c.the_geom,clc.the_geom)
        
       -- AND substring(code_00 from 1 for 1)='1'
       )  AS intersected; --2h30 min
ALTER TABLE clc.clipped_spain ADD column id serial PRIMARY KEY;
alter table clc.clipped_spain add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table clc.clipped_spain add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table clc.clipped_spain add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexclc00clipped_spain ON clc.clipped_spain
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
--------------------------------------
--------------------------------------
--MERGING
--------------------------------------
--------------------------------------
DROP TABLE IF EXISTS clc.clipped_spain1;
CREATE TABLE clc.clipped_spain1 AS (
SELECT gid,code_00,
           ST_Multi(ST_Collect(f.the_geom)) as the_geom
         FROM (SELECT gid, code_00,(ST_Dump(the_geom)).geom As the_geom
                                FROM
                                 clc.clipped_spain
                                ) As f 
GROUP BY gid,code_00);--171 s
ALTER TABLE clc.clipped_spain1 add column id serial PRIMARY KEY;
alter table clc.clipped_spain1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table clc.clipped_spain1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table clc.clipped_spain1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexclc00clipped_spain1 ON clc.clipped_spain1
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
ALTER TABLE clc.clipped_spain1 add constraint c_ck_uk_spain  UNIQUE(gid,code_00); -- contrainte d'unicité
--------------------------------------
--------------------------------------
--AREA
--------------------------------------
--------------------------------------
ALTER TABLE clc.clipped_spain1 add column area numeric;
UPDATE clc.clipped_spain1 set area=ST_Area(the_geom); 
--------------------------------------
--------------------------------------
--AREA PER COLUMN FOR CLC TYPE (agregation)
--------------------------------------
--------------------------------------
SELECT gid,code_00, id,round(area) as area FROM clc.clipped_spain1 order by gid, code_00 limit 10;
DROP TABLE IF EXISTS clc.surf_area;
CREATE TABLE clc.surf_area AS (
SELECT DISTINCT ON (init.gid) init.gid,
        artificial_surfaces_11_13,
        artificial_vegetated_14,
         arable_land_21,
         permanent_crops_22,
         pastures_23,
         heterogeneous_agricultural_24,
         forest_31,
         natural_32_33,
         wetlands_4,
         inland_waterbodies_51 ,
         marine_water_52
        -- SELECT * 
         FROM (
        SELECT  gid from clc.clipped_spain1    ) as init        
        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='11' 
                        OR  substring(code_00 from 1 for 2)='12'
                        OR substring(code_00 from 1 for 2)='13' 
                        GROUP BY gid) AS artificial_surfaces
                       on (init.gid) =(artificial_surfaces.gid)         
        FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_vegetated_14 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='14'
                        GROUP BY gid) AS artificial_vegetated
                        on artificial_vegetated.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS arable_land_21 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='21'
                        GROUP BY gid) AS arable_land
                        on arable_land.gid =init.gid
        FULL OUTER JOIN (SELECT gid, sum(area) AS permanent_crops_22 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='22'
                        GROUP BY gid) AS permanent_crops
                        on permanent_crops.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS pastures_23 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='23'
                        GROUP BY gid) AS pastures
                        on pastures.gid =init.gid
        FULL OUTER JOIN (SELECT gid, sum(area) AS heterogeneous_agricultural_24 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='24'
                        GROUP BY gid) AS heterogeneous_agricultural
                        on heterogeneous_agricultural.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS forest_31 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='31'
                        GROUP BY gid) AS forest
                        ON forest.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS natural_32_33 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='32'
                        OR  substring(code_00 from 1 for 2)='33'
                        GROUP BY gid) AS nature
                        ON nature.gid =init.gid
        FULL OUTER JOIN (SELECT gid, sum(area) AS wetlands_4  FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 1)='4'
                        GROUP BY gid) AS wetlands
                        on wetlands.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS inland_waterbodies_51 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='51'
                        GROUP BY gid) AS waterbodies
                        on waterbodies.gid =init.gid
        FULL OUTER JOIN (SELECT gid,sum(area) AS marine_water_52 FROM clc.clipped_spain1 WHERE 
                        substring(code_00 from 1 for 2)='52'
                        GROUP BY gid) AS marine_water
                        on marine_water.gid =init.gid); --375 ms
ALTER TABLE clc.surf_area ADD CONSTRAINT c_pk_gid_surf_area_spain PRIMARY KEY (gid);
SELECT * FROM clc.surf_area;
--------------------------------------
--------------------------------------
--REMOVING ZEROS AND JOINING RIVERSEGMENTS AND CATCHMENTS TABLES
--------------------------------------
--------------------------------------
-- this table drops the previous one but final calculations are stored in surf_area_final
DROP TABLE IF EXISTS clc.surf_area1;
CREATE TABLE clc.surf_area1 AS( 
SELECT 
        r.gid,
        C.area/1e6 as catchment_area,
        CASE WHEN p.artificial_surfaces_11_13 IS NOT NULL THEN p.artificial_surfaces_11_13/1e6
        ELSE 0
        END AS artificial_surfaces_11_13,
        CASE WHEN p.artificial_vegetated_14 IS NOT NULL THEN p.artificial_vegetated_14/1e6 
        ELSE 0
        END AS artificial_vegetated_14,
        CASE WHEN p.arable_land_21 IS NOT NULL THEN p.arable_land_21/1e6 
        ELSE 0
        END AS arable_land_21,
        CASE WHEN p.permanent_crops_22 IS NOT NULL THEN p.permanent_crops_22/1e6 
        ELSE 0
        END AS permanent_crops_22,
        CASE WHEN p.pastures_23 IS NOT NULL THEN p.pastures_23/1e6 
        ELSE 0
        END AS pastures_23,
        CASE WHEN p.heterogeneous_agricultural_24 IS NOT NULL THEN p.heterogeneous_agricultural_24/1e6
        ELSE 0
        END AS heterogeneous_agricultural_24,
        CASE WHEN p.forest_31 IS NOT NULL THEN p.forest_31/1e6 
        ELSE 0
        END AS forest_31,
        CASE WHEN p.natural_32_33 IS NOT NULL THEN p.natural_32_33/1e6 
        ELSE 0
        END AS natural_32_33,
        CASE WHEN p.wetlands_4 IS NOT NULL THEN p.wetlands_4/1e6 
        ELSE 0
        END AS wetlands_4,
        CASE WHEN p.inland_waterbodies_51 IS NOT NULL THEN p.inland_waterbodies_51 /1e6 
        ELSE 0
        END AS inland_waterbodies_51,
        CASE WHEN  p.marine_water_52 IS NOT NULL THEN p.marine_water_52/1e6 
        ELSE 0
        END AS marine_water_52,
        c.wso1_id,
        c.the_geom      
FROM clc.surf_area p
JOIN ccm21.catchments c ON c.gid=p.gid
JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id
);
-- sauvegarde des données 
insert into clc.surf_area_final select * from clc.surf_area1;
}}}
  [[Image(source:data/Docs/trac/clc/integration_clc_spain.jpeg,600px)]] [[BR]]


= saving and restore scripts =
{{{
CEDRIC
cd C:\eda\backup
C:\"Program Files"\PostgreSQL\8.4\bin\psql -U postgres -d eda2.0 -p 5433  -f europe.wso.sql

CELINE
}}}