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 catchment_area numeric;
ALTER TABLE ccm21.riversegments add column up_catchment_area numeric;
ALTER TABLE ccm21.riversegments add column art_11_13 numeric;
ALTER TABLE ccm21.riversegments add column up_art_11_13 numeric;
ALTER TABLE ccm21.riversegments add column art_14 numeric;
ALTER TABLE ccm21.riversegments add column up_art_14 numeric;
ALTER TABLE ccm21.riversegments add column arable_21 numeric;
ALTER TABLE ccm21.riversegments add column up_arable_21 numeric;
ALTER TABLE ccm21.riversegments add column permcrop_22 numeric;
ALTER TABLE ccm21.riversegments add column up_permcrop_22 numeric;
ALTER TABLE ccm21.riversegments add column pasture_23 numeric;
ALTER TABLE ccm21.riversegments add column up_pasture_23 numeric;
ALTER TABLE ccm21.riversegments add column hetagr_24 numeric;
ALTER TABLE ccm21.riversegments add column up_hetagr_24 numeric;
ALTER TABLE ccm21.riversegments add column forest_31 numeric;
ALTER TABLE ccm21.riversegments add column up_forest_31 numeric;
ALTER TABLE ccm21.riversegments add column natural_32_33 numeric;
ALTER TABLE ccm21.riversegments add column up_natural_32_33 numeric;
ALTER TABLE ccm21.riversegments add column wetlands_4 numeric;
ALTER TABLE ccm21.riversegments add column up_wetlands_4 numeric;
ALTER TABLE ccm21.riversegments add column inwat_51 numeric;
ALTER TABLE ccm21.riversegments add column up_inwat_51 numeric;
ALTER TABLE ccm21.riversegments add column marwat_52 numeric;
ALTER TABLE ccm21.riversegments add column up_marwat_52 numeric;