back to first page..

EDACCM\sql

---With Laurent files
create schema pose_wrbd;

D:
cd D:\CelineJouanin\RiverWidthWesternAnglian
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "width_irl.sql"

-- détail adapté par Cédric

-- script adapaté du script de laurent
CREATE TABLE wrbd.western_wso1
(
  id serial NOT NULL,
  wso_id integer,
  wso1_id integer,
  area character varying(25),
  CONSTRAINT wso1_pkey PRIMARY KEY (id)
);
--select distinct on (area) area from europe.wso1;
insert into wrbd.western_wso1 select * from europe.wso1 where area='Western'; --915

CREATE TABLE wrbd.ccm_river_segment_wrbb AS SELECT r.* FROM wrbd.western_wso1 w, ccm21.riversegments r WHERE r.wso1_id=w.wso1_id;

CREATE TABLE wrbd.ccm_river_segment_irluk AS SELECT r.* FROM ccm21.riversegments r WHERE "window"=2001;

DROP TABLE if exists wrbd.ccm_lakes_wrbb;
CREATE TABLE wrbd.ccm_lakes_wrbb AS SELECT "lakes".* FROM (SELECT DISTINCT ON(wso_id) wso_id FROM wrbd.western_wso1) as western_wso1, "ccm21"."lakes" WHERE "lakes"."wso_id"=western_wso1.wso_id;

-- j'ai envoyé le script de sauvegarde et restauration de cette table à céline
{{{
D:
cd D:\CelineJouanin\POSEProject\Ireland
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "wrbd.western_width.sql"
}}}
/*
drop table wrbd.western_width
CREATE TABLE wrbd.western_width
(
  id serial NOT NULL,
  KEY_SEGCD character varying(10),
  measered_wetted_width real,
  catchment_area real,
  shreve integer,
  CONSTRAINT western_width_pkey PRIMARY KEY (id)
);

COPY wrbd.western_width FROM 'C:/base/Original training dataset.csv' CSV HEADER DELIMITER ';'
*/

-- Impport de discovery_rivers.shp à partir de wgs84 4326
{{{
---In cmd consol
D:
cd D:\CelineJouanin\POSEProject\Ireland\Riverwidth
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 4326 -c -g the_geom -W LATIN1 -I Discoervy_rivers.shp Discovery_rivers > Discovery_rivers.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "discovery_rivers.sql"
---In sql consol
ALTER TABLE discovery_rivers SET SCHEMA wrbd;
}}}

-- TODO a partir d'ici !!!
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;


--- rapprochement couche IRL / couche CCM
DROP TABLE IF EXISTS wrbd.width_ccm ;
CREATE TABLE wrbd.width_ccm AS
SELECT ccm_river_segment_irluk.*, seg_cd, measered_wetted_width, catchment_area, shreve, ST_Distance(ST_Transform(wrbd_width_geom.the_geom,3035), ccm_river_segment_irluk.the_geom)  AS st_distance, ST_HausdorffDistance(ST_Transform(wrbd_width_geom.the_geom,3035), ccm_river_segment_irluk.the_geom) AS ST_HausdorffDistance FROM wrbd.wrbd_width_geom, wrbd.ccm_river_segment_irluk WHERE ST_Transform(wrbd_width_geom.the_geom,3035) &&ccm_river_segment_irluk.the_geom AND ST_Intersects(ST_Transform(wrbd_width_geom.the_geom,3035), ST_Buffer(ccm_river_segment_irluk.the_geom,100));


SELECT wso_id , count(*) 
FROM 
        (       SELECT * 
                FROM (  SELECT width_ccm."WSO1_ID"as wso_id , count(*), max(measered_wetted_width) as max_width  
                                FROM (  SELECT "WSO1_ID" , count(*), min(st_distance) AS min_dist 
                                                FROM wrbd.width_ccm  group by "WSO1_ID"
                                        ) as min_dist, wrbd.width_ccm 
                                WHERE st_distance=min_dist AND width_ccm."WSO1_ID" = min_dist."WSO1_ID" GROUP BY width_ccm."WSO1_ID"
        ) as max_width,  wrbd.width_ccm 
WHERE 
        measered_wetted_width=max_width AND width_ccm."WSO1_ID" = max_width.wso_id) as toto group by wso_id
;

CREATE TABLE wrbd.ccm_width_final AS 
SELECT width_ccm.*
FROM
        (SELECT "WSO1_ID" , count(*), min(st_distance) AS min_dist , min(st_hausdorffdistance) AS min_hausdorff, 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 min_hausdorff = st_hausdorffdistance AND measered_wetted_width = max_width 

D:
cd D:\CelineJouanin\RiverWidthWesternAnglian
C:\"Program Files"\PostgreSQL\8.4\bin\psql  -U postgres --dbname "eda2.0" -f "ccm_width.sql"

CREATE TABLE wrbd.width AS 
SELECT wso_id, wso1_id, seg_cd, measered_wetted_width, st_distance, st_hausdorffdistance  FROM wrbd.ccm_width_final;