Changes between Version 1 and Version 2 of Surface en eau

Timestamp:

Jan 31, 2012 2:52:10 PM (13 years ago)

Author:

celine

Comment:

--

Surface en eau

@@ -2 +2 @@
 
 Récupération de la surface en eau (couche surfacique de Bdcarthage) sur le RHT
+
+Calcul de la surface en eau de la couche surfacique[[BR]]
+Plan d'eau, bassin, réservoir  + Ensemble de petits plans d'eau +Ecoulement d'eau = 3431.711333+15.37315377+173.866688[[BR]]
 {{{
 #!sql
-select id_drain, id_bdcarth from rht.rhtvs2 r inner join bd_carthage2011.hydrographie_surfacique bd on st_within(r.the_geom, 
-st_buffer(bd.the_geom,100)) order by id_bdcarth;
+alter table bd_carthage2011.hydrographie_surfacique add column surface numeric;
+update bd_carthage2011.hydrographie_surfacique set surface=sub.surface from (select id_bdcarth, area2d(the_geom) as surface from bd_carthage2011.hydrographie_surfacique) as sub;
+
+COMMENT ON COLUMN bd_carthage2011.hydrographie_surfacique.surface IS "Surface en m² calculée avec requête spatiale area2d"
+
+select sum(area2d(the_geom)), type from bd_carthage2011.hydrographie_surfacique group by type;
 }}}
+
+Création d'un buffer 100m pour récupérer les tronçons inclus dans la couche surfacique bdcarthage
+{{{
+#!sql
+drop table if exists bd_carthage2011.hydrographie_surfacique_100;
+create table bd_carthage2011.hydrographie_surfacique_100 as 
+        select id_bdcarth, nature, type, toponyme, st_buffer(the_geom,100) as the_geom from bd_carthage2011.hydrographie_surfacique;
+CREATE INDEX indexhydro_100 ON bd_carthage2011.hydrographie_surfacique_100
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+}}}
+
+Jointure couche surfacique bdcarthage - RHT
+{{{
+#!sql
+select id_drain, id_bdcarth from rht.rhtvs2 r inner join bd_carthage2011.hydrographie_surfacique_100 bd on st_contains(bd.the_geom, 
+r.the_geom) order by id_bdcarth;
+
+select id_drain, id_bdcarth from rht.rhtvs2 r inner join bd_carthage2011.hydrographie_surfacique_100 bd on st_within(r.the_geom, 
+bd.the_geom) order by id_bdcarth;
+}}}