I have been regularly considering the best ways to visualize trees – especially large ones. Having a background in Oracle, I often revisit the employee hierarchy that is included as part of the HR demonstration schema.
The query to construct a result set that represents the employee hierarchy relies upon Oracle’s non-standard hierarchical query syntax. Employees are associated with other records in the employee table by a manager id. The root node of the tree is the record with a null manager id (the top manager who has no superior within the organization).
ch = odbcConnect(“XE”,uid=”HR”,pwd=”HR”)
replace(m.last_name,’ ‘,’_’) manager_name,
replace(e.last_name,’ ‘,’_’) employee_name
FROM employees e
LEFT OUTER JOIN employees m
ON m.employee_id = e.manager_id
WHERE e.manager_id is not null
START WITH e.manager_id IS NULL
CONNECT BY PRIOR e.employee_id = e.manager_id
ORDER siblings BY e.last_name,e.first_name”
r=sqlQuery(ch, paste(sql, collapse=’ ‘))
The data returned is a simple listing that pairs up each employee with his/her manager.
1 Steven_King Gerald_Cambrault
2 Gerald_Cambrault Elizabeth_Bates
3 Gerald_Cambrault Harrison_Bloom
4 Gerald_Cambrault Tayler_Fox
5 Gerald_Cambrault Sundita_Kumar
6 Gerald_Cambrault Lisa_Ozer
The igraph library can then be used to plot the data.
g = graph.data.frame(r, directed = T)
V(g)$label = V(g)$name
The results are a bit cluttered due to the number of nodes – but can be manually manipulated when viewed using tkplot. The typical organizational chart structure can be obtained using the Reingold-Tilford layout.
Other possibilities include the circle layout…
…the Fruchterman-Reingold layout…
…and the Kamada-Kawai layout.
When a different node is clicked, it is shifted to the center and the remaining nodes are arranged around it. In this example Gerald Cambrault is selected and the graph smoothly transitions to the image that follows.
If you prefer a tree that is more like the traditional organizational chart, the space tree might be used instead.
This visualization is also will adjust when a node is selected.