back to first page..

Integrate roe_pate_france.csv (from Virginie 23 mars 2012) into access data and export ODBC link

alter table "PATE_France" rename to pate_france;
alter table pate_france set schema rht;

alter table rht.pate_france rename column "nomCart" to nomCart;
alter table rht.pate_france rename column "nomTopo" to nomTopo;
alter table rht.pate_france rename column "commNom" to commNom;
alter table rht.pate_france rename column "Id_ROE" to Id_ROE;
alter table rht.pate_france rename column "Nom" to Nom;
alter table rht.pate_france rename column "XL93" to XL93;
alter table rht.pate_france rename column "YL93" to YL93;
alter table rht.pate_france rename column "typeNom" to typeNom;
alter table rht.pate_france rename column "stypeNom" to stypeNom;
alter table rht.pate_france rename column "staNom" to staNom;
alter table rht.pate_france rename column  "deptNom" to deptNom;
alter table rht.pate_france rename column "note_fr_Anguille" to note_fr_Anguille;

alter table rht.pate_france add column a_conserver boolean default TRUE;


SELECT AddGeometryColumn ( 'rht','pate_france','the_geom',3035,'POINT',2);
update rht.pate_france set the_geom=obstacle.the_geom from roe_v3.obstacle
 where obstacle.id_roe=pate_france.id_roe; 

select count(*) from rht.pate_france where the_geom is null;  ---0

--correction de la requete précédente pour utiliser la version roev2 en attendant la version v3 projettée
update rht.pate_france set a_conserver=FALSE where stanom like '%Obso%'--61
update rht.pate_france set the_geom=NULL;
update rht.pate_france set the_geom=rhtvs2_roev2.the_geom from rht.rhtvs2_roev2 where rhtvs2_roev2.id_roe=pate_france.id_roe; --3310
select * from rht.pate_france where the_geom is null; --198


select count(*) from rht.pate_france; --3508
-- je rajoute l'id_drain dans la table rht.pate_france
alter table rht.pate_france add column id_drain integer;
update rht.pate_france set id_drain=sub.id_drain from (
select rhtvs2_roev2.id_drain, rhtvs2_roev2.id_roe from rht.pate_france join rht.rhtvs2_roev2 on rhtvs2_roev2.id_roe=pate_france.id_roe) sub
where sub.id_roe=pate_france.id_roe; --3310

Avec la version 3 d'obstacle

alter table rht.pate_france add column id_drain integer;
update rht.pate_france set id_drain=null;

update rht.pate_france set id_drain=sub.id_drain from (
select rhtvs2_roev3.id_drain, rhtvs2_roev3.id_roe from rht.pate_france join rht.rhtvs2_roev3 on rhtvs2_roev3.id_roe=pate_france.id_roe) sub
where sub.id_roe=pate_france.id_roe; --3390 lines

update rht.pate_france set the_geom=NULL;
update rht.pate_france set the_geom=rhtvs2_roev3.the_geom from rht.rhtvs2_roev3 where rhtvs2_roev3.id_roe=pate_france.id_roe; --3390 lines

Version obstacle version 3

DROP TABLE IF EXISTS rht.rhtvs2_roev3;
CREATE TABLE rht.rhtvs2_roev3 as (
        SELECT distinct on (id_roe) id_roe, id_drain, min(distance) as distance, the_geom FROM (
               SELECT id_roe, id_drain ,CAST(distance(r.the_geom, s.the_geom) as  decimal(15,1)) as distance, s.the_geom 
               FROM roe_v3.obstacle As s
               INNER JOIN  rht.rhtvs2 r ON ST_DWithin(r.the_geom, s.the_geom,300)
               WHERE s.the_geom IS NOT NULL
               ORDER BY id_roe) AS sub 
        GROUP BY id_roe, distance,id_drain, the_geom  ---49269 lines
); 
alter table rht.rhtvs2_roev3 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 '', 'rht', 'rhtvs2_roev3', 'the_geom', ST_CoordDim(the_geom), ST_SRID(the_geom), GeometryType(the_geom)
FROM rht.rhtvs2_roev3 LIMIT 1;

-- creation d'index, clé primaire, et constraintes qui vont bien
alter table rht.rhtvs2_roev3 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table rht.rhtvs2_roev3 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL);
alter table rht.rhtvs2_roev3 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
alter table rht.rhtvs2_roev3 ADD CONSTRAINT pk_idroe PRIMARY KEY(id);
alter table rht.rhtvs2_roev3 drop CONSTRAINT pk_idroe;
CREATE INDEX indexroev2_rhtvs3 ON rht.rhtvs2_roev3
  USING GIST ( the_geom GIST_GEOMETRY_OPS );

---Ajout des colonnes height et score
alter table rht.rhtvs2_roev3 add column height integer;
alter table rht.rhtvs2_roev3 add column score integer;
update rht.rhtvs2_roev3 set height=1;
update rht.rhtvs2_roev3 set score=1;


---Ajout de la colonne nbdams =1
alter table rht.rhtvs2_roev3 add column nbdams integer;
update rht.rhtvs2_roev3 set nbdams=1;

---Clause group by par id_drain
select id_drain, count(nbdams) as nbdams from rht.rhtvs2_roev3 group by id_drain;

drop table if exists rht.rhtvs2_roev3_nbdams;
create table rht.rhtvs2_roev3_nbdams as
select id_drain, count(nbdams) as nbdams from rht.rhtvs2_roev3 as r group by id_drain;
alter table rht.rhtvs2_roev3_nbdams add column height integer;
alter table rht.rhtvs2_roev3_nbdams add column score integer;
update rht.rhtvs2_roev3_nbdams set height=1;
update rht.rhtvs2_roev3_nbdams set score=1;

select * from roe_v3.obstacle where idtrcart is null  ---2960 lines   etanom validé
select count(etatnom), etatnom from roe_v3.obstacle group by etatnom   --- 57745 (avant 54057) obstacles marqués comme validé.
select * from rht.rhtvs2_roev3 where id_roe in (select id_roe from roe_v3.obstacle where idtrcart is null)  --- 1895 (avant 1710) obstacles

alter table rht.rhtvs2_roev3 add column bdc boolean default FALSE;
update rht.rhtvs2_roev3 set bdc=TRUE where id_roe in (select id_roe from roe_v3.obstacle where idtrcart is null)

Export table

cd d:\CelineJouanin\export_table\version2
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2_roev3 eda2.0_RHT> rhtvs2_roev3.sql
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5432 -t rht.rhtvs2_roev3_nbdams eda2.0_RHT> rhtvs2_roev3_nbdams.sql