U: Rooms and Spaces

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

Screenshot of demoRoom import

load 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.

Close of up B Block rooms on Frenchay Campus

An alternative view includes locations in City Campus.

Frenchay and City Campus rooms

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.

// 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

Example table of Room Occupancy Hours

// frequency
MATCH (room:room )<-[:OCCUPIES]-(activity:activity)
RETURN room.roomName AS room, 
       COUNT(activity) AS totalActivities

Example of Room frequency

// 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

Example of Room Utilisation

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.