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