# pgRouting Manual (2.5)

## pgr_withPointsDD - Proposed

## Name

`pgr_withPointsDD` - Returns the driving distance from a starting point.

Warning

Proposed functions for next mayor release.

- They are not officially in the current release.
- They will likely officially be part of the next mayor release:
  - The functions make use of ANY-INTEGER and ANY-NUMERICAL
  - Name might not change. (But still can)
  - Signature might not change. (But still can)
  - Functionality might not change. (But still can)
  - pgTap tests have being done. But might need more.
  - Documentation might need refinement.

Boost Graph Inside

Availability: 2.2.0

## Synopsis

Modify the graph to include points and using Dijkstra algorithm, extracts all the nodes and points that have costs less than or equal to the value `distance` from the starting point. The edges extracted will conform the corresponding spanning tree.

## Signature Summary

    pgr_withPointsDD(edges_sql, points_sql, start_vid, distance)
    pgr_withPointsDD(edges_sql, points_sql, start_vid, distance, directed, driving_side, details)
    pgr_withPointsDD(edges_sql, points_sql, start_vids, distance, directed, driving_side, details, equicost)
    RETURNS SET OF (seq, node, edge, cost, agg_cost)

## Signatures

### Minimal Use

The minimal signature:
  - Is for a directed graph.
  - The driving side is set as b both. So arriving/departing to/from the point(s) can be in any direction.
  - No details are given about distance of other points of the query.

    pgr_withPointsDD(edges_sql, points_sql, start_vid, distance)
        directed:=true, driving_side:='b', details:=false)
    RETURNS SET OF (seq, node, edge, cost, agg_cost)

Example: |

    SELECT * FROM pgr_withPointsDD(
        'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
        'SELECT pid, edge_id, fraction, side from pointsOfInterest',
        -1, 3.8);
     seq | node | edge | cost | agg_cost
    -----+------+------+------+----------
       1 |   -1 |   -1 |    0 |        0
       2 |    1 |    1 |  0.4 |      0.4
       3 |    2 |    1 |  0.6 |      0.6
       4 |    5 |    4 |  0.3 |      1.6
       5 |    6 |    8 |    1 |      2.6
       6 |    8 |    7 |    1 |      2.6
       7 |   10 |   10 |    1 |      2.6
       8 |    7 |    6 |  0.3 |      3.6
       9 |    9 |    9 |    1 |      3.6
      10 |   11 |   11 |    1 |      3.6
      11 |   13 |   14 |    1 |      3.6
    (11 rows)

### Driving distance from a single point

Finds the driving distance depending on the optional parameters setup.

    pgr_withPointsDD(edges_sql, points_sql, start_vids, distance,
        directed:=true, driving_side:='b', details:=false)
    RETURNS SET OF (seq, node, edge, cost, agg_cost)

Example: | Right side driving topology

    SELECT * FROM pgr_withPointsDD(
        'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
        'SELECT pid, edge_id, fraction, side from pointsOfInterest',
        -1, 3.8,
        driving_side := 'r',
        details := true);
     seq | node | edge | cost | agg_cost
    -----+------+------+------+----------
       1 |   -1 |   -1 |    0 |        0
       2 |    1 |    1 |  0.4 |      0.4
       3 |    2 |    1 |    1 |      1.4
       4 |   -6 |    4 |  0.7 |      2.1
       5 |    5 |    4 |  0.3 |      2.4
       6 |    6 |    8 |    1 |      3.4
       7 |    8 |    7 |    1 |      3.4
       8 |   10 |   10 |    1 |      3.4
    (8 rows)

### Driving distance from many starting points

Finds the driving distance depending on the optional parameters setup.

    pgr_withPointsDD(edges_sql, points_sql, start_vids, distance,
        directed:=true, driving_side:='b', details:=false, equicost:=false)
    RETURNS SET OF (seq, node, edge, cost, agg_cost)

## Description of the Signatures

### Description of the edges_sql query for dijkstra like functions

edges_sql: | an SQL query, which should return a set of rows with the following columns:

Column | Type | Default | Description
--- | --- | --- | ---
id | `ANY-INTEGER` |  | Identifier of the edge.
source | `ANY-INTEGER` |  | Identifier of the first end point vertex of the edge.
target | `ANY-INTEGER` |  | Identifier of the second end point vertex of the edge.

cost

`ANY-NUMERICAL`

Weight of the edge (source, target)

- When negative: edge (source, target) does not exist, therefore it’s not part of the graph.

reverse_cost

`ANY-NUMERICAL`

-1

Weight of the edge (target, source),

- When negative: edge (target, source) does not exist, therefore it’s not part of the graph.

Where:

ANY-INTEGER: | SMALLINT, INTEGER, BIGINT
ANY-NUMERICAL: | SMALLINT, INTEGER, BIGINT, REAL, FLOAT

### Description of the Points SQL query

points_sql: | an SQL query, which should return a set of rows with the following columns:

Column | Type | Description
--- | --- | ---

pid

`ANY-INTEGER`

(optional) Identifier of the point.

