Changes between Version 27 and Version 28 of import layers postgis

Timestamp:

Jul 11, 2018 1:10:41 PM (7 years ago)

Author:

cedric

Comment:

--

import layers postgis

@@ -197 +197 @@
 {{{#!sql
 CREATE EXTENSION ltree;
-}}}
+ALTER TABLE spain.oria_a_rios_v2 ADD COLUMN chemin ltree;
+CREATE INDEX chemin_gist_oria_idx ON spain.oria_a_rios_v2 USING GIST(chemin);
+CREATE INDEX chemin_oria_idx ON spain.oria_a_rios_v2 USING btree(chemin);
+
+
+
+-- WHAT IS THE MOST DOWNSTREAM SEGMENT ?
+
+-- reprojection of the wise layer
+-- still a bit far
+create view european_wise2008.coastal_waters_25830 as select gid, code, name, st_transform(the_geom,25830) as geom from
+european_wise2008.costal_waters
+where cty_id='ES';
+
+st_distance(c.geom, r.geom)
+
+
+-- renvoit l'id du point le plus proche de la mer
+select id from (
+select v.id, st_distance(c.geom, v.the_geom) as dist  from 
+european_wise2008.coastal_waters_25830 c JOIN
+spain.oria_a_rios_v2_vertices_pgr v ON
+ST_DWithin(c.geom, v.the_geom, 1000)) jointure
+order by dist limit 1
+
+
+--calcule le nombre de lignes de la table
+Select count(*) from spain.oria_a_rios_v2 ;
+select * from spain.oria_a_rios_v2 limit 10;
+select * from spain.oria_a_rios_v2 where target=6
+
+-- this query returns the ltree 
+"1003714113.1003714115.1003714117(...)"
+
+SELECT text2ltree(string_agg(pfafcuen, '.')) AS pfafcuenlist  
+FROM (SELECT pfafcuen FROM pgr_dijkstra(
+   'SELECT gid as id, source, target, st_length(geom) as cost FROM spain.oria_a_rios_v2',
+   5,
+   540,
+  FALSE
+)  pt
+JOIN spain.oria_a_rios_v2 r ON pt.edge = r.gid order by seq) sub
+
+
+-- function to create ltree from two nodes from and to
+DROP function get_path(integer,integer);
+CREATE OR REPLACE FUNCTION spain.get_path(_from integer, _to integer)
+RETURNS SETOF ltree AS
+$$
+BEGIN
+RETURN QUERY 
+SELECT text2ltree(string_agg(pfafcuen, '.')) AS pfafcuenlist  
+FROM (SELECT pfafcuen FROM pgr_dijkstra(
+   'SELECT gid as id, source, target, st_length(geom) as cost FROM spain.oria_a_rios_v2',
+   _from,
+   _to,
+  FALSE
+)  pt
+JOIN spain.oria_a_rios_v2 r ON pt.edge = r.gid order by seq) sub;
+
+END
+$$
+LANGUAGE plpgsql VOLATILE;
+
+select spain.get_path(5,540);
+
+
+
+
+SELECT seq, node, edge, agg_cost, gid, pfafcuen
+FROM pgr_dijkstra(
+   'SELECT gid as id, source, target, st_length(geom) as cost FROM spain.oria_a_rios_v2',
+   5,
+   540,
+  FALSE
+)  pt
+JOIN spain.oria_a_rios_v2 r ON pt.edge = r.gid order by seq;
+
+UPDATE spain.oria_a_rios_v2 set chemin=get_path(6,540) where source=540
+
+
+/*
+didn't work but keep it for later checking
+-- rio the most downstream
+with pfaf_length as (
+select length(pfafrio) l,pfafrio from spain.oria_a_rios_v2)
+select pfafrio from pfaf_length order by l limit 1
+-- removing the rio
+
+with shortened_pfaf as (
+select regexp_replace(pfafcuen,'1003714','') pfaf_short from spain.oria_a_rios_v2)
+select substring(pfaf_short,1,3) from shortened_pfaf
+
+select substring (1,2,
+*/
+select format('Select count(*) FROM %1$I','toto') 
+/*
+TODO test that the routing algorythm always set the most downstream point as a target ...
+If so develop a formal testing within the function
+*/
+
+CREATE TYPE resum AS (source int, pfafcuen character varying(254));
+
+drop if exists function spain.create_path(regclass,integer);
+CREATE OR REPLACE FUNCTION spain.create_path(_nametable regclass, downstream_point integer) 
+RETURNS integer AS
+$$
+DECLARE
+nrow int;
+row resum%rowtype;
+BEGIN
+EXECUTE 'Select count(*) FROM '|| _nametable INTO nrow;
+ for row in EXECUTE 'select source, pfafcuen from '|| _nametable 
+   LOOP  
+                EXECUTE 'UPDATE '||_nametable||' t set chemin=
+                spain.get_path('||downstream_point||',source) where t.source=source';
+                RAISE NOTICE 'Calculation for %', row.pfafcuen;                
+                end loop;
+return nrow;
+
+END
+$$
+LANGUAGE plpgsql VOLATILE;
+
+
+select spain.create_path('spain.oria_a_rios_v2',6);
+select source from spain.oria_a_rios_v2 where pfafcuen='10037141142'
+select spain.get_path(6,40)
+Select count(*) FROM spain.oria_a_rios_v2
+
+select * from downstream_segments('10037146457');
+
+Select * FROM spain.oria_a_rios_v2 limit 10
+
+}}}