networkReach/networkReach.sql at master · GregersP/networkReach · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
/*
--Returns the following :
Table
(
  id integer,			-- The edge's ID
  geom geometry, 		-- The geometry (is partial if factor <> 1)
  factor double precision 	-- How much of the original edge (based on length) did we take
);
*/

/*
networkReach parameters:
1: The edge table
2: The cost field in the edgeTable to base calculations on
3: The geometry field in the edgeTable
4: Id of the starting point in the vertices_tmp table
5: Network reach distance (in units of the edge table cost column)
*/
CREATE OR REPLACE FUNCTION parkering_routing.networkReachPartial(edgeTable regclass, costfield character varying, geomfield character varying, startnode integer, networkReachDistance double precision)
RETURNS TABLE(id integer, geom geometry, factor double precision) AS
$BODY$
  DECLARE
	-- nothing to declare here, we now return a table instead of declaring types
  BEGIN

    -- Create a temporary table for the network whilst we process it
    -- DROP on commit so we do not get problems reusing it again in this session
    CREATE TEMP TABLE tempNetwork
    (
      id integer NOT NULL,
      geom geometry NOT NULL,
      factor double precision NOT NULL
    )
    ON COMMIT DROP;


    EXECUTE '
    INSERT INTO tempNetwork(id, geom, factor)
    SELECT et.id, et.' || format('%s',geomfield) || ' AS geom, 1 as factor
    FROM
        (SELECT id1,cost from pgr_drivingDistance(
          ''SELECT id, source, target, ' || quote_ident(costField) || ' AS cost FROM ' || format('%s',edgeTable) || ''',
          ' || format('%s',startnode) || ',' || format('%s', networkReachDistance) || ',false,false)
         ) firstPath
    CROSS JOIN
        (SELECT id1,cost from pgr_drivingDistance(
          ''SELECT id, source, target, ' || quote_ident(costField) || ' AS cost FROM ' || format('%s',edgeTable) || ''',
          ' || format('%s',startnode) || ',' || format('%s', networkReachDistance) || ',false,false)
         ) secondPath
    INNER JOIN ' || format('%s',edgeTable) || ' et
    ON firstPath.id1 = et.source
    AND secondPath.id1 = et.target;';

    EXECUTE '
    INSERT INTO tempNetwork(id, geom, factor)
    SELECT allNodes.id, st_line_substring(allNodes.geom, 0.0, (' || format('%s', networkReachDistance) || '-allNodes.distance)/allNodes.Cost), (' || format('%s', networkReachDistance) || '-allNodes.distance)/allNodes.Cost AS factor
    FROM
      (SELECT reachNodes.id1, et.id, et.cost, reachNodes.cost distance, et.' || format('%s',geomfield) || '
       FROM
         (SELECT id1, cost FROM pgr_drivingDistance(
         ''SELECT id, source, target, ' || quote_ident(costField) || ' AS cost FROM ' || format('%s',edgeTable) || ''',
         ' || format('%s',startnode) || ',' || format('%s', networkReachDistance) || ',false,false)
         ) reachNodes
      JOIN (SELECT p.id, p.target, p.source, p.' || quote_ident(costField) || ' AS cost, p.geom FROM ' || format('%s',edgeTable) || ' p) et ON reachNodes.id1 = et.source
      ORDER BY reachNodes.id1
     ) allNodes
    FULL OUTER JOIN tempNetwork
    ON tempNetwork.id = allNodes.id
    WHERE tempNetwork.id IS NULL;';

    EXECUTE '
    INSERT INTO tempNetwork(id, geom, factor)
    SELECT allNodes.id, st_line_substring(allNodes.geom,1-((' || format('%s', networkReachDistance) || '-allNodes.distance)/allNodes.Cost),1), (' || format('%s', networkReachDistance) || '-allNodes.distance)/allNodes.Cost AS factor
    FROM
      (SELECT reachNodes.id1, et.id, et.cost, reachNodes.cost distance, et.' || format('%s',geomfield) || '
       FROM
         (SELECT id1, cost FROM pgr_drivingDistance(
         ''SELECT id, source, target, ' || quote_ident(costField) || ' AS cost FROM ' || format('%s',edgeTable) || ''',
         ' || format('%s',startnode) || ',' || format('%s', networkReachDistance) || ',false,false)
         ) reachNodes
       JOIN (SELECT p.id, p.target, p.source, p.' || quote_ident(costField) || ' AS cost, p.' || format('%s',geomfield) || ' FROM ' || format('%s',edgeTable) || ' p) et ON reachNodes.id1 = et.target
       ORDER BY reachNodes.id1
      ) allNodes
    FULL OUTER JOIN tempNetwork
    ON tempNetwork.id = allNodes.id
    WHERE tempNetwork.id IS NULL;';


    RETURN QUERY SELECT t.id, t.geom, t.factor FROM tempNetwork t;

END;
$BODY$
  LANGUAGE plpgsql;