Graph Of War

loadwargraph.cql

//loading countries
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI/export?format=csv&id=1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI&gid=1243842209" 
as countries
fieldterminator ','
merge (country:Country {short: countries.Short, cowcode: countries.COWcode, name: countries.Name});

//load some more detailed country data
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI/export?format=csv&id=1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI&gid=10793080"
as countries
fieldterminator ','
match 
(c:Country {short: countries.stateabb})
where countries.irst is not null
set
c.ironsteel = toInt(countries.irst),
c.milexpend = toInt(countries.milex),
c.milpers = toInt(countries.milper),
c.primencom = toFloat(countries.pec),
c.totalpop = toInt(countries.tpop),
c.urbanpop = toInt(countries.upop),
c.compnatcap = toFloat(countries.cinc)
return c 
limit 10;

//add some metadata
//load outcomes
create (n:Outcome {id:1, name:'Victory for side A'});
create (n:Outcome {id:2, name:'Victory for side B'});
create (n:Outcome {id:3, name:'Yield by side A'});
create (n:Outcome {id:4, name:'Yield by side B'});
create (n:Outcome {id:5, name:'Stalemate'});
create (n:Outcome {id:6, name:'Compromise'});
create (n:Outcome {id:7, name:'Released'});
create (n:Outcome {id:8, name:'Unclear'});
create (n:Outcome {id:9, name:'Joins ongoing war'});
create (n:Outcome {id:-9, name:'Missing'});

//load Settlements
create (n:Settlement {id:1, name:'Negotiated'});
create (n:Settlement {id:2, name:'Imposed '});
create (n:Settlement {id:3, name:'None'});
create (n:Settlement {id:4, name:'Unclear'});
create (n:Settlement {id:-9, name:'Missing'});

//load fatalities
create (n:Fatality {id:0, name:'None'});
create (n:Fatality {id:1, name:'1-25 deaths'});
create (n:Fatality {id:2, name:'26-100 deaths'});
create (n:Fatality {id:3, name:'101-250 deaths'});
create (n:Fatality {id:4, name:'251-500 deaths'});
create (n:Fatality {id:5, name:'501-999 deaths'});
create (n:Fatality {id:6, name:'> 999 deaths'});
create (n:Fatality {id:-9, name:'Missing'});

//load high actions
create (n:HighAction {id:0, name:'No militarized action [1]'});
create (n:HighAction {id:1, name:'Threat to use force [2]'});
create (n:HighAction {id:2, name:'Threat to blockade [2]'});
create (n:HighAction {id:3, name:'Threat to occupy territory [2]'});
create (n:HighAction {id:4, name:'Threat to declare war [2]'});
create (n:HighAction {id:5, name:'Threat to use CBR weapons [2]'});
create (n:HighAction {id:6, name:'Threat to join war'});
create (n:HighAction {id:7, name:'Show of force [3]'});
create (n:HighAction {id:8, name:'Alert [3]'});
create (n:HighAction {id:9, name:'Nuclear alert [3]'});
create (n:HighAction {id:10, name:'Mobilization [3]'});
create (n:HighAction {id:11, name:'Fortify border [3]'});
create (n:HighAction {id:12, name:'Border violation [3]'});
create (n:HighAction {id:13, name:'Blockade [4]'});
create (n:HighAction {id:14, name:'Occupation of territory [4]'});
create (n:HighAction {id:15, name:'Seizure [4]'});
create (n:HighAction {id:16, name:'Attack [4]'});
create (n:HighAction {id:17, name:'Clash [4]'});
create (n:HighAction {id:18, name:'Declaration of war [4]'});
create (n:HighAction {id:19, name:'Use of CBR weapons [4]'});
create (n:HighAction {id:20, name:'Begin interstate war [5]'});
create (n:HighAction {id:21, name:'Join interstate war [5]'});
create (n:HighAction {id:-9, name:'Missing [-9]'});

//create hostility levels
create (n:HostLevel {id:1, name:'No militarized action'});
create (n:HostLevel {id:2, name:'Threat to use force'});
create (n:HostLevel {id:3, name:'Display of force'});
create (n:HostLevel {id:4, name:'Use of force'});
create (n:HostLevel {id:5, name:'War'});

