18/02/2017

1

Neo4j – Exercise 2

Emmanuel Stefanakis

estef@unb.ca

CREATE A GRAPH DB FOR CANADIAN CITIES

GGE5402/6405: Geographic Databases Fall 2016

CALL spatial.procedures

Spatial Procedures

18/02/2017

2

Spatial Procedures

CALL spatial.procedures

Spatial Procedures CALL spatial.procedures

18/02/2017

3

Dataset (Canadian Cities)

Dataset (Canadian Cities)

18/02/2017

4

// Create a point layer (cities) CALL spatial.addPointLayer('cities');

18/02/2017

5

// Import cities from shapefile CALL spatial.importShapefileToLayer('cities', 'C:/Users/estef/Documents/GGE6405/2016/data/Canada_Cities.shp')

18/02/2017

6

18/02/2017

7

// Retrieve the layer and the cities MATCH (n) WHERE n.layer = 'cities' RETURN n

18/02/2017

8

18/02/2017

9

MATCH (m) WHERE m.COUNTRY = 'CAN‘ RETURN m

18/02/2017

10

// Create a relationship between the layer (node) and the cities (nodes) [it seems that there is no connection established between layer and cities] MATCH (n) WHERE n.layer = 'cities' MATCH (m) WHERE m.COUNTRY = 'CAN' CREATE (n)-[:INCLUDES]->(m)

// Retrieve all cities (nodes) in layer 'cities' MATCH (n)-[r:INCLUDES]->(m) RETURN m

18/02/2017

11

// Count all nodes MATCH (n) RETURN count(n)

// Nodes that are not part of the spatial index. MATCH (n) WHERE NOT (n)-[:RTREE_REFERENCE]-() RETURN count(n)

18/02/2017

12

MATCH (n) WHERE NOT (n)-[:RTREE_REFERENCE]-() RETURN ID(n)

MATCH (n) WHERE NOT (n)-[:RTREE_REFERENCE]-() RETURN n

18/02/2017

13

18/02/2017

14

// Nodes that are part of the spatial index. MATCH (n) WHERE (n)-[:RTREE_REFERENCE]-() RETURN count(n)

MATCH (n) WHERE (n)-[:RTREE_REFERENCE]-() RETURN n

18/02/2017

15

18/02/2017

16

// Non leaf nodes in the tree (1+10) MATCH (n) WHERE (n)-[:RTREE_REFERENCE]->() RETURN count(n)

MATCH (n) WHERE (n)-[:RTREE_REFERENCE]->() RETURN n

18/02/2017

17

MATCH (n) WHERE (n)-[:RTREE_REFERENCE]->() RETURN n.bbox (distributed across the country)

R-tree nodes

18/02/2017

18

// Retrieve ids of non-leaf nodes MATCH (n) WHERE (n)-[:RTREE_REFERENCE]->() RETURN ID(n)

(pick one id and find how many children) MATCH (n) WHERE ID(n)=1029 MATCH (n)-[:RTREE_REFERENCE]->(m) RETURN count(m)

18/02/2017

19

MATCH (n) WHERE ID(n)=1132 MATCH (n)-[:RTREE_REFERENCE]->(m) RETURN m.NAME

// Report city properties MATCH (n) RETURN n.NAME

18/02/2017

20

(WITH is like RETURN; to use values in subsequent commands) MATCH (n) WITH distinct n RETURN n.NAME

MATCH (n) RETURN n.NAME, n.CAPITAL

18/02/2017

21

MATCH (n) RETURN n.NAME, n.latitude, n.longitude

MATCH (n) WHERE n.NAME = 'Toronto' RETURN n.NAME, n.latitude, n.longitude

18/02/2017

22

MATCH (n), (m) WHERE n.NAME = 'Toronto' AND m.NAME = 'Fredericton' RETURN n.CAPITAL, m.STATEABB

// Labels - returns all NULL (no labels) MATCH (n) RETURN n:label

18/02/2017

23

//Assign labels (Atlantic cities) MATCH (n) where n.longitude > -70 WITH COLLECT (distinct(n)) as nn FOREACH (n in nn | SET n:atlantic)

MATCH (n) WHERE n:atlantic RETURN n.NAME as ATLANTIC_CITIES

18/02/2017

24

// List of procedures CALL spatial.procedures

// Cities within 100km from location CALL spatial.withinDistance('cities',{lon:-66.0,lat:45.0},100)

18/02/2017

25

CALL spatial.withinDistance('cities',{lon:-66.0,lat:45.0},100) YIELD node as n RETURN n

// Cities within 100km from Fredericton MATCH (n) WHERE n.NAME = 'Fredericton' CALL spatial.withinDistance('cities',{lon:n.longitude,lat:n.latitude},100) YIELD node as m RETURN m

18/02/2017

26

MATCH (n) WHERE n.NAME = 'Fredericton' CALL spatial.withinDistance('cities',{lon:n.longitude,lat:n.latitude},100) YIELD node as m RETURN m.NAME

// Create relationships MATCH (n) WHERE n.NAME = 'Fredericton' CALL spatial.withinDistance('cities',{lon:n.longitude,lat:n.latitude},100) YIELD node as m CREATE (n)-[r:NEAR_FREDERICTON]->(m)

18/02/2017

27

MATCH (n) WHERE n.NAME = 'Fredericton' RETURN n --> see the graph and expand relationships

MATCH (n)-[r:NEAR_FREDERICTON]->(m) WHERE n.NAME = 'Fredericton' RETURN m.NAME

18/02/2017

28

// Create relationships by iteration MATCH (n:atlantic) MATCH (m) WHERE m.NAME = 'Toronto' MERGE (m)-[:Toronto2Atlantic]->(n)

MATCH (n)-[r:Toronto2Atlantic]->(m) RETURN m.NAME

18/02/2017

29

// Create relationships by iteration MATCH (m) WHERE m.NAME in ["Fredericton", "Ottawa", "Calgary"] CALL spatial.withinDistance('cities',{lon:m.longitude,lat:m.latitude},100) YIELD node as j MERGE (j)-[:network3cities_near]-(m)

MATCH (n)-[r:network3cities_near]->(m) RETURN n.NAME, m.NAME

18/02/2017

30

// Create network betweeen atlantic cities with a dist < 100km MATCH (m) WHERE m.longitude > -70 CALL spatial.withinDistance('cities',{lon:m.longitude,lat:m.latitude},100) YIELD node as j MERGE (j)-[:networkAtlantic_near]-(m)

MATCH (n)-[r:networkAtlantic_near]->(m) RETURN n.NAME, m.NAME

18/02/2017

31

//A network between capital cities MATCH (n) WHERE n.CAPITAL=1 RETURN n.NAME

MATCH (n) WHERE n.CAPITAL=1 MATCH (m) WHERE m.CAPITAL=1 MERGE (n)-[:networkCapitals]->(m)

18/02/2017

32

MATCH (n)-[r:networkCapitals]->(m) RETURN n.NAME, m.NAME

//Intersection of geometries CALL spatial.intersects('cities','POLYGON((-70 45, -70 53, -60 55, -60 47, -70 45))') YIELD node as m RETURN m

18/02/2017

33

CALL spatial.intersects('cities','POLYGON((-70 45, -70 53, -60 55, -60 47, -70 45))') YIELD node as m RETURN m.NAME

18/02/2017

34

// Remove layer CALL spatial.removeLayer('cities')

// Remove all nodes/relationships MATCH (n) OPTIONAL MATCH (n)-[r]-() DELETE n,r