| Version 17 (modified by celine, 14 years ago) (diff) |
|---|
back to first page ..
data in ftp.marine.ie/acms
--WRB insert into europe.wso(wso_id) select distinct on (wso_id) wso_id from ccm21.riversegments r join (SELECT the_geom FROM european_wise2008.rbd_f1v3 As f where gid=39) as sub ON ST_Intersects(sub.the_geom,r.the_geom); UPDATE europe.wso set area='Western' where area IS NULL;
-------------------------------------------- --------------------------------------------- -- Corinne Landcover --------------------------------------------- --------------------------------------------- -------------------------------------- DROP INDEX IF EXISTS ccm21.indexriversegments_wso_id; CREATE INDEX indexriversegments_wso_id ON ccm21.riversegments USING btree (wso_id); DROP INDEX IF EXISTS ccm21.indexriversegments_wso1_id; CREATE INDEX indexriversegments_wso1_id ON ccm21.riversegments USING btree (wso1_id); CREATE INDEX indexriversegments_sp ON ccm21.riversegments USING GIST ( the_geom GIST_GEOMETRY_OPS ); CREATE INDEX indexcatchment_sp ON ccm21.catchments USING GIST ( the_geom GIST_GEOMETRY_OPS ); -------- --SURFACE CUT -------------------------------------- -------------------------------------- DROP TABLE IF EXISTS clc.clipped_ireland; CREATE TABLE clc.clipped_ireland 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='Western')) 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_ireland ADD column id serial PRIMARY KEY; alter table clc.clipped_ireland add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table clc.clipped_ireland add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); alter table clc.clipped_ireland add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); CREATE INDEX indexclc00clipped_ireland ON clc.clipped_ireland USING GIST ( the_geom GIST_GEOMETRY_OPS ); -------------------------------------- -------------------------------------- --MERGING -------------------------------------- -------------------------------------- DROP TABLE IF EXISTS clc.clipped_ireland1; CREATE TABLE clc.clipped_ireland1 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_ireland ) As f GROUP BY gid,code_00);--171 s ALTER TABLE clc.clipped_ireland1 add column id serial PRIMARY KEY; alter table clc.clipped_ireland1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table clc.clipped_ireland1 add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); alter table clc.clipped_ireland1 add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); CREATE INDEX indexclc00clipped_ireland1 ON clc.clipped_ireland1 USING GIST ( the_geom GIST_GEOMETRY_OPS ); ALTER TABLE clc.clipped_ireland1 add constraint c_ck_uk_ireland UNIQUE(gid,code_00); -- contrainte d'unicité -------------------------------------- -------------------------------------- --AREA -------------------------------------- -------------------------------------- ALTER TABLE clc.clipped_ireland1 add column area numeric; UPDATE clc.clipped_ireland1 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_ireland1 order by gid, code_00 limit 10; DROP TABLE IF EXISTS clc.surf_area_ireland; CREATE TABLE clc.surf_area_ireland 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_ireland1 ) as init FULL OUTER JOIN (SELECT gid,sum(area) AS artificial_surfaces_11_13 FROM clc.clipped_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland1 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_ireland ADD CONSTRAINT c_pk_gid_surf_area_ireland PRIMARY KEY (gid); SELECT * FROM clc.surf_area_ireland; -------------------------------------- -------------------------------------- --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_area_ireland_final; CREATE TABLE clc.surf_area_ireland_final 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_ireland p JOIN ccm21.catchments c ON c.gid=p.gid JOIN ccm21.riversegments r on r.wso1_id=c.wso1_id ); );
Station WRBD (Western River Basin District)
CREATE SCHEMA wrbd; drop table if exists wrbd.station_st; create table wrbd.station_st ( st_foliono character varying(50), st_id character varying(10), st_district character varying(30), st_catchment character varying(15), st_river character varying(40), st_sitetributary character(5), st_eastings numeric, st_northing numeric, st_location text, st_x numeric, st_y numeric ); alter table wrbd.station_st add constraint pk_st_id primary key (st_id); set client_encoding to 'latin1'; copy wrbd.station_st from 'D:/CelineJouanin/POSEProject/Ireland/DataIreland/Station_14_Eel_Db_MI_Rivers.csv' with csv delimiter as ';' header; -- projection sur le SRID 3035 SELECT AddGeometryColumn('wrbd', 'station_st','the_geom', 3035,'POINT',2); UPDATE wrbd.station_st SET the_geom=ST_Transform(PointFromText('POINT(' || st_x || ' ' || st_y || ')',29901),3035); ALTER TABLE wrbd.station_st SET WITH OIDS; --srid =29903 or srid=29901, 29900 CREATE INDEX indexstation_st ON wrbd.station_st USING GIST ( the_geom GIST_GEOMETRY_OPS );
-- operations
-- changing format of column i
d: cd D:\CelineJouanin\POSEProject\Ireland cd C:\eda\ireland C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4326 -I -W LATIN1 Outline_of_ireland.shp Outline_of_ireland > Outline_of_ireland.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4326 -W LATIN1 -I River_basin_districts.shp River_basin_districts > River_basin_districts.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I Irish_catchments.shp Irish_catchments > Irish_catchments.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I Irish_lakes.shp Irish_lakes > Irish_lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I Irish_water_bodies.shp Irish_water_bodies > Irish_water_bodies.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I WFD_LakeSegment_WE.shp WFD_LakeSegment_WE > WFD_LakeSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I WFD_LakeWaterBodies_WE.shp WFD_LakeWaterBodies_WE > WFD_LakeWaterBodies_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I WFD_RiverSegment_WE.shp WFD_RiverSegment_WE > WFD_RiverSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I WFD_RiverSubBasins_WE.shp WFD_RiverSubBasins_WE > WFD_RiverSubBasins_WE.sql REM ne marche pas REM C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 29903 -W LATIN1 -I WFD_RiverWaterBodies_WE.shp WFD_RiverWaterBodies_WE > WFD_RiverWaterBodies_WE.sql -- Cédric C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f Outline_of_ireland.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table outline_of_ireland set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f River_basin_districts.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table river_basin_districts set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f Irish_catchments.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table irish_catchments set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f Irish_lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table irish_lakes set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f Irish_water_bodies.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table irish_water_bodies set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f WFD_LakeSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table wfd_lakesegment_we set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f WFD_RiverSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table wfd_riversegment_we set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -f WFD_RiverSubBasins_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -h localhost -U postgres -c "alter table wfd_riversubbasins_we set schema wrbd" -- Céline d: cd C:\eda\ireland C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f Outline_of_ireland.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table outline_of_ireland set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f River_basin_districts.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table river_basin_districts set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f Irish_catchments.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table irish_catchments set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f Irish_lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table irish_lakes set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f Irish_water_bodies.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table irish_water_bodies set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f WFD_LakeSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table wfd_lakesegment_we set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f WFD_RiverSegment_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table wfd_riversegment_we set schema wrbd" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -f WFD_RiverSubBasins_WE.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5432 -h localhost -U postgres -c "alter table wfd_riversubbasins_we set schema wrbd"