//create highactions part of hostlevels
match (n:HighAction {id:0}), (m:HostLevel {id:1}) create n-[:PART_OF]->m;
match (n:HighAction {id:1}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:2}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:3}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:4}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:5}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:6}), (m:HostLevel {id:2}) create n-[:PART_OF]->m;
match (n:HighAction {id:7}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:8}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:9}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:10}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:11}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:12}), (m:HostLevel {id:3}) create n-[:PART_OF]->m;
match (n:HighAction {id:13}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:14}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:15}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:16}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:17}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:18}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:19}), (m:HostLevel {id:4}) create n-[:PART_OF]->m;
match (n:HighAction {id:20}), (m:HostLevel {id:5}) create n-[:PART_OF]->m;
match (n:HighAction {id:21}), (m:HostLevel {id:5}) create n-[:PART_OF]->m;
match (n:HighAction {id:-9}), (m:HostLevel {id:-9}) create n-[:PART_OF]->m;

//create years
with range(1800,2014) as RANGE 
foreach (r in RANGE | create (y:Year {name:r}));

match (y0:Year), (y1:Year)
where y0.name=y1.name-1
create y0-[:PRECEDES]->y1;


//load disputes
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI/export?format=csv&id=1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI&gid=0"
as dispute
fieldterminator ','
match 
(oc:Outcome {id: toInt(dispute.Outcome)}),
(set:Settlement {id: toInt(dispute.Settle)}), 
(fat:Fatality {id: toInt(dispute.Fatality)}), 
(ha:HighAction {id: toInt(dispute.HiAct)}),
(hl:HostLevel {id: toInt(dispute.HostLev)}),
(y1:Year {name: toInt(dispute.StYear)}),
(y2:Year {name: toInt(dispute.EndYear)})
merge
(d:Dispute {name: 'Dispute nr '+dispute.DispNum3, dispnum3: toInt(dispute.DispNum3), dispnum4: toInt(dispute.DispNum4), maxdur: toInt(dispute.MaxDur), mindur: toInt(dispute.MinDur)})-[:STARTED_IN]->(y1)
merge (d)-[:ENDED_IN]->(y2)
merge (d)-[:HAS_OUTCOME]->oc
merge (d)-[:WAS_SETTLED]->set
merge (d)-[:HAS_FATALITY]->fat
merge (d)-[:HAS_HIACT]->ha
merge (d)-[:HAS_HOSTLEV]->hl;

//load participants
using periodic commit 500
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI/export?format=csv&id=1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI&gid=89700841"
as participant
fieldterminator ','
match 
(dispute:Dispute {dispnum3: toInt(participant.DispNum3)}),
(country:Country {short: participant.StAbb})
merge 
(country)-[:PARTICIPATES_IN {startyear: toInt(participant.StYear), endyear: toInt(participant.EndYear), orginated: toInt(participant.Orig), sidea: toInt(participant.SideA)}]->(dispute);


//Add Religions
create (n:Religion {id:1, name:'Christianity', short:'chrstgen'});
create (n:Religion {id:2, name:'Judaism', short:'judgen'});
create (n:Religion {id:3, name:'Islam', short:'islmgen'});
create (n:Religion {id:4, name:'Buddhism', short:'budgen'});
create (n:Religion {id:5, name:'Zoroastrian', short:'zorogen'});
create (n:Religion {id:6, name:'Hindu', short:'hindgen'});
create (n:Religion {id:7, name:'Sikh', short:'sikhgen'});
create (n:Religion {id:8, name:'Shinto', short:'shntgen'});
create (n:Religion {id:9, name:'Bahai', short:'bahgen'});
create (n:Religion {id:10, name:'Taoism', short:'taogen'});
create (n:Religion {id:11, name:'Jaingen', short:'jaingen'});
create (n:Religion {id:12, name:'Confucianism', short:'confgen'});
create (n:Religion {id:13, name:'Syncretic Religions', short:'syncgen'});
create (n:Religion {id:14, name:'Animist Religions', short:'anmgen'});
create (n:Religion {id:15, name:'Non-religious', short:'nonrelig'});
create (n:Religion {id:16, name:'Other Religions', short:'othrgen'});

