Changes between Version 26 and Version 27 of CookBook join ROE_RHT

Timestamp:

Nov 2, 2011 11:12:21 AM (13 years ago)

Author:

celine

Comment:

--

CookBook join ROE_RHT

@@ -187 +187 @@
 }}}
 
-New version de rht_bdcarthage_roev2 avec données pre_ice_v2
+
+Données pre_ice_2  --- problème avec les hauteurs de chute non renseignées.
+{{{
+select count(*) from roe_v2.pre_ice_v2 where hauteur_chute='-999';  ---31611 lignes non renseignées
+select * from roe_v2.pre_ice_v2 where hauteur_chute='-999' and hauteur_terrain>0;   ---663 lignes renseignés
+}}}
+
+New version de rht_bdcarthage_roev2 avec données pre_ice_v2 et rht_topology
 {{{
 drop table if exists rht.rht_bdcarthage_roev2;
 create table rht.rht_bdcarthage_roev2 as
-select geo.*, bdc.*, roe.the_geom, roe.nom, roe.typecd, roe.typenom, roe.stypecd, roe.stypenom, roe.stanom, roe.etatcd, roe.source,
-ice.etatnom as etatnomice, ice.hauteur_terrain, ice.hauteur_chute, ice.note_fr_anguille from roe_v2.roe_geo_v2 as geo
+select geo.*, bdc.*, topo.fnode, topo.tnode, topo.length, topo.nextdownid, topo.noeudmer, topo.cumnbbar, topo.dmer, topo.id_draintopo,
+roe.the_geom, roe.nom, roe.typecd, roe.typenom, roe.stypecd, roe.stypenom, roe.stanom, roe.etatcd, roe.source,
+ice.etatnom as etatnomice, ice.hauteur_terrain, ice.hauteur_chute, ice.note_fr_anguille, ice.usage1, ice.fpi1 from roe_v2.roe_geo_v2 as geo
 join  roe_v2.roe_v2 as roe on roe.id_roe=geo.id_roe
 join rht.rht_bdcarthage2 bdc on bdc.id_bdcarth=cast(geo.idtrcart as integer)
-join roe_v2.pre_ice_v2 as ice on roe.id_roe=ice.id_roe; ---49212 lines
-}}}
+join roe_v2.pre_ice_v2 as ice on roe.id_roe=ice.id_roe
+join rht.rht_topology as topo on bdc.id_drain=topo.id_drain; ---49212 lines
+
+alter table rht.rht_bdcarthage_roev2 add column nb_dams integer;
+update rht.rht_bdcarthage_roev2 set nb_dams=1;
+
+---Clause group by par id_bdcarthage
+select id_drain, count(nb_dams) as nb_dams2 from rht.rht_bdcarthage_roev2 group by id_bdcarth, id_drain;
+
+}}}