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

(Shane A Husson) #1

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'})
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) #2

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),
RETURN a,b,c LIMIT 100;

(Joe Depeau) #3

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

MATCH p1 = (a:Crime {type: 'Drugs'})-[:OCCURRED_AT]->(l:Location)
     WITH p1, l, split(, '/') as drugdate
MATCH p2 = (b:Crime {type: 'Bicycle theft'})-[:OCCURRED_AT]->(l)
     WITH p1, p2, l, drugdate, split(, '/') as bikedate
MATCH p3 = (c:Crime {type: 'Burglary'})-[:OCCURRED_AT]->(l)
     WITH p1, p2, p3, l, drugdate, bikedate, split(, '/') 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 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) #4

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.