Compute distance input / output csv

[Michel]

Hi 

 I have a graph:

There is a list of node-pairs in a csv file:

input.csv
case, from, to
c1,AAA,BBB
c1,EEE,FFF
c2,AAA,EEE

Task: The distance between two nodes should be calculated and put in a  csv like:

ouput.csv
case, from, to, distance
c1,AAA,CCC,2
c1,EEE,FFF,1
c2,AAA,EEE,0

Could you please give me some istructions or code, how to do it ?

Tanks in advance,

Michel

here the script creating the graph:

CREATE (a:City {DS: 'AAA'})
CREATE (b:City {DS: 'BBB'})
CREATE (c:City {DS: 'CCC'})
CREATE (d:City {DS: 'DDD'})
CREATE (e:City {DS: 'EEE'})
CREATE (f:City {DS: 'FFF'})
CREATE (g:City {DS: 'GGG'})

MATCH (p1:City {DS: 'AAA'}), (p2:City {DS: 'BBB'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'BBB'}), (p2:City {DS: 'CCC'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'CCC'}), (p2:City {DS: 'DDD'})
CREATE (p1)-[:NB {route: '66'}]->(p2)

MATCH (p1:City {DS: 'EEE'}), (p2:City {DS: 'FFF'})
CREATE (p1)-[:NB {route: '77'}]->(p2)

MATCH (p1:City {DS: 'FFF'}), (p2:City {DS: 'GGG'})
CREATE (p1)-[:NB {route: '77'}]->(p2)

[Cobra · Accepted solution]

Hello @Michel 😊

This is the query you need to compute distance for each row in the input file:

LOAD CSV WITH HEADERS FROM 'file:///input.csv' AS row 
MATCH (a:City {DS: row.from}) 
MATCH (b:City {DS: row.to}) 
RETURN row.case AS case, row.from AS from, row.to AS to, coalesce(length(shortestPath((a)-[:NB*]->(b))), 0) AS distance

Moreover, if you directly want to export the result as CSV, then you need to use apoc.export.csv.query() procedure from the APOC plugin (you will need a apoc.conf file next to the neo4j.conf file😞

WITH "
    LOAD CSV WITH HEADERS FROM 'file:///input.csv' AS row 
    MATCH (a:City {DS: row.from}) 
    MATCH (b:City {DS: row.to}) 
    RETURN row.case AS case, row.from AS from, row.to AS to, coalesce(length(shortestPath((a)-[:NB*]->(b))), 0) AS distance
" AS query
CALL apoc.export.csv.query(query, "output.csv", {quotes: "ifNeeded"})
YIELD file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data
RETURN file, source, format, nodes, relationships, properties, time, rows, batchSize, batches, done, data;

 Regards,
Cobra

[Michel]

@Cobra : Thank you so much