cancel
Showing results for 
Search instead for 
Did you mean: 

Join the community at Nodes 2022, our free virtual event on November 16 - 17.

Find distance between the nodes and Show the shortest route

drajesh
Node Link

I am trying to calculate the distance between the nodes and shortest distance using the longitude & latitude for each node but unable to get the required results in the table & graph.

Please check my code

  1. Total Distance

MATCH (p:HUB)-[r1]->(w)-[r2]->(re:Retailer)
WITH DISTINCT(substring(w.name, 10)) AS Num,
toInteger(avg(r1.km + r2.km)) AS Average_Distance,
toInteger(sum(r1.km + r2.km)) AS Total_Distance
RETURN "Wholesaler" + Num AS Wholesaler, Total_Distance, Average_Distance
ORDER BY Total_Distance

Wholesaler Total_Distance Average_Distance
Wholesaler0 51139 10227
Wholesaler1 106252 21250

2.Distance<14000

MATCH chain=(rs:VENDORA)-[*]->(re:Retailer)
WITH reduce(dist = 0, s IN relationships(chain)| dist + s.km) AS distance, chain
WHERE distance < 14000
WITH [n IN nodes(chain)| n.name] AS SupplyChain
RETURN collect(DISTINCT(SupplyChain[0])) AS VENDOR, collect(DISTINCT(SupplyChain[1])) AS WAREHOUSE, collect(DISTINCT(SupplyChain[3])) AS Wholesaler, collect(DISTINCT(SupplyChain[4])) AS Retailer
LIMIT 10

VENDOR WAREHOUSE Wholesaler Retailer
[Nestle] [WAREHOUSEA1] [Wholesaler0] [Target]

My requirement is

  1. Calculate distance from every vendor to every retailer--Table Format
  2. Shortest route for the each retailer--Graph Format

Please do needful. waiting for the reply on this.

1 ACCEPTED SOLUTION

Do you want the shortest path across all vendors for a specific retailer? This would assume that all vendors can source all goods, so the shortest path across all vendors makes sense in this case. If not, you would have to have item nodes that represent what each vendor can source and have them linked to each vendor. Then you could ask the question what vendor is preferred to source a specific product to a specific retailer.

Assuming you want the shortestPath for a specific retailer, regardless of vendor, the following query should do that. The basic differences are limiting to one retailer and aggregating over retailer only, instead of retailer and vendor.

Change 'Safeway' to the name of the retailer you want.

MATCH (re:Retailer {name: 'Safeway'})
MATCH p=(v)-[r1]->(wh)-[r2]->(:HUB)-[r3]->(:Wholesaler)-[r4]->(re)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
WITH p, re.name as retailer, r1.km + r2.km + r3.km + r4.km as distance
WITH retailer, min(distance) as minDist, collect({path: p, distance: distance}) as paths 
WITH retailer, [x in paths where x.distance = minDist] as shortestPaths
UNWIND shortestPaths as shortestPath
RETURN shortestPath.path as path, shortestPath.distance as distance

Currently we are using 'distance' as your cost metric. Do you have a more complex cost metric to use based on the additional attributes you mentioned? If so, we can probably incorporate the calculation of that cost metric instead of total distance for determining the optimal path.

View solution in original post

25 REPLIES 25

glilienfield
Ninja
Ninja

I am a little confused with your data model and what you are asking. Can you expand on what is wrong with your 'Total Distance' query and what you are looking for?

For item #2, I am assuming you want to calculate the shortest path between a retailer and each of its vendors. If true, the below code should work. It finds all the paths between a retailer and its vendors, it then calculates the distance for each supply chain, then it groups the supply chains by retailer (including the minimum distance of all supply chains for the given retailer), then it concludes by filtering out the supply chains for the retailer that equal the minimum distance. The result is a collection, as it is possible for multiple vendors to have the same minimum distance. The results are converted into rows by unwinding the collection of vendors with the minimum supply chance and returning the attributes you wanted, with the distance.

Is this what you are looking for? Sorry, I have not data to test with.

