Filtering relations on attributes

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

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

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

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

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

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

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

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

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

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

Yes, I got a table with all relations.

Stefan

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

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

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.

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

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 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:

Hope this works for you.

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