We created the mind map of the book “The pedagogy of the Ramchal” with WiseMapping. In the future, we will develop our own visual mind mapping tool bs”d.
We want now
- to import this mind map in our database using the ltree extension of PostgreSQL, a implementation of trees with materialized paths, pre-calculated paths that describe ancestry of nodes
- to encapsulate this ltree in RelGraph, our relational data model for graphs
- to draw the tree with a R script
We export the mind map in text format and we remove the leading spaces in vi with :
Then we load raw data in a staging table because it is often a good idea to use the expressive power of SQL to parse and arrange raw data :
# we start psql with user postgres (needed by copy) sudo -i -u postgres psql -d higayone # we do a bulk copy copy tree_staging from '/home/mchl/Desktop/Blog/map.txt';
The table tree_staging is like that :
input_line ------------------------------------------------------------------------------ 1 The pedagogy of the Ramchal 1.1 The man 1.1.1 His life 184.108.40.206 Padua 220.127.116.11.1 Yeshayah Bassan 18.104.22.168.2 Isaac Cantarini 22.214.171.124.3 Aviad Sar Shalom Basilea 126.96.36.199 Amsterdam 188.8.131.52.1 Talmidim 184.108.40.206.1.1 Marranes 220.127.116.11.1.2 Modern science 18.104.22.168 Eretz Israel ...
Then we create extension ltree :
create extension if not exists ltree;
Then we create table and sequence for ltree :
drop table if exists ltree_node; drop sequence if exists ltree_node_sequence; create sequence ltree_node_sequence; create table ltree_node ( node_id integer primary key default nextval('ltree_node_sequence') ,node_name text ,node_path ltree );
It is necessary to drop the table before the sequence, otherwise PostgreSQL will raise an error. Then we add a gist index on the column ltree :
create index path_gist_idx on ltree_node using gist(node_path);
Then we populate the ltree with the data of the staging table :
insert into ltree_node(node_name, node_path) select substring(input_line, char_length(substring( input_line from '[0-9.]*[ ]')) + 1) as node_name ,cast(substring(input_line from '[0-9.]*') as ltree) as node_path from tree_staging;
substring(input_line, char_length(substring( input_line from '[0-9.]*[ ]')) + 1) as node_name
means that we take every character since the beginning of the line until the first white space. And the line
cast(substring(input_line from '[0-9.]*') as ltree) as node_path
means that we take every character since the first white space until the end of the line.
Let’s have now a look on a first application of ltree using the <@ operator. This query gives all the paths of the tree :
select ln1.node_id ,array_to_string( array_agg(ln2.node_name order by ln2.node_path) ,'/') as full_path_name from ltree_node as ln1 inner join ltree_node as ln2 on ln1.node_path <@ ln2.node_path group by ln1.node_id, ln1.node_path, ln1.node_name order by ln1.node_id, full_path_name;
ln1.node_path <@ ln2.node_path
means that the left argument is a descendant of the right one. And array_agg is an aggregate function (working with group by) transforming his arguments into an array.
The output is like that :
node_id full_path_name ------- -------------- 1 The pedagogy of the Ramchal 2 The pedagogy of the Ramchal/The man 3 The pedagogy of the Ramchal/The man/His life 4 The pedagogy of the Ramchal/The man/His life/Padua 5 The pedagogy of the Ramchal/The man/His life/Padua/Yeshayah Bassan 6 The pedagogy of the Ramchal/The man/His life/Padua/Isaac Cantarini 7 The pedagogy of the Ramchal/The man/His life/Padua/Aviad Sar Shalom Basilea 8 The pedagogy of the Ramchal/The man/His life/Amsterdam 9 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim 10 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim/Marranes 11 The pedagogy of the Ramchal/The man/His life/Amsterdam/Talmidim/Modern science 12 The pedagogy of the Ramchal/The man/His life/Eretz Israel ...
We populate now the tables of RelGraph, our relational model for graphs.
1. We create a new graph :
insert into graph(graph_name) values('Book');
2. We populate the table node :
insert into node(graph_id, node_name) select (select graph_id from graph where graph_name = 'Book') ,node_name from ltree_node;
3. We populate the table edge :
insert into edge(from_node, to_node) select ln2.node_id as from_node ,ln1.node_id as to_node from ltree_node ln1 ,ltree_node ln2 where ln1.node_path = subpath(ln2.node_path ,0 ,nlevel(ln2.node_path) - 1);
We see here two other functions of ltree :
- subpath : subpath of tree starting at the beginning and starting at the penultimate component
- nlevel : gives the number of components of the path
But we have to find solutions to make visible in RelGraph the insert / update / delete performed in the ltree. It will be the topic of a future post.
Having node and edge populated, we can run the following R script (which, in my private language, I call a graph slurper) to visualize the tree :
require(visNetwork) require(RPostgreSQL) # establish connection driver ← dbDriver("PostgreSQL") connexion ← dbConnect(driver, dbname="higayone" ,host="localhost", port=5432 ,user="mchl", password="secret") # populates nodes and egdes nodes ← dbGetQuery(connexion, " select node_id as id ,node_name as label from node order by node_id;") edges ← dbGetQuery(connexion, " select from_node as from ,to_node as to from edge order by from_node, to_node;") visNetwork(nodes, edges) %>% visNodes(font = '10px', shape = 'ellipse')
Here is a thumbnail (I am afraid I cannot do better with the free version of WordPress) :
If we want to generate a table of contents, we must think at two things :
- node_path ‘1’ becoming the title of the book, it must be removed from the tree
- for all other node_path, we simply remove the ‘1.’ at the beginning
select substring(node_path::text, 3, char_length(node_path::text)) ,repeat(' ', nlevel(node_path)- 2) || node_name from ltree_node where node_path != '1' order by node_path;
That gives :
1 The man 1.1 His life 1.1.1 Padua 22.214.171.124 Yeshayah Bassan 126.96.36.199 Isaac Cantarini 188.8.131.52 Aviad Sar Shalom Basilea 1.1.2 Amsterdam 184.108.40.206 Talmidim 220.127.116.11.1 Marranes 18.104.22.168.2 Modern science 1.1.3 Eretz Israel ...