this is ticket: #49 We need to only select a geographical region from the ccm plus all downstream segments within this region.
Below we use the ST_Contains function

Warning st_contains has been changed by st_intersects

/*boolean ST_Contains(geometry geomA, geometry geomB);
Description
Geometry A contains Geometry B 
*/boolean ST_Intersects(  geometry geomA  , raster rastB  );
Description
Geometry A intersects Geometry B 
*/
-- first trial..
/*
SELECT count (*) from ccm21.riversegments r
join (SELECT ST_Union(f.the_geom) as singlegeom
    FROM france.departement As f) as sub
ON ST_Intersects(sub.singlegeom,r.the_geom); 
*/
 -- we try to get the vector of seaoutlets that are within the geographical area

-- a table containing three columns

 drop table  if exists france.wso;
 CREATE TABLE france.wso (
 id serial PRIMARY KEY,
 wso_id integer,
 area varchar(12)
 )

insert into france.wso(wso_id) 
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_Intersects(sub.singlegeom,r.the_geom); --170828 lignes   (avec st_contains 170703 lines)

UPDATE france.wso set area='France' where area IS NULL --693 sea nodes

select * from ccm21.riversegments where wso_id in (select wso_id from france.wso where area='France')--170703 lines

CREATE INDEX france_wso_id
  ON france.wso
  (wso_id);

-- pour des essais sélection de la Bretagne
insert into france.wso(wso_id) 
select distinct on (wso_id)  wso_id from ccm21.riversegments r
join (SELECT the_geom
    FROM france.region where code_reg='53') as sub
ON ST_Contains(sub.the_geom,r.the_geom); --170703 lines

UPDATE france.wso set area='Bretagne' where area IS NULL

select * from ccm21.riversegments where wso_id in (select wso_id from france.wso where area='Bretagne')--2115 lines

Creating local tables for Britany (to enhance speed and allow fast testing and displaying in Qgis

-- Creating a table for riversegments Bretagne
DROP TABLE IF EXISTS ccm21.riversegments_Bretagne;   
CREATE TABLE  ccm21.riversegments_Bretagne AS
    SELECT * FROM ccm21.riversegments
        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne');
ALTER TABLE ccm21.riversegments_Bretagne ADD CONSTRAINT c_pk_gid_riversegments_Bretagne PRIMARY KEY  (gid);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ccm21', 'riversegments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ccm21.riversegments_Bretagne LIMIT 1;

-- creating a table for catchments Bretagne.
DROP TABLE IF EXISTS ccm21.catchments_Bretagne;   
CREATE TABLE  ccm21.catchments_Bretagne AS
    SELECT * FROM ccm21.catchments
        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='Bretagne');  
ALTER TABLE ccm21.catchments_Bretagne ADD CONSTRAINT c_pk_gid_catchments_Bretagne PRIMARY KEY  (gid);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ccm21', 'catchments_Bretagne', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ccm21.catchments_Bretagne LIMIT 1;

Table for France

DROP TABLE IF EXISTS ccm21.riversegments_france;   
CREATE TABLE  ccm21.riversegments_france AS
    SELECT * FROM ccm21.riversegments
        WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='France');
ALTER TABLE ccm21.riversegments_France ADD CONSTRAINT c_pk_gid_riversegments_france PRIMARY KEY  (gid);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ccm21', 'riversegments_France', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ccm21.riversegments_france LIMIT 1;
alter table ccm21.riversegments_france add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ccm21.riversegments_france add    CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING'::text OR the_geom IS NULL);
alter table clc.ccm21.riversegments_france add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX index_riversegments_france ON ccm21.riversegments_france
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 

-- creating a table for catchments france
DROP TABLE IF EXISTS ccm21.catchments_france;   
CREATE TABLE  ccm21.catchments_france AS
SELECT * FROM ccm21.catchments
WHERE wso_id IN (SELECT wso_id FROM france.wso WHERE area='france'); 
               
ALTER TABLE ccm21.catchments_france ADD CONSTRAINT c_pk_gid_catchments_france PRIMARY KEY  (gid);
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'ccm21', 'catchments_france', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM ccm21.catchments_france LIMIT 1;

alter table ccm21.catchments_france add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ccm21.catchments_france add    CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table ccm21.catchments_france add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX index_catchments_france ON ccm21.catchments_france
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
CREATE INDEX indexcatchmentsfrance_wso1_id
  ON ccm21.catchments
  USING btree
  (wso1_id);