Filtering relations on attributes

Neo4j Graph Platform Cypher
1

Hello,
I am at the absolute beginning of learning Cypher and need some help for the first time.
I have in my DB airports as nodes and flights as relations. Now I only want to see the flights that happen on only one day. Unfortunately I don't get any results back. Here is my query:

MATCH path=(n:Airport{id: "TIA"})<-[:Flight]->(f:Airport)<-[:Flight]->(e:Airport{id: "LUX"})
Where all(rel in relationships(path) Where date(rel.dateSTD) = '2019-12-02')
RETURN n, f, e
LIMIT 25

The first part (without "Where") gives me three nodes ("TIA", "FRA" and "LUX") but all flights are shown. Therefore I would like to see only the flights, which only happens on 02.Dec 2019.

I imported the relations via a CSV with

WITH "file:///flights.csv" AS uri
LOAD CSV WITH HEADERS FROM uri AS row
MATCH (origin:Airport {id: row.Origin})
MATCH (destination:Airport {id: row.Destination})
MERGE (origin)-[:Flight {name: toString(row.Flight),
dateSTD: DateTime(row.SDTD),
dateSTA: DateTime(row.SDTA),
distance: toFloat(row.Block)}]->(destination)

Please, can someone give me a hint what I'm doing wrong?

Thank you

Stefan

2

Did you try:

MATCH (n:Airport{id: "TIA"})-[rel1:Flight]-(f:Airport)-[rel2:Flight]-(e:Airport{id: "LUX"})
WHERE date(rel1.dateSTD) = '2019-12-02' OR date(rel2.dateSTD) = '2019-12-02'
RETURN n, f, e
LIMIT 25

Elaine

3

Dear Elaine,

yes, I have tried some thing similar to your query before. The result is also nothing. I forgot to mention that in my CSV I import the date in the following format: "2019-12-01T06:30:00.000+0100" maybe that's it.

Thanks for your reply

Stefan

4

Yes, it is important to make sure that the data imported can be properly interpreted for queries.

You might want to check out this resource on date/times:

Elaine

5

Hello, Elaine,

Yes, I noticed. It can also be imported without any problems. Can I somehow determine whether it was really saved as a datetime value in neo4j or as a string?

Thanks

Stefan

6

According to your MERGE statement, it has been stored as a datetime value.

To compare it, you will need to do something like this:

WHERE rel1.dateSTD = date({year: 2019, month: 6, day: 1})

Elaine

7

Again no results! It looks like neo4j will save my datetime as a string.

But also the following:

MATCH (n:Airport{id: "TIA"})-[rel1:Flight]-(f:Airport)-[rel2:Flight]-(e:Airport{id: "LUX"})
WHERE rel1.dateSTD contains '2019-12-01'
RETURN n, f, e
LIMIT 25

It doesn't produce any results either. Have I chosen the right data model?

Thanks for your support

Stefan

8

Try this:

MATCH (n:Airport{id: "TIA"})-[rel1:Flight]-(f:Airport)-[rel2:Flight]-(e:Airport{id: "LUX"})
WHERE rel1.dateSTD = date('2019-12-01')
RETURN n, f, e
LIMIT 25

9

Still no result. It looks like a query of attributes in relations is not possible. Maybe I have to import the flights as nodes with departing flights and arriving flights as relations to the airports.

Thanks ones again

Stefan

10

It is indeed possible.

What do you see when you specify this query:

MATCH (n:Airport{id: "TIA"})-[rel1:Flight]-(f:Airport)-[rel2:Flight]-(e:Airport{id: "LUX"})
//WHERE rel1.dateSTD = date('2019-12-01')
RETURN n.id, rel1.dateSTD, f.id, rel2.dateSTD,e.id
LIMIT 25

This should return a table with values from your graph.

Elaine

11

Yes, I got a table with all relations.

Stefan

12

Hello,
here are my test data in two csv files:

