cancel
Showing results for
Did you mean:

Head's Up! Site maintenance this Wednesday, February 1. Disruptions expected as we migrate the forums.

## Compute distance input / output csv

Node Clone

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 ?

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)

1 ACCEPTED SOLUTION
Ninja

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 "
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

2 REPLIES 2
Ninja

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 "
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

Node Clone

@Cobra : Thank you so much