Public transportation of So Paulo in a graph database IGOR ROZANI - - PowerPoint PPT Presentation

Public transportation of São Paulo in a graph database

IGOR ROZANI

Igor Rozani

@IgorRozani IgorRozani IgorRozani IgorRozani

Table of contents

▪What’s this project? ▪The database ▪Query examples ▪Q&A ▪The Hunger Games

What’s this project?

What’s this project?

Query examples

• Which lines does a company own?
• What station is part of which line?
• How many stations have each line?
• What is the shortest way between stations?

Which lines does a company own?

MATCH (c:Company)-[:OWN]-(l:Line) WITH c, l ORDER BY l.number, l.name WITH c, collect(CASE WHEN l.number IS NULL THEN l.name ELSE l.number + ' - ' + l.name END) as lines RETURN c.name AS Company, lines AS Lines ORDER BY c.name Company Lines "CPTM" ["7 - Ruby", "8 - Diamond", "9 - Emerald", "10 - Turquoise", "11 - Coral", "12

• Sapphire", "13 - Jade",

"Airport Connect", "Airport Express", "Touristic Express"] ... ... "ViaMobilidade" ["5 - Lilac"] "ViaQuatro" ["4 - Yellow"]

What station is part of which line?

MATCH (l:Line)-[:PART_OF]-(s) RETURN l.name AS Line, collect(s.name) AS Stations

Line Stations "Blue" ["Jabaquara", "Tiradentes", "Praça da Árvore", "Portuguesa - Tietê", "Luz", "São Bento", "Carandiru", "Parada Inglesa", "São Judas", "Saúde", "Paraíso", "Conceição", "Liberdade", "Jardim São Paulo - Ayrton Senna", "São Joaquim", "Santa Cruz", "Vergueiro", "Armênia", "Sé", "Ana Rosa", "Santana", "Tucuruvi", "Vila Mariana"] "Green" ["Trianon-Masp", "Vila Prudente", "Chácara Klabin", "Santuário Nossa senhora de Fátima - Sumaré", "Ana Rosa", "Clínicas", "Santos Imigrantes", "Vila Madalena", "Paraíso", "Brigadeiro", "Consoloação", "Alto do Ipiranga", "Tamanduateí", "Sacomã"] "Red" ["Anhangabaú", "Artur Alvim", "Bresser - Mooca", "Guilermina - Esperança", "Mal. Deodoro", "Belém", "Pedro II", "Tatuapé", "República", "Patriarca", "Carrão", "Sé", "Penha", "Corinthians - Itaquera", "Vila Matilde", "Brás", "Palmeiras - Barra Funda", "Santa Cecília"]

How many stations have each line?

MATCH (l:Line)-[:PART_OF]-(s) WITH l, count(s) as qtd RETURN l.name as Line, qtd ORDER BY qtd DESC

Line qtd "Blue" 23 "Diamond" 20 "Red" 18 "Emerald" 18 "Lilac" 17 ... ... "Diamond - Operational Extension" 3 "Jade" 3 "Orca Shuttle Service" 2 "Airport Express" 2

What is the shortest way between stations?

What is the shortest way between stations?

Directions ["Grajaú", "Primavera - Interlagos", "Autódromo", "Jurubatuba", "Socorro", "Santo Amaro", "Granja Julieta", "Morumbi", "Brooklin“, "Diadema", "Piraporinha", "São Bernardo", "Santo André", "Capuava", "Mauá", "Guapituba", "Ribeirão Pires", "Rio Grande da Serra"] MATCH x = shortestPath((s1{name:"Grajaú"})-[:CONNECT*]-(s2{name:"Rio Grande da Serra"})) RETURN EXTRACT(n IN NODES(x) | n.name) AS Directions

What is the shortest way between stations?

What is the shortest way between stations?

MATCH (s1{name:"Grajaú"}), (s2{name:"Rio Grande da Serra"}), p = shortestPath((s1)-[:CONNECT*]-(s2)) WHERE ALL (x IN RELATIONSHIPS(p) WHERE x.transport='train' OR x.transport='metro') RETURN EXTRACT(n IN NODES(p) | n.name) AS Directions Directions ["Grajaú", "Primavera - Interlagos", "Autódromo", "Jurubatuba", "Socorro", "Santo Amaro", "Largo Treze", "Adolpho Pinheiro", "Alto da Boa Vista", "Borba Gato", "Brooklin", "Campo Belo", "Eucaliptos", "Moema", "AACD - Servidor", "Hospital São Paulo", "Vila Mariana", "Chácara Klabin", "Santos Imigrantes", "Alto do Ipiranga", "Sacomã", "Tamanduateí", "São Caetano do Sul", "Utinga", "Prefeito Saladino", "Santo André", "Capuava", "Mauá", "Guapituba", "Ribeirão Pires", "Rio Grande da Serra"]

What is the shortest way between stations?

Learn more about it

This project is available on GitHub. https://github.com/IgorRozani/Public-Transport-SP-Graph-Database

Q&A

Thank you!

The Hunger Games

1 - Which of the following options is not a node label in the project? A) MetroStation B) PointOfInterest C) Connect 2 - What does the following query do? MATCH (n) WHERE n.hasBikeAttachingPost = true OR n.hasBikeParkingTerminal = true RETURN n.name ORDER BY n.name DESC A) Get all nodes that have true in the property "hasBikeAttachingPost" or "hasBikeParkingTerminal" and return the property "name" sorted by descending order B) Get all nodes that have true in the property "hasBikeAttachingPost" and "hasBikeParkingTerminal" and return the property "name" sorted by descending

• rder

C) Get all nodes that have true in the property "hasBikeAttachingPost" or "hasBikeParkingTerminal" and return the property "name" sorted by ascending order 3 - Which of the queries can get the shortest path between two stations, using only train transport? A) MATCH (s1{name:"Grajaú"}), (s2{name:"Rio Grande da Serra"}), p = shortestPath((s1)-[:CONNECT*]-(s2)) WHERE ALL (x IN RELATIONSHIPS(p) WHERE x.transport='train') RETURN EXTRACT(n IN NODES(p) | n.name) AS Directions B) MATCH (s1{name:"Grajaú"}), (s2{name:"Rio Grande da Serra"}), p = shortestPath((s1)-[:CONNECT*]-(s2)) WHERE ALL (x IN RELATIONSHIPS(p) WHERE x.transport='train' OR x.transport='metro') RETURN EXTRACT(n IN NODES(p) | n.name) AS Directions C) MATCH x = shortestPath((s1{name:"Grajaú"})-[:CONNECT*]- (s2{name:"Rio Grande da Serra"})) RETURN EXTRACT(n IN NODES(x) | n.name) AS Directions Answer here: http://r.neo4j.com/hunger-games