Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
132 lines (107 sloc) 4.28 KB
// Legis-graph LOAD CSV cypher script
// https://github.com/legis-graph/legis-graph
// Load Legislators
CREATE INDEX ON :Legislator(bioguideID);
CREATE INDEX ON :Legislator(thomasID);
CREATE INDEX ON :Legislator(lisID);
CREATE INDEX ON :Legislator(govtrackID);
CREATE INDEX ON :Legislator(opensecretsID);
CREATE INDEX ON :Legislator(votesmartID);
CREATE INDEX ON :Legislator(cspanID);
CREATE INDEX ON :Legislator(wikipediaID);
CREATE INDEX ON :Legislator(washpostID);
CREATE INDEX ON :Legislator(icpsrID);
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/legislators-current.csv' AS line
WITH line WHERE line.thomasID IS NOT NULL
MERGE (legislator:Legislator { thomasID: line.thomasID })
ON CREATE SET legislator = line
ON MATCH SET legislator = line
MERGE (s:State {code: line.state})
CREATE UNIQUE (legislator)-[:REPRESENTS]->(s)
MERGE (p:Party {name: line.currentParty})
CREATE UNIQUE (legislator)-[:IS_MEMBER_OF]->(p)
MERGE (b:Body {type: line.type})
CREATE UNIQUE (legislator)-[:ELECTED_TO]->(b);
// Load Bills
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/bills.csv'
AS line
MERGE (bill:Bill { billID: line.billID })
ON CREATE SET bill = line
ON MATCH SET bill = line;
CREATE INDEX ON :Bill(billID);
// Load
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/subjects.csv' AS line
MERGE (subject:Subject { title: line.title });
CREATE INDEX ON :Subject(title);
// Load Congresses
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/congresses.csv' AS line
MERGE (congress:Congress { number: line.number });
// Laod Bills Congresses
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/bill_congresses.csv'
AS line
MATCH (bill:Bill { billID: line.billID }),
(congress:Congress { number: line.number })
MERGE (bill)-[r:PROPOSED_DURING]->(congress);
// Load Bills Subjects
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/bill_subjects.csv'
AS line
MATCH (bill:Bill { billID: line.billID }),
(subject:Subject { title: line.title })
MERGE (bill)-[r:DEALS_WITH]->(subject);
// Load Bills Legislators
// Load current sponsorships
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/sponsors.csv'
AS line
MATCH (bill:Bill { billID: line.billID }),
(legislator:Legislator { bioguideID: line.bioguideID })
MERGE (bill)-[r:SPONSORED_BY]->(legislator)
ON CREATE SET r.cosponsor = line.cosponsor;
// Load Votes
USING PERIODIC COMMIT
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/votes.csv'
AS line
MATCH (bill:Bill { billID: line.billID }),
(legislator:Legislator { bioguideID: line.bioguideID })
MERGE (bill)<-[r:VOTED_ON]-(legislator)
ON CREATE SET r.vote = line.vote;
// Load Committees
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/committees-current.csv' AS line
MERGE (c:Committee {thomasID: line.thomasID})
ON CREATE SET c = line
ON MATCH SET c = line;
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/bill_committees.csv' AS line
MATCH (b:Bill {billID: line.billID})
MATCH (c:Committee {thomasID: line.committeeID})
CREATE UNIQUE (b)-[:REFERRED_TO]->(c);
CREATE INDEX ON :Committee(thomasID);
// Load Committee Members
LOAD CSV WITH HEADERS
FROM 'https://raw.githubusercontent.com/legis-graph/legis-graph/master/outputs/committee-members.csv' AS line
MATCH (c:Committee {thomasID: line.committeeID})
MATCH (l:Legislator {thomasID: line.legislatorID})
CREATE UNIQUE (l)-[r:SERVES_ON]->(c)
SET r.rank = line.rank;
// Create District nodes
LOAD CSV WITH HEADERS
FROM 'https://github.com/legis-graph/legis-graph/blob/master/outputs/cb_2014_districts.csv?raw=true' AS line
CREATE (d:District)
SET d.state = line.state,
d.district = line.district,
d.wkt = line.polygon
WITH d,line
MATCH (l:Legislator) WHERE l.state = line.state AND l.district = line.district
CREATE UNIQUE (l)-[:REPRESENTS]->(d);