This article discusses travel path optimization in two typical scenarios: round trip, and starting at one point and arriving at a different terminal point.

By Digoal

Background

Let's say that you are going on a family trip in the coming vacation. How can you design the optimal travel route involving multiple places? (The traveling expenses and traveling time also need to be considered.)

Other scenarios such as carpooling, food and beverage distribution, parcel pickup, and parcel distribution also involve calculating optimal paths.

PostgreSQL has been widely applied in the GIS field, with a large number of users. The following describes how PostgreSQL calculates optimal paths.

Core Features of pgRouting

pgRouting library contains the following features:

All Pairs Shortest Path, Johnson's Algorithm
All Pairs Shortest Path, Floyd-Warshall Algorithm
Shortest Path A*
Bi-directional Dijkstra Shortest Path
Bi-directional A* Shortest Path
Shortest Path Dijkstra
Driving Distance
K-Shortest Path, Multiple Alternative Paths
K-Dijkstra, One to Many Shortest Path
Traveling Salesperson
Turn Restriction Shortest Path (TRSP)

Optimal Path Plan 1: Starting from One Point and Returning to This Point After Passing Through Multiple Other Points

This plan provides an optimal path for traveling, parcel distribution, and food and beverage distribution. The plan is as follows:

A person starts from one point and returns to this point after passing through multiple other points.

Given a collection of cities and travel costs between each pair, find the cheapest way to visit all of the cities and return to the starting point.

For more information, see this article.

Example 1

pgr_TSP — Returns a route that visits all the nodes exactly once.

Start from point 5 and return to point 5 after passing through points specified in array [-1, 3, 5, 6, -6].

SELECT * FROM pgr_TSP(  
    $$  
    SELECT * FROM pgr_withPointsCostMatrix(  
        'SELECT id, source, target, cost, reverse_cost FROM edge_table ORDER BY id',  
        'SELECT pid, edge_id, fraction from pointsOfInterest',  
        array[-1, 3, 5, 6, -6], directed := false);  
    $$,  
    start_id := 5,  
    randomize := false  
);  
  
 seq | node | cost | agg_cost   
-----+------+------+----------  
   1 |    5 |    1 |        0  
   2 |    6 |    1 |        1  
   3 |    3 |  1.6 |        2  
   4 |   -1 |  1.3 |      3.6  
   5 |   -6 |  0.3 |      4.9  
   6 |    5 |    0 |      5.2  
(6 rows)

Example 2

pgr_eucledianTSP - Returns a route that visits all the coordinate pairs exactly once.

SET client_min_messages TO DEBUG1;  
SET  
SELECT* from pgr_eucledianTSP(  
    $$  
    SELECT id, st_X(the_geom) AS x, st_Y(the_geom) AS y FROM edge_table_vertices_pgr  
    $$,  
    tries_per_temperature := 0,  
    randomize := false  
);  
DEBUG:  pgr_eucledianTSP Processing Information  
Initializing tsp class ---> tsp.greedyInitial ---> tsp.annealing ---> OK  
  
Cycle(100) total changes =0    0 were because  delta energy < 0  
Total swaps: 3  
Total slides: 0  
Total reverses: 0  
Times best tour changed: 4  
Best cost reached = 18.7796  
 seq | node |       cost       |     agg_cost       
-----+------+------------------+------------------  
   1 |    1 |  1.4142135623731 |                0  
   2 |    3 |                1 |  1.4142135623731  
   3 |    4 |                1 | 2.41421356237309  
   4 |    9 | 0.58309518948453 | 3.41421356237309  
   5 |   16 | 0.58309518948453 | 3.99730875185762  
   6 |    6 |                1 | 4.58040394134215  
   7 |    5 |                1 | 5.58040394134215  
   8 |    8 |                1 | 6.58040394134215  
   9 |    7 | 1.58113883008419 | 7.58040394134215  
  10 |   14 |   1.499999999999 | 9.16154277142634  
  11 |   15 |              0.5 | 10.6615427714253  
  12 |   13 |              1.5 | 11.1615427714253  
  13 |   17 | 1.11803398874989 | 12.6615427714253  
  14 |   12 |                1 | 13.7795767601752  
  15 |   11 |                1 | 14.7795767601752  
  16 |   10 |                2 | 15.7795767601752  
  17 |    2 |                1 | 17.7795767601752  
  18 |    1 |                0 | 18.7795767601752  
(18 rows)

Optimal Path Plan 2 - Starting from One Point and Arriving at the Terminal After Passing Through Multiple Other Points

The plan for carpooling is more complicated.

Start from one point (the driver's location) and go to multiple points (passenger drop-off locations) after passing through multiple other points (passenger pickup locations).

The plan involves two stages:

The first stage starts from the driver's original location and ends when all the passengers are picked up.
The second stage starts from the last passenger's pickup and ends when all the passengers are dropped off.

In this way, the two stages have the same planning requirement: starting from one point and arriving at the terminal after passing through multiple other points.

For more information, see this article.

Example

For more information, read this article.

Given a list of vertices and a graph, this function is equivalent to finding the shortest path between vertexivertexi and vertexi+1vertexi+1 for all i

References

For more information about the routing functions involved and point-to-point cost matrix functions, see the following articles:

Community

Multipoint Optimal Path Planning in Travel, Carpooling, Parcel Distribution

Background

Core Features of pgRouting

Optimal Path Plan 1: Starting from One Point and Returning to This Point After Passing Through Multiple Other Points

Example 1

Example 2

Optimal Path Plan 2 - Starting from One Point and Arriving at the Terminal After Passing Through Multiple Other Points

Example

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution