Not able to load relationships from CSV

Question

I have a list of cities their latitude and longitude, their states and country which is available here.

I want to a make hierarchical structure like:

(CITY)-[:HAS_LATITUDE]->(LATITUDE_VALUE)
(CITY)-[:HAS_LONGITUDE]->(LONGITUDE_VALUE)
(CITY)-[:SITUATED_IN]->(STATE)
(STATE)-[:LIES_IN]->(COUNTRY)

This is a sample of the CSV data:

Id,City,Latitude,Longitude,State,Country
1,Port Blair,11.67 N,92.76 E,Andaman and Nicobar Islands,India
2,Adilabad,19.68 N,78.53 E,Andhra Pradesh,India
3,Adoni,15.63 N,77.28 E,Andhra Pradesh,India

I am trying to use the LOAD_CSV clause to load the data from the CSV and merging them, but I keep on getting:

(no changes, no records)

This is the query I am trying:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM 'http://botcognitivenlu.eastus.cloudapp.azure.com/CityAssets/IndianCities.csv' AS line
WITH line
WHERE NOT line.Latitude IS NULL AND NOT line.Longitude IS NULL
MATCH (city: CITY { name: trim(line.City), Id: randomUUID(), type: 'CITY' })
MERGE (country: COUNTRY { name: trim(line.Country), Id: randomUUID(), type: 'COUNTRY' })
MERGE (state: STATE { name: trim(line.State), Id: randomUUID(), type: 'STATE' })-[:LIES_IN]->(country)
CREATE (latitude: LOCATION { name: trim(line.Latitude), Id: randomUUID(), type: 'LOCATION' })
CREATE (longitude: LOCATION { name: trim(line.Longitude), Id: randomUUID(), type: 'LOCATION' })
CREATE (city)-[:HAS_LONGITUDE]->(longitude)
CREATE (city)-[:HAS_LATITUDE]->(latitude)
CREATE (city)-[:SITUATED_IN]->(state)
RETURN *

Any help is appreciated.

score 2 · Answer 1 · answered Jul 29 '19 at 13:13

2

Use of randomUUID() in MATCH (and MERGE) clause is the real issue here.

When you try to match CITY node, randomUUID() generates new value every time which will definitely not match with the old value of Id field. As there will be no matching CITY node with exact properties LOAD CSV will ignore skip the creating nodes/relationships.

Remove the randomUUID() from all the MATCH and MERGE clauses. If you want to set Id for newly created nodes in the MERGE clause use ON CREATE SET.

It's not recommended to load everything in a single query, so If possible use separate queries to load latitude & longitude, states, and country.

answered Jul 29 '19 at 13:13

Rajendra Kadam

4,004
1
10
24

what do you need example for `ON CREATE SET` or separate queries? – Rajendra Kadam Jul 29 '19 at 14:11
both would be nice – Kunal Mukherjee Jul 29 '19 at 14:57
Sharing a link to the official documentation for ON CREATE SET: https://neo4j.com/docs/cypher-manual/current/clauses/merge/#query-merge-on-create-on-match – Rajendra Kadam Jul 29 '19 at 15:42
I know about ON CREATE SET, but in this context how would I seperate my queries – Kunal Mukherjee Jul 29 '19 at 15:44
okay, separate queries are the optional part here. It is for the performance of the data load query. You can check why and how we can use separate queries at this blog: https://markhneedham.com/blog/2014/10/23/neo4j-cypher-avoiding-the-eager/ – Rajendra Kadam Jul 29 '19 at 15:49

score 0 · Answer 2 · answered Jul 30 '19 at 21:52

`Here is a solution: CREATE CONSTRAINT ON (s:State) ASSERT s.name IS UNIQUE; CREATE CONSTRAINT ON (c:Country) ASSERT c.name IS UNIQUE;

LOAD CSV WITH HEADERS FROM "file:/IndianCities.csv" As line WITH line WHERE line.Latitude IS NOT NULL

MERGE (c:Country {name: line.Country}) MERGE (s:State {name: line.State}) MERGE (ci:City {id: line.Id, name: line.City, latitude: line.Latitude, longitude: line.Longitude})

MERGE (c)-[:STATE]->(s) MERGE (s)-[:CITY]-> (ci)`

Not able to load relationships from CSV

2 Answers2