Find distance between the nodes and Show the shortest route

drajesh · April 12, 2022, 7:01am

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

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

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

Please do needful. waiting for the reply on this.

glilienfield · April 12, 2022, 10:00am

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 · April 14, 2022, 9:52am

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 · April 14, 2022, 1:17pm

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 · April 14, 2022, 1:21pm

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 · April 14, 2022, 3:48pm

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 · April 15, 2022, 5:26am

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 · April 15, 2022, 8:50am

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 · April 15, 2022, 9:14am

I can see the km between each node

glilienfield · April 15, 2022, 9:33am

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 · April 15, 2022, 9:49am

//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 · April 15, 2022, 10:01am

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 · April 15, 2022, 10:04am

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 · April 15, 2022, 10:07am

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 · April 15, 2022, 10:18am

Thank you

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

glilienfield · April 15, 2022, 10:35am

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 · April 15, 2022, 11:28am

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 · April 15, 2022, 12:37pm

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 · April 15, 2022, 1:06pm

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 · April 15, 2022, 2:51pm

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

Topic		Replies	Views
Cypher: Calculate distances Neo4j Graph Platform migrated	7	161	September 21, 2022
Alternative of shortestPath Cypher performance , cypher	2	167	March 3, 2023
Get shortest path between nodes going via specific node Cypher cypher , shortest-path	1	517	November 12, 2020
Shortest Path between nodes excluding nodes with a certain label Cypher cypher , shortest-path	4	1912	February 2, 2021
Distance calculation Cypher	3	2706	January 21, 2020

Get Certified in June!

Find distance between the nodes and Show the shortest route

Related topics