Changes between Version 24 and Version 25 of import layers postgis

Timestamp:

Jun 23, 2018 10:37:26 PM (7 years ago)

Author:

cedric

Comment:

--

import layers postgis

@@ -95 +95 @@
 [[Image(source:eda/data/Docs/trac/sudoang/oria.png,200px)]]
 
-
+== Trials for the pfafsteter system ==
 To be able to explore the topology we could use pfafstetter
 
@@ -148 +148 @@
 where `1037146(47)` is immediately upstream from `1037146(457)` [[BR]]
 [[Image(source:eda/data/Docs/trac/sudoang/network_topology.PNG,400px)]][[BR]]
+
+== pg_routing ==
+
+https://docs.pgrouting.org/2.5/en/pgRouting-concepts.html#description-of-the-edges-sql-query-for-dijkstra-like-functions
+{{{#!slq
+-------------------------------------
+-- pgrouting installation and configuration on table
+CREATE EXTENSION pgrouting;
+ALTER TABLE spain.oria_a_rios_v2 DROP COLUMN source;
+ALTER TABLE spain.oria_a_rios_v2 DROP COLUMN target;
+ALTER TABLE spain.oria_a_rios_v2 ADD COLUMN source integer;
+ALTER TABLE spain.oria_a_rios_v2 ADD COLUMN target integer;
+CREATE INDEX oria_a_rios_v2_source_idx ON spain.oria_a_rios_v2 (source);
+CREATE INDEX oria_a_rios_v2_target_idx ON spain.oria_a_rios_v2 (target);
+
+---------------------------------------------------------------------
+-- createTopology also creates spain.oria_a_rios_v2_vertices_pgr
+------------------------------------------------------------------
+set search_path to spain,public;
+SELECT pgr_createTopology('oria_a_rios_v2', 0.0001, 'geom', 'gid');--1.4s
+---------------------------------------------------------------------
+-- routing this is done using nodes, FALSE is necessery otherwise fails (using a directed path=
+------------------------------------------------------------------
+SELECT seq, node, edge, agg_cost, gid, pfafcuen
+FROM pgr_dijkstra(
+   'SELECT gid as id, source, target, st_length(geom) as cost FROM oria_a_rios_v2',
+   5,
+   540,
+  FALSE
+)  pt
+JOIN oria_a_rios_v2 r ON pt.edge = r.gid order by seq;
+}}}
+||"seq"||"node"||"edge"||"agg_cost"||"gid"||"pfafcuen"||
+||1||5||774||0||774||"1003714113"||
+||2||28||783||340.239109235141||783||"1003714115"||
+||3||33||916||434.71909815275||916||"1003714117"||
+||4||61||920||1381.42299559645||920||"100371413"||
+||5||62||956||1931.41349601216||956||"10037141511"||
+||6||54||992||2751.07786782504||992||"10037141513"||
+||7||53||1023||3006.69704002442||1023||"10037141515"||
+||8||16||1028||3923.51604452287||1028||"10037141517"||
+||9||15||962||4550.98432547035||962||"10037141531"||
+||10||47||879||5241.61915518433||879||"10037141533"||
+||11||39||880||5777.24646955956||880||"10037141535"||