before i run this code, we need to create a clc schema within eda2 on my postgres. right click on schema and choose new schema then use the following code in the cmd window to load the clc data from the c drive to the database {{{ c: ---to change from h drive to c drive cd data ---to locate thr data folder psql -U postgres -f "clc00_v2_europe.sql" eda2 ---to get the data from the file eda2.clc into postgres in the eda2 database --open pgadmin and run this sql qyery to check its right select count(*) from clc.clc00_v2_europe --20709 good --ctrl c will cancel a command if you know you have messed it up --if something goes into the wrong schema - for example if you havent set up the clc schema, or the.sql file doenst -- --know where to out it, you can use this command to rest its location }}} {{{ #!sql --alter table mytable set schema myschema --e.g. alter table clc00_v2_europe set schema clc }}} {{{ riverbasin districts from EU, download them at my ftp/cedric/elvira OPEN COMMAND batch cmd CREATE SCHEMA psql -U postgres -c "create schema european_wise2008" eda2 RESTORE TABLE psql -U postgres -f "european_wise2008.rbd_f1v3.sql" eda2 select count(*) from european_wise2008.rbd_f1v3--215 }}} {{{ #!sql -- Irish wrbd select name_engl, the_geom from european_wise2008.rbd_f1v3 where gid in (30,31,32,33,34,35,36,37,38,39); -- 39 is already inserted -- what is the sructure of the output table ? --"Neagh Bann" --"North Western" --"South Eastern" --"Shannon" --"South Western" --"Western" --"Neagh Bann" --"North Western" --"North Eastern" --"Eastern" 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 gid in (30,31,32,33,34,35,36,37,38) ) as sub ON ST_Intersects(sub.the_geom,r.the_geom) limit 10; --83746;"Neagh Bann" --83747;"Shannon" --83749;"South Eastern" --83772;"North Western" --83773;"North Western" --83783;"Eastern" --83787;"Eastern" --83798;"South Western" --83918;"North Western" --83932;"North Western" -- i'll insert this in europe.wso insert into europe.wso(wso_id,area) 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 gid in (30,31,32,33,34,35,36,37,38) ) as sub ON ST_Intersects(sub.the_geom,r.the_geom) ;--472 -- Which basin names now ? select max(id) from europe.wso; --8419 select distinct on (area) area from (select * from europe.wso where id >(8419-472))sub; /* "Eastern" "Neagh Bann" "North Eastern" "North Western" "Shannon" "South Eastern" "South Western" */ }}} === Dumping and reloading the files already done for western RBD === I'm not sure you'll need it. I suggest doing the work for all basins in Ireland including western, anyway here it is {{{ -- saving for Elvira, the file is named clc_western_tables.zip on my ftp pg_dump -U postgres -h 192.168.1.104 -f "clc_western_tables.sql" --table clc.clipped_western --table clc.surf_area_western --table clc.surf_area_western_final --verbose eda2 -- reloading psql -U postgres -f "clc_western_tables.sql" eda2 }}} === Checking data === {{{ select count(*) from clc.surf_area_western_final --958 yes ok }}} {{{ #!sql -------------------------------------------- --------------------------------------------- -- Corinne Landcover --------------------------------------------- --------------------------------------------- CREATE INDEX indexcatchment_sp ON ccm21.catchments USING GIST ( the_geom GIST_GEOMETRY_OPS ); -------------------------------------- /* -- Elvira : here I'm renaming the tables labelled something_ireland to something_western -- so that you can run the code below alter table clc.clipped_ireland rename to clipped_western; alter table clc.clipped_ireland1 rename to clipped_western1; alter table clc.surf_area_ireland rename to surf_area_western; alter table clc.surf_area_ireland_final rename to surf_area_western_final; */ /* --have switched these files around to relabel something_western to something_irleand alter table clc.clipped_western rename to clipped_ireland;--renamed fine alter table clc.clipped_western1 rename to clipped_ireland1;---this one doesnt exist - do i need it? alter table clc.surf_area_western rename to surf_area_ireland;---renamed fine alter table clc.surf_area_western_final rename to surf_area_ireland_final;---this one doesnt exist - although it looks like it is there - but the number of rows is 0i did do the count above and it worked though */ -------- --SURFACE CUT -------------------------------------- -------------------------------------- -- Below i'm using a BEGIN COMMIT statement so that if the program drops at some point it will not loose everything -- Eastern BEGIN; DROP TABLE IF EXISTS clc.clipped; CREATE TABLE clc.clipped AS SELECT intersected.clcgid, intersected.gid, code_00,the_geom FROM (SELECT clc.gid as clcgid, sub1.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, sub1.the_geom)) the_geom FROM clc.clc00_v2_europe clc INNER JOIN ( SELECT gid, c.the_geom FROM ccm21.catchments c where wso1_id IN ( SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Eastern') ) )AS sub1 ON ST_Intersects (sub1.the_geom,clc.the_geom) ) AS intersected; COMMIT; -- launch first to there }}} {{{ #!sql BEGIN; --ALTER TABLE clc.clipped ADD column id serial PRIMARY KEY; alter table clc.clipped add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table clc.clipped add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL); alter table clc.clipped add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); CREATE INDEX indexclc00clipped ON clc.clipped USING GIST ( the_geom GIST_GEOMETRY_OPS ); COMMIT; CREATE INDEX indexclipped ON clc.clipped USING btree (gid); ---all fine to here /* "Eastern"---done 5059 rows ----Query returned successfully with no result in 33812360 ms. "Western"---- done 14709 rows retrieved.Query returned successfully with no result in 23451828 ms. "Neagh Bann"----done 19007 rows. Query returned successfully with no result in 47476391 ms. "North Eastern"--- one 20542 rows. Query returned successfully with no result in 8935422 ms. "North Western"----done 30667 rows. Query returned successfully with no result in 48430063 ms. "Shannon"----done 47274 rows. Query returned successfully with no result in 102877984 ms. "South Eastern"----57130 rows. Query returned successfully with no result in 51168469 ms. "South Western" ---66480 rows all done. Query returned successfully with no result in 37618156 ms. */ -- Neagh Bann BEGIN; INSERT INTO clc.clipped SELECT clc.gid as clcgid, sub1.gid,code_00, ST_Multi(ST_Intersection(clc.the_geom, sub1.the_geom)) the_geom FROM clc.clc00_v2_europe clc INNER JOIN ( SELECT gid, c.the_geom FROM ccm21.catchments c where wso1_id IN ( SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Neagh Bann') ) )AS sub1 ON ST_Intersects (sub1.the_geom,clc.the_geom); COMMIT; -- add the others }}} {{{ #!html

This all seemes to work OK,hve clipped the clc for all the rbds (8 in total)

}}} {{{ #!sql -------------------------------------- -------------------------------------- --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 ); }}}