back to first page..
back to RHT

Integrating data into postgre (rht.shp and Attributs_RHT_fev_2011.txt)

--Celine
d:
cd D:\CelineJouanin\RHT_Estimkart
C:\"Program Files"\PostgreSQL\8.4\bin\shp2pgsql -s 27582 -c -g the_geom -W LATIN1 -I rht.shp rht > rht.sql 
We have to convert it into utf8 (as the database is posgis), in notepad, open the uga.sql file and convert it to utf8
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d eda2.0 -h localhost -U postgres -p 5432 -f rhtutf8.sql 

--Creation du schema rht
CREATE SCHEMA rht;
ALTER TABLE rht SET SCHEMA rht;

--Changement du type de projection :  NTF_France_II_degrees  (srid 27582) --> ETRS1989-LAEA (srid 3035)
ALTER TABLE rht.rht DROP CONSTRAINT enforce_srid_the_geom;
UPDATE rht.rht SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE rht.rht ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

ALTER TABLE rht.rht DROP CONSTRAINT rht_pkey
ALTER TABLE rht.rht ADD CONSTRAINT  pk_id_drain PRIMARY KEY (id_drain);

-- Create an index on rht and attributs tables
CREATE INDEX indexrht ON rht.rht
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

Attribut Integrating (Attributs_RHT_fev_2011.txt) See in CookBook export access to integrate the database into postgre from ACCESS (it's usefull when you got lots of variables in the table) This allows us to create the table quickly.

drop table  if exists rht.attributs_rht_fev_2011;
CREATE TABLE rht.attributs_rht_fev_2011
(
  ID_DRAIN integer,
  TJAN_MOY double precision,
  TJUIL_MOY double precision,
  NONCALC integer,
  REGION_CSP character varying(255),
  D_SOURCE character varying(255),
  ALTITUDE character varying(255),
  SFBVU double precision,
  LONG_ integer,
  STRAHLER integer,
  SURF_BVICinf character varying(255),
  SURF_BV double precision,
  SURF_BVICsup character varying(255),
  PENTEICinf character varying(255),
  PENTE character varying(255),
  PENTEICsup character varying(255),
  MINQMXICinf character varying(255),
  MINQMX double precision,
  MINQMXICsup character varying(255),
  MODULE_ICinf character varying(255),
  MODULE_ double precision,
  MODULE_ICsup character varying(255),
  LICinf character varying(255),
  L character varying(255),
  LICsup character varying(255),
  HICinf character varying(255),
  H character varying(255),
  HICsup character varying(255),
  PESP_ABL character varying(255),
  PESP_ANG character varying(255),
  PESP_BAF character varying(255),
  PESP_BAM character varying(255),
  PESP_BLN character varying(255),
  PESP_BOU character varying(255),
  PESP_BRB character varying(255),
  PESP_BRO character varying(255),
  PESP_CAS character varying(255),
  PESP_CCO character varying(255),
  PESP_CHA character varying(255),
  PESP_CHE character varying(255),
  PESP_EPI character varying(255),
  PESP_EPT character varying(255),
  PESP_GAR character varying(255),
  PESP_GOU character varying(255),
  PESP_GRE character varying(255),
  PESP_HOT character varying(255),
  PESP_LOF character varying(255),
  PESP_LOT character varying(255),
  PESP_LPP character varying(255),
  PESP_OBR character varying(255),
  PESP_PCH character varying(255),
  PESP_PER character varying(255),
  PESP_PES character varying(255),
  PESP_ROT character varying(255),
  PESP_SAN character varying(255),
  PESP_SAT character varying(255),
  PESP_SPI character varying(255),
  PESP_TAN character varying(255),
  PESP_TOX character varying(255),
  PESP_TRF character varying(255),
  PESP_VAI character varying(255),
  PESP_VAN character varying(255)
);

copy rht.attributs_rht_fev_2011  from 'D:/CelineJouanin/RHT_Estimkart/Attributs_RHT_fev_2011_UTF8.csv' with csv delimiter as ';' header; 
UPDATE rht.attributs_rht_fev_2011 SET id_drain=240000 where id_drain=223988 and surf_bv=14.3;  ---probleme de doublons
alter table rht.attributs_rht_fev_2011 add constraint  pk_id_drain primary key (id_drain);
set client_encoding to 'latin1';