//Add Subrelgions
create (n:SubReligion:Religion {id:101, name:'Christianity - protestants', short:'chrstprot'});
create (n:SubReligion:Religion {id:102, name:'Christianity - Roman Catholics', short:'chrstcat'});
create (n:SubReligion:Religion {id:103, name:'Christianity - Eastern Orthodox', short:'chrstorth'});
create (n:SubReligion:Religion {id:104, name:'Christianity - Anglican', short:'chrstang'});
create (n:SubReligion:Religion {id:105, name:'Christianity - others', short:'chrstothr'});
create (n:SubReligion:Religion {id:106, name:'Judaism - Orthodox', short:'judorth'});
create (n:SubReligion:Religion {id:107, name:'Judaism - Conservatives', short:'jdcons'});
create (n:SubReligion:Religion {id:108, name:'Judaism - Reform', short:'judref'});
create (n:SubReligion:Religion {id:109, name:'Judaism - other', short:'judothr'});
create (n:SubReligion:Religion {id:110, name:'Islam - Sunni', short:'islmsun'});
create (n:SubReligion:Religion {id:111, name:'Islam - Shia', short:'islmshi'});
create (n:SubReligion:Religion {id:112, name:'Islam - Ibadhi', short:'islmibd'});
create (n:SubReligion:Religion {id:113, name:'Islam - Nation of Islam', short:'islmnat'});
create (n:SubReligion:Religion {id:114, name:'Islam - Alawite', short:'islmalw'});
create (n:SubReligion:Religion {id:115, name:'Islam - Ahmadiyya', short:'islmahm'});
create (n:SubReligion:Religion {id:116, name:'Islam - Other', short:'islmothr'});
create (n:SubReligion:Religion {id:117, name:'Buddhism - Mahayana', short:'budmah'});
create (n:SubReligion:Religion {id:118, name:'Buddhism - Theravada', short:'budthr'});
create (n:SubReligion:Religion {id:119, name:'Buddhism - Other', short:'budothr'});

//Link subreligions to religions
match (n:SubReligion {id:101}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:102}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:103}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:104}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:105}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:106}), (m:Religion {id:1}) create n-[:PART_OF]->m;
match (n:SubReligion {id:107}), (m:Religion {id:2}) create n-[:PART_OF]->m;
match (n:SubReligion {id:108}), (m:Religion {id:2}) create n-[:PART_OF]->m;
match (n:SubReligion {id:109}), (m:Religion {id:2}) create n-[:PART_OF]->m;
match (n:SubReligion {id:110}), (m:Religion {id:2}) create n-[:PART_OF]->m;
match (n:SubReligion {id:111}), (m:Religion {id:2}) create n-[:PART_OF]->m;
match (n:SubReligion {id:112}), (m:Religion {id:3}) create n-[:PART_OF]->m;
match (n:SubReligion {id:113}), (m:Religion {id:3}) create n-[:PART_OF]->m;
match (n:SubReligion {id:114}), (m:Religion {id:3}) create n-[:PART_OF]->m;
match (n:SubReligion {id:115}), (m:Religion {id:3}) create n-[:PART_OF]->m;
match (n:SubReligion {id:116}), (m:Religion {id:3}) create n-[:PART_OF]->m;
match (n:SubReligion {id:117}), (m:Religion {id:4}) create n-[:PART_OF]->m;
match (n:SubReligion {id:118}), (m:Religion {id:4}) create n-[:PART_OF]->m;
match (n:SubReligion {id:119}), (m:Religion {id:4}) create n-[:PART_OF]->m;

//create index on religions
create index on :Religion(short);

//link countries to religions
using periodic commit 500
load csv with headers from "https://docs.google.com/a/neotechnology.com/spreadsheets/d/1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI/export?format=csv&id=1yGt3jb-tnhPCCpNoyCgJV2ChcxoDToLal4GEQWkwvLI&gid=1818620073"
as religions
fieldterminator ','
match 
(country:Country {short: religions.countryshort}),
(chrstprot:Religion {short:'chrstprot'}),
(chrstcat:Religion {short:'chrstcat'}),
(chrstorth:Religion {short:'chrstorth'}),
(chrstang:Religion {short:'chrstang'}),
(chrstothr:Religion {short:'chrstothr'}),
(judorth:Religion {short:'judorth'}),
(jdcons:Religion {short:'jdcons'}),
(judref:Religion {short:'judref'}),
(judothr:Religion {short:'judothr'}),
(islmsun:Religion {short:'islmsun'}),
(islmshi:Religion {short:'islmshi'}),
(islmibd:Religion {short:'islmibd'}),
(islmnat:Religion {short:'islmnat'}),
(islmalw:Religion {short:'islmalw'}),
(islmahm:Religion {short:'islmahm'}),
(islmothr:Religion {short:'islmothr'}),
(budmah:Religion {short:'budmah'}),
(budthr:Religion {short:'budthr'}),
(budothr:Religion {short:'budothr'}),
(chrstgen:Religion {short:'chrstgen'}),
(judgen:Religion {short:'judgen'}),
(islmgen:Religion {short:'islmgen'}),
(budgen:Religion {short:'budgen'}),
(zorogen:Religion {short:'zorogen'}),
(hindgen:Religion {short:'hindgen'}),
(sikhgen:Religion {short:'sikhgen'}),
(shntgen:Religion {short:'shntgen'}),
(bahgen:Religion {short:'bahgen'}),
(taogen:Religion {short:'taogen'}),
(jaingen:Religion {short:'jaingen'}),
(confgen:Religion {short:'confgen'}),
(syncgen:Religion {short:'syncgen'}),
(anmgen:Religion {short:'anmgen'}),
(nonrelig:Religion {short:'nonrelig'}),
(othrgen:Religion {short:'othrgen'})
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.chrstprot)}]-(chrstprot)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.chrstcat)}]-(chrstcat)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.chrstorth)}]-(chrstorth)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.chrstang)}]-(chrstang)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.chrstothr)}]-(chrstothr)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.judorth)}]-(judorth)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.jdcons)}]-(jdcons)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.judref)}]-(judref)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.judothr)}]-(judothr)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmsun)}]-(islmsun)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmshi)}]-(islmshi)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmibd)}]-(islmibd)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmnat)}]-(islmnat)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmalw)}]-(islmalw)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmahm)}]-(islmahm)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.islmothr)}]-(islmothr)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.budmah)}]-(budmah)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.budthr)}]-(budthr)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.budothr)}]-(budothr)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.zorogen)}]-(zorogen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.hindgen)}]-(hindgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.sikhgen)}]-(sikhgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.shntgen)}]-(shntgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.bahgen)}]-(bahgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.taogen)}]-(taogen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.jaingen)}]-(jaingen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.confgen)}]-(confgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.syncgen)}]-(syncgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.anmgen)}]-(anmgen)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.nonrelig)}]-(nonrelig)
merge (country)-[:HAS_ADHERENTS {number: toInt(religions.othrgen)}]-(othrgen);

