I have a large database of 155 million nodes with properties osm_id, node_id, latitude, longitude. I didn't make the nodes of type point, but that can be an option if makes processing faster. I added indexing for node_id because I will need that later to find the nodes to start traversal in the graph.
There are some nodes in the database with the same latitude and longitude values. I would like to add an EQUAL_TO relationship between them. At least one of these pairs of nodes currently has only one relationship (it is at the end of a graph chain) so I first search for these nodes, and try to find some other nodes with the same latitude and longitude values. There are around 11 million nodes which have only one relationship. These are candidates for having an EQUAL_TO relationship to some other node. I tried the following query:
CALL {
MATCH (n1:WaterNode)
WITH n1, size([(n1)-[:NEXT_TO]-() | 1]) AS relCount
WHERE relCount = 1
WITH n1
MATCH (n2:WaterNode)
WHERE n1 <> n2 AND n1.latitude = n2.latitude AND n1.longitude = n2.longitude
MERGE (n1)-[r:EQUAL_TO]-(n2)
SET r.distance = 0
} IN TRANSACTIONS of 1000 rows;
This query has been running for over 30 hours with no sign of finishing. Could this query be improved? I would prefer to only change this query and not change the database structure but that could be an option too. Should I create point type nodes when loading them from a CSV file and add indexing by the point, to make matching them by latitude and longitude faster? The best case would be to search all nodes for other EQUAL_TO nodes and not restrict to nodes at the end of chains, but that would be n times n queries.
Thank you.
Oh boy, for each of your 11 million nodes, a full scan will be done for each WaterNode node. You could try a composite index for WaterNode label and your latitude and longitude properties.
I would try that first, as it doesn’t require you to update every node with a point property.
BTW, you can use the count subquery directly instead of indirectly with list comprehension.
CALL {
MATCH (n1:WaterNode)
WHERE count{(n1)-[:NEXT_TO]-()} = 1
WITH n1
MATCH (n2:WaterNode)
WHERE n1 <> n2 AND n1.latitude = n2.latitude AND n1.longitude = n2.longitude
MERGE (n1)-[r:EQUAL_TO]-(n2)
SET r.distance = 0
} IN TRANSACTIONS of 1000 rows;
Thank you for your help.
It is strange, but this query got a memory overrun at 5.6GB even when going down to 10 rows per transaction. I will try a different strategy of creating those relationships and generate the pairs of nodes with the same location data before importing them into neo4j.
Thanks again for the quick answer.
Your problem is the IN_TRANSACTIONS counts rows that come into the CALL. Since only one row comes in then everything happens in one row. Try this instead.
MATCH (n1:WaterNode)
WHERE count{(n1)-[:NEXT_TO]-()} = 1
CALL {
WITH n1
MATCH (n2:WaterNode)
WHERE n1 <> n2 AND n1.latitude = n2.latitude AND n1.longitude = n2.longitude
MERGE (n1)-[r:EQUAL_TO]-(n2)
SET r.distance = 0
} IN TRANSACTIONS of 1000 rows;
I don't think it works that way. If you look at the EXPLAIN plan for both versions, they each process all the steps and end with a TransactionsForeach step. What it looks like to me is that the data is derived and then the rows are batched.
Here is a very simple stripped down example showing the two approaches, but the plans look very similar in terms of the transaction processing.
Thank you for your help. I ended up generating a table with duplicates in Postgres, exported it to CSV, imported it into Neo4j. In this way I looked up the duplicate nodes by node_id and created an EQUAL_TO relationship between them.
True but the count is based on the left branch. If you run this with enough creates to get the out of memory error with the second one, you will find that the first still works. I've seen this happen when one of our developers put the call in the wrong spot, like your second example, and ran out of memory. Once they moved the call down it worked like a charm.
