back to first page [..][[BR]]
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"][[BR]] for the first version. [[BR]]
The trouble is that some dams or electrofishing stations are sometimes badly projected [[BR]]
We should start at step 6 [[BR]]

== 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 ==
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

}}}
[[Image(source:data/Docs/trac/bd_carthage/distance_source_hylcov_arc.png,600px)]] 

== 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 ===

Some stations were missing...
 
 * first create two index to speed up queries [[BR]]
bdcarthage 

{{{
#!sql
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
{{{
#!sql
-- creation de la table bd_map_bd_carthage, je rajoute un the_geom a la requete pour pouvoir voir la table dans Qgis  
DROP TABLE IF EXISTS bd_map_bd_carthage;
CREATE TABLE bd_map_bd_carthage as (
               SELECT st_codecsp, n.distance_source,n.distance_mer,n.strahler ,a.id_trhyd ,CAST(distance(a.the_geom, s.the_geom) as  decimal(15,1)) as distance,s.the_geom 
               FROM stationsp2 As s
               INNER JOIN hylcov_arc a ON ST_DWithin(a.the_geom, s.the_geom,300)
		LEFT JOIN noeud_troncon_final n ON a.id_som_f::numeric = n.id_bdcarthage
               WHERE s.the_geom IS NOT NULL
               ORDER BY st_codecsp

);
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 );

}}}

== 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"][[BR]]
Then within those dams selecting those with conflicting distances...

First an analysis of the distance source from