Changes between Version 31 and Version 32 of import layers postgis

Timestamp:

Jul 13, 2018 12:42:34 PM (7 years ago)

Author:

maria

Comment:

--

import layers postgis

@@ -318 +318 @@
 }}}
 
-
-----------------------------------------------
--- IMPORT LAYER POSTGIS: UROLA RIVER '1003716%'
----------------------------------------------------------------------
--- 1003716 corresponds to Urola river
--- Creating a table corresponding to UROLA
--- Table of basins
-create table spain.urola_cuenca as select * from a_cuencas_rios_atl_norte where pfafrio like '1003716%';
-
-CREATE INDEX urola_cuenca_geom_idx
-  ON urola_cuenca
-  USING gist
-  (geom);
-
--- Table of rivers
-drop table if exists spain.urola_a_rios_v2;
-create table spain.urola_a_rios_v2 as select * from a_rios_v2 where pfafrio like '1003716%';
-
-CREATE INDEX urola_a_rios_v2_geom_idx
-  ON urola_a_rios_v2
-  USING gist
-  (geom);
-  
-select * from urola_a_rios_v2 limit 10;
-
-ALTER TABLE spain.urola_a_rios_v2 DROP COLUMN source;
-ALTER TABLE spain.urola_a_rios_v2 DROP COLUMN target;
-ALTER TABLE spain.urola_a_rios_v2 ADD COLUMN source integer;
-ALTER TABLE spain.urola_a_rios_v2 ADD COLUMN target integer;
-CREATE INDEX urola_a_rios_v2_source_idx ON spain.urola_a_rios_v2 (source);
-CREATE INDEX urola_a_rios_v2_target_idx ON spain.urola_a_rios_v2 (target);
-
----------------------------------------------------------------------
--- createTopology also creates spain.urola_a_rios_v2_vertices_pgr
-------------------------------------------------------------------
-set search_path to spain,public,european_wise2008;
-SELECT pgr_createTopology('urola_a_rios_v2', 0.0001, 'geom', 'gid');--1.4s
-
-
-
+== select some bassins to calculate topology ==
+
+set search_path to public, spain, european_wise2008 ; -- location of the tables in the scheme
+
+-- Rivers
+--select  * from a_rios_v2 limit 10;            -- visualize 10 first rows
+--ALTER TABLE a_rios_v2 DROP COLUMN pfafbas;    -- delete columns
+--ALTER TABLE a_rios_v2 DROP COLUMN rio;
+
+ALTER TABLE a_rios_v2 ADD COLUMN pfafbas varchar;
+ALTER TABLE a_rios_v2 ADD COLUMN rio varchar;
+
+UPDATE 
+   a_rios_v2
+SET 
+   pfafbas = SUBSTR(pfafrio, 1, 5), -- New columns are created as character!
+   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;
+
+