CODEWITHSUNDEEP
neo4jcypherload-csv
Kunal Mukherjee
Kunal Mukherjee

Reputation: 5853

Not able to load relationships from CSV

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.

Upvotes: 0

Views: 77

Answers (2)

Kamal Murthy
Kamal Murthy

Reputation: 1

`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)`

Upvotes: 0

Rajendra Kadam
Rajendra Kadam

Reputation: 4052

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.

Upvotes: 2

Related Questions