Exploring the U-Bahn with Neo4j

01-introduction.md

Analyzing the U-Bahn Network with Neo4J

The goal of this exercise is to extract information about the Berlin metro system from Wikidata and to analyze its relationships with Neo4j.

02-wikidata-stations.xqy

xquery version "3.1";

declare namespace sparql = "http://www.w3.org/2005/sparql-results#";

let $query := "
  SELECT ?stationLabel ?coord
  WHERE {
    ?station wdt:P361 wd:Q68646 ;
    wdt:P625 ?coord .
    SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de'}
  }
"
  
let $request := " https://query.wikidata.org/bigdata/namespace/wdq/sparql" || "?query=" || fn:encode-for-uri($query)
let $records :=
  for $result in fn:doc($request)//sparql:result
  let $station := $result/sparql:binding[@name="stationLabel"]/sparql:literal/text()
  let $point := fn:replace($result/sparql:binding[@name="coord"]/sparql:literal/text(), "Point\((.*)\)", "$1")
  return
    element record {
       element name  { $station },
       element longitude { fn:tokenize($point, " ")[1] },
       element latitude { fn:tokenize($point, " ")[2] }
  }
return csv:serialize(element csv { $records }, map { 'header': true() })

03-stations.csv

04-wikidata-connections.xqy

xquery version "3.1";

declare namespace sparql = "http://www.w3.org/2005/sparql-results#";

let $query := "
SELECT ?stationLabel ?adjacentLabel ?lineLabel WHERE {
  ?station wdt:P361 wd:Q68646.
  ?station wdt:P197 ?adjacent.
  SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de'. }
  OPTIONAL { ?station wdt:P81 ?line. }
}
"
  
let $request := "https://query.wikidata.org/bigdata/namespace/wdq/sparql" || "?query=" || fn:encode-for-uri($query)
let $records :=
  for $result in fn:doc($request)//sparql:result
  let $station := $result/sparql:binding[@name="stationLabel"]/sparql:literal/text()
  let $adjacent := $result/sparql:binding[@name="adjacentLabel"]/sparql:literal/text()
  let $line :=  $result/sparql:binding[@name="lineLabel"]/sparql:literal/text()
return
    element record {
       element station { $station },
       element adjacent { $adjacent },
       element line { $line }
  }
return csv:serialize(element csv { $records }, map { 'header': true() })

05-connections.csv

06-neo4j.md

Load Nodes

LOAD CSV FROM 'https://gist.githubusercontent.com/CliffordAnderson/7fb7473af31f9343f8a55518545480a0/raw/0f8ba34e5240a4a5599c412cb59e5961cc6cbe33/stations.csv' AS line
CREATE (:Station { name: line[0], longitude: toFloat(line[1]), latitude: toFloat(line[2]) } )

Load Relationships

LOAD CSV FROM 'https://gist.githubusercontent.com/CliffordAnderson/7fb7473af31f9343f8a55518545480a0/raw/d82e2659e01a976cc47b727bdf6b3d7d4f35df08/connections.csv' AS line
MATCH (station:Station { name: line[0] })
MATCH (adjacent:Station { name: line[1] })
CREATE (station)-[:Line { name: line[2] }]->(adjacent)

Query Stations

match (a {name:"U-Bahnhof Walther-Schreiber-Platz"})-[r]-(c) return a,r,c

Query ShortestPaths

match (a:Station {name: "U-Bahnhof Voltastraße"})
match (b:Station {name: "U-Bahnhof Rosa-Luxemburg-Platz"})
match path = AllShortestPaths((a)-[*..5]->(b))
return path

Query Distance

match (a:Station {name: "U-Bahnhof Voltastraße"})
match (b:Station {name: "U-Bahnhof Rosa-Luxemburg-Platz"})
with point({ longitude: a.longitude, latitude: a.latitude }) AS start, point({ longitude: b.longitude, latitude: b.latitude }) AS stop
RETURN round(distance(start, stop)) AS travelDistance

Query Path Distances

MATCH (a:Station {name:'U-Bahnhof Walther-Schreiber-Platz'})
MATCH (b:Station {name:'U-Bahnhof Berliner Straße'})
MATCH p=(b)-[*]->(a)
WITH collect(p) as paths
CALL apoc.spatial.sortByDistance(paths) YIELD path, distance
RETURN path,distance

Route Around Problems

A problem with our analyses to this point is that we have duplicated relationships in our data. So, let's clean up the data set before conducting more advanced graph algorithms.

Drop Relationships

match (a)-[r]-(c)
delete r

Add De-Duplicated Relationship

LOAD CSV FROM 'https://gist.githubusercontent.com/CliffordAnderson/7fb7473af31f9343f8a55518545480a0/raw/6e2d565bb61767c686bd8bfd0dcfa7a69c1d027b/connections-no-dupes.csv' AS line
MATCH (station:Station { name: line[0] })
MATCH (adjacent:Station { name: line[2] })
CREATE (station)-[:Line { name: line[1] }]->(adjacent)

Query Centrality

CALL algo.closeness.stream('Station', 'Line')
YIELD nodeId, centrality

RETURN algo.asNode(nodeId).name AS node, centrality
ORDER BY centrality DESC
LIMIT 20;

07-wikidata-connections-no-dupes.xqy

xquery version "3.1";

import module namespace functx = 'http://www.functx.com';

declare namespace sparql = "http://www.w3.org/2005/sparql-results#";

let $query := "
SELECT ?stationLabel ?adjacentLabel ?lineLabel WHERE {
  ?station wdt:P361 wd:Q68646.
  ?station wdt:P197 ?adjacent.
  SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de'. }
  OPTIONAL { ?station wdt:P81 ?line. }
}
"
  
let $request := " https://query.wikidata.org/bigdata/namespace/wdq/sparql" || "?query=" || fn:encode-for-uri($query)
let $records :=
  for $result in fn:doc($request)//sparql:result
  let $station := $result/sparql:binding[@name="stationLabel"]/sparql:literal/text()
  let $adjacent := $result/sparql:binding[@name="adjacentLabel"]/sparql:literal/text()
  let $line :=  $result/sparql:binding[@name="lineLabel"]/sparql:literal/text()
  let $points :=  
    for $point in ($station, $adjacent)
    order by $point
    return $point
return
    element record {
       element point1 { $points[1] },
       element line { $line },
       element point2 { $points[2] }
  }
let $distinct := functx:distinct-deep($records)
return csv:serialize(element csv { $distinct }, map { 'header': true() })

08-connections-no-dupes.csv