Find multiple crimes in the same location but happened at a relative time to each other

[shane_a_husson]

I'm testing out Neo4j for a potential use case we have using the Crime Investigation sandbox. Essentially I want to find a sequence of crimes that happened in the same location.

In particular: "Burglary" crimes that happened after "Bicycle theft" in which the Bicycle theft happened after "Drugs" crimes .

I can find the first part easily enough:

MATCH p =(a:Crime{type:'Burglary'})-[:OCCURRED_AT*2]-(b:Crime{type:'Bicycle theft'})
WHERE a.date > b.date
return p LIMIT 100;

But how would I chain the second part of the question ie: "Bicycle theft" after "Drugs" crimes?

I've asked a similar question on SO

[michael_hunger]

You probably want to add an arrow to your relationship.

And then just add a location:

MATCH (a:Crime{type:'Burglary'})-[:OCCURRED_AT]->(loc),
             (b:Crime{type:'Bicycle theft'})-[:OCCURRED_AT]->(loc),
             (c:Crime{type:'Drugs'})-[:OCCURRED_AT]->(loc)
WHERE a.date > b.date AND b.date > c.date
RETURN a,b,c LIMIT 100;

[joe_depeau]

So, I came up with something a little more complicated:

MATCH p1 = (a:Crime {type: 'Drugs'})-[:OCCURRED_AT]->(l:Location)
     WITH p1, l, split(a.date, '/') as drugdate
MATCH p2 = (b:Crime {type: 'Bicycle theft'})-[:OCCURRED_AT]->(l)
     WITH p1, p2, l, drugdate, split(b.date, '/') as bikedate
MATCH p3 = (c:Crime {type: 'Burglary'})-[:OCCURRED_AT]->(l)
     WITH p1, p2, p3, l, drugdate, bikedate, split(c.date, '/') as burglarydate
WHERE date( {year: toInteger(bikedate[2]), month: toInteger(bikedate[1]), day: toInteger(bikedate[0])} ) > date( {year: toInteger(drugdate[2]), month: toInteger(drugdate[1]), day: toInteger(drugdate[0])} )
     AND date( {year: toInteger(burglarydate[2]), month: toInteger(burglarydate[1]), day: toInteger(burglarydate[0])} ) > date( {year: toInteger(bikedate[2]), month: toInteger(bikedate[1]), day: toInteger(bikedate[0])} )
RETURN p1, p2, p3

The reason for this is that this demo was built against an older version of Neo4j which didn't support native temporal property types - and so, the type of the Crime.date properties is STRING. The query above takes the strings and converts them to date types, so that we can do proper comparisons. Without that, I was getting unexpected results. A good endorsement for the native temporal property types, and also a good reminder that I should update the database for the sandbox 🙂

Let me know how you get on!

[shane_a_husson]

Thanks @joe.depeau, that query is indeed the one I was looking for. I now have a related performance issue though which I created a separate post for: How to improve the performance of a query that compares properties relative to other nodes?. Any help there would be much appreciated.