Changes between Version 24 and Version 25 of BDCarthage2011 into eda2.0_RHT

Timestamp:

Sep 29, 2011 11:01:15 AM (14 years ago)

Author:

celine

Comment:

--

BDCarthage2011 into eda2.0_RHT

@@ -34 +34 @@
 == Joining !BdCarthage with RHT ==
 {{{
+#!sql
 select ST_IsClosed(the_geom) from rht.rht_200 order by ST_IsClosed(the_geom);
 select ST_IsClosed(the_geom), id_bdcarth from bd_carthage2011.troncon_hydrographique order by ST_IsClosed(the_geom);  ---5 fermés
@@ -47 +48 @@
 === Bretagne ===
 {{{
+#!sql
 --- Create table uga2010.id_drain with id_drain from RHT for Bretagne 
 drop table if exists uga2010.wso;
@@ -112 +114 @@
 
 {{{
+#!sql
 ---essai1
 select r.*, id_bdcarth as gidbdc,
@@ -176 +179 @@
 }}}
 
-
+For France
+{{{
+#!sql
+drop table if exists rht.rht_150;
+create table rht.rht_150 as select id_drain, gid, st_buffer(the_geom,150) as the_geom from rht.rht_bretagne;   ---12578 ms
+CREATE INDEX indexrht_150 ON rht.rht_150
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+CREATE INDEX indexrht150
+ON rht.rht_150
+USING btree (id_drain);
+
+drop table if exists rht.rht_bdcarthage;
+create table rht.rht_bdcarthage as
+        select distinct on (id_bdcarth) id_bdcarth, min(distance) as distance, id_drain from (
+                select r.id_drain,
+                id_bdcarth,
+                ST_Distance(r.the_geom,bdc.the_geom) as distance
+        from rht.rht r join rht.rht_150 rht150 on r.id_drain=rht150.id_drain
+                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150.the_geom)
+        where nature !='Aqueduc, conduite forcée' and num_superp !=1 ) as toto
+        group by id_bdcarth, distance, id_drain
+        order by id_bdcarth ;  ---22519 lines 86526ms
+
+select count(*) from bd_carthage2011.troncon_hydrographique  ---528422 lines
+select count(*) from rht.rht  ---114601 lines
+select count(*) from rht.rht_bdcarthage --- 22519 lines
+}}}