CLC upstream basin

back to cookbook eda CookBook Eda

Trial on the Vilaine

select sum(catchment_area) as up_catchment_area,
sum(artificial_surfaces_11_13) as up_artificial_surfaces_11_13,
sum(artificial_vegetated_14) as up_artificial_vegetated_14,
sum(arable_land_21) as up_arable_land_21,
sum(permanent_crops_22) as up_permanent_crops_22,
sum(pastures_23) as up_pastures_23,
sum(heterogeneous_agricultural_24) as up_heterogeneous_agricultural_24,
sum(forest_31) as up_forest_31,
sum(natural_32_33) as up_natural_32_33,
sum(wetlands_4) as up_wetland_4,
sum(inland_waterbodies_51) as up_inland_waterbodies_51,
sum(marine_water_52) as up_marine_water_52
from clc.surf_area_final
where  "gid" IN (select ccm21.upstream_segments(272506))

to gain 80 % speed

CREATE INDEX index_clc_surf_area_final
  ON clc.surf_area_final
  USING btree
  (gid);

ALTER TABLE ccm21.riversegments add column up_catchment_area numeric;
ALTER TABLE ccm21.riversegments add column up_art_11_13 numeric;
ALTER TABLE ccm21.riversegments add column up_art_14 numeric;
ALTER TABLE ccm21.riversegments add column up_arable_21 numeric;
ALTER TABLE ccm21.riversegments add column up_permcrop_22 numeric;
ALTER TABLE ccm21.riversegments add column up_pasture_23 numeric;
ALTER TABLE ccm21.riversegments add column up_hetagr_24 numeric;
ALTER TABLE ccm21.riversegments add column up_forest_31 numeric;
ALTER TABLE ccm21.riversegments add column up_natural_32_33 numeric;
ALTER TABLE ccm21.riversegments add column up_wetlands_4 numeric;
ALTER TABLE ccm21.riversegments add column up_inwat_51 numeric;
ALTER TABLE ccm21.riversegments add column up_marwat_52 numeric;

--the request with france had troubles in getting some of the riversegments, the short ones that were not fully
-- france and were not part of a larger basin (the request was done on wso_id)
-- here I'm searching for those missing gid
select gid from ccm21.riversegments where wso_id in (select wso_id from france.wso where area='France')
except select gid from ccm21.riversegments where wso_id in (
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT ST_Union(f.the_geom) as singlegeom
    FROM france.departement As f) as sub
ON ST_Contains(sub.singlegeom,r.the_geom));
--125 gid

I will save those as a txt file, load them in R and process them