back to fisrt page ..
back to Gis Structures

Describing the CCM layers

To download and load the data into postgreSQL you must follow CCM2 download and load

table riversegments

gid: id (corresponds to line number)
wso1_id: id of the primary catchment drained by the river segment.
wso_id: id of the Sea-outlet to which the river segment belongs.
rvr_id: reference of the name of the river to which the segment belongs (often 0)
fromnode: upstream node
tonode: downstream node
strahler: the stream order hierarchy, the sizes range from a first order stream to the largest a 11th order stream
pfafstette: Pfafstetter code (?) of the river segment and its primary catchment
nextdownid: next segment identifyer downstream points to wso_id
longpath: identifies wether the river segment is part of the longest flow path in the River Basin (sea-outlet), Y(es) or NULL
maindrain_: ?
maindrain1: ?
length: length of a river segment in meters
cum_len: cumulated length of the upstream flow network, including the river segment itself (longest route ?)-in meters
pixels_100: area directly drained by the river segment (area of the primary catchment), in 100m*100m grid-cells
catchment_: ?
cont_pixel: area upstream of the From Node drained by the river segment in 100m*100m grid-cells
drain_km2: full area drained by the river segment and upstream area - in square kilometres
alt_gradie: relief Energy of the river segment : ((Elevation at From Node-Elevation at ToNode?)/Segment Length)*100 in per cent
burned: percentage grid-cells of the river segment having a high confidence level with respect to its geographical position
confidence: percentage of grid-cells of the river segment having a high confidence level with respect to its geographical position
window: here 2003
shape_leng: segmpent length in metres
the_geom: the_geom

table rivernodes

gid: id (corresponds to line number ?)
id: id of the river Basin (Seaoutlet) to which the catchment belongs
wso_id: Id of the river basin (sea outlet) to which the river node belongs
source: Type of source Y = River source, N =No not river source, M = river mouth
len_tom: Length from the node to the river mouth in meter
num_seg: Number of segments to reach the river mouth from the node
elev: Elevation at node in meter
x_laea: x re-projected to LAEA at a 1 kilometer resolution (I assume)
y_laea: ordinate re-projected to LAEA at a 1 kilometer resolution (I assume)
"window": here 2003

table catchments

Two tables : Catchment_E and Catchment_W (with the version of Laurent, now just one catchment)

"objectid":
"wso1_id, wso2_id...wso11_id": id of the Primary catchment (wso1-id) and of all higher order catchment to which the primary catchement belongs. wso1_id=-9999 : inclusions due to a lake, wso1_id=0 : inclusion due to the Sea
"wso_id": River Bassin (sea-outlet) ID to which the catchment belongs
"strahler": strahler order of the river segment draining the catchment. If no river segment exist in the catchment, Strahler order 0 (zero) is assigned
"feature_co":
"nextdownid":
"y_centroid":
"x_centroid":
"x_inside_L":
"Y_inside_L":
"xmax_laea":
"ymax_laea":
"area":
"area_km2": area of the primary catchment in km²
"perimeter":
"elev_min": minimum elevation in the primary catchment
"elev_max": maximum elevation in the primary catchment
"elev_mean": average elevation in the primary catchment
"elev_std": standard deviation of the elevation in the primary catchment
"slope_min": minimum slope in the primary catchment
"slope_max": maximum slope in the primary catchment
"slope_mean": average slope in the primary catchment, in percent
"slope_std": standard deviation of the slope in the primary catchment
"rain_min": minimum long-term average annual precipitation in the primary catchment, in millimeters
"rain_max": maximum long-term average annual precipitation in the primary catchment, in millimeters
"rain_mean": mean long-term average annual precipitation in the primary catchment, in millimeters
"rain_std": standard deviation of the long-term average annual precipitation in the primary catchment, in millimeters
"temp_min": minimum long-term average annual temperature in the primary catchment, in degrees Celsius
"temp_max": maximum long-term average annual temperature in the primary catchment, in degrees Celsius
"temp_mean": mean long-term average annual temperature in the primary catchment, in degrees Celsius
"temp_std": standard deviation of the long-term average annual temperature in the primary catchment, in degrees Celsius
"window": the ID of the data window to which the named river belongs
"Shape_Leng":
"Shape_Area":

Comparison BDCarthage (green) - CCM (red) from different views

Comparison WFD art3 (red) - WFD art5(gray) - CCM (large light blue) from different rivers and lakes in Ireland

Comparision of riversegments length CCM #32

Layers necessary for France

• Europe - France
  

• Loire Bretagne - Bretagne
  

• Bassins français
  

How to explore the ccm database

which segments are upstream from the current segment ?

-- Vilaine
--- creating a second index to accelerate the requests
CREATE INDEX indexriversegments_wso_id
ON ccm21.riversegments
  USING btree
  (wso_id);

select * from ccm21.riversegments where wso_id=291146;
CREATE INDEX indexcatchments_wso_id
ON ccm21.catchments
  USING btree
  (wso_id);
select * from ccm21.catchments where wso_id=291146 and wso3_id=291828 ;
-- extraction du chiffre immediatement à gauche du segment selectionné
select * from ccm21.riversegments where wso_id=291146 order by cum_len desc;
select round(pfafstette) from ccm21.riversegments where wso_id=291146 order by cum_len desc;
select CAST(round(pfafstette) AS TEXT) from ccm21.riversegments where wso_id=291146 order by cum_len desc;
select substring(CAST(round(pfafstette) AS TEXT),2,2) from ccm21.riversegments where wso_id=291146 order by cum_len desc;

Create an index on all wso1_id
CREATE INDEX indexriversegments_wso1_id
ON ccm21.riversegments
  USING btree
  (wso1_id);

Create an index on all wso1..._id
CREATE INDEX indexcatchments_wso1_id
ON ccm21.catchments
  USING btree
  (wso1_id);

-- segment mer vilaine
select * from ccm21.riversegments where gid=234706;
-- le rang de strahler est 6
-- recupération de l'identifiant du bv correspondant
select wso6_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=234706;
-- 291146
select wso1_id from ccm21.catchments c  where wso6_id = 291146;
-- liste de wso1_id
-- extraction des segments contenus dans ces bassins
select * from ccm21.riversegments where wso1_id in (select wso1_id from ccm21.catchments c  where wso6_id = 291146);