Join the community at Nodes 2022, our free virtual event on November 16 - 17.
β04-12-2022 12:01 AM
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
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
Please do needful. waiting for the reply on this.
Solved! Go to Solution.
β04-15-2022 11:01 AM
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.
β04-12-2022 03:00 AM
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
β04-14-2022 02:52 AM
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.
β04-14-2022 06:17 AM
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.
β04-14-2022 06:21 AM
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);
β04-14-2022 08:48 AM
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)
β04-14-2022 10:26 PM
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.
β04-15-2022 01:50 AM
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
β04-15-2022 02:14 AM
I can see the km between each node
β04-15-2022 02:33 AM
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.
β04-15-2022 02:49 AM
//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)
β04-15-2022 03:01 AM
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.
β04-15-2022 03:07 AM
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.
β04-15-2022 03:18 AM
Thank you
I got the distances now.
But how to find the shortest route with respective to each retailer in the graph?
β04-15-2022 03:04 AM
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
β04-15-2022 03:35 AM
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.
β04-15-2022 04:28 AM
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
β04-15-2022 05:37 AM
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
β04-15-2022 06:06 AM
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:
β04-15-2022 07:51 AM
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
β04-15-2022 08:29 AM
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?
β04-15-2022 08:53 AM
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
β04-15-2022 09:57 AM
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
β04-15-2022 11:01 AM
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.
β04-16-2022 03:32 AM
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.
β04-16-2022 04:17 AM
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?