back to first page ..
Pêches électriques
---Création du schéma oria sous eda2.0 puis :
-- 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; drop view if exists europe.wsoe; CREATE VIEW europe.wsoe AS select r.*, e.wso_id as wso_ide, e.area as areae from ccm21.riversegments r inner join europe.wsoe e on e.wso_id=r.wso_id
/*
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;
-- 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 );
/*
Integration of Basque Government data
*/
alter table oria.estaciones_st drop column st_date;
alter table oria.estaciones_st drop column st_21fpcomun_num;
alter table oria.estaciones_st drop column st_nha;
set client_encoding to 'latin1';
copy oria.estaciones_st from 'c:/base/stationsGV.csv' with csv delimiter as ';' header;
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;
-- 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 );
-- operations (note I had to change A03800 to ANA03800 to avoid foreign key troubles)
-- remove #NA remove NA remove #NA lines
drop table if exists oria.operation_op;
create table oria.operation_op (
op_date date,
op_river character varying(3),
op_st_id character varying(10),
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_density 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/gipuzkoa(3).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 ;
-- operationGV
alter table oria.operation_op alter column op_river type character varying(50);
alter table oria.operation_op rename column op_op_id to op_id;
alter table oria.operation_op alter column op_id type character varying(20);
set client_encoding to 'latin1';
copy oria.operation_op from 'c:/base/operationGV.csv' with csv delimiter as ';' header null as 'NA';
-- 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
Description Obstacles
Obstacles Score Spain
-- Obstacles drop table if exists oria.obstacles; create table oria.obstacles ( ob_basin character varying(3), ob_river character varying(3), ob_pk numeric, ob_id character varying(20), --'IDCODIGO ob_x numeric, ob_y numeric, ob_xfin numeric, ob_yfin numeric, ob_name text, ob_slope character varying(25), ob_height numeric, --'CFCOALTURA ob_length numeric, ob_depth numeric, ob_slope_num numeric, ob_slope_score numeric, ob_concrete boolean, ob_parpaing boolean, ob_stone boolean, ob_other boolean, ob_other_text text, ob_rug_score numeric, ob_score numeric, ob_score_expert numeric, ob_eel_cross character varying(10), ob_comment text ); set client_encoding to 'latin1'; copy oria.obstacles from 'c:/base/11OBSTACULOS.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,height,score,nbdams FROM ( SELECT ob_id, gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom, s.ob_height as height, s.ob_score_expert As score, 1 AS nbdams -- pour jointure ultérieure 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,height,score,nbdams ); 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); /* INTEGRATION DES BARRAGES DE BISKAYA */ -- export vers 'public' via export source de données ODBC alter table "TABLA CONJUNTA DE TODO" rename to obstacles_biskaia; alter table obstacles_biskaia set schema oria; alter table oria.obstacles_biskaia add constraint pk_cod_impacto primary key ("COD_Impacto"); SELECT AddGeometryColumn('oria', 'obstacles_biskaia','the_geom', 3035,'POINT',2); UPDATE oria.obstacles_biskaia SET the_geom=ST_Transform(PointFromText('POINT(' || "Coord X" || ' ' || "Coord Y" || ')',23030),3035); ALTER TABLE oria.obstacles_biskaia SET WITH OIDS;
COPY oria.obstacles_biskaia to 'd:/CelineJouanin/obstacles_biskaia.csv' delimiters';' CSV HEADER; ---pour faire les calculs sous excel drop table if exists oria.obstacles_biskaia2; CREATE TABLE oria.obstacles_biskaia2 ( "COD_Impacto" character varying(5) NOT NULL, "Incluído en Ríos Tramificados" character(1), "Uh2" character varying(50), "N" character varying(1), "Lugar/Arroyo" character varying(50), "Río" character varying(50), "Municipio" character varying(100), "Hoja 5000" character varying(50), "Coord X" integer, "Coord Y" integer, "Observaciones" text, "Tipo" character varying(10), "Altura" double precision, "Hormigón" character(1), "Mampostería" character(1), "Otros materiales" character(1), "Otros materiales txt" character varying(20), "ob_rug_score" numeric, "ob_score" numeric, "Toma" character varying(10), "Estado" character varying(10), "X embalsamiento" integer, "Y embalsamiento" integer, "Longitud embalsamiento" integer, "Profundidad" character varying(50), "ob_slope_num" numeric, "ob_slope_score" numeric, "Grado colmatación" character varying(10), "X derivación" integer, "Y derivación" integer, "Margen" character varying(10), "Longitud" integer, "Anchura" real, "Caudal" real, "Codigo Red Vigilancia" character varying(20), "¿Estudiado en Red Vigilancia?" character(1), "Id tramo" character varying(20), "Franqueabilidad" character varying(20), "ob_score_exper" numeric, "Estacion Red" character varying(20), "Distancia (m) Aguas Arriba" double precision, "Estacion" character varying(50), "Distancia (m) Aguas Abajo" integer, "Estación Red Foronómica" character varying(50), "Distancia en m Aguas Abajo" integer, "CuentaDeCODIGO1" integer, "Datos Administrativos" character(1), "Sin Datos Administrativos" character(1), "Altamente Modificado" character(1), "Interes Conservación" character(1), "Espacio Natural" character(1), "Red Natura 2000" character(1), "DENOM MASA" character varying(20), "Estado Ecológico" character varying(50), "Represa" character varying(20), "Fuerza Motriz" character(1), "Molinería" character(1), "Hidroeléctrico" character(1), "Industrial" character(1), "Abastecimiento" character(1), "Riego" character(1), "Doméstico" character(1), "Agropecuario" character(1), "Otras" character(1), "Nombre Otras" character varying(50), "Uso" character(1), "No se Usa" character(1), "Ocasionalmente" character(1), "Permanente" character(1), "Sin informacion" character(1), "¿Mantiene Agua aguas abajo del azud?" character varying(10), "Tipo escala" character varying(20), "Funcionalidad escala" character varying(15), "Mantenimiento escala" character varying(15), "¿Presenta Escala?" character(1), "¿Deriva Agua?" character(1), "Tipo barrera" character varying(15), "Grado funcional" character varying(15), "Relacionado con Patrimonio Histórico" character(1), "Reciben vertidos directos" character(1), "Interes segun libro presas" character(1), "Barrera Anti-peces en el Canal" character(1), "¿Foto?" character(1), "Impacto en la Continuidad del Río" character varying(50), "Impacto en la Calidad de las Aguas" character varying(50), the_geom geometry ); alter table oria.obstacles_biskaia2 add constraint pk_cod_imp PRIMARY KEY("COD_Impacto"); copy oria.obstacles_biskaia2 from 'd:/CelineJouanin/oria/obstacles_biskaia2.csv' with csv delimiter as ';' header; alter table oria.obstacles_biskaia2 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table oria.obstacles_biskaia2 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table oria.obstacles_biskaia2 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); -- capture des obstacles de biskaya INSERT into oria.obstacles_ccm_500 as ( SELECT distinct on ("COD_Impacto") "COD_Impacto" , gid, wso1_id, min(distance) as distance, the_geom,height,score,nbdams FROM ( SELECT "COD_Impacto", gid , wso1_id, CAST(distance(r.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom, "Altura" as height, "ob_score_exper" As score, 1 AS nbdams -- pour jointure ultérieure FROM oria.obstacles_biskaia2 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 "COD_Impacto") AS sub GROUP BY "COD_Impacto", gid, wso1_id, distance,the_geom,height,score,nbdams ); UPDATE oria.obstacles_ccm_500 SET score=abs(score);
select "Incluído en Ríos Tramificados","Franqueabilidad", "Abastecimiento", "Represa", "Anchura" from oria.obstacles_biskaia where "Abastecimiento"='1' and "Represa"='Sí' and "Franqueabilidad" is not null order by "Anchura"; select "Incluído en Ríos Tramificados","Franqueabilidad", "Abastecimiento", "Represa", "Anchura" from oria.obstacles_biskaia where "Abastecimiento"='1' and "Represa" is null and "Franqueabilidad" is not null; select "Incluído en Ríos Tramificados", "Franqueabilidad", "Abastecimiento", "Represa", "Anchura" from oria.obstacles_biskaia where "Abastecimiento"='1' and "Represa" is null and "Franqueabilidad" is not null; select "Incluído en Ríos Tramificados", "Franqueabilidad" from oria.obstacles_biskaia where "Franqueabilidad" is null order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad", "Abastecimiento" from oria.obstacles_biskaia where "Franqueabilidad" is null and "Abastecimiento"='1' order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad", "Abastecimiento" from oria.obstacles_biskaia where "Franqueabilidad" is not null and "Abastecimiento"='1' order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad", "Abastecimiento" from oria.obstacles_biskaia where "Abastecimiento"='1' order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad", "Molinería", "Hidroeléctrico", "Doméstico","Abastecimiento","Industrial", "Riego" from oria.obstacles_biskaia where "Riego" is not null order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad","Hidroeléctrico" from oria.obstacles_biskaia where "Doméstico" is not null order by "Incluído en Ríos Tramificados"; select "Incluído en Ríos Tramificados", "Franqueabilidad","Represa" from oria.obstacles_biskaia where "Represa" is not null order by "Incluído en Ríos Tramificados", "Represa";
CLC
-------------------------------------------- --------------------------------------------- -- 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;
/* UPSTREAM CATCHMENTS SPAIN */ CREATE INDEX index_clc_surf_area_final_spain ON clc.surf_area_final USING btree (gid); --the rest is done in R
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
Joining two riversegements : ticket#73
To make a map of the results (riversegments)
create table oria.result as( select sub.*,res_value as number_of_eel from (SELECT * FROM ccm21.riversegments where wso1_id in (select wso1_id from europe.wso1 where area='Basque'))as sub join ccm21.resultmodel on res_wso1_id=wso1_id where res_mod_id=5); alter table oria.result add constraint pk_wso1_id primary key (wso1_id); select Probe_Geometry_Columns(); alter table oria.result add CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2); alter table oria.result add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL); alter table oria.result add CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
Last modified 14 years ago
Last modified on Jun 26, 2011 3:44:33 PM
