## pgr_withPointsDD - Proposed

``` 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.

Availability

• Version 2.2.0

• New proposed function

### Description

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.

### Signatures

Summary

```pgr_withPointsDD(edges_sql, points_sql, from_vids, distance [, directed] [, driving_side] [, details] [, equicost])
RETURNS SET OF (seq, node, edge, cost, agg_cost)
```

Using defaults

• 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)
RETURNS SET OF (seq, node, edge, cost, agg_cost)
```
Example :

From point \(1\) with \(agg\_cost <= 3.8\)

• 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.

```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     1       1.6
5     6     8     1       2.6
6     8     7     1       2.6
7    10    10     1       2.6
8     7     6     1       3.6
9     9     9     1       3.6
10    11    11     1       3.6
11    13    14     1       3.6
(11 rows)

```

#### Single vertex

Finds the driving distance depending on the optional parameters setup.

```pgr_withPointsDD(edges_sql, points_sql, from_vid,  distance [, directed] [, driving_side] [, details])
RETURNS SET OF (seq, node, edge, cost, agg_cost)
```
Example :

Right side driving topology, from point \(1\) with \(agg\_cost <= 3.8\)

```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)

```

#### Multiple vertices

Finds the driving distance depending on the optional parameters setup.

```pgr_withPointsDD(edges_sql, points_sql, from_vids, distance [, directed] [, driving_side] [, details] [, equicost])
RETURNS SET OF (seq, node, edge, cost, agg_cost)
```

### Parameters

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.

### Inner query

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

### Result Columns

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 from point \(1\) with \(agg\_cost <= 3.8\) , with details

```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 :

From point \(1\) with \(agg\_cost <= 3.8\) , does not matter driving side, with details

```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.