MATCH chain=(rs:VENDORA)-[*]->(retailer:Retailer)
WITH retailer, chain, reduce(dist = 0, s IN relationships(chain) | dist + s.km) AS distance
WITH retailer, min(distance) as minDistance, collect({chain: chain, distance: distance}) as chains
WITH retailer, [c in chains where c.distance = minDistance] as retailersShortestChains
UNWIND retailersShortestChains as retailerChain
WITH retailer, retailerChain.distance as distance, [n IN nodes(retailerChain.chain) | n.name] AS SupplyChain
RETURN retailer, SupplyChain[0] AS VENDOR, SupplyChain[1] AS WAREHOUSE, SupplyChain[3] AS Wholesaler, distance

Hi Gary,
For
#1. Distance
I need to find the distance between each Vendor and each Retailer
Data should be as in the below format.
Example:

VENDORA WAREHOUSEA HUB WHOLESALER RETAILER Distance(KM)
Nestle WAREHOUSEA0 HUB WHOLESALER0 Aldi 14000
Nestle WAREHOUSEA1 HUB WHOLESALER0 Aldi 13000
Nestle WAREHOUSEA2 HUB WHOLESALER0 Aldi 15000
Nestle WAREHOUSEA0 HUB WHOLESALER1 Aldi 17000
Nestle WAREHOUSEA1 HUB WHOLESALER1 Aldi 12000
Nestle WAREHOUSEA2 HUB WHOLESALER1 Aldi 19000
Kraft WAREHOUSEA0 HUB WHOLESALER0 Aldi 4000
Kraft WAREHOUSEA1 HUB WHOLESALER0 Aldi 23000
Kraft WAREHOUSEA2 HUB WHOLESALER0 Aldi 15000
Kraft WAREHOUSEA0 HUB WHOLESALER1 Aldi 18000
Kraft WAREHOUSEA1 HUB WHOLESALER1 Aldi 8000
Kraft WAREHOUSEA2 HUB WHOLESALER1 Aldi 10000
Heinz WAREHOUSEA0 HUB WHOLESALER0 Kroger 11000
Heinz WAREHOUSEA1 HUB WHOLESALER0 Kroger 16000
Heinz WAREHOUSEA2 HUB WHOLESALER0 Kroger 9000
Heinz WAREHOUSEA0 HUB WHOLESALER1 Kroger 7800
Heinz WAREHOUSEA1 HUB WHOLESALER1 Kroger 12000
Heinz WAREHOUSEA2 HUB WHOLESALER1 Kroger 16600

#2. Shortest Path
As you can see from the above table, from Kraft-Warehouse0-HUB-Wholesaler0-Aldi, we have the least distance as 4000.
a) I need to find the shortest path for each Retailer and
b) the one retailer holding shortest path among all the retailers.
Example:
a)

VENDORA WAREHOUSEA HUB WHOLESALER RETAILER Distance(KM)
Kraft WAREHOUSEA0 HUB WHOLESALER0 Aldi 4000
Heinz WAREHOUSEA0 HUB WHOLESALER1 Kroger 7800

b)

VENDORA WAREHOUSEA HUB WHOLESALER RETAILER Distance(KM)
Kraft WAREHOUSEA0 HUB WHOLESALER0 Aldi 4000

I need a query to show the shortest path in the graph format with nodes and relationships as below.

My code:
//Creation of Nodes

