back to first page..
back to CookBook Eda
back to RHT Go to CookBook RHT_UGA
See #80

Joining RHT with BDMAP

Joining the riversegment from RHT and stationsp2 (??)

-- creation de la table bdmap_rht, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
DROP TABLE IF EXISTS rht.bdmap_rht;
CREATE TABLE rht.bdmap_rht as (
        SELECT distinct on (st_codecsp) st_codecsp, id_drain, min(distance) as distance, the_geom FROM (
               SELECT st_codecsp, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM bd_map.stationsp2 As s
               INNER JOIN  rht.rht r ON ST_DWithin(r.the_geom, s.the_geom,300)
               WHERE s.the_geom IS NOT NULL
               ORDER BY st_codecsp) AS sub 
        GROUP BY st_codecsp, distance,id_drain, the_geom
);
alter table rht.bdmap_rht add column id serial;
-- mise à jour de la table geometry_columns
INSERT INTO geometry_columns(f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type")
SELECT '', 'rht', 'bdmap_rht', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.bdmap_rht LIMIT 1;

-- creation d'index, clé primaire, et constraintes qui vont bien
alter table rht.bdmap_rht add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table rht.bdmap_rht add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table rht.bdmap_rht add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table rht.bdmap_rht ADD CONSTRAINT pk_id PRIMARY KEY(id);
CREATE INDEX indexbdmap_rht ON rht.bdmap_rht
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

select count(*) from bd_map.stationsp2  --10193 lignes
select count(*) from rht.rht  --114601 lignes
select count(*) from rht.bdmap_rht  -- 9650 lignes

Soit 94% des stations projetées sur le RHT

select count(*) from bd_map.bdmap_ccm_gid  --8188 lignes

Soit 80% des stations projetées sur la CCM

Some station must be deleted or reproject and the id_drain must be changed:

DROP table if exists rht.bdmap_rht_id;
CREATE TABLE rht.bdmap_rht_id as (
select distinct on (st_codecsp) * from rht.bdmap_rht
);

---Tous les cours d'eau situés à plus de 200m ont été vérifiés.
delete from rht.bdmap_rht_id where st_codecsp in ('03760141','03270131','01620009','01620005','01620137','01590014','06890285','06210238','03890095','02570237','02670291','02670264','02670097','02670092','02670098','02670230','02670231','02670232','02680172','02680166','02680039','02680033','02680037','02680144','02680143');  -- 25 stations supprimées
delete from rht.bdmap_rht_id where st_codecsp in ('05400178','05400044','0547C020','06040033','06040206','06070217','06840018','03600120','03910037','03910021','04450005','04181015','04181000','03510116','06390254','06250304','06730233','02880208','06700020','02550268','02080064','02080065','03510111','03520110','02670261','02670202','02670203');  -- 27 stations supprimées
delete from rht.bdmap_rht_id where st_codecsp in ('04420483','04420401','04420181','04420190','04430154','03890102','03890135','04630032','04420437'); -- 9 stations supprimées
delete from rht.bdmap_rht_id where st_codecsp in ('06260192','06380273','02880051','02670109','02670055','02670176','02670177','02670143','02670178','02670141','02670082','02670039','02570237','02540023','02570203');  --14 lignes
delete from rht.bdmap_rht_id where st_codecsp in ('06880149','02880048','04420458','02680085','02680039','02670004','02670053','06520051','03080085','02080027','06840052');  -- 11 stations

update rht.bdmap_rht_id SET id_drain='7866' where st_codecsp='06010369'; 

Verification des stations projetées sur ccm et dont gid a été modifié --> changement si besoin sur le rht de l'id_drain Stations vérifiées et bien projetées :

st_codecsp IN ('06210010','05405071','05645242','05471015','05245023','03580002','03760112','05332023','05330018','06250227','05630038','05470066','03500175','06250317,'03580002','06690101','03760112')

BDMAP with data from 2009

---Create database BDMAP2009
d:
cd D:\CelineJouanin\BDMAP
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d BDMAP2009 -h localhost -U postgres -p 5432 -f bdmap20110803.backup

d:
cd D:\CelineJouanin\export_schema
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t station_bdmapv2_final BDMAP2009> station_bdmapv2_final.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f station_bdmapv2_final.sql 
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t station BDMAP2009> station.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0_RHT -h localhost -U postgres -p 5432 -f station.sql 

---Create schema bdmap2009 into eda2.0_RHT
create schema bdmap2009;
Alter table station_bdmapv2_final set schema bdmap2009;
Alter table station set schema bdmap2009;

select * from bdmap2009.station_bdmapv2_final where codehydro is null   ---440 lines
select * from bdmap2009.station_bdmapv2_final where th_code is null   ---1 lines
select * from bdmap2009.station_bdmapv2_final where cgenelin is null  ---440 lines
select * from bdmap2009.station_bdmapv2_final where eh_codegen is null  ---0 lines
select count(*) from bdmap2009.station_bdmapv2_final ---8759 lines
select * from bdmap2009.station_bdmapv2_final stat inner join bd_carthage2011.troncon_hydrographique as bdc on bdc.code_hydro=stat.eh_codegen;
select * from bdmap2009.station_bdmapv2_final stat left join bd_carthage2011.troncon_hydrographique as bdc on bdc.code_hydro=stat.eh_codegen; 
select count(*) from bdmap2009.station_bdmapv2_final stat inner join bd_carthage2011.troncon_hydrographique as bdc on bdc.code_hydro=stat.eh_codegen; --58875 ca ne marche pas du tout