Changes between Version 35 and Version 36 of import layers postgis

Timestamp:

Jul 13, 2018 1:43:29 PM (7 years ago)

Author:

maria

Comment:

--

import layers postgis

@@ -347 +347 @@
 == Select some bassins to calculate topology ==
 {{{#!sh
-set search_path to public, spain, european_wise2008 -- location of the tables in the scheme
-
+set search_path to public, spain, european_wise2008 ; -- location of the tables in the scheme
+
+-- First trial with two layers ("a_cuencas_rios_atl_norte" and "a_rios_v2")
+-- Function to select some bassins to calculate topology
 -- Rivers
 --select  * from a_rios_v2 limit 10;            -- visualize 10 first rows
@@ -363 +365 @@
    rio = SUBSTR(pfafrio, 6, 2)
 
--- Test with '10037%' that  corresponds to the Biscay coast of Spain:
-create table spain.biscay_coast_a_rios_v2 as select * from a_rios_v2 where pfafrio like '10037%';
-
 --SELECT *, pfafrio, (SUBSTR(pfafrio, 1, 5) = '10037%') FROM a_rios_v2; -- create a column boolean to say TRUE/FALSE 
 --SELECT *, SUBSTR(pfafrio, 1, 5) "pfafbas", SUBSTR(pfafrio, 6, 2) "rios"
 --FROM a_rios_v2;
+
+
+-- Test with '10037%' that  corresponds to the Biscay coast of Spain:
+-- Table of Basins
+drop table if exists biscay_coast_cuenca;
+create table biscay_coast_cuenca as select * from a_cuencas_rios_atl_norte where pfafrio like '10037%';
+
+CREATE INDEX biscay_coast_cuenca_geom_idx
+  ON biscay_coast_cuenca
+  USING gist
+  (geom);
+
+-- Table of rivers
+drop table if exists biscay_coast_a_rios_v2;
+create table biscay_coast_a_rios_v2 as select * from a_rios_v2 where pfafrio like '10037%';
+
+CREATE INDEX biscay_coast_a_rios_v2_geom_idx
+  ON biscay_coast_a_rios_v2
+  USING gist
+  (geom);
+
 }}}
 
 Check in QGis the rivers created according to the 6th and 7th position of code in pfafrio [[BR]]
-[[Image(source:eda/data/Docs/trac/sudoang/test_biscay_coast.png,400px)]]
+[[Image(source:eda/data/Docs/trac/sudoang/test_biscay_coast.png,400px)]] [[BR]]
+
+pgRouting, configuration on table and ltree
+{{{#!sh
+ALTER TABLE biscay_coast_a_rios_v2 DROP COLUMN if exists source;
+ALTER TABLE biscay_coast_a_rios_v2 DROP COLUMN if exists target;
+ALTER TABLE biscay_coast_a_rios_v2 ADD COLUMN source integer;
+ALTER TABLE biscay_coast_a_rios_v2 ADD COLUMN target integer;
+CREATE INDEX biscay_coast_a_rios_v2_source_idx ON biscay_coast_a_rios_v2 (source);
+CREATE INDEX biscay_coast_a_rios_v2_target_idx ON biscay_coast_a_rios_v2 (target);
+
+---------------------------------------------------------------------
+-- createTopology also creates biscay_coast_a_rios_v2_vertices_pgr
+------------------------------------------------------------------
+SELECT pgr_createTopology('biscay_coast_a_rios_v2', 0.0001, 'geom', 'gid');
+}}}
+
+Get error creating topology