Databases Reference
In-Depth Information
To complete the calculation, we need then simply add the paths for legs one, three, and
two, which gives the full path from the start to the end location.
Finding the shortest delivery route using Cypher
The Cypher query to implement the parcel route calculation engine is as follows:
START
s=
node
:location(name=
{startLocation}
),
e=
node
:location(name=
{endLocation}
)
MATCH
upLeg = (s)<-[:DELIVERY_ROUTE*1..2]-(db1)
WHERE
all
(r
in
relationships
(upLeg)
WHERE
r.start_date <=
{intervalStart}
AND
r.end_date >=
{intervalEnd}
)
WITH
e, upLeg, db1
MATCH
downLeg = (db2)-[:DELIVERY_ROUTE*1..2]->(e)
WHERE
all
(r
in
relationships
(downLeg)
WHERE
r.start_date <=
{intervalStart}
AND
r.end_date >=
{intervalEnd}
)
WITH
db1, db2, upLeg, downLeg
MATCH
topRoute = (db1)<-[:CONNECTED_TO]-()-[:CONNECTED_TO*1..3]-(db2)
WHERE
all
(r
in
relationships
(topRoute)
WHERE
r.start_date <=
{intervalStart}
AND
r.end_date >=
{intervalEnd}
)
WITH
upLeg, downLeg, topRoute,
reduce
(weight=0, r
in
relationships
(topRoute) : weight+r.cost)
AS
score
ORDER BY
score
ASC
LIMIT
1
RETURN
(
nodes
(upLeg) +
tail
(
nodes
(topRoute)) +
tail
(
nodes
(downLeg)))
AS
n
At first glance this query appears quite complex. It is, however, made up of four simpler
queries joined together with
WITH
clauses. We'll look at each of these subqueries in turn.
Here's the first subquery:
START
s=
node
:location(name=
{startLocation}
),
e=
node
:location(name=
{endLocation}
)
MATCH
upLeg = (s)<-[:DELIVERY_ROUTE*1..2]-(db1)
WHERE
all
(r
in
relationships
(upLeg)
WHERE
r.start_date <=
{intervalStart}
AND
r.end_date >=
{intervalEnd}
)
This query calculates the first leg of the overall route. It can be broken down as follows:
•
START
finds the start and end locations in an index, binding them to the
s
and
e
identifiers, respectively. (Remember, the
endLocation
lookup term describes a de‐
livery unit, but the node returned from the
location
index represents a delivery
segment.)
•
MATCH
finds the route from the start location,
s
, to a delivery base using a directed,
variable-length
DELIVERY_ROUTE
path. This path is then bound to the identifier
upLeg
. Because delivery bases are always the root nodes of
DELIVERY_ROUTE
trees,
