Import ROME dataset into Neo4j

import_ROME.cql

//import rome tree into Neo4j
load csv with headers from "https://docs.google.com/spreadsheets/d/1ks_hjKbO-n2We2glViM5D9RixU1ZtPi-0f7rJbfAY00/export?format=csv&id=1ks_hjKbO-n2We2glViM5D9RixU1ZtPi-0f7rJbfAY00&gid=744365839" as row
create (l:Leaf)
set l = row;

//set up the indexes
create index on :Leaf(Main);
create index on :Leaf(Category);
create index on :Leaf(Subcategory);
create index on :Leaf(Description);
create index on :Leaf(`Code OGR`);

//create the root node
create (l:Leaf:Root {name:"ROME", description:"Répertoire Opérationnel des Métiers et des Emplois (ROME)"});

//label the main nodes and connect them to the root
match (l:Leaf {Category:"0", Subcategory:"00"})
set l:Main;

match (m:Main), (l:Leaf {name:"ROME", description:"Répertoire Opérationnel des Métiers et des Emplois (ROME)"})
create (m)-[:PART_OF]->(l);

//label the category nodes
match (l:Leaf {Subcategory:"00"})
where l.`Code OGR`="N/A" and not(l.Category = "0")
set l:Category;

//label the subcategory nodes
match (l:Leaf)
where not (l.Category = "0")
and not (l.Subcategory ="00")
and (l.`Code OGR`="N/A")
set l:Subcategory;

//label the activity nodes
match (l:Leaf)
where not(l.`Code OGR`="N/A")
set l:Activity;

//check the results
match (n)
return labels(n), count(n);

//connect the activities to the subcategories
match (a:Activity), (sc:Subcategory)
where a.Subcategory = sc.Subcategory
and a.Category = sc.Category
and a.Main = sc.Main
create (a)-[:PART_OF]->(sc);

match (sc:Subcategory), (c:Category)
where sc.Category = c.Category
and sc.Main = c.Main
create (sc)-[:PART_OF]->(c);

match (c:Category), (m:Main)
where c.Main = m.Main
create (c)-[:PART_OF]->(m);

queries_ROME.cql

//example queries on ROME

//link between two activities
match (a1:Activity {`Code OGR`:"18092"}), (a2:Activity {`Code OGR`: "18785"}),
path = allshortestpaths ((a1)-[*]-(a2))
return path;

//sweep a part of the tree
match (m:Main)--(c:Category)--(sc:Subcategory)--(a:Activity)
return m.Main, c.Category, sc.Subcategory, count(a);

match (m:Main)--(c:Category)--(sc:Subcategory)--(a:Activity)
return m.Main, m.Description, count(a)
order by count(a) desc;

//looking at specific branches
match (l:Leaf)
where l.Description contains "logiciel"
return count(l);

match (l:Leaf)
where l.Description contains "logiciel"
with l
match path = ((l)-[*]-(m:Main))
return path

match (l:Leaf)
where l.Description contains "logiciel"
with l
match path = allshortestpaths((l)-[*]->(m:Main))
return path

match (l:Leaf)
where l.Description contains "bière"
with l
match path = allshortestpaths((l)-[*]->(m:Main))
return path

match (l:Leaf)
where l.Description contains "bière"
with l
match path = allshortestpaths((l)-[*]->(m:Root))
return path