- If column present, it can not be NULL.
- If column not present, a sequential identifier will be given automatically.

edge_id | `ANY-INTEGER` | Identifier of the “closest” edge to the point.
fraction | `ANY-NUMERICAL` | Value in <0,1> that indicates the relative postition from the first end point of the edge.

side

`CHAR`

(optional) Value in [‘b’, ‘r’, ‘l’, NULL] indicating if the point is:

- In the right, left of the edge or
- If it doesn’t matter with ‘b’ or NULL.
- If column not present ‘b’ is considered.

Where:

ANY-INTEGER: | smallint, int, bigint
ANY-NUMERICAL: | smallint, int, bigint, real, float

### Description of the parameters of the signatures

Parameter | Type | Description
--- | --- | ---
edges_sql | `TEXT` | Edges SQL query as described above.
points_sql | `TEXT` | Points SQL query as described above.
start_vid | `ANY-INTEGER` | Starting point id
distance | `ANY-NUMERICAL` | Distance from the start_pid
directed | `BOOLEAN` | (optional). When `false` the graph is considered as Undirected. Default is `true` which considers the graph as Directed.

driving_side

`CHAR`

(optional). Value in [‘b’, ‘r’, ‘l’, NULL] indicating if the driving side is:
  - In the right or left or
  - If it doesn’t matter with ‘b’ or NULL.
  - If column not present ‘b’ is considered.

details | `BOOLEAN` | (optional). When `true` the results will include the driving distance to the points with in the `distance`. Default is `false` which ignores other points of the points_sql.
equicost | `BOOLEAN` | (optional). When `true` the nodes will only appear in the closest start_v list. Default is `false` which resembles several calls using the single starting point signatures. Tie brakes are arbitrary.

### Description of the return values

Returns set of `(seq, node, edge, cost, agg_cost)`

Column | Type | Description
--- | --- | ---
seq | `INT` | row sequence.
node | `BIGINT` | Identifier of the node within the Distance from `start_pid`. If `details =: true` a negative value is the identifier of a point.

edge

`BIGINT`

Identifier of the edge used to go from `node` to the next node in the path sequence.
  - `-1` when `start_vid` = `node`.

cost

`FLOAT`

Cost to traverse `edge`.
  - `0` when `start_vid` = `node`.

agg_cost

`FLOAT`

Aggregate cost from `start_vid` to `node`.
  - `0` when `start_vid` = `node`.

## Examples for queries marked as `directed` with `cost` and `reverse_cost` columns

The examples in this section use the following Network for queries marked as directed and cost and reverse_cost columns are used

Example: | Left side driving topology

    SELECT * FROM pgr_withPointsDD(
        'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
        'SELECT pid, edge_id, fraction, side from pointsOfInterest',
        -1, 3.8,
        driving_side := 'l',
        details := true);
     seq | node | edge | cost | agg_cost
    -----+------+------+------+----------
       1 |   -1 |   -1 |    0 |        0
       2 |    2 |    1 |  0.6 |      0.6
       3 |   -6 |    4 |  0.7 |      1.3
       4 |    5 |    4 |  0.3 |      1.6
       5 |    1 |    1 |    1 |      1.6
       6 |    6 |    8 |    1 |      2.6
       7 |    8 |    7 |    1 |      2.6
       8 |   10 |   10 |    1 |      2.6
       9 |   -3 |   12 |  0.6 |      3.2
      10 |   -4 |    6 |  0.7 |      3.3
      11 |    7 |    6 |  0.3 |      3.6
      12 |    9 |    9 |    1 |      3.6
      13 |   11 |   11 |    1 |      3.6
      14 |   13 |   14 |    1 |      3.6
    (14 rows)

Example: | Does not matter driving side.

    SELECT * FROM pgr_withPointsDD(
        'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',
        'SELECT pid, edge_id, fraction, side from pointsOfInterest',
        -1, 3.8,
        driving_side := 'b',
        details := true);
     seq | node | edge | cost | agg_cost
    -----+------+------+------+----------
       1 |   -1 |   -1 |    0 |        0
       2 |    1 |    1 |  0.4 |      0.4
       3 |    2 |    1 |  0.6 |      0.6
       4 |   -6 |    4 |  0.7 |      1.3
       5 |    5 |    4 |  0.3 |      1.6
       6 |    6 |    8 |    1 |      2.6
       7 |    8 |    7 |    1 |      2.6
       8 |   10 |   10 |    1 |      2.6
       9 |   -3 |   12 |  0.6 |      3.2
      10 |   -4 |    6 |  0.7 |      3.3
      11 |    7 |    6 |  0.3 |      3.6
      12 |    9 |    9 |    1 |      3.6
      13 |   11 |   11 |    1 |      3.6
      14 |   13 |   14 |    1 |      3.6
    (14 rows)

The queries use the Sample Data network.

History

- Proposed in version 2.2

## See Also

- pgr_drivingDistance - Driving distance using dijkstra.
- pgr_alphaShape - Alpha shape computation.
- pgr_pointsAsPolygon - Polygon around set of points.

Indices and tables

- Index
- Search Page