| Version 78 (modified by celine, 15 years ago) (diff) |
|---|
back to first page ..
back to CookBook Eda
CCM2 download and load
Download on ccm on the web site
You can download the CCM2 Data at : http://ccm.jrc.ec.europa.eu/php/index.php?action=view&id=24
If you don't have a login and a password create it.
And select the data you want to add. Use the projection system : ETRS 1989 LAEA
Change the format with ArcGis
In ArcCatalog, open the data (for example CCM21_LAEA_RiverBasins\ccm21\LAEA_RiverBasins.gdb) and clic rigth on LAEA_RiverBasins.gdb > Export > To Shapefile (multiple)
and choose the "Output Folder" where the data will be stored.
Unzip the folder.
Append the different shape files
We have obtained shapefiles from different sources, folder (http://ccm.jrc.ec.europa.eu/images/CCM2_Windows2b.JPG), and it's easier to combine them into one single shapefile for ease of analysis and mapping.
This process is known as "merging" within ArcGIS.
- Tables: Rivernodes - Riversegments- Catchments
| Layer | Region | Rivernodes Nb of records | Riversegments Nb of records | Catchments Nb of records |
| 2000 | North | 64308 | 63298 | 65395 |
| 2001 | Western Islands | 34507 | 32408 | 36613 |
| 2002 | South | 121630 | 119096 | 123104 |
| 2003 | West | 117455 | 116764 | 118265 |
| 2004 | South West | 96378 | 95424 | 96826 |
| 2005 | Danube | 141112 | 140998 | 141216 |
| 2006 | Dnjepr | 64439 | 64016 | 64668 |
| 2007 | Balticum | 140998 | 138443 | 162488 |
| 2008 | Scandinavia | 86671 | 82433 | 96189 |
| 2009 | South East | 108279 | 104696 | 110005 |
| 2010 | Macronesia | 1722 | 1314 | 1771 |
| 2011 | Caucasus | 50320 | 49534 | 50461 |
| 2012 | Ural | 33198 | 32850 | 33470 |
| 2013 | Volga | 250212 | 250183 | 250291 |
| 2015 | Azores | 94 | 49 | 118 |
| 2016 | Northern Islands | 108 | 58 | 371 |
| 2017 | Tigris | 54734 | 54730 | 54731 |
| 2018 | Iceland | 3476 | 2998 | 3662 |
| After "Merge" Number of record expected Total | EUROPE | 1369641 (ok) | 1349292 (ok) | 1409644 (ok) |
some problems with the dataset from the windows = 2005, 2007, 2008, 2013, 2018, the column : wso_id, len_tom, Num_seg are empty with zero values
--> il faut recalculer la distance à la mer et attribuer un wso_id pour ces données
The Merge tool can be found within Data Management Tools > General > Merge. Double-clicking on the tool opens up its dialog box.
The input datasets are those shapefiles which you wish to merge together. Select the different shapefiles as you wish as long as they are all of the same data type.
Add Rivernodes in : CCM21_LAEA_window2000\ccm21\LAEA_W2000.gdb\RIVERNODES (from CCM21_LAEA_window2000 to CCM21_LAEA_window2018)
And specify a location and name for the output file which will be created : CCM2\RIVERNODES_Merge.
You must do the same thing with "Rivernodes", "Riversegments", "Catchments".
Add data into Postgres
See CookBook shptopostgres the same procedure was applied to obtain : (refer to your version so far Cédric or Céline)
REM Ouvrir ce fichier avec notepad++ et copier coller les commandes dans une invite de commande REM================================================ REM Script pour cédric (la connexion change - p 5433) REM================================================ REM Création de la base de donnée REM correspond au point 1 de http://62.160.92.241:8066/trac/wiki/CookBook%20shptopostgres C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "CREATE DATABASE ccm21_eda WITH OWNER = postgres template=postgis ENCODING = 'UTF8' LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1;" REM on se place dans le répertoire ou sont les données CD C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\couches_SIG\coucheccm\CCM2 C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 COAST coast > coast.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 ISLANDS islands > islands.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 LAKES lakes > lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 MAINRIVERS mainrivers > mainrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 NAMEDRIVERS namedrivers > namedrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 SEAOUTLETS seaoutlets > seaoutlets.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 RIVERNODES_Merge rivernodes > rivernodes.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 RIVERSEGMENTS_Merge riversegments > riversegments.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 CATCHMENTS_Merge catchments > catchments.sql REM changement d'encodage du client C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "SET client_encoding to 'LATIN1';" REM utilisation de psql pour la réintégration des données C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f coast.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f islands.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f mainrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f namedrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f seaoutlets.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f rivernodes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f riversegments.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -p 5433 -f catchments.sql REM================================================ REM Script pour Céline (la connexion change - p 5432 REM================================================ REM Création de la base de donnée REM correspond au point 1 de http://62.160.92.241:8066/trac/wiki/CookBook%20shptopostgres C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5432 -U postgres -c "CREATE DATABASE ccm21_eda WITH OWNER = postgres template=postgis ENCODING = 'UTF8' LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1;" REM on se place dans le répertoire ou sont les données CD D:\CelineJouanin\CCM21 C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 COAST coast > coast.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 ISLANDS islands > islands.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 LAKES lakes > lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 MAINRIVERS mainrivers > mainrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 NAMEDRIVERS namedrivers > namedrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 SEAOUTLETS seaoutlets > seaoutlets.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 RIVERNODES_Merge rivernodes > rivernodes.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 RIVERSEGMENTS_Merge riversegments > riversegments.sql C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -W LATIN1 CATCHMENTS_Merge catchments > catchments.sql REM changement d'encodage du client C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5432 -U postgres -c "SET client_encoding to 'LATIN1';" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f coast.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f islands.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f lakes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f mainrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f namedrivers.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f seaoutlets.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f rivernodes.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f riversegments.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d ccm21_eda -h localhost -U postgres -f catchments.sql
change schema to ccm21
Which can be downloaded at source:trunk/EDACCM/ccm21_build.bat
open this file with notepad++ and copy / paste to the command batch
CREATE SCHEMA ccm21; ALTER TABLE riversegments SET SCHEMA ccm21; ALTER TABLE rivernodes SET SCHEMA ccm21; ALTER TABLE catchments SET SCHEMA ccm21; ALTER TABLE coast SET SCHEMA ccm21; ALTER TABLE islands SET SCHEMA ccm21; ALTER TABLE mainrivers SET SCHEMA ccm21; ALTER TABLE namedrivers SET SCHEMA ccm21; ALTER TABLE seaoutlets SET SCHEMA ccm21; ALTER TABLE lakes SET SCHEMA ccm21;
A PLSQL function to get the upstream segments
creating indexes
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); DROP INDEX IF EXISTS ccm21.indexcatchments_wso_id; CREATE INDEX indexcatchments_wso_id ON ccm21.catchments USING btree (wso_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso1_id; CREATE INDEX indexcatchments_wso1_id ON ccm21.catchments USING btree (wso1_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso2_id; CREATE INDEX indexcatchments_wso2_id ON ccm21.catchments USING btree (wso2_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso3_id; CREATE INDEX indexcatchments_wso3_id ON ccm21.catchments USING btree (wso3_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso4_id; CREATE INDEX indexcatchments_wso4_id ON ccm21.catchments USING btree (wso4_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso5_id; CREATE INDEX indexcatchments_wso5_id ON ccm21.catchments USING btree (wso5_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso6_id; CREATE INDEX indexcatchments_wso6_id ON ccm21.catchments USING btree (wso6_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso7_id; CREATE INDEX indexcatchments_wso7_id ON ccm21.catchments USING btree (wso7_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso8_id; CREATE INDEX indexcatchments_wso8_id ON ccm21.catchments USING btree (wso8_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso9_id; CREATE INDEX indexcatchments_wso9_id ON ccm21.catchments USING btree (wso9_id); DROP INDEX IF EXISTS ccm21.indexcatchments_wso10_id; CREATE INDEX indexcatchments_wso10_id ON ccm21.catchments USING btree (wso10_id);
Exploration done in ticket #52
plsql documentation can be found there :
http://www.postgresql.org/docs/8.4/static/plpgsql.html
plpgsql function which returns the id of the primary catchments within the catchment corresponding to the strahler order
-- it is necessary to create a type to hold the output type DROP TYPE IF EXISTS wso1_id; CREATE TYPE wso1_id as (wso1_id int); -- In the function I tried to create a wso type to simplify the text. Pb you need to specify -- a tablename.columname%TYPE which will have to change along with the column, hence the big ugly script below... DROP FUNCTION IF EXISTS ccm21.mycatchment(gid_ numeric); CREATE OR REPLACE FUNCTION ccm21.mycatchment(gid_ numeric) RETURNS setof int AS $$ DECLARE result RECORD; pri wso1_id%rowtype; resultcount RECORD; BEGIN SELECT INTO result strahler FROM ccm21.riversegments where gid=gid_; RAISE NOTICE 'result is %' ,result; IF result.strahler=2 THEN RAISE NOTICE 'WSO_2 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso2_id = (select wso2_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso2_id = (select wso2_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=3 THEN RAISE NOTICE 'WSO_3 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso3_id = (select wso3_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso3_id = (select wso3_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=4 THEN RAISE NOTICE 'WSO_4 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso4_id = (select wso4_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso4_id = (select wso4_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=5 THEN RAISE NOTICE 'WSO_5 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso5_id = (select wso5_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso5_id = (select wso5_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=6 THEN RAISE NOTICE 'WSO_6 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso6_id = (select wso6_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso6_id = (select wso6_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=7 THEN RAISE NOTICE 'WSO_7 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso7_id = (select wso7_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso7_id = (select wso7_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=8 THEN RAISE NOTICE 'WSO_8 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso8_id = (select wso8_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso8_id = (select wso8_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=9 THEN RAISE NOTICE 'WSO_9 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso9_id = (select wso9_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso9_id = (select wso9_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSIF result.strahler=10 THEN RAISE NOTICE 'WSO_10 used'; SELECT INTO resultcount count(*) from ccm21.catchments c where wso10_id = (select wso10_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_); RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount; for pri in select wso1_id from ccm21.catchments c where wso10_id = (select wso10_id from ccm21.riversegments r join ccm21.catchments c on r.wso1_id=c.wso1_id where r.gid=gid_) loop pri.wso1_id=CAST(pri.wso1_id AS int8); return next pri.wso1_id; end loop; return; ELSE RAISE NOTICE 'Strahler is larger than this function can handle !'; END IF; RETURN; END; $$ LANGUAGE 'plpgsql' ; COMMENT ON FUNCTION ccm21.mycatchment (gid_ numeric) IS 'Uses the gid from ccm21.riversegments, checks strahler, and select the catchment accordingly, then returns all primary catchments from this larger catchment';
Same code with some differences Function mycatchment
--examples of use SELECT ccm21.mycatchment(234706); --la vilaine dans son ensemble -- NOTICE: result is (6) -- NOTICE: WSO_6 used -- NOTICE: number of primary catchments in the catchment =(759) -- durée=2s -- SELECT * from ccm21.catchments where wso1_id in (SELECT ccm21.mycatchment(234706));
Plpgsql function which returns the riversegments upstream from the riversegment selected
Note : It is necessary to create a table called ccm21.upstream_segments. Indeed, the pfafstetter along an axis can have a shorter length upstream than the length of the selected segment
The maximum length for a pfafstetter is 14 in the ccm. Transforming the database to store a column number with 14 number would increase the size of the database.
It is also not possible to create a variable in plpgsql storing more than one line. For these two reasons, a table upstream_segments is created and is filled with the segments of the basin, and further reduced to the segments upstream from the segment passed as parameter to the function. The function returns a resultset of integer values (gid)
DROP TYPE IF EXISTS gid;
CREATE TYPE gid as (gid int);
DROP FUNCTION IF EXISTS ccm21.upstream_segments(gid_ numeric);
CREATE OR REPLACE FUNCTION ccm21.upstream_segments(gid_ numeric) RETURNS setof int AS $$
DECLARE
gid_riversegments gid%ROWTYPE;
rec_pfafstette_length RECORD;
rec_riversegments ccm21.riversegments%ROWTYPE;
-- pfafstette_chain_length int;
pfafstette_max_chain_length int;
pfafstette_value int8;
BEGIN
-- filling a new table with the results from a catchment
DROP TABLE IF EXISTS ccm21.upstream_riversegments;
CREATE TABLE ccm21.upstream_riversegments AS SELECT * FROM ccm21.riversegments
where wso1_id in (SELECT ccm21.mycatchment(gid_));
ALTER TABLE ccm21.upstream_riversegments ADD CONSTRAINT pk_upstream_riversegments PRIMARY KEY (gid);
-- pfafstette chain length
--select into pfafstette_chain_length character_length(CAST(round(pfafstette) AS TEXT)
-- FROM ccm21.upstream_riversegments WHERE gid=gid_;
-- RAISE NOTICE 'pfafstette chain length is %', pfafstette_chain_length;
-- pfafstette max chain length in the selected basin
select into pfafstette_max_chain_length max(character_length(CAST(round(pfafstette) AS TEXT)))
FROM ccm21.upstream_riversegments;
-- pfafstette value
-- the chain is lengthened to the pfafstette max chain length
Update ccm21.upstream_riversegments set pfafstette =
floor(pfafstette)*power(10,(pfafstette_max_chain_length-character_length(CAST(floor(pfafstette) AS TEXT) )));
select into pfafstette_value CAST(round(pfafstette) AS int8) FROM ccm21.upstream_riversegments where gid=gid_;
RAISE NOTICE 'pfafstette is %', pfafstette_value;
DELETE FROM ccm21.upstream_riversegments WHERE pfafstette < pfafstette_value;
-- extracting the riversegments corresponding to the basin
for gid_riversegments in select gid from ccm21.upstream_riversegments order by gid loop
gid_riversegments.gid=CAST(gid_riversegments.gid AS int8);
return next gid_riversegments.gid;
end loop;
return;
END;
$$
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION ccm21.upstream_segments(gid_ numeric) IS 'This function uses ccm21.mycatchment to get all the segments from a catchment and searches all segments with a pfafstetter higher than the segment in the bassin. The pfafstette are trucated to the level of the segment used as input in the function';
Usage example
select ccm21.upstream_segments(234706); -- Vilaine downstream node
gets messages
NOTICE: pfafstette is 1 NOTICE: pfafstette chain length is 1 NOTICE: result is (6) CONTEXT: fonction PL/pgsql « upstream_segments », ligne 16 à FOR sur des lignes de SELECT NOTICE: WSO_6 used CONTEXT: fonction PL/pgsql « upstream_segments », ligne 16 à FOR sur des lignes de SELECT NOTICE: number of primary catchments in the catchment =(759) CONTEXT: fonction PL/pgsql « upstream_segments », ligne 16 à FOR sur des lignes de SELECT Durée totale d'exécution de la requête :1922 ms. 759 lignes récupérées.
Qgis examples, in the where box fill in
"gid" IN (select ccm21.upstream_segments(272506))
or to get the catchment area
"gid" IN (select ccm21.ccm21.mycatchment(272506))
Think about updating table public_geometry_column to speed up Qgis
