Changes between Version 4 and Version 5 of PATE France

Timestamp:

Apr 2, 2012 10:41:25 AM (13 years ago)

Author:

celine

Comment:

--

PATE France

@@ -47 +47 @@
 where sub.id_roe=pate_france.id_roe; --3310
 }}}
+
+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 lignes   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)
+}}}
+