How to join the BDMAP and CCM

1. Load the BDMAP database, it will be saved by Laurent using a Talend work
2. load the codier_csp table (it is also necessary for R treatments),the following code will create the table and copy it from the csv file (here located in base) I had to make a utf8 file
   • attachment:ticket:20:BDMAP_CODIER.csv​
   • ticket #20

     create schema csp;
     drop table if exists csp.codier;
     create table csp.codier ("cd_id" integer,
     
         "cd_fc_id" integer,
         "cd_code" varchar(30),
         "cd_libc" varchar(30),
         "cd_libl" text,
         "cd_chaine1" varchar(60),
         "cd_chaine2" varchar(30),
         "cd_num1" integer,
         "cd_num2" integer,
         "cd_date1" timestamp,
         "cd_date2" timestamp,
         "cd_dt_cre"timestamp,
         "cd_dt_maj" timestamp,
         "cd_qi_maj" varchar(20),
         CONSTRAINT c_pk_cd_id PRIMARY KEY (cd_id)
         );
     
     copy csp.codier from 'c:/base/BDMAP_CODIER.csv'
     WITH DELIMITER ';'
         CSV HEADER ;
     

3. Import the shape file with correctly located electrofishing stations
   • here we set the directory where shapes are stored

     cd C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\BDMAP\station arcgis
     

   • we use shp2pgsql to create sql file, see also CookBook shptopostgres for more details and links on the procedure. Gdal does not work as easily so just stick to this recipe.

     C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 bdmapv2_vf2 stationsp > stationsp.sql
     

   • We have to convert it into utf8 (as the database is posgis), in notepad, open the stationsp.sql file and convert it to utf8

     encodage>convertir en utf8
     

   • then we use psql to restore the file while connecting to BDMAP ...

     C:\"Program Files"\PostgreSQL\8.4\bin\psql -d BDMAP -h localhost -U postgres -p 5433 -f stationsputf8.sql 
     

     notes
   • use SRID 27572 if you want to keep the file in the current (LambertII) projection
   • also I'm using 4533 port and you probably use 5432...
   • this was ticket : #21

4. Now we will join the new station table (with wrong geom but new data) to the old table stationsp ( which has been properly projected by Hélène) notes
   • We first create a table using the AS SELECT command, it is populated with colums from station, and with columns from stationsp2, note the CASE WHEN syntax, which allow to use station.the_geom when stationsp.the_geom is not present in the table. So in practise we use all the old coordinates plus the new ones which have not been verified. Also noteworthy in the script is the CREATE TABLE ... WITH OIDS. The OIDS are mandatory if you want to display the table in Qgis.

     DROP TABLE IF EXISTS stationsp2;
     CREATE TABLE stationsp2 WITH OIDS AS(
       select 
       station.st_altitude,
       station.st_abcisse,
       station.st_codecsp,
       station.st_codesei,
       station.st_datearret,
       station.st_datecreation,
       station.st_distancesource,
       station.st_distancemer,
       station.st_finalite,
       station.st_imageign,
       station.st_imagedept,
       station.st_lieudit,
       station.st_limites,
       station.st_localisation,
       station.st_longueur,
       station.st_moduleia,
       station.st_cd_naturecourseau,
       station.st_ordonnee,
       station.st_penteign,
       station.st_pkaval,
       station.st_raisremp,
       station.st_sbv,
       station.st_t_janvier,
       station.st_t_juillet,
       station.st_cd_typecourseau,
       station.st_cd_tet,
       station.st_st_id,
       station.st_cm_id,
       station.st_cx_id,
       station.st_th_id,
       station.st_eh_id,
       station.st_uh_id,
       station.st_dt_cre,
       station.st_dt_maj,
       station.st_qi_maj,
       station.st_masseeau,
       stationsp.x,
       stationsp.y,
       stationsp.fnode_,
       stationsp.tnode_,
       stationsp.id_trhyd,
       stationsp.st_id,
       CASE WHEN stationsp.the_geom IS NULL THEN station.the_geom
            ELSE stationsp.the_geom
            END AS the_geom
       FROM stationsp right join bdmap.station on stationsp.st_codecsp=station.st_codecsp)
     COMMENT ON TABLE stationsp2 is 'table BDMAP extraite janvier 2010 et mise à jour avec les coordonnées reprojetées par Hélène'
     

     • note
     • the result is displayed there : [screenshot:4], [screenshot:5]
     • this work corresponds to ticket : #22