ESCO database in Neo4j

import_ESCO_csv_en.cql

//Import ESCO using CSV files
create index ON :Occupation(ISCOGroup);
create index ON :Occupation(altLabels);
create index ON :Skill(altLabels);
create index ON :ISCOGroup(code);
create index ON :Skill(conceptUri);
create index ON :ISCOGroup(conceptUri);
create index ON :Occupation(conceptUri);
create index ON :Occupation(preferredLabel);
create index ON :Skill(preferredLabel);

//import skills and skillgroups
//skillgroups are also skills
load csv with headers from "file:///skillGroups_en.csv" as row
create (s:Skill:Skillgroup)
set s = row;
//skills
load csv with headers from "file:///skills_en.csv" as row
create (s:Skill)
set s = row;
//add the BROADER_THAN relationship between different skills
load csv with headers from "file:///broaderRelationsSkillPillar.csv" as row
match (smaller:Skill {conceptUri: row.conceptUri}), (broader:Skill {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);

//import occupations
load csv with headers from "file:///occupations_en.csv" as row
create (o:Occupation)
set o = row;

//import the International Standard Classification for Occupations of the ILO
load csv with headers from "file:///ISCOGroups_en.csv" as row
create (isco:ISCOGroup)
set isco = row;
//impirt the BROADER_THAN relationships between ISCO groups
load csv with headers from "file:///broaderRelationsOccPillar.csv" as row
match (smaller:ISCOGroup {conceptUri: row.conceptUri}), (broader:ISCOGroup {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//connect the occupations to their ISCOGroup
match (isco:ISCOGroup), (o:Occupation)
where isco.code = o.iscoGroup
create (o)-[:PART_OF_ISCOGROUP]->(isco);

//Connect Skills to Occupations
using periodic commit 500
load csv with headers from "file:///occupationSkillRelations.csv" as row
match (s:Skill {conceptUri: row.skillUri}), (o:Occupation {conceptUri: row.occupationUri})
CREATE (s)-[:RELATED_TO {type: row.relationType}]->(o);

// match ()-[r:RELATED_TO]->()
// return distinct r.type;

//differentiate the different types of relations between occupations and skills
match (a)-[r:RELATED_TO]->(b)
where r.type = "essential"
create (a)-[:ESSENTIAL_FOR]->(b);
match (a)-[r:RELATED_TO]->(b)
where r.type = "optional"
create (a)-[:OPTIONAL_FOR]->(b);
//remove the old relationships
match (a)-[r:RELATED_TO]->(b)
delete r;

import_ESCO_csv_nl.cql

//Import ESCO using CSV files
create index ON :Occupation(ISCOGroup);
create index ON :Occupation(altLabels);
create index ON :Skill(altLabels);
create index ON :ISCOGroup(code);
create index ON :Skill(conceptUri);
create index ON :ISCOGroup(conceptUri);
create index ON :Occupation(conceptUri);
create index ON :Occupation(preferredLabel);
create index ON :Skill(preferredLabel);

//import skills and skillgroups
//skillgroups are also skills
load csv with headers from "file:///skillGroups_nl.csv" as row
create (s:Skill:Skillgroup)
set s = row;
//skills
load csv with headers from "file:///skills_nl.csv" as row
create (s:Skill)
set s = row;
//add the BROADER_THAN relationship between different skills
load csv with headers from "file:///broaderRelationsSkillPillar.csv" as row
match (smaller:Skill {conceptUri: row.conceptUri}), (broader:Skill {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);

//import occupations
load csv with headers from "file:///occupations_nl.csv" as row
create (o:Occupation)
set o = row;

//import the International Standard Classification for Occupations of the ILO
load csv with headers from "file:///ISCOGroups_nl.csv" as row
create (isco:ISCOGroup)
set isco = row;
//impirt the BROADER_THAN relationships between ISCO groups
load csv with headers from "file:///broaderRelationsOccPillar.csv" as row
match (smaller:ISCOGroup {conceptUri: row.conceptUri}), (broader:ISCOGroup {conceptUri: row.broaderUri})
create (broader)-[:BROADER_THAN]->(smaller);
//connect the occupations to their ISCOGroup
match (isco:ISCOGroup), (o:Occupation)
where isco.code = o.iscoGroup
create (o)-[:PART_OF_ISCOGROUP]->(isco);

//Connect Skills to Occupations
using periodic commit 500
load csv with headers from "file:///occupationSkillRelations.csv" as row
match (s:Skill {conceptUri: row.skillUri}), (o:Occupation {conceptUri: row.occupationUri})
CREATE (s)-[:RELATED_TO {type: row.relationType}]->(o);

// match ()-[r:RELATED_TO]->()
// return distinct r.type;

//differentiate the different types of relations between occupations and skills
match (a)-[r:RELATED_TO]->(b)
where r.type = "essential"
create (a)-[:ESSENTIAL_FOR]->(b);
match (a)-[r:RELATED_TO]->(b)
where r.type = "optional"
create (a)-[:OPTIONAL_FOR]->(b);
//remove the old relationships
match (a)-[r:RELATED_TO]->(b)
delete r;

import_ESCO_rdf.cql

//Import ESCO database using RDF

CREATE INDEX ON :Resource(uri);

//Because of the size of the Turtle file, the import is split in 2 .ttl files

CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/esco1.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/esco2.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });

//refactoring the labels
MATCH (n:`http://data.europa.eu/esco/model#Occupation`) set n:ModelOccupation remove n:`http://data.europa.eu/esco/model#Occupation`;
MATCH (n:`http://data.europa.eu/esco/model#NodeLiteral`) set n:ModelNodeLiteral remove n:`http://data.europa.eu/esco/model#NodeLiteral`;
MATCH (n:`http://data.europa.eu/esco/model#MemberConcept`) set n:ModelMemberConcept remove n:`http://data.europa.eu/esco/model#MemberConcept`;
MATCH (n:`http://data.europa.eu/esco/model#LabelRole`) set n:ModelLabelRole remove n:`http://data.europa.eu/esco/model#LabelRole`;
MATCH (n:`http://data.europa.eu/esco/model#Skill`) set n:ModelSkill remove n:`http://data.europa.eu/esco/model#Skill`;
MATCH (n:`http://data.europa.eu/esco/model#Structure`) set  n:ModelStructure remove n:`http://data.europa.eu/esco/model#Structure`;
MATCH (n:`http://data.europa.eu/esco/regulated-professions/Regulation`) set n:RegulatedProfessionsRegulation remove n:`http://data.europa.eu/esco/regulated-professions/Regulation`;
MATCH (n:`http://www.w3.org/2004/02/skos/core#Concept`) set n:CoreConcept remove n:`http://www.w3.org/2004/02/skos/core#Concept`;
MATCH (n:`http://www.w3.org/2004/02/skos/core#ConceptScheme`) set n:CoreConceptScheme remove n:`http://www.w3.org/2004/02/skos/core#ConceptScheme`;
MATCH (n:`http://data.europa.eu/esco/model#AssociationObject`) set n:ModelAssociationObject remove n:`http://data.europa.eu/esco/model#AssociationObject`;
MATCH (n:`http://data.europa.eu/esco/model#ConceptScheme`) set n:ModelConceptScheme remove n:`http://data.europa.eu/esco/model#ConceptScheme`;
//final refactoring is done with iterative batches
CALL apoc.periodic.iterate(
"MATCH (n:`http://www.w3.org/2008/05/skos-xl#Label`) return n",
"SET n:SkosXlLabel REMOVE n:`http://www.w3.org/2008/05/skos-xl#Label`", {batchSize:10000, parallel:true});


CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/ict_skills_collection.ttl","Turtle", { shortenUrls: false, typesToLabels: true, commitSize: 5000 });
CALL semantics.importRDF("file:///Users/rvanbruggen/Library/Application Support/Neo4j Desktop/Application/neo4jDatabases/database-2ece41e7-abb9-4150-8565-b63abc7f409e/installation-3.4.6/import/ict_skills_collection.ttl","Turtle", { shortenUrls: true, typesToLabels: false, commitSize: 5000 });

query_ESCO.cql

//query ESCO database

match (o:Occupation)
where o.preferredLabel contains "software"
return o;

match (o:Occupation)
where o.preferredLabel contains "bier"
return o

match (o1:Occupation), (o2:Occupation)
where o1.preferredLabel contains "software manager"
and o2.preferredLabel contains "bier"
return o1, o2;

match (o1:Occupation), (o2:Occupation),
paths = allshortestpaths ((o1)-[*]-(o2))
where o1.preferredLabel contains "software manager"
and o2.preferredLabel contains "bier"
return paths;

match (o1:Occupation), (o2:Occupation),
paths = allshortestpaths ((o1)-[:ESSENTIAL_FOR*]-(o2))
where o1.preferredLabel contains "software developer"
and o2.preferredLabel contains "bier"
return paths
limit 10