Create a relationship route with node created in a FOREACH statement

[Zaszigre]

Hello everyone,

For an educational purpose (I'm learnig Neo4J, so some of my syntaxes or design decisions may be incorrect), I have a CSV file of bus lines containing a JSON column with the geographic points of all the Bus line's stops. I have to import it in Neo4J to add value to these data.

I've already imported the Busline's nodes (1 node by row) and skipped the Json column. I'm now trying to MERGE one node by bus stop (by geographic point) and to create a [:NEXT_TO] (or [:FOLLOWS], I'm not yet sure which name fits the best) relationship between adjacents points in the bus line.

here is an exemple of how the Json string is formated:

{
    "type" : "LineString",
    "coordinates" : [
        [2.72300, 48.8905], [2.72272, 48.89037], [2.72246, 48.89017]
    ]
}

I'm creating the bus stops node in a FOREACH and I would like to create relationships like:

CALL apoc.convert.fromJsonMap(line.`Geo Shape`) YIELD value AS geoShape

FOREACH(point in geoShape.coordinates | 
    MERGE (s:BusStop {
        longitude = point[0],
        latitude = point[1]
     } )
    (s)-[:NEXT_TO]->(previousCreatedNode)
    (previousCreatedNode)-[:NEXT_TO]->(s)
)

I found a similar topic computing train stations in a CSV file but the main difference is that the station in the autor's CSV file are numbered. [Topic: CSV Import]

Thank you for your help !

[ganesanmithun32 · Accepted solution]

CALL apoc.convert.fromJsonMap(line.`Geo Shape`) YIELD value AS geoShape
WITH  geoShape.coordinates as coordinates
WITH coordinates , RANGE(0,SIZE(coordinates)-2) as iterateList 
FOREACH(x in iterateList | 
MERGE (s1:BusStop {latLong:Point({longitude:coordinates[x][0],latitude:coordinates[x][1]})})
MERGE (s2:BusStop {latLong:Point({longitude:coordinates[x+1][0],latitude:coordinates[x+1][1]})}) 
MERGE  (s2)-[:NEXT_TO]->(s1)
)

I have stored the lat long as POINT , so you can use it to compute distance between two bus stops now .

and there is no need to create two relations between consecutive bustops as its redundant.

And you may want to store some bus number or something in the relations to identify which buses go from one stop to the other.

[Zaszigre]

Hello, thank you very much for your answer !
I just check it works before to mark the topic as solved.

However, isn't it redundant to set a bus line id attribute in :NEXT_TO whereas I plan to create a relationship between BusStop nodes and BusLines (containing, among others, the busline id) Nodes ?

EDIT:
As I said, I have a Json with two fields. My exemple shows a 'LineString' type but many rows has a 'MultiString' type representing buslines having alternative ways.

for 'MultiString' types, coordinates stores a 3 level nested array: an array of lines beeing arrays of bus stops beeing arrays of floats : [ [ [float, float ], [ float, float ], ... ], [ ... ] , ... ].

With this particular context, I'm using FOREACH( CASE ) syntax two discriminates those two cases. Unfortunately, having the coordinates alliases outside the the FOREACH statement throws the following error :

 Variable `geoShape` not defined (line 9, column 32 (offset: 356) )
"        FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |"
                                    ^ 

With the following code :

LOAD CSV WITH HEADERS FROM "file:///bus_lignes.csv" AS line FIELDTERMINATOR ";"

MATCH (l:BusLine) WHERE l.id_ligne = line.ID

CALL apoc.convert.fromJsonMap(line.`Geo Shape`) YIELD value AS geoShape
WITH geoShape.coordinates as coordinates
WITH coordinates , RANGE(0, SIZE(coordinates)-2) as iterList
FOREACH(i in iterList |
    FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |
    MERGE (s0:BusStop:GeographicPoint {
    point : Point({
            longitude : coordinates[i][0],
                latitude : coordinates[i][1]
            })
  })
        MERGE (s1:BusStop:GeographicPoint {
        point : Point({
            longitude : coordinates[i+1][0],
                latitude : coordinates[i+1][1]
            })
        })
        MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        busLineId : l.id_ligne
        }]->(s1)
) FOREACH(trash in CASE WHEN geoShape.type = 'MultiString' THEN[1] ELSE[] END |
    FOREACH(subline in coordinates |
WITH subline , RANGE(0, SIZE(subline)-2) as iterList
            FOREACH(i in iterList |
                MERGE (s0:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i][0],
                        latitude : subline[i][1]
                    })
                })
                MERGE (s1:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i+1][0],
                        latitude : subline[i+1][1]
                    })
                })
                MERGE (s0)-[:SERVED_BY]->(l)
                MERGE (s1)-[:SERVED_BY]->(l)
                MERGE (s0)-[:NEXT_TO {
                    busLineId : l.id_ligne
                }]->(s1)
            )
        )
    )
)

