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
   • we use shp2pgsql to create sql file, see also CookBook shptopostgres
   • use SRID 27572 if you want to keep the file in the current (LambertII) projection

     cd C:\Documents and Settings\cedric\Mes documents\Migrateur\eda\BDMAP\station arcgis
     C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 3035 bdmapv2_vf2 stationsp > stationsp.sql
     C:\"Program Files"\PostgreSQL\8.4\bin\psql -d BDMAP -h localhost -U postgres -p 5433 -f stationsp.sql 
     ... ca plante, je pense qu'il faut convertir en utf8, dans notepad encodage>convertir en utf8
     C:\"Program Files"\PostgreSQL\8.4\bin\psql -d BDMAP -h localhost -U postgres -p 5433 -f stationsputf8.sql