"Iata","Name","latitude","longitude"
"FRA","Frankfurt am Main International Airport",50.026402,8.54313
"TIA","Tirana International Airport Mother Teresa",41.4146995544,19.7206001282
"LUX","Luxembourg-Findel International Airport",49.626598358154,6.2115201950073

"Flight","AL","FNR","Origin","Destination","SDTD","SDTA","Block"
"AX399","AX","399","LUX","FRA","2019-12-01T06:50:00.000+0100","2019-12-01T07:40:00.000+0100",0.83
"AX395","AX","395","LUX","FRA","2019-12-02T14:20:00.000+0100","2019-12-02T15:10:00.000+0100",0.83
"AX393","AX","393","LUX","FRA","2019-12-02T11:15:00.000+0100","2019-12-02T12:05:00.000+0100",0.83
"AX1455","AX","1455","TIA","FRA","2019-12-01T06:10:00.000+0100","2019-12-01T08:30:00.000+0100",2.33
"AX397","AX","397","LUX","FRA","2019-12-02T18:40:00.000+0100","2019-12-02T19:30:00.000+0100",0.83
"AX393","AX","393","LUX","FRA","2019-12-01T11:05:00.000+0100","2019-12-01T11:55:00.000+0100",0.83
"AX395","AX","395","LUX","FRA","2019-12-01T14:20:00.000+0100","2019-12-01T15:10:00.000+0100",0.83
"AX399","AX","399","LUX","FRA","2019-12-02T06:35:00.000+0100","2019-12-02T07:25:00.000+0100",0.83
"AX397","AX","397","LUX","FRA","2019-12-01T18:40:00.000+0100","2019-12-01T19:30:00.000+0100",0.83
"AX391","AX","391","LUX","FRA","2019-12-02T08:35:00.000+0100","2019-12-02T09:25:00.000+0100",0.83
"AX1425","AX","1425","TIA","FRA","2019-12-01T13:50:00.000+0100","2019-12-01T16:10:00.000+0100",2.33
"AX1425","AX","1425","TIA","FRA","2019-12-02T13:50:00.000+0100","2019-12-02T16:10:00.000+0100",2.33
"AX391","AX","391","LUX","FRA","2019-12-01T08:30:00.000+0100","2019-12-01T09:20:00.000+0100",0.83
"AX1455","AX","1455","TIA","FRA","2019-12-02T06:05:00.000+0100","2019-12-02T08:25:00.000+0100",2.33
"AX392","AX","392","FRA","LUX","2019-12-01T09:50:00.000+0100","2019-12-01T10:30:00.000+0100",0.67
"AX390","AX","390","FRA","LUX","2019-12-02T07:20:00.000+0100","2019-12-02T08:00:00.000+0100",0.67
"AX394","AX","394","FRA","LUX","2019-12-02T12:40:00.000+0100","2019-12-02T13:20:00.000+0100",0.67
"AX392","AX","392","FRA","LUX","2019-12-02T10:00:00.000+0100","2019-12-02T10:40:00.000+0100",0.67
"AX394","AX","394","FRA","LUX","2019-12-01T12:50:00.000+0100","2019-12-01T13:30:00.000+0100",0.67
"AX390","AX","390","FRA","LUX","2019-12-01T07:10:00.000+0100","2019-12-01T07:50:00.000+0100",0.67
"AX396","AX","396","FRA","LUX","2019-12-02T17:10:00.000+0100","2019-12-02T17:50:00.000+0100",0.67
"AX398","AX","398","FRA","LUX","2019-12-01T21:30:00.000+0100","2019-12-01T22:10:00.000+0100",0.67
"AX398","AX","398","FRA","LUX","2019-12-02T21:30:00.000+0100","2019-12-02T22:10:00.000+0100",0.67
"AX396","AX","396","FRA","LUX","2019-12-01T17:10:00.000+0100","2019-12-01T17:50:00.000+0100",0.67
"AX1424","AX","1424","FRA","TIA","2019-12-01T11:15:00.000+0100","2019-12-01T13:15:00.000+0100",2
"AX1424","AX","1424","FRA","TIA","2019-12-02T11:15:00.000+0100","2019-12-02T13:15:00.000+0100",2
"AX1454","AX","1454","FRA","TIA","2019-12-02T20:40:00.000+0100","2019-12-02T22:40:00.000+0100",2
"AX1454","AX","1454","FRA","TIA","2019-12-01T20:45:00.000+0100","2019-12-01T22:45:00.000+0100",2

