calculation (re) of the riverwidth for Ireland bassed on sample sites

back to top ..

SELECT * FROM wrbd.discovery_rivers, wrbd.western_width WHERE seg_cd = key_segcd;

DROP TABLE IF EXISTS wrbd.wrbd_width_geom;
CREATE TABLE wrbd.wrbd_width_geom AS 
SELECT * FROM wrbd.discovery_rivers, wrbd.western_width WHERE seg_cd = key_segcd;
-- select * from wrbd.wrbd_width_geom

--- this is the joining between IRL / couche CCM
DROP TABLE IF EXISTS wrbd.width_ccm ;

CREATE TABLE wrbd.width_ccm AS
SELECT wso1_id,
seg_cd,
 measered_wetted_width, 
 wrbd_width_geom.catchment_area,
 wrbd_width_geom.shreve,
 ST_Distance(ST_Transform(wrbd_width_geom.the_geom,3035),  riversegments.the_geom)  AS st_distance
   FROM wrbd.wrbd_width_geom, 
   (select wso1_id,the_geom from ccm21.riversegments where wso1_id in (select wso1_id from europe.wso1 where area ='Ireland')) as riversegments
   WHERE ST_Transform(wrbd_width_geom.the_geom,3035) && riversegments.the_geom 
   AND ST_Intersects(ST_Transform(wrbd_width_geom.the_geom,3035), 
   ST_Buffer( riversegments.the_geom,100));



-- select * from wrbd.width_ccm
CREATE TABLE wrbd.ccm_width_final AS 
SELECT width_ccm.*
FROM
        (SELECT wso1_id , count(*), min(st_distance) AS min_dist , max(measered_wetted_width) as max_width  
                FROM wrbd.width_ccm  group by wso1_id) as r_min_dist, 
        wrbd.width_ccm
        WHERE width_ccm.wso1_id = r_min_dist.wso1_id 
        AND st_distance=min_dist 
        AND measered_wetted_width = max_width