CREATE (:HUB { name: "HUB", lat: tan(rand())*100, lon: tan(rand())*100, co2: 200, cost: 100, time: 0 })
FOREACH (r IN range(0,1)|
CREATE (:Wholesaler { name:"Wholesaler" + r, cost: round(exp(rand()*3)+20), co2: round(exp(rand()*8)+250), lat: tan(rand())*100, lon: tan(rand())*100, time: round(rand()*5)}))
FOREACH (r IN range(0,2)|
CREATE (:WAREHOUSEA { name:"WAREHOUSEA" + r, cost: round(exp(rand()*3)+20), co2: round(exp(rand()*8)+250), lat: tan(rand())*100, lon: tan(rand())*100, time: round(rand()*5)}))
FOREACH (r IN range(0,1)|
CREATE (:WAREHOUSEB { name:"WAREHOUSEB" + r, cost: round(exp(rand()*3)+20), co2: round(exp(rand()*8)+250), lat: tan(rand())*100, lon: tan(rand())*100, time: round(rand()*5)}))
CREATE(:Retailer{name:"kroger",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:1})
CREATE(:Retailer{name:"Aldi",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:1})
CREATE(:Retailer{name:"Safeway",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:1})
CREATE(:Retailer{name:"Aroma",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:1})
CREATE(:Retailer{name:"Target",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*50,lon:tan(rand())*50,time:1})
CREATE(:VENDORA{name:"Nestle",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORA{name:"Danone",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORA{name:"Schreiber",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORA{name:"Kraft",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORA{name:"Heinz",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORB{name:"Kimberly Clark",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORB{name:"P&G",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORB{name:"J&J",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORB{name:"Colgate",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})
CREATE(:VENDORB{name:"Palmolive",cost:round(exp(rand()*3)+20),co2:round(exp(rand()*8)+250),lat:tan(rand())*100,lon:tan(rand())*100,time:round(rand()*5)})

//Creation of Relations

match (p:HUB), (w:Wholesaler)
with p,w
merge (p)-[:DELIVER]->(w)

MATCH (w:Wholesaler), (r:Retailer)
WITH w, r
MERGE (w)-[:DELIVER]->(r)

MATCH (sa:WAREHOUSEA), (p:HUB)
WITH p, sa
MERGE (sa)-[:DELIVER]->(p)

MATCH (sb:WAREHOUSEB), (p:HUB)
MERGE (sb)-[:DELIVER]->(p)
WITH sb, p
MATCH (va:VENDORA), (vb:VENDORB)
MERGE (va)-[:DELIVER]->(sa)
MERGE (vb)-[:DELIVER]->(sb)

Please do needful.

I will be glad to look at it.

Can you review your 'create relationship' code. It does not run as a whole. Are these four individual scripts? If so, the last one is dependent on 'sa', which is defined in the previous block. I don't want to patch it up with 'with' clauses to get it to run, as I may change the intention of the code.

glilienfield
Ninja
Ninja

Never mind, I used this.

match (p:HUB)
match (w:Wholesaler)
merge (p)-[:DELIVER]->(w);

MATCH (w:Wholesaler)
match (r:Retailer)
MERGE (w)-[:DELIVER]->(r);

MATCH (sa:WAREHOUSEA)
match (p:HUB)
MERGE (sa)-[:DELIVER]->(p);

MATCH (sb:WAREHOUSEB)
match (p:HUB)
MERGE (sb)-[:DELIVER]->(p);

MATCH (sa:WAREHOUSEA)
MATCH (va:VENDORA)
MERGE (va)-[:DELIVER]->(sa);

MATCH (sb:WAREHOUSEB)
MATCH (vb:VENDORB)
MERGE (vb)-[:DELIVER]->(sb);

glilienfield
Ninja
Ninja

The first thing I tried to do is to calculate the distance between each node along a path. Since you have provided, what I think is supposed to be longitude and latitude coordinates, I tried using neo4j distance methods. I got the following error, stating that a latitude value is not within legal range. Here is the code I used to test calculating the distance. Do you already have a worked out approach for calculating the distance?

MATCH chain=(rs:VENDORA)-[*]->(retailer:Retailer)
WITH rs.name as vendor, relationships(chain) as rels
UNWIND rels as rel
WITH vendor, id(rel) as relId, 
point({longitude: startNode(rel).lon, latitude: startNode(rel).lat}) as startPoint,
point({longitude: endNode(rel).lon, latitude: endNode(rel).lat}) as endPoint
return vendor, relId, point.distance(startPoint, endPoint)

I got an approach for calculating the total distance from VENDORA to HUB and HUB to Retailer

I have used the following code

MATCH (p:HUB)-[r1]->(w)-[r2]->(re:Retailer)
WITH distinct(substring(w.name, 10)) AS Num,
toInteger(avg(r1.km + r2.km)) AS Average_Distance,
toInteger(sum(r1.km + r2.km)) AS Total_Distance
RETURN "Wholesaler" + Num AS Wholesaler, Total_Distance, Average_Distance
ORDER BY Total_Distance

output:

Wholesaler Total_Distance Average_Distance
Wholesaler0 51139 10227
Wholesaler1 106252 21250

MATCH (v:VENDORA)-[r1]->(w)-[r2]->(p:HUB)
WITH distinct(substring(w.name, 10)) AS Num,
toInteger(avg(r1.km + r2.km)) AS Average_Distance,
toInteger(sum(r1.km + r2.km)) AS Total_Distance
RETURN "WAREHOUSEA" + Num AS WAREHOUSE, Total_Distance, Average_Distance
ORDER BY Total_Distance

output:

WAREHOUSE Total_Distance Average_Distance
WAREHOUSEA1 54564 10912
WAREHOUSEA0 75643 15128
WAREHOUSEA2 106672 21334

I tried to put the 2 tables data in one but got the error.

MATCH (v:VENDORA)-[r1]->(wh)-[r2]->(h:HUB)-[r3]->(ws)-[r4]->(re:Retailer)
WITH distinct(substring(wh.name, 10)) AS Num1,
distinct(substring(ws.name, 10)) AS Num2,
toInteger(avg(r1.km + r2.km + r3.km + r4.km)) AS Average_Distance,
toInteger(sum(r1.km + r2.km + r3.km + r4.km)) AS Total_Distance
RETURN "WAREHOUSEA" + Num1 AS WAREHOUSEA,"Wholesaler" + Num2 AS Wholesaler, Total_Distance, Average_Distance
ORDER BY Total_Distance
Error: Unknown function 'distinct' (line 3, column 10 (offset: 139))
"WITH DISTINCT substring(((wh).name), (10)) AS Num1, distinct((substring(((ws).name), (10)))) AS Num2, toInteger((avg((((((r1).km) + ((r2).km)) + ((r3).km)) + ((r4).km))))) AS Average_Distance, toInteger((sum((((((r1).km) + ((r2).km)) + ((r3).km)) + ((r4).km))))) AS Total_Distance"

But that is not the required output. FOR SHORTEST PATH
I need the data in the below format

VENDORA WAREHOUSEA HUB WHOLESALER RETAILER Distance(KM)
Kraft WAREHOUSEA0 HUB WHOLESALER0 Aldi 4000

or

Retailer Distance Path
Aldi 4000 [Kraft,WAREHOUSEA0,HUB,WHOLESALER0,Aldi]

We need to find the distance between 2 nodes first and then it will be easy to get the required output. As you saw in the code I have used Longitude and Latitude for calculating the distance, is there any other alternative to calculate the distance? Because I am stuck at here.

Please do needful.

glilienfield
Ninja
Ninja

But there is not a ‘km’ relationship attribute in your test data. Do have a cypher script that creates that data.

Your code uses the ‘avg’ and ‘sum’ methods. These are aggregating methods. In the two separate queries, you are aggregating the value ‘r1.km + r2.km’ over all paths that go through a wholesaler in the first query and a warehouse in the second query. I don’t think that is what you want. Instead, I think you want to add the ‘km’ values along a single path between two endpoints, which are ‘vendors’ and ‘retailers’.

To get the sum I recommend, requires you to get the relationships for a single path and add the ‘km’ attributes for each relationship along that path. That would be the distance for that one path. That can be achieved using the reduce method for lists.

Each total path starts at a vendor and ends at a retailer, so the following should calculate the total distance by adding the length of each segment.

Match path = (v:Vendor)-[*]->(r:Retailer)
Return v, r, reduce(d=0, x in relationships(path) | d + x.km) as totalDistance

I can see the km between each node

glilienfield
Ninja
Ninja

Do you have the cypher you used to create those relationships. It is not what you posted earlier. Those statements don’t set any relationship properties.

//For adding the distance(km)
MATCH (a)-[r]->(b)
WITH r, a, b, 2 * 6371 * asin(sqrt(haversin(radians(toInteger(a.lat) - toInteger(b.lat))) + cos(radians(a.lat))*
cos(radians(b.lat))* haversin(radians(a.lon - b.lon)))) AS dist
SET r.km = round(dist)

Match path = (v:VENDORA)-[*]->(r:Retailer)

Return v, r, reduce(d=0, x in relationships(path) | d + x.km) as totalDistance

There is no relationship between nodes.

That is because that query only returns tha path’s beginning and end nodes, thus the graph of the query results will noth show the connections. That was only to demonstrate how to calculate the distance between two connected paths.

Thank you

I got the distances now.
But how to find the shortest route with respective to each retailer in the graph?

glilienfield
Ninja
Ninja

Thank you.

Since your paths are fix, using the explicit path makes it easier to generate your table of distances. The following should give you a table of each path between a vendor and retailer by distance.

MATCH (v)-[r1]->(wh)-[r2]->(h:HUB)-[r3]->(ws:Wholesaler)-[r4]->(re:Retailer)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
Return v.name as vendor, wh.name as warehouse, h.name as hub, ws.name as wholesaler, re.name as retailer,  r1.km + r2.km + r3.km + r4.km as distance

glilienfield
Ninja
Ninja

If the posted query works to get the table of distances, we can modify it to find the shortest path between any vendor and retailer.

MATCH (v)-[r1]->(wh)-[r2]->(h:HUB)-[r3]->(ws:Wholesaler)-[r4]->(re:Retailer)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
WITH v.name as vendor, wh.name as warehouse, h.name as hub, ws.name as wholesaler, re.name as retailer,  r1.km + r2.km + r3.km + r4.km as distance
WITH vendor, retailer, min(distance) as minDist, collect({warehouse: warehouse, hub: hub, wholesaler: wholesaler, distance: distance}) as paths 
WITH vendor, retailer, [x in paths where x.distance = minDist] as shortestPaths
UNWIND shortestPaths as shortestPath
RETURN vendor, retailer, shortestPath.warehouse as warehouse, shortestPath.hub as hub, shortestPath.wholesaler as wholesaler, shortestPath.distance as distance

The above should work if I didn’t make any syntax errors. I can debug in a bit if there are any issues.

Thank you.
The above query worked, I got the shortest distances for every retailer.

vendor retailer warehouse hub wholesaler distance
Danone Safeway WAREHOUSEA1 HUB Wholesaler0 15513
Schreiber Safeway WAREHOUSEA0 HUB Wholesaler0 18278
Kraft Safeway WAREHOUSEA1 HUB Wholesaler0 19423
Nestle Safeway WAREHOUSEA1 HUB Wholesaler0 14579
Heinz Safeway WAREHOUSEA1 HUB Wholesaler0 16058
Danone Aldi WAREHOUSEA1 HUB Wholesaler0 25612
Schreiber Aldi WAREHOUSEA0 HUB Wholesaler0 28377
Kraft Aldi WAREHOUSEA1 HUB Wholesaler0 29522
Nestle Aldi WAREHOUSEA1 HUB Wholesaler0 24678
Heinz Aldi WAREHOUSEA1 HUB Wholesaler0 26157
Danone Target WAREHOUSEA1 HUB Wholesaler0 14680
Schreiber Target WAREHOUSEA0 HUB Wholesaler0 17445
Kraft Target WAREHOUSEA1 HUB Wholesaler0 18590
Nestle Target WAREHOUSEA1 HUB Wholesaler0 13746
Heinz Target WAREHOUSEA1 HUB Wholesaler0 15225
Danone Aroma WAREHOUSEA1 HUB Wholesaler0 23641
Schreiber Aroma WAREHOUSEA0 HUB Wholesaler0 26406
Kraft Aroma WAREHOUSEA1 HUB Wholesaler0 27551
Nestle Aroma WAREHOUSEA1 HUB Wholesaler0 22707
Heinz Aroma WAREHOUSEA1 HUB Wholesaler0 24186
Danone kroger WAREHOUSEA1 HUB Wholesaler0 18593
Schreiber kroger WAREHOUSEA0 HUB Wholesaler0 21358
Kraft kroger WAREHOUSEA1 HUB Wholesaler0 22503
Nestle kroger WAREHOUSEA1 HUB Wholesaler0 17659
Heinz kroger WAREHOUSEA1 HUB Wholesaler0 19138
Colgate Safeway WAREHOUSEB1 HUB Wholesaler0 23729
J&J Safeway WAREHOUSEB1 HUB Wholesaler0 21619
Kimberly Clark Safeway WAREHOUSEB1 HUB Wholesaler0 17249
Palmolive Safeway WAREHOUSEB1 HUB Wholesaler0 23381
P&G Safeway WAREHOUSEB1 HUB Wholesaler0 17390
Colgate Aldi WAREHOUSEB1 HUB Wholesaler0 33828
J&J Aldi WAREHOUSEB1 HUB Wholesaler0 31718
Kimberly Clark Aldi WAREHOUSEB1 HUB Wholesaler0 27348
Palmolive Aldi WAREHOUSEB1 HUB Wholesaler0 33480
P&G Aldi WAREHOUSEB1 HUB Wholesaler0 27489
Colgate Target WAREHOUSEB1 HUB Wholesaler0 22896
J&J Target WAREHOUSEB1 HUB Wholesaler0 20786
Kimberly Clark Target WAREHOUSEB1 HUB Wholesaler0 16416
Palmolive Target WAREHOUSEB1 HUB Wholesaler0 22548
P&G Target WAREHOUSEB1 HUB Wholesaler0 16557
Colgate Aroma WAREHOUSEB1 HUB Wholesaler0 31857
J&J Aroma WAREHOUSEB1 HUB Wholesaler0 29747
Kimberly Clark Aroma WAREHOUSEB1 HUB Wholesaler0 25377
Palmolive Aroma WAREHOUSEB1 HUB Wholesaler0 31509
P&G Aroma WAREHOUSEB1 HUB Wholesaler0 25518
Colgate kroger WAREHOUSEB1 HUB Wholesaler0 26809
J&J kroger WAREHOUSEB1 HUB Wholesaler0 24699
Kimberly Clark kroger WAREHOUSEB1 HUB Wholesaler0 20329
Palmolive kroger WAREHOUSEB1 HUB Wholesaler0 26461
P&G kroger WAREHOUSEB1 HUB Wholesaler0 20470

Can we show the shortest path in the graph ?
I got a cypher which shows distance <14000 but shows in the Table .The below query is also not that organized because it is filtering the distance but as in my case I need the shortest path for the Retailer among all the Vendors, Warehouses & Wholesalers.

MATCH chain=(rs:VENDORA)-[*]->(re:Retailer)
WITH reduce(dist = 0, s IN relationships(chain)| dist + s.km) AS distance, chain
WHERE distance < 14000
WITH [n IN nodes(chain)| n.name] AS SupplyChain
RETURN collect(DISTINCT(SupplyChain[0])) AS VENDOR, collect(DISTINCT(SupplyChain[1])) AS WAREHOUSE, collect(DISTINCT(SupplyChain[3])) AS Wholesaler, collect(DISTINCT(SupplyChain[4])) AS Retailer
LIMIT 10

Output:

VENDOR WAREHOUSE Wholesaler Retailer
[Nestle] [WAREHOUSEA1] [Wholesaler0] [Target]

My required output is
3X_0_a_0a18bf541f47c75f882f61fb7c7479bb5d5c896e.png

glilienfield
Ninja
Ninja

The following should return the paths with the minimum distance. I did not approach it by sorting in descending order of distance and limiting the return to 1 row, as there could be multiple paths with the same minimum distance.

MATCH p=(v)-[r1]->(wh)-[r2]->(h:HUB)-[r3]->(ws:Wholesaler)-[r4]->(re:Retailer)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
with p, r1.km + r2.km + r3.km + r4.km as distance
with collect({path:p , distance: distance}) as paths, min(distance) as minDistance
with [x in paths where x.distance = minDistance] as minPaths
unwind minPaths as path
return path.path as path, path.distance as distance

Thank you.

But can we modify the code for showing the Min Distance for Every Retailer?. As shown in the below graph, it is the shortest distance for the Retailer 'Safeway'
Ex:

glilienfield
Ninja
Ninja

Try this. It does the same thing, but carries through just the path instead of all the intermediate nodes, and then returns just the path for visualization.

MATCH p=(v)-[r1]->(wh)-[r2]->(:HUB)-[r3]->(:Wholesaler)-[r4]->(re:Retailer)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
WITH p, v.name as vendor, re.name as retailer, r1.km + r2.km + r3.km + r4.km as distance
WITH vendor, retailer, min(distance) as minDist, collect({path: p, distance: distance}) as paths 
WITH vendor, retailer, [x in paths where x.distance = minDist] as shortestPaths
UNWIND shortestPaths as shortestPath
RETURN shortestPath.path as path, shortestPath.distance as distance

It does not make any point from which vendor we getting the shortest distance because the graph is showing with multiple vendors and retailers.

Can't we optimize the path from the original graph so that we can see the shortest path for the particular retailer. The best route for the retailer who can get the goods from vendor depending on the constraints like distance, time, co2e. If in case we have the shortest distance between V1 & R1 but what is the best alternative for the retailer R1 to fetch the goods from other vendors like V2,V3,V4 & V5 with respective to the constraints?

glilienfield
Ninja
Ninja

it is the way the data is visualized by Neo4j Desktop. It is showing all the nodes in the result and links between them. It does not visualize all paths separately. You can see what I am referring to if you limit the result to one path.

MATCH p=(v)-[r1]->(wh)-[r2]->(:HUB)-[r3]->(:Wholesaler)-[r4]->(re:Retailer)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
WITH p, v.name as vendor, re.name as retailer, r1.km + r2.km + r3.km + r4.km as distance
WITH vendor, retailer, min(distance) as minDist, collect({path: p, distance: distance}) as paths 
WITH vendor, retailer, [x in paths where x.distance = minDist] as shortestPaths
UNWIND shortestPaths as shortestPath
RETURN shortestPath.path as path, shortestPath.distance as distance
limit 1

drajesh
Node Link

I don't get it why we are seeing the Retailer 'Safeway' in the graph even though it is not mentioned in the code?

And can't we optimize the path from the original graph so that we can see the shortest path for the particular retailer. The best route for the retailer who can get the goods from vendor depending on the constraints like distance, time, co2e. If in case we have the shortest distance between V1 & R1 but what is the best alternative for the retailer R1 to fetch the goods from other vendors like V2,V3,V4 & V5 with respective to the constraints?

Best recommendation for the Retailers to get the goods from the vendors considering the above constraints

Do you want the shortest path across all vendors for a specific retailer? This would assume that all vendors can source all goods, so the shortest path across all vendors makes sense in this case. If not, you would have to have item nodes that represent what each vendor can source and have them linked to each vendor. Then you could ask the question what vendor is preferred to source a specific product to a specific retailer.

Assuming you want the shortestPath for a specific retailer, regardless of vendor, the following query should do that. The basic differences are limiting to one retailer and aggregating over retailer only, instead of retailer and vendor.

Change 'Safeway' to the name of the retailer you want.

MATCH (re:Retailer {name: 'Safeway'})
MATCH p=(v)-[r1]->(wh)-[r2]->(:HUB)-[r3]->(:Wholesaler)-[r4]->(re)
where (v:VENDORA or v:VENDORB) and (wh:WAREHOUSEA or wh:WAREHOUSEB)
WITH p, re.name as retailer, r1.km + r2.km + r3.km + r4.km as distance
WITH retailer, min(distance) as minDist, collect({path: p, distance: distance}) as paths 
WITH retailer, [x in paths where x.distance = minDist] as shortestPaths
UNWIND shortestPaths as shortestPath
RETURN shortestPath.path as path, shortestPath.distance as distance

Currently we are using 'distance' as your cost metric. Do you have a more complex cost metric to use based on the additional attributes you mentioned? If so, we can probably incorporate the calculation of that cost metric instead of total distance for determining the optimal path.

Thank you.

Can we highlight by using different colors or increasing the size of the nodes both the shortest path for the Safeway Retailer in the original graph and the best alternative route based on the attributes like co2e, cost, time & waste.

Ex:
3X_5_0_50faa955ca1b7508b20cfd9b19aa939940927605.png

Can we highlight the Vendor 'Nestle' on the basis of the shortest distance and 'Danone' for the best recommendation in the graph?
I am trying to get most recommended vendor for the retailer based on calculating the cost metrics: distance, time, co2e, cost, wasteR.

glilienfield
Ninja
Ninja

You can manually change color and size of the nodes in neo4j desktop. I have not used it, but neo4j Bloom may have the capabilities to visualize the data the way you want to.

Here is a discussion of visualization options

Do you want to update the queries to use a more representative cost metric?