However, putting the aliasing things inside the FOREACH gives the following error:

Invalid use of WITH inside FOREACH (line 8, column 3 (offset: 279))
"    WITH geoShape.coordinates as coordinates"
     ^

caused by the following code :

LOAD CSV WITH HEADERS FROM "file:///bus_lignes.csv" AS line FIELDTERMINATOR ";"

MATCH (l:BusLine) WHERE l.id_ligne = line.ID

CALL apoc.convert.fromJsonMap(line.`Geo Shape`) YIELD value AS geoShape

FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |
  WITH geoShape.coordinates as coordinates
  WITH coordinates , RANGE(0, SIZE(coordinates)-2) as iterList

  FOREACH(i in iterList |
    MERGE (s0:BusStop:GeographicPoint {
    point : Point({
            longitude : coordinates[i][0],
                latitude : coordinates[i][1]
            })
  })
        MERGE (s1:BusStop:GeographicPoint {
        point : Point({
            longitude : coordinates[i+1][0],
                latitude : coordinates[i+1][1]
            })
        })
        MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        busLineId : l.id_ligne
        }]->(s1)
)
)
FOREACH(trash in CASE WHEN geoShape.type = 'MultiString' THEN[1] ELSE[] END |
      WITH geoShape.coordinates as coordinates

      FOREACH(subline in coordinates |
WITH subline , RANGE(0, SIZE(subline)-2) as iterList
            FOREACH(i in iterList |
                MERGE (s0:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i][0],
                        latitude : subline[i][1]
                    })
                })
                MERGE (s1:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i+1][0],
                        latitude : subline[i+1][1]
                    })
                })
                MERGE (s0)-[:SERVED_BY]->(l)
                MERGE (s1)-[:SERVED_BY]->(l)
                MERGE (s0)-[:NEXT_TO {
                    busLineId : l.id_ligne
                }]->(s1)
            )
      )
)

How can I work around this limitation ? May be I have a bad understanding of the statement I'm using ?
I'm sorry for this amount of code, I wanted to be exhaustive for a better understanding of my work.

Thank you for your help !

[ganesanmithun32]

Inside FOREACH , you can only create or merge stuff and cant use the nodes or relations formed outside the foreach .

LOAD CSV WITH HEADERS FROM "file:///bus_lignes.csv" AS line FIELDTERMINATOR ";"

MATCH (l:BusLine) WHERE l.id_ligne = line.ID

CALL apoc.convert.fromJsonMap(line.`Geo Shape`) YIELD value AS geoShape
WITH geoShape.coordinates as coordinates,geoShape
WITH coordinates,geoShape , RANGE(0, SIZE(coordinates)-2) as iterList
FOREACH(i in iterList |
    FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |
    MERGE (s0:BusStop:GeographicPoint {
    point : Point({
            longitude : coordinates[i][0],
                latitude : coordinates[i][1]
            })
  })
        MERGE (s1:BusStop:GeographicPoint {
        point : Point({
            longitude : coordinates[i+1][0],
                latitude : coordinates[i+1][1]
            })
        })
        MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        busLineId : l.id_ligne
        }]->(s1)
) FOREACH(trash in CASE WHEN geoShape.type = 'MultiString' THEN[1] ELSE[] END |
    FOREACH(subline in coordinates |
WITH subline , RANGE(0, SIZE(subline)-2) as iterList
            FOREACH(i in iterList |
                MERGE (s0:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i][0],
                        latitude : subline[i][1]
                    })
                })
                MERGE (s1:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i+1][0],
                        latitude : subline[i+1][1]
                    })
                })
                MERGE (s0)-[:SERVED_BY]->(l)
                MERGE (s1)-[:SERVED_BY]->(l)
                MERGE (s0)-[:NEXT_TO {
                    busLineId : l.id_ligne
                }]->(s1)
            )
        )
    )
)

I just added geoShape in the consecutive WITH statements , so you can use it inside FOREACH now

WITH geoShape.coordinates as coordinates,geoShape
WITH coordinates,geoShape , RANGE(0, SIZE(coordinates)-2) as iterList

[Zaszigre]

Oh I understand ! Thank you for your help !
I've learned so much thanks to you.