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
}}}


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

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