Neo4j

stefan · ‎11-20-2019

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

elaine_rosenber · ‎11-20-2019

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

stefan · ‎11-20-2019

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

elaine_rosenber · ‎11-20-2019

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

stefan · ‎11-20-2019

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

elaine_rosenber · ‎11-20-2019

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

stefan · ‎11-21-2019

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

elaine_rosenber · ‎11-21-2019

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

stefan · ‎11-25-2019

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

elaine_rosenber · ‎11-25-2019

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

stefan · ‎11-28-2019

Yes, I got a table with all relations.

Stefan

stefan · ‎11-28-2019

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

stefan · ‎12-05-2019

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

shan · ‎12-05-2019

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.

stefan · ‎12-06-2019

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

ameyasoft · ‎12-07-2019

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.

stefan · ‎12-09-2019

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

Neo4j

Filtering relations on attributes