back to first page ..
back to Recipes for EDA CookBook Eda
How to join the BDMAP and CCM version 2
see Joining BDMAP and the CCM layer CookBook join BDMAP_CCM
for the first version.
The trouble is that some dams or electrofishing stations are sometimes badly projected
We should start at step 6
1. putting bd_carthage into postgis
below an example on how to load from latin1 database to postgis only hylcov_arc
cd C:\eda\couches_SIG\BDCarthage09 C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "CREATE DATABASE bd_carthage WITH OWNER = postgres template=postgis ENCODING = 'UTF8' LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1;" C:\"Program Files"\PostgreSQL\8.4\bin\psql -p 5433 -U postgres -c "COMMENT ON DATABASE bd_carthage IS 'database bd_carthage2009" C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I hylcov_arc.shp hylcov_arc> hylcov_arc.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d bd_carthage -h localhost -U postgres -p 5433 -f hylcov_arc.sql
We also need the nodes from bd_carthage use downstream node instead
cd C:\eda\couches_SIG\BDCarthage09 C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 -c -g the_geom -W LATIN1 -I hylcov_node.shp hylcov_node> hylcov_node.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d bd_carthage -h localhost -U postgres -p 5433 -f hylcov_node.sql
changing the projections (if the layer is not already in 3035 the first four lines can be avoided by putting -s 2154 in the previous box
-- les projections n'étaient pas en 3035, erreur ci dessus que je corrige alter table hylcov_node drop constraint enforce_srid_the_geom ; alter table hylcov_arc drop constraint enforce_srid_the_geom ; update hylcov_node set the_geom=ST_SetSRID(the_geom, 2154); update hylcov_arc set the_geom=ST_SetSRID(the_geom, 2154); update hylcov_arc set the_geom=ST_Transform(the_geom,3035); update hylcov_node set the_geom=ST_Transform(the_geom,3035); alter table hylcov_node add constraint enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035); alter table hylcov_arc add constraint enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
2. getting distance sea and distance source for bd_carthage
Below after some search, but we have to trust the last data in the bd_carthage... Do we really ? Coming back from the end of the calculations, I am now reversing and putting back Cédric's calculation of distance source.
load(paste(datawd,"/dataEDAcommun/chainage/parcours_source/noeud&troncon_final.RData",sep=""))
ex(colnames(noeuds_final))
noeuds_1<-read.delim(paste(datawd,"/dataEDAcommun/amorce_mer/bassin final/source/troncon_parcouru_source.txt",sep=""),sep="\t")
#write.table(noeuds_final,file=paste(datawd,"/dataEDAcommun/chainage/parcours_source/noeud_troncon_final.csv",sep=""),sep=";",row.names=FALSE,col.names=TRUE)
str(noeuds_final)
str(noeuds_1)
noeuds_final.1<-merge(noeuds_1[,c("nouveau_noeud_aval","dist_source_max_aval")],noeuds_final,by.x="nouveau_noeud_aval",by.y="Id_BDCARTHAGE")
plot(noeuds_final.1$dist_source_max_aval,noeuds_final.1$distance_source) # inquiétant non ?
str(noeuds_final.1)
noeuds_final.1[is.infinite(noeuds_final.1)]
write.table(noeuds_final.1,file=paste(datawd,"/dataEDAcommun/chainage/parcours_source/noeud_troncon_final_2.csv",sep=""),sep=";",row.names=FALSE,col.names=TRUE)
-- in bd_carthage database, here is the second version accounting for inclusion of firstly calculated distances source -- in fact these have been also calculated by Laurent and seem really wrong drop table noeud_troncon_final CASCADE; create table noeud_troncon_final ( Id_BDCARTHAGE integer, dist_source_max_aval numeric, situation varchar(10), distance_mer numeric, bassin integer, noeud_mer integer, niveau integer, nb_confluence integer, nb_defluence integer, noeud_source integer, distance_source numeric, distance_source_cumulee numeric, statut varchar(30), strahler integer, niveau_defluence numeric, distance_affluent_defluence numeric, id_carthage_bis varchar(10), type_noeud_source varchar(30)); COPY noeud_troncon_final from 'C:/base/noeud_troncon_final_2.csv' with CSV header delimiter as ';' NULL as 'NA'
CREATE INDEX hylcov_node_ID_SOM ON hylcov_node USING btree (ID_SOM); CREATE INDEX hylcov_arc_id_som_f ON hylcov_arc USING btree (id_som_f); CREATE INDEX hylcov_arc_id_som_i ON hylcov_arc USING btree (id_som_i); CREATE INDEX hylcov_node_ID_SOM ON hylcov_node USING btree (ID_SOM); CREATE INDEX noeud_troncon_final_Id_BDCARTHAGE ON noeud_troncon_final USING btree (Id_BDCARTHAGE); CREATE INDEX noeud_Id_BDCARTHAGE ON noeuds USING btree (Id_BDCARTHAGE); drop view if exists hylcov_arc_dist; create view hylcov_arc_dist as( select * from hylcov_arc h left join noeud_troncon_final n on id_som_f=Id_BDCARTHAGE );
-- troisième essai -- Les min et max de noeud_source_total$Id_BDCARTHAGE et hylcov_node$id_som correspondent -- importation de la table puis jointure avec la table des noeuds.... drop table if exists noeuds; create table noeuds ( Id_BDCARTHAGE integer, situation varchar(10), distance_mer numeric, bassin integer, noeud_mer integer, niveau integer, dist_source_max numeric, dist_source_cum numeric, noeud_source integer); -- saving the table in c:/base in .csv format COPY noeuds from 'C:/base/noeud_source_total.csv' with CSV header delimiter as ';' NULL as 'NA' CREATE INDEX noeud_Id_BDCARTHAGE ON noeuds USING btree (Id_BDCARTHAGE); -- first a trial on downstream segment see figure downstream segment drop view if exists hylcov_arc_dist2; create view hylcov_arc_dist2 as( select * from hylcov_arc left join noeuds on id_som_f=Id_BDCARTHAGE ); -- calculating distance for the upstream segment drop table if exists hylcov_arc_dist2; create table hylcov_arc_dist2 as( select * from hylcov_arc left join noeuds on id_som_i=Id_BDCARTHAGE ); -- the :: numeric is essential -- On est obligé de faire la jointure avec le troncon amont autrement les troncons partant du cours principal se voient attribués -- des distances source qui sont celles de l'axe. Par contre il faut rajouter à tous les tronçons la longueur du tronçon. update hylcov_arc_dist2 set dist_source_max= 0 where dist_source_max=-1; update hylcov_arc_dist2 set dist_source_max= dist_source_max+length/1000;
to shape
C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "C:\eda\exports_shape\hylcov_arc_dist" -p 5433 -u postgres -P postgres -g the_geom -r -k bd_carthage hylcov_arc_dist REM The second is the right one, but it does not contains strahler... C:\"Program Files"\PostgreSQL\8.4\bin\pgsql2shp -f "C:\eda\exports_shape\hylcov_arc_dist2" -p 5433 -u postgres -P postgres -g the_geom -r -k bd_carthage hylcov_arc_dist2
distances from Laurent are ok but not on the whole river length
distances from Laurent (2 nd trial not shown)... simply a mess ... possibly join pb
distances from Cédric joining downstream node
distances from Cédric joining ustream node and adding the length of the segment
3. Get distances into the stationsp2 table
31. Saving table stationsp2 from eda20 to bd_carthage
cd C:\eda\backup C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t bd_map.stationsp2 eda2.0> stationsp2.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d bd_carthage -p 5433 -U postgres -f stationsp2.sql REM une erreur quand il essaye de mettre le schéma bd_map mais là on a pas besoin d'avoir les données dans un schéma
at this stage it is usefull to check the geometry column table and correct it eventually
32. project stationsp2 again and get table of all projections within 300
Some stations were missing...
- first create two index to speed up queries
bdcarthage
CREATE INDEX indexhylcov_arc ON hylcov_arc USING GIST ( the_geom GIST_GEOMETRY_OPS ); CREATE INDEX indexStationsp2 ON stationsp2 USING GIST ( the_geom GIST_GEOMETRY_OPS );
Joining the hylcovarc and stationsp2, the_geom is that of stationsp2, first version at source:trunk/EDAcommun/sql/bd_map_bd_carthage_version1.sql
Script correspond to trial 3 above is written below
One big error solved ticket #72
CREATE INDEX indexhylcov_arc_dist2 ON hylcov_arc_dist2 USING GIST ( the_geom GIST_GEOMETRY_OPS ); -- creation de la table bd_map_bd_carthage la jointure entre la bd_carthage et la table des distances par Cédric est déjà faite dans un left join. -- Ici on projette les stations de pêche sur la bd_carthage DROP TABLE IF EXISTS bd_map_bd_carthage; CREATE TABLE bd_map_bd_carthage as ( SELECT distinct on (st_codecsp) st_codecsp, dist_source,dist_sea,strahler,id_trhyd, min(distance) as distance, the_geom FROM ( SELECT st_codecsp, n.distance_mer as dist_sea,n.strahler ,d.id_trhyd ,d.dist_source_max as dist_source, CAST(distance(d.the_geom, s.the_geom) as decimal(15,1)) as distance,s.the_geom FROM stationsp2 As s INNER JOIN hylcov_arc_dist2 d ON ST_DWithin(d.the_geom, s.the_geom,300) LEFT JOIN noeud_troncon_final n ON d.id_som_f = n.id_bdcarthage -- pour récupérer les strahler WHERE s.the_geom IS NOT NULL ORDER BY st_codecsp) as sub GROUP BY st_codecsp, distance, dist_source,dist_sea,strahler, id_trhyd, the_geom ); alter table bd_map_bd_carthage 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 '', 'public', 'bd_map_bd_carthage', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom) FROM bd_map_bd_carthage LIMIT 1; -- creation d'index, clé primaire, et constraintes qui vont bien alter table bd_map_bd_carthage add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2); alter table bd_map_bd_carthage add CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL); alter table bd_map_bd_carthage add CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035); alter table bd_map_bd_carthage ADD CONSTRAINT pk_id PRIMARY KEY(id); CREATE INDEX indexbd_map_bd_carthage ON bd_map_bd_carthage USING GIST ( the_geom GIST_GEOMETRY_OPS );
33. Saving back table stationsp2 from bd_carthage to eda
cd C:\eda\backup C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t bd_map_bd_carthage bd_carthage> bd_map_bd_carthage.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "drop table if exists bd_map.bd_map_bd_carthage" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -f bd_map_bd_carthage.sql C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage drop CONSTRAINT pk_id;" C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage ADD CONSTRAINT pk_id_bd_map_bd_carthage PRIMARY KEY(id); C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -p 5433 -U postgres -c "alter table bd_map_bd_carthage set schema bd_map"
4. define criteria helping to do the projection
Below this table does not only select minimum distance but all ccm segments which are within a 300 m distance from the electrofishing station.
-- launch in eda2.0 DROP TABLE IF EXISTS bd_map.bdmap_ccm2; CREATE TABLE bd_map.bdmap_ccm2 as ( SELECT st_codecsp, r.cum_len_sea as dist_sea_ccm, r.distance_source as dist_source_ccm, r.strahler as strahler_ccm,gid ,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 ccm21.riversegments r ON ST_DWithin(r.the_geom, s.the_geom,300) WHERE s.the_geom IS NOT NULL ORDER BY st_codecsp ); alter table bd_map.bdmap_ccm2 add column id serial;
The table from the request joining bd_map.bd_map_bd_carthage with the bd_map.bdmap_ccm2 is exported there (use .csv exort to C:\Documents and Settings\cedric\Mes documents\Migrateur\programmes\workspace3.5\EDAdata\dataEDAccm\export_jointure_bdcarthage_ccm.csv
select * from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp;
source:data/dataEDAccm/export_jointure_bdcarthage_ccm.csv
Some processing with R and some results in carto_trial.R
We are trying to see if distance_source distance_sea and strahler are consistent between bd_carthage and ccm
VERSION 1.0 DEPRECATED !!!!!!!!!!!!!!!!!! # analyse des relations entre distance source et distance mer pour les projections sur la ccm library(lattice) library(ggplot2) ccm_bd<-read.csv(paste(datawd,"/dataEDAccm/export_jointure_bdcarthage_ccm.csv",sep=""),sep=";",stringsAsFactors = FALSE) str(ccm_bd) ccm_bd$dist_source_ccm=ccm_bd$dist_source_ccm/1000 ccm_bd$dist_sea_ccm=ccm_bd$dist_sea_ccm/1000 ccm_bd$pbstrahler=ccm_bd$strahler_ccm>(ccm_bd$strahler+1) # which strahler are larger in ccm than in bd_carthage, allowing diff of one ratio_dist_mer=ccm_bd$cum_len_sea/ccm_bd$distance_mer ccm_bd$pb_ratio_dist_mer<-ratio_dist_mer>2&ccm_bd$cum_len_sea>100 # la distance mer est le tripe pour la ccm (peut arriver pour les faibles valeurs) ratio_sources<-ccm_bd$distance_source_ccm/ccm_bd$distance_source&ccm_bd$cum_len_sea>100 ccm_bd$pbratiosource<-ratio_sources>2 # la distance source ccm est égale à trois fois la distance source bd_carthage sum(ccm_bd$pbratiosource,na.rm=TRUE)/nrow(ccm_bd) # 16 % 2279 sum(ccm_bd$pb_ratio_dist_mer,na.rm=TRUE)/nrow(ccm_bd) # 0.1 % 15 sum(ccm_bd$pbstrahler,na.rm=TRUE)/nrow(ccm_bd) #3% 517 sum(!ccm_bd$pbratiosource&!ccm_bd$pbstrahler&!ccm_bd$pb_ratio_dist_mer,na.rm=TRUE)/nrow(ccm_bd) # on garde 79 % des données # hitograms layout(matrix(c(1,2), 1, 2, byrow = TRUE)) hist(ratio_dist_mer[ratio_dist_mer<2],100,main="ratio dist. sea") abline(v=2,col="red") mtext(paste(round(sum(ccm_bd$pb_ratio_dist_mer,na.rm=TRUE)/nrow(ccm_bd),2)*100,"% out"),col="red") hist(ratio_sources[ratio_sources<10],100,main="ratio dist source") abline(v=3,col="red") mtext(paste(round(sum(ccm_bd$pbratiosource,na.rm=TRUE)/nrow(ccm_bd),3)*100,"% out"),col="red") # plots g<-ggplot(ccm_bd) g+geom_point(aes(x=distance_source,y=distance_source_ccm,colour=pbstrahler)) g+geom_point(aes(distance_mer,cum_len_sea,colour=pb_ratio_dist_mer))+geom_abline(slope=1,colour="red") +geom_abline(slope=1,intercept=1,colour="green") #g+geom_point(aes(distance_mer,cum_len_sea,colour=pbratiosource))+geom_abline(slope=1,colour="red") g+geom_jitter(aes(x=strahler,y=strahlerccm,colour=pbstrahler),alpha=0.3) g1<-g+geom_point(aes(x=distance_source,y=distance_source_ccm,colour=pbratiosource))+geom_abline(slope=2,colour="red") ccm_bd1<-ccm_bd[ccm_bd$pbstrahler,] g1+geom_point(aes(x=distance_source,y=distance_source_ccm),size=2,data=ccm_bd1,pch=1)
The following criteria have been used to select stations v1
For distance_sea(ccm) > 100 distance_seaccm/distance_sea_bd_carthage<=2 => we mostly strive to avoid the projection of stations on short tributaries on larger streams from the ccm
Distance_source(ccm)/distance_source(bd_carthage)<=2
ccm_bd$strahlerccm-ccm_bd$strahler<=1
Finally 78% lines kept but this include doubles
VERSION 3.0
#---------------------------------------------------------------------------------------------
#---------------------------------------------------------------------------------------------
# analyse des relations entre distance source et distance mer pour les projections sur la ccm
#---------------------------------------------------------------------------------------------
#---------------------------------------------------------------------------------------------
source("EDACCM/init.r")
library(lattice)
library(ggplot2)
ccm_bd<-read.csv(paste(datawd,"/dataEDAccm/export_jointure_bdcarthage_ccm.csv",sep=""),sep=";",stringsAsFactors = FALSE)
str(ccm_bd)
ccm_bd$dist_source_ccm=ccm_bd$dist_source_ccm/1000
ccm_bd$dist_sea_ccm=ccm_bd$dist_sea_ccm/1000
ccm_bd$pbstrahler=ccm_bd$strahler_ccm>(ccm_bd$strahler+1) # which strahler are larger in ccm than in bd_carthage, allowing diff of one
ratio_dist_sea=ccm_bd$dist_sea_ccm/ccm_bd$dist_sea
ccm_bd$pb_ratio_dist_sea<-ratio_dist_sea>2&ccm_bd$dist_sea_ccm>100 # la distance mer est le tripe pour la ccm (peut arriver pour les faibles valeurs)
layout(matrix(c(1,2), 2, 1, byrow = TRUE))
hist(ccm_bd$dist_source,100,ylim=c(50,15000))
hist(ccm_bd$dist_source,100,ylim=c(0,50))
diffsource<-ccm_bd$dist_source_ccm-ccm_bd$dist_source
ratiosource<-ccm_bd$dist_source_ccm/ccm_bd$dist_source
ccm_bd$pbdiffsource<-!((diffsource>-20&diffsource< 20)|(ratiosource>0.7&ratiosource<1.3))
sum(ccm_bd$pbdiffsource,na.rm=TRUE)/nrow(ccm_bd) # 14 % 2027
sum(ccm_bd$pb_ratio_dist_sea,na.rm=TRUE)/nrow(ccm_bd) # 12 0.00087
sum(ccm_bd$pbstrahler,na.rm=TRUE)/nrow(ccm_bd) #3% 457
#sum(!ccm_bd$pbdiffsource&!ccm_bd$pbstrahler&!ccm_bd$pb_ratio_dist_sea,na.rm=TRUE)/nrow(ccm_bd) # 64 % si on garde strahler
# finally I don't trust there is a strahler rank pb
sum(!ccm_bd$pbdiffsource&!ccm_bd$pb_ratio_dist_sea,na.rm=TRUE)/nrow(ccm_bd) # on garde 74% des données
# histograms
layout(matrix(c(1,2), 1, 2, byrow = TRUE))
hist(ratio_dist_sea[ratio_dist_sea<4],100,main="ratio dist. sea")
abline(v=2,col="red")
mtext(paste(round(sum(ccm_bd$pb_ratio_dist_sea,na.rm=TRUE)/nrow(ccm_bd),2)*100,"% out"),col="red")
hist(diffsource,100,main="ratio dist source",100)
abline(v=-20,col="red")
abline(v=20,col="red")
mtext(paste(round(sum(ccm_bd$pbdiffsource,na.rm=TRUE)/nrow(ccm_bd),3)*100,"% out"),col="red")
# plots
g<-ggplot(ccm_bd)
breaks=as.vector(c(1, 2, 5) %o% 10^(-1:3))
g+geom_point(aes(x=dist_source,y=dist_source_ccm,colour=pbdiffsource),alpha=0.5,size=0.3)+
geom_abline(slope=1.3,colour="blue",lty=2)+
geom_abline(slope=0.7,colour="blue",lty=2)+
#scale_x_log10(name="distance source bd carthage (log scale)",breaks = breaks, labels = breaks,limits=c(1,1000))+
#scale_y_log10(name="distance source ccm (log scale)",breaks = breaks, labels = breaks,limits=c(1,1000)) +
geom_abline(slope=1,colour="brown")
### same with a log scale
breaks=as.vector(c(1, 2, 5) %o% 10^(-1:3))
g1<-g+geom_point(aes(x=dist_source,y=dist_source_ccm,colour=pbdiffsource),alpha=0.5,size=0.3)+
geom_hline(yintercept=1.3,colour="green")+
geom_vline(xintercept=1.3,colour="green")+
scale_x_log10(name="distance source bd carthage (log scale)",breaks = breaks, labels = breaks,limits=c(1,1000))+
scale_y_log10(name="distance source ccm (log scale)",breaks = breaks, labels = breaks,limits=c(1,1000)) +
geom_abline(slope=1,colour="brown")
ccm_bd1<-ccm_bd[ccm_bd$pbstrahler,]
g1+geom_point(aes(x=dist_source,y=dist_source_ccm),size=2,data=ccm_bd1,pch=1)
g+geom_point(aes(dist_sea,dist_sea_ccm,colour=pb_ratio_dist_sea))+geom_abline(slope=1,colour="red") +geom_abline(slope=1,intercept=1,colour="green")
#g+geom_point(aes(dist_sea,dist_sea_ccm,colour=pbdiffsource))+geom_abline(slope=1,colour="red") +geom_abline(slope=1,intercept=1,colour="green")
g+geom_jitter(aes(x=strahler,y=strahler_ccm,colour=pbstrahler),alpha=0.3)
Criteria have been used to select stations (v3)
dist sea unchanged, strahler might detect some pb but less than distance source so left out
For the distance source, final choice below = either a difference less than 20 km or a ratio departing less than 0.3 from 1
5. query to select the final data from bd_map.bdmap_ccm2
select count(*) from bd_map.bdmap_ccm2; --13695 this one is with repeated projection on ccm riversegments within 300 m select count(*) from bd_map.bdmap_ccm; --8884 this is without selection (first treatment) for the riversegement corresponding to the lowest proj dist select count(*) from bd_map.bd_map_bd_carthage where dist_sea=0; -- 0 update bd_map.bd_map_bd_carthage set dist_sea=0.00001 where dist_sea=0; /* REQUEST WITH SELECTION OF CORRECT CRITERIA FOR dist SEA, dist SOURCE, (STRAHLER RANK not taken) */ select * from ( select b.st_codecsp, b.dist_source as dist_source_bdcar, c.dist_source_ccm/1000 as dist_source_ccm, (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR ((c.dist_source_ccm/1000-b.dist_source)> -20 AND (c.dist_source_ccm/1000-b.dist_source)<20) OR (c.dist_source_ccm >100 AND b.dist_source >100) AS dist_source_ratio, b.dist_sea as dist_sea_bdcar, c.dist_sea_ccm/1000 as dist_sea_ccm, NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio, b.strahler as strahler_bdcar, c.strahler_ccm as strahler_ccm, (c.strahler_ccm-b.strahler)<=1 as strahler_diff, b.distance as distproj_bdcar, c.distance as distproj_ccm, id_trhyd, gid, c.the_geom from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp ) as sub WHERE (dist_source_ratio is TRUE OR dist_source_ratio IS NULL) AND (dist_sea_ratio is TRUE OR dist_sea_ratio IS NULL); --AND strahler_diff is TRUE; --11868 lines /* FINAL REQUEST */ drop table if exists bd_map.bdmap_ccm_final; create table bd_map.bdmap_ccm_final as ( select distinct on(st_codecsp) st_codecsp, gid, id_trhyd, dist_source_bdcar, dist_source_ccm, dist_sea_bdcar, dist_sea_ccm, strahler_bdcar, strahler_ccm, distproj_bdcar, min(distproj_ccm) as distproj_ccm, the_geom from( select * from ( select b.st_codecsp, b.dist_source as dist_source_bdcar, c.dist_source_ccm/1000 as dist_source_ccm, (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR ((c.dist_source_ccm/1000-b.dist_source)> -20 AND (c.dist_source_ccm/1000-b.dist_source)<20) OR (c.dist_source_ccm >100 AND b.dist_source >100) AS dist_source_ratio, b.dist_sea as dist_sea_bdcar, c.dist_sea_ccm/1000 as dist_sea_ccm, NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio, b.strahler as strahler_bdcar, c.strahler_ccm as strahler_ccm, (c.strahler_ccm-b.strahler)<=1 as strahler_diff, b.distance as distproj_bdcar, c.distance as distproj_ccm, id_trhyd, gid, c.the_geom from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp ) as sub WHERE (dist_source_ratio is TRUE or dist_source_ratio is NULL) AND (dist_sea_ratio is TRUE OR dist_sea_ratio IS NULL) --AND strahler_diff is TRUE ) as sub1 group by st_codecsp, gid,id_trhyd,dist_source_bdcar,dist_source_ccm,dist_sea_bdcar,dist_sea_ccm,strahler_bdcar,strahler_ccm,distproj_bdcar, distproj_ccm, the_geom order by st_codecsp ) ; /* * ANALYSIS OF WITHDRAWN NUMBERS */ select count(*) from bd_map.bd_map_bd_carthage where dist_source=0 ;-- 0 select count(*) from bd_map.bd_map_bd_carthage where dist_sea=0; -- 5 -- voir comment on gère ces cas particuliers par la suite.... select sum(cast(not(dist_sea_ratio) as integer)) as sum_pb_sea, sum(cast(not(dist_source_ratio) as integer)) as sum_pb_source, sum(cast((dist_source_ratio is NULL) as integer)) as sum_pb_source_NULL, sum(cast(not(strahler_diff) as integer)) as sum_pb_strahler from ( select b.st_codecsp, b.dist_source as dist_source_bdcar, c.dist_source_ccm/1000 as dist_source_ccm, (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR ((c.dist_source_ccm/1000-b.dist_source)> -20 AND (c.dist_source_ccm/1000-b.dist_source)<20) OR (c.dist_source_ccm >100 AND b.dist_source >100) AS dist_source_ratio, b.dist_sea as dist_sea_bdcar, c.dist_sea_ccm/1000 as dist_sea_ccm, NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio, b.strahler as strahler_bdcar, c.strahler_ccm as strahler_ccm, (c.strahler_ccm-b.strahler)<=1 as strahler_diff, b.distance as distproj_bdcar, c.distance as distproj_ccm, id_trhyd, gid, c.the_geom from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp ) as sub; -- 12 (R=12), 1807-571 NULLS (R=2027 including zero), 457 (R=457) OK /* * TABLE TO SHOW THE RESULTS IN A MAP FOR THOSE THAT WERE NOT SELECTED TOO */ drop table if exists bd_map.bdmap_ccm_full; create table bd_map.bdmap_ccm_full as ( select b.st_codecsp, b.dist_source as dist_source_bdcar, c.dist_source_ccm/1000 as dist_source_ccm, (((c.dist_source_ccm/(1000*b.dist_source))<1.3) AND (c.dist_source_ccm/(1000*b.dist_source))>0.7) OR ((c.dist_source_ccm/1000-b.dist_source)> -20 AND (c.dist_source_ccm/1000-b.dist_source)<20) OR (c.dist_source_ccm >100 AND b.dist_source >100) AS dist_source_ratio, b.dist_sea as dist_sea_bdcar, c.dist_sea_ccm/1000 as dist_sea_ccm, NOT((c.dist_sea_ccm/(1000*b.dist_sea))>2 and c.dist_sea_ccm>100000) as dist_sea_ratio, b.strahler as strahler_bdcar, c.strahler_ccm as strahler_ccm, (c.strahler_ccm-b.strahler)<=1 as strahler_diff, b.distance as distproj_bdcar, c.distance as distproj_ccm, id_trhyd, gid, c.the_geom from bd_map.bd_map_bd_carthage b join bd_map.bdmap_ccm2 c on b.st_codecsp=c.st_codecsp );
cd C:\eda\backup C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t bd_map.bdmap_ccm_full eda2.0> bdmap_ccm_full.sql C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t bd_map.bdmap_ccm_final eda2.0> bdmap_ccm_final.sql C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t hylcov_arc_dist2 bd_carthage> hylcov_arc_dist2.sql
v0.1 There is a problem of distance source too short for bd_carthage this trouble was already seen on the first figure on this page
Reference for the qgis project for Cédric c/eda/bdmpa/projection_bdmap.qgs En normandie, pas mal de segments manquants.. Pourquoi ? Le ratio distance source est null, l'autoriser Problèmes de calculs bd_carthage sur les rivières en réseau...
10896 station sp2=> 8884 projetées à 300m => 6896 après projection final Après examen détaillé il s'agit plus de calculs de distances sources que de réels problèmes de projection. A vérifier sur les pb de rang de strahler....
Last modified 14 years ago
Last modified on Nov 22, 2010 10:03:22 AM
