-
Notifications
You must be signed in to change notification settings - Fork 0
/
appendix-cypher7.qmd
103 lines (79 loc) · 3.65 KB
/
appendix-cypher7.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
---
title: "U: Rooms and Spaces"
lightbox:
match: auto
effect: fade
desc-position: bottom
loop: false
---
<br>
The timetabling database contains some information about rooms and spaces on campus, but the master data systems contain much more. I have added a few rooms and properties from the master source, as an experiment.
## Room Geo-location
### Import locations from file
### load cypher
```{cypher}{scroll-cypher}
UNWIND $nodeRecords AS nodeRecord
WITH *
WHERE NOT nodeRecord.`graphid` IN $idsToSkip AND NOT nodeRecord.`graphid` IS NULL
MERGE (n: `demoRoom` { `graphid`: nodeRecord.`graphid` })
SET n.`#rm.bl_id` = nodeRecord.`#rm.bl_id`
SET n.`fl_id` = toInteger(trim(nodeRecord.`fl_id`))
SET n.`rm_id` = nodeRecord.`rm_id`
SET n.`rm_type` = nodeRecord.`rm_type`
SET n.`dp_id` = nodeRecord.`dp_id`
SET n.`bu_id` = nodeRecord.`bu_id`
SET n.`rm_std` = nodeRecord.`rm_std`
SET n.`rm_use` = nodeRecord.`rm_use`
SET n.`site_id` = nodeRecord.`site_id`
SET n.`dv_id` = nodeRecord.`dv_id`
SET n.`asb_risk` = nodeRecord.`asb_risk`
SET n.`rm_cat` = nodeRecord.`rm_cat`
SET n.`lon` = toFloat(trim(nodeRecord.`lon`))
SET n.`lat` = toFloat(trim(nodeRecord.`lat`))
SET n.`roomHostKey` = nodeRecord.`roomHostKey`;
```
### Thoughts
Some rooms have longitude and latitude, which have been used to calculate distance. The screenshot below shows locations on Frenchay campus - you can clearly see rooms aligned into the shape our the buildings.
An alternative view includes locations in City Campus.
The above images show the potential of using coordinates although there is a lot more to consider including actual locations, accuracy, missing data, and coordinate transformations.
## Room use
Being able to measure room usage - utilisation, frequency, occupancy - is a key metric for the university.
```{cypher}{scroll-cypher}
// room occupancy
MATCH (room:room )<-[:OCCUPIES]-(activity:activity)
WITH room, activity,
activity.actDurationInMinutes / 60.0 AS occupancyHours
RETURN room.roomName AS room,
SUM(occupancyHours) AS totalOccupancyHours
```
```{cypher}{scroll-cypher}
// frequency
MATCH (room:room )<-[:OCCUPIES]-(activity:activity)
RETURN room.roomName AS room,
COUNT(activity) AS totalActivities
```
```{cypher}{scroll-cypher}
// simple utilisation
MATCH (room:room {roomName: "2Q12 FR"})<-[:OCCUPIES]-(activity:activity)
WITH room, activity,
CASE
WHEN activity.actStartTime.hour = activity.actEndTime.hour
THEN 1
ELSE activity.actEndTime.hour - activity.actStartTime.hour + 1
END AS occupiedHours
UNWIND range(activity.actStartTime.hour, activity.actEndTime.hour) AS hour
RETURN room.roomName AS room,
hour AS hourBlockStart,
hour + 1 AS hourBlockEnd,
SUM(CASE WHEN hour IN range(activity.actStartTime.hour, activity.actEndTime.hour) THEN 1 ELSE 0 END) AS utilizationCount,
occupiedHours AS totalOccupiedHours
ORDER BY hourBlockStart
```
The above queries need to be developed further as they are very simplistic. For example, the utilisation query does not take into account the day of the week, nor does it consider the number of people in the room. The occupancy query does not consider the capacity of the room.