GraphConnect Europe 2016 Schedule graph

graphconnect europe 2016 graphgist.adoc

The GraphConnect Europe 2016 Schedule Graph

Yey! It’s that time of the year again! We are full-steam getting ready for the Bi-Yearly Festival of Graphs also known as GraphConnect. There’s another great conference lined up for us. The entire Neo4j crew will be there in full force - and of course we had to create another Schedule graph - just for fun.

A Google Sheet as the main repository

I had to of course start from the schedule on the GraphConnect website, and convert that into a google sheet with all the data. Once I had that, I could add the data pretty easily with this model:

Very simple - but it’s so much nicer when you can make it interactive and load it into Neo4j. Let’s do that. Let’s load that data into this graphgist.

load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
merge (d:Day {date: toInt(csv.day)});

//connect the days
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2);

//add the rooms, tracks, speakers and speaker's companies
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c);

//add the timeslots to each day
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d);

//add the sessions and connect them up
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t);

Let’s take a look at what we have now:

Ok - so that looks like a big fat hairball. Not very useful. So let’s try to zoom in a bit, and run a simple query over our graph: let’s find a couple of sessions in Day 1:

match (d:Day {date:20160426})<--(t:Time)<--(s:Session)--(connections)
return d,t,s,connections
limit 50

and here’s a sample of the graph:

Let’s do another query:

match path = allshortestpaths( (p1:Person)-[*]-(p2:Person) )
where p1.name contains "Hunger"
and p2.name contains "Webber"
return path

and display the result

Let’s now look at a link between a person (Jim Webber, of Neo fame) and an Organisation (ICIJ, of PanamaPaper fame).

match (c:Company {name:"ICIJ"}), (p:Person {name:"Jim Webber"}),
path = allshortestpaths( (c)-[*]-(p) )
return path

and again diplay the result:

Last one for fun: let’s look at the sessions that have more than one speaker:

match (s:Session)-[r:SPEAKS_IN]-(p:Person)
with s, collect(p) as person, count(p) as count
where count > 1
return s,person

and display it:

Just a start…​

There are so many other things that we could look at. Use the console below to explore if you are interested in more.

I hope this gist was interesting for you, and that we will see each other soon.

This gist was created by Rik Van Bruggen

• My Blog

• the Graphistania Neo4j Graph Database Podcast

• My Book

• On Twitter

• On LinkedIn

import graphconnect europe 2016 schedule.cql

create index on :Company(name);
create index on :Day(date);
create index on :Time(time);
create index on :Room(name);
create index on :Session(title);
create index on :Track(name);

schema await;

// schedule
https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967

//add the days
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
merge (d:Day {date: toInt(csv.day)});

//connect the days
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2);

//add the rooms, traxcks, speakers and speaker's companies
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c);

//add the timeslots to each day
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d);

//add the sessions and connect them up
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t);



//ALL IN ONE QUERY
//add the hosts
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg/export?format=csv&id=10sswmRmY5FjYMLU5c5rJlXr4m9Idxw7tC8OImb7v4Yg&gid=16326967" as csv
merge (d:Day {date: toInt(csv.day)})
with csv
match (d:Day), (d2:Day)
where d.date = d2.date-1
merge (d)-[:PRECEDES]-(d2)
with csv
merge (r:Room {name: csv.room})
merge (t:Track {name: csv.track})
merge (p:Person {name: csv.speaker, title: csv.title})
merge (c:Company {name: csv.company})
merge (p)-[:WORKS_FOR]->(c)
with csv
match (d:Day {date: toInt(csv.day)})
merge (t1:Time {time: toInt(csv.starttime)})-[:PART_OF]->(d)
merge (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d)
with csv
match (t2:Time {time: toInt(csv.endtime)})-[:PART_OF]->(d:Day {date: toInt(csv.day)})<-[:PART_OF]-(t1:Time {time: toInt(csv.starttime)}), (r:Room {name: csv.room}), (t:Track {name: csv.track}), (p:Person {name: csv.speaker, title: csv.title})
merge (s:Session {title: csv.talk})
merge (s)<-[:SPEAKS_IN]-(p)
merge (s)-[:IN_ROOM]->(r)
merge (s)-[:STARTS_AT]->(t1)
merge (s)-[:ENDS_AT]->(t2)
merge (s)-[:IN_TRACK]->(t);

query graphconnect europe 2016.cql

//Querying the GraphConnect Europe 2016 Schedule

//Look at day 2
match (d:Day {date:20160426})<--(t:Time)<--(s:Session)--(connections)
return d,t,s,connections
limit 50

//Look at two people
match (p1:Person), (p2:Person),
path = allshortestpaths( (p1)-[*]-(p2) )
where p1.name contains "Hunger"
and p2.name contains "Webber"
return path

//Look at a person and a company
match (c:Company {name:"ICIJ"}), (p:Person {name:"Jim Webber"}),
path = allshortestpaths( (c)-[*]-(p) )
return path

//Look at sessions with more than one speaker
match (s:Session)-[r:SPEAKS_IN]-(p:Person)
with s, collect(p) as person, count(p) as count
where count > 1
return s,person

schedule.csv