//delete 0 adherents
match ()-[r:HAS_ADHERENTS {number:0}]-()
delete r;

//create indexes
create index on :Year(name)
create index on :Country(short)

querywar.cql

//find USA
match (n:Country {short:"USA"})-[r]-() return n,r
limit 10

//countries with most disputes
match (n:Country)-[r:PARTICIPATES_IN]->(d:Dispute) 
return n.name, count(r) 
order by count(r) desc
limit 10;


//countries with most disputes per capita
match (n:Country)-[r:PARTICIPATES_IN]->(d:Dispute)
where n.totalpop is not null
with n, count(r) as NrOfDisputes
return n.name, n.totalpop, NrOfDisputes, 1.0*NrOfDisputes/n.totalpop as DisputesPerCapita
order by DisputesPerCapita desc
limit 10

//disputes in first half of 20th century
match (y1:Year {name:1900})-[:PRECEDES*..51]->(y2),
(d:Dispute)-[:STARTED_IN]->(y2)
return distinct d.name as Dispute, y2.name as StartYear;

//Number of distputes in first half of 20th century
match (y1:Year {name:1900})-[:PRECEDES*..51]->(y2),
(d:Dispute)-[:STARTED_IN]->(y2)
return y2.name as StartYear, count(d) as NrOfDisputes
order by StartYear ASC;

//countries with most disputes in first half of 20th century
match (n:Country)-[r:PARTICIPATES_IN]->(d:Dispute),
(y1:Year {name:1900})-[:PRECEDES*..50]->(y2),
(d)-[:STARTED_IN]-(y2)
return distinct n.name, count(r) order by count(r) desc
limit 10;

//countries with most disputes in second half of 20th century
match (n:Country)-[r:PARTICIPATES_IN]->(d:Dispute),
(y1:Year {name:1950})-[:PRECEDES*..50]->(y2),
(d)-[:STARTED_IN]-(y2)
return distinct n.name, count(r) order by count(r) desc
limit 10;

//countries with most religious adherents
match (n:Religion)<-[r:HAS_ADHERENTS]-(c:Country)
where n.name <> "Non-religious"
with distinct c.short as country, r.number as nrofadherents
order by nrofadherents DESC
limit 10
with country
match (c:Country {short: country})-[:PARTICIPATES_IN]->(d:Dispute)
return distinct c.short, c.name, count(d);

//paths between USA and Israel
match (u:Country {short:"USA"}), (i:Country {short:"ISR"}),
p = allshortestpaths((u)-[r*]-(i))
return p;

match (u:Country {short:"USA"}), (i:Country {short:"ISR"}),
p = allshortestpaths((u)-[:PARTICIPATES_IN*]-(i))
return p;

match (u:Country {short:"USA"}), (i:Country {short:"ISR"}),
p = allshortestpaths((u)-[:HAS_ADHERENTS*]-(i))
return p;