Find distance between the nodes and Show the shortest route

[drajesh]

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.

[glilienfield]

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

[drajesh]

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.

[glilienfield]

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]

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]

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)

[drajesh]

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]

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

[drajesh]

I can see the km between each node

[glilienfield]

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.

[drajesh]

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

[drajesh]

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.

[glilienfield]

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.

[drajesh]

Thank you

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

[glilienfield]

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]

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.

[drajesh]

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

[glilienfield]

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

[drajesh]

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]

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

[drajesh]

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]

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]

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

[glilienfield · 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.

[drajesh]

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:

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]

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.

Neo4j Graph Data Platform

Neo4j Bloom 2.1 - Neo4j Bloom

Neo4j Bloom 2.1

Here is a discussion of visualization options

Neo4j Graph Data Platform

Graph Visualization - Developer Guides

This guide explains graph visualization tool options, and how to get insights from your data using visualization tools.

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