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 

2. getting distance sea and distance source for bd_carthage

Les identifiants des noeuds de dataEDAcommun\amorce_mer\bassin final\source\dataEDAcommun\amorce_mer\bassin final\source\troncon_parcouru_source.txt ne correspondent probablement plus aux nouveaux identifiants des noeuds. Je regarde ce qu'avait stocké laurent...

load(paste(datawd,"/dataEDAcommun/chainage/parcours_source/noeud&troncon_final.RData",sep="")) 
write.table(noeuds_final,file=paste(datawd,"/dataEDAcommun/chainage/parcours_source/noeud&troncon_final.csv",sep=""),sep=";",row.names=FALSE,col.names=TRUE)

drop table noeud_troncon_final;
create table noeud_troncon_final (
Id_BDCARTHAGE varchar,
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_final1.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
  ON hylcov_node
  USING btree
  (ID_SOM);

CREATE INDEX noeud_troncon_final_Id_BDCARTHAGE
  ON noeud_troncon_final
  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 );;

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

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 bdmap.stationsp2 eda2.0> stationsp2.sql
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d bd_carthage -p 5433 -U postgres -f stationsp2.sql

4. include distance source constraint when doing the projection

ideally it should be a two step process, first projection to be used CookBook join BDMAP_CCM
Then within those dams selecting those with conflicting distances...

First an analysis of the distance source from