joining two concurrent riversegments tables

[cedric]

-- requête pour grouper des données depuis deux tables concurrentes qu'on a remplit en même temps
drop table if exists ccm21.riversegments2;
create table ccm21.riversegments2 AS(
SELECT

r.gid,
r.wso1_id,
r.wso_id,
r.rvr_id,
r.fromnode,
r.tonode,
r.strahler,
r.pfafstette,
r.nextdownid,
r.longpath,
r.maindrain_,
r.maindrain1,
r.length,
r.cum_len,
r.pixels_100,
r.catchment_,
r.cont_pixel,
r.drain_km2,
r.alt_gradie,
r.burned,
r.confidence,
r."window",
r.shape_leng,
r.the_geom,
r.cum_len_sea,
r.c_height,
r.c_score,
r.nbdams,
r.cs_height,
r.cs_nbdams,
r.cs_score,
r.summer,
r.winter,
r.catchment_area,
r.artificial_surfaces_11_13,
r.artificial_vegetated_14,
r.arable_land_21,
r.permanent_crops_22,
r.pastures_23,
r.heterogeneous_agricultural_24,
r.forest_31,
r.natural_32_33,
r.wetlands_4 ,
r.inland_waterbodies_51,
r.marine_water_52,
r1.up_catchment_area,
r1.up_art_11_13,
r1.up_art_14,
r1.up_arable_21,
r1.up_permcrop_22,
r1.up_pasture_23,
r1.up_hetagr_24,
r1.up_forest_31,
r1.up_natural_32_33,
r1.up_wetlands_4,
r1.up_inwat_51,
r1.up_marwat_52,
r.distance_source,
r.shree,
r.scheid ,
r.distance_relative ,
r.up_area,
r.uga ,
r.area

FROM

ccm21.riversegments r join ccm21.riversegments1 r1 on r.wso1_id=r1.wso1_id);

alter table ccm21.riversegments2 drop column area;

drop table ccm21.riversegments CASCADE;

drop table ccm21.riversegments1 CASCADE;
alter table ccm21.riversegments2 rename to riversegments;
comment on COLUMN ccm21.riversegments.up_area is 'sum area of the unit basins from upstream node including this segment catchment surface';

comment on COLUMN ccm21.riversegments.up_catchment_area is 'area calculated from downtream node see R script which is runned on all up_blabla columns as it not calculated on strahler rank 1 (most upstream basins)';

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);

alter table ccm21.riversegments add constraint
-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_dims_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2);
-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_geotype_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL);
-- ALTER TABLE ccm21.riversegments1 DROP CONSTRAINT enforce_srid_the_geom;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
-- ALTER TABLE ccm21.riversegments DROP CONSTRAINT riversegments_pkey;
ALTER TABLE ccm21.riversegments ADD CONSTRAINT riversegments_pkey PRIMARY KEY(gid);

[cedric]

CREATE INDEX indexriversegments_sp ON ccm21.riversegments

USING GIST ( the_geom GIST_GEOMETRY_OPS );

[cedric]

Milestone Data integration deleted