This are the commands to import the data:

WITH "file:///airports.csv" AS uri
LOAD CSV WITH HEADERS FROM uri AS row
MERGE (place:Airport {id:row.Iata})
SET place.latitude = toFloat(row.latitude),
place.longitude = toFloat(row.longitude),
place.name = toString(row.Iata),
place.APName = toString(row.Name)

Using Periodic Commit
LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS row
MATCH (origin:Airport {id: row.Origin})
MATCH (destination:Airport {id: row.Destination})
MERGE (origin)-[:Flight {name: toString(row.Flight),
dateSTD: DateTime(row.SDTD),
dateSTA: DateTime(row.SDTA),
distance: toFloat(row.Block)}]->(destination)

Maybe you can test it on your installation or I'm doing something wrong on import?

Thanks

Stefan

13

Can some one help me with to solve my problem, please?

14

This should work:

MATCH (n:Airport{id: "TIA"})-[rel1:Flight]-(f:Airport)-[rel2:Flight]-(e:Airport{id: "LUX"})
WHERE date(rel1.dateSTD) = date("2019-12-02") AND date(rel2.dateSTD) = date("2019-12-02")
RETURN *
LIMIT 25

The problem with your first query is that you are comparing a string with a date.

15

Thanks, but it still doesn't work. I can see also flights at 1th of December in my results.

You can see in my test data above that I have imported flights from 1th and 2nd December, but I wand to filter for only flights on one day.

Stefan

16

Here flights are hidden in the relationship. Actually flight numbers are very important in air travel and hence should be treated as a separate node.

Here is how I solve this using your data:

Airports Cypher is correct and used that.

Modified the flights Cypher:

LOAD CSV WITH HEADERS FROM "file:///flights.csv" AS row

MATCH (origin:Airport) where origin.id = row.Origin
MATCH (destination:Airport) where destination.id = row.Destination

MERGE (f:Flight {name: row.Flight, departure: row.SDTD, arrival: row.SDTA, distance: toFloat(row.Block), destination: row.Destination})
MERGE (origin)-[:FLIGHT]->(f)
MERGE (f)-[:DESTINATION]->(destination)
;

Result:

flights
flights750×397 118 KB

Flights on 02.Dec 2019:

match (a:Airport)-[:FLIGHT]->(d:Flight)-[:DESTINATION]->(e:Airport)
where apoc.date.format(apoc.date.parse(d.departure, 'ms', 'yyyy-MM-dd'), 'ms', 'yyyy-MM-dd') = "2019-12-02"
return a, d, e

You have to use APOC date format and parse functions to convert dateSTD.

Result:

flights2
flights2826×313 105 KB

Hope this works for you.

17

Thanks a lot.

With this query I receive the expected result:

match (a:Airport{id:"TIA"})-[:FLIGHT]->(d:Flight)-[:DESTINATION]->(f:Airport)-[:FLIGHT]->(g:Flight)-[:DESTINATION]->(e:Airport{id:"LUX"})
where apoc.date.format(apoc.date.parse(d.departure, 'ms', 'yyyy-MM-dd'), 'ms', 'yyyy-MM-dd') = "2019-12-02"
AND apoc.date.format(apoc.date.parse(g.departure, 'ms', 'yyyy-MM-dd'), 'ms', 'yyyy-MM-dd') = "2019-12-02"
return a, d, e, f, g

Stefan