Changes between Version 32 and Version 33 of BDCarthage2011 into eda2.0_RHT

Timestamp:

Oct 7, 2011 3:40:00 PM (14 years ago)

Author:

celine

Comment:

--

BDCarthage2011 into eda2.0_RHT

@@ -182 +182 @@
 select count(*) from rht.rht  ---114601 lines
 select count(*) from rht.rht_bdcarthage --- 393393 lines
+
+select id_drain from rht.rht_bdcarthage where id_drain not in select id_drain from rht.rht_bdcarthage;  ---
+
+---Changement de buffer
+drop table if exists rht.rht_150b;
+create table rht.rht_150b as select id_drain, gid, st_buffer(the_geom,150,endcap=flat) as the_geom from rht.rht;   ---12578 ms
+CREATE INDEX indexrht_150b ON rht.rht_150b
+  USING GIST ( the_geom GIST_GEOMETRY_OPS );
+CREATE INDEX indexrht150b
+ON rht.rht_150b
+USING btree (id_drain);
+
+drop table if exists rht.rht_bdcarthage2;
+create table rht.rht_bdcarthage2 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_150b rht150b on r.id_drain=rht150b.id_drain
+                        join    bd_carthage2011.troncon_hydrographique bdc on st_intersects(bdc.the_geom,rht150b.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 ;  ---393393 lines
+CREATE INDEX indexrhtbdc2
+ON rht.rht_bdcarthage2
+USING btree (id_bdcarth);
+CREATE INDEX indexrhtbdc22
+ ON rht.rht_bdcarthage2
+ USING btree
+ (id_drain);
 }}}