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

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

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

-- 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
Then within those dams selecting those with conflicting distances...

First an analysis of the distance source from