back to start page [[..]]

= Import layer postgis =

== first fixing a problem with the database ==

At first the following failed and I had to fix a problem in the database

~~ spatial_ref_sys is empty in the eda.0 database => collecting it from another database ~~

{{{#!sh
pg_dump -U postgres -f "spatial_ref_sys.sql" --data-only --table public.spatial_ref_sys --inserts --verbose eda2.2
psql -U postgres -f spatial_ref_sys.sql eda2.0
}}}

this does not work, it's a known bug
https://trac.osgeo.org/postgis/ticket/1815

I'm using the sql file from the postgis installation. For this we will try on a subset in the oria

{{{#!sh
psql -U postgres -f "C:/Program Files/PostgreSQL/9.6/share/contrib/postgis-2.4/spatial_ref_sys.sql" eda2.0
}}}

== First trial with two layers ==

Our objective is to test the best way to route the river network

{{{#!sql
-- first create a new schema for spain
psql -U postgres -c "create schema spain" eda2.0

-- shp2pgsql with create index option
-- Subcuencas de ríos completos clasificadas según Pfafstetter modificado
shp2pgsql -s 4258 -I A_cuencas_rios_Atl_Norte.shp spain.a_cuencas_rios_atl_norte > a_cuencas_rios_atl_norte.sql
psql -U postgres -f a_cuencas_rios_atl_norte.sql eda2.0

-- Tramos de ríos de España clasificados según Pfafstetter modificado
shp2pgsql -s 4258 -W LATIN1 -I A_Rios_v2.shp spain.a_rios_v2 > a_rios_v2.sql
psql -U postgres -f a_rios_v2.sql eda2.0
}}}

in fact the system of projection indicated on the inspire layers is wrong it is 25830
http://www.mapama.gob.es/es/cartografia-y-sig/ide/directorio_datos_servicios/caracteristicas_wms.aspx
{{{#!sql
select UpdateGeometrySRID('spain','a_cuencas_rios_atl_norte', 'geom', 25830 );
select UpdateGeometrySRID('spain','a_rios_v2', 'geom', 25830 );
}}}


Now we need to select only the Oria and neighbouring rivers for our trial


{{{#!sql
set search_path to public, spain ;-- using this I will not have to say that the tables are in the spain scheme
select  * from a_cuencas_rios_atl_norte limit 10
select  * from a_cuencas_rios_atl_norte where nom_rio_1 like '%ORIA%';
}}}

||802F802||"A"||172988||"1003714"||"ORIA IBAIA"||""||2707||2707||"A"||175359||"10037294"||...|| 

10037 corresponds to the Biscay coast of Spain, 1003714 correspond à Oria

{{{#!sql
select  * from a_cuencas_rios_atl_norte where pfafrio like '10037%';
select  * from a_cuencas_rios_atl_norte where pfafrio like '1003714%';
}}}

Creating a table corresponding to ORIA

Table of basins

{{{#!sql
drop table if exists spain.oria_cuenca;
create table spain.oria_cuenca as select * from a_cuencas_rios_atl_norte where pfafrio like '1003714%';--422

CREATE INDEX oria_cuenca_geom_idx
  ON oria_cuenca
  USING gist
  (geom);
}}}

Table of rivers

{{{#!sql
drop table if exists spain.oria_a_rios_v2;
create table spain.oria_a_rios_v2 as select * from a_rios_v2 where pfafrio like '1003714%';--422

CREATE INDEX oria_a_rios_v2_geom_idx
  ON oria_a_rios_v2
  USING gist
  (geom);
}}}

[[Image(source:eda/data/Docs/trac/sudoang/oria.png,200px)]]


To be able to explore the topology we could use pfafstetter

{{{#!sql
/*-----------------------------------------
Function to get all segments downstream from one one in the same subbasin
Problem when the subbasin changes it is difficult to go downstream
-----------------------------------------*/
CREATE OR REPLACE FUNCTION downstream_segments(_id text) 
RETURNS TABLE(pfafcuen character varying(254), lngtramo_m numeric) AS
$$
BEGIN
	RAISE NOTICE 'downstream segment for %', _id;    -- prints the selected segment
	RETURN QUERY   
	     SELECT r.pfafcuen, r.lngtramo_m from  oria_a_rios_v2 r 
		WHERE
		 substring(_id::text,1,char_length(_id::text)-1)= 
		   substring(r.pfafcuen::text,1,char_length(r.pfafcuen::text)-1) 
		AND
		 _id>r.pfafcuen
		AND
                 (r.pfafcuen::numeric % 2) = 1 
               order by r.pfafcuen::numeric DESC;  -- even number		
END
$$
  LANGUAGE plpgsql VOLATILE;
select * from downstream_segments('10037146457'); 
}}}

this query returns the three odd nodes dowstream from '10037146457'

||pfafcuen||	pfafrio||
||10037146451||100371464||
||10037146453||100371464||
||10037146455||100371464||
Problem from that step we need to go to another number

10037146451 => 1003714643

Before trying too much the following problem arises [[BR]]
where ||1037146(47)|| is immediately upstream from ||1037146(457)|| [[BR]]
[[Image(source:eda/data/Docs/trac/sudoang/network_topology.PNG,400px)]][[BR]]