Skip to content
This repository has been archived by the owner on Apr 25, 2023. It is now read-only.

Running SQL queries on Open Knesset database using re:dash

Jump to bottom
DougieHauser edited this page Sep 12, 2016 · 22 revisions

re:dash allows to run SQL queries on the Open Knesset database.

Warning!

Official Knesset Data which we depend on might be unreliable and open to interpretation.
Queries might show wrong data, depending on the interpretation of the data.

some useful queries:

Ratio of speaking/attending committee meetings

http://data.obudget.org/queries/626/source?p_mk_names=mk,names,separated by,commas

  • Get all committees an MK(s) spoke at, spoke at only once during meeting, and attended. ratios are calculated.
  • Important: name has to be exactly as it appears in the List of MK Names

http://data.obudget.org/queries/630/source?p_from_date=2016-05-22&p_to_date=2016-08-07

  • Data for all Current MKs.
  • Data is filtered between dates (yyyy-mm-dd).

how many times MK spoke in committee meetings

http://data.obudget.org/queries/604/source#table

  • change member_id=943 to the id of the mk you want to investigate. See getting memeber id query below.
  • protocolpart.header LIKE '%נאוה בוקר%' or protocolpart.header LIKE '%נוה בוקר%' - change to the mk name, including alternate spellings, you an add more or protocolpart.header parts if there are more alternate names.
  • you can use getting all MK names query below to get all the alternative spellings of the MK name.

http://data.obudget.org/queries/625/source?p_mk_names=יאיר לפיד,נאוה בוקר,ג`מאל זחאלקה

  • Get all committees an MK spoke at, and how many times he/she did - for multiple MKs, by their name.
  • Important: name has to be exactly as it appears in the List of MK Names

all meetings an MK attended

http://data.obudget.org/queries/639/source?p_from_date=2016-05-22&p_to_date=2016-08-08

  • For each Committee and each MK, the query returns the number of meetings attended, total number of meetings, ratio, and whether MK is a committee member (or chairperson)
  • All Current MKs
  • Plenum Excluded
  • Date Range as parameter

http://data.obudget.org/queries/608/source#table

  • change mks_attended.member_id = 943 to the mk ID you want to check. See getting memeber id query below.
  • this is based on open knesset code that looks for member names in the protocol. This process works for most cases, but there might be problems with Knesset members that have names with different spellings.

http://data.obudget.org/queries/620/source?p_mk_name=אמיר אוחנה

  • Use this Query, to get all MKs attended by MK Name (excluding מליאה)
  • Important: name has to be exactly as it appears in the List of MK Names

http://data.obudget.org/queries/623/source?p_mk_names=נאוה בוקר,יאיר לפיד,אמיר אוחנה

  • Use this Query, to get a list of MKs, and how many times they attended all committees (excluding מליאה)
  • Important: names have to be exactly as they appear in the List of MK Names

all meetings two or more MKs attended together

[http://data.obudget.org/queries/719?source?p_mk_names=נאוה בוקר,יאיר לפיד,אמיר אוחנה] (http://data.obudget.org/queries/719?source?p_mk_names=נאוה בוקר%20יאיר לפיד%20אמיר אוחנה)

  • Use this Query, which receives a list of MK names as input, to get committees in which 2 or more of the given MKs attended (excluding מליאה)
  • In case the user gave more than 2 MK names, the results will appear in descending order starting with the committees containing the most relevant MKs.
  • Important: name has to be exactly as it appears in the List of MK Names

Missing Protocols Data

http://data.obudget.org/queries/642/source?p_from_date=2015-03-30&p_to_date=2016-08-07

  • Between given dates, for every committee, return number of known meetings (from events_event table) and number of meetings without a protocol (not found also in committees_committeemeeting).

Queries Regarding Voting

http://data.obudget.org/queries/641/source?p_from_date=2016-05-22&p_to_date=2016-08-07

  • Get all current MKs between date range that voted against the coalition

find private proposal proposers by order of appearance in the text

http://data.obudget.org/queries/605/source#table

  • change member_id=943 to the id of the mk you want to investigate. See getting member id query below.
  • only works for private proposal laws, not for all laws.
  • sorts the Knesset members which proposed the law by order of appearance in the law text.

getting all MK names

http://data.obudget.org/queries/615/source

  • returns all known alternative spellings of the MK name

http://data.obudget.org/queries/621/source?p_mk_id=756

  • Return by ID (replace the number at the end)

http://data.obudget.org/queries/622/source?p_mk_name=יאיר לפיד

  • By Name (replace the name at the end)
  • Important: Note that the name has to be exactly as it appears in theList of MK Names)

getting member id

  • in other queries you might need the ID of the Knesset Member (MK)
  • you can get it by going to the member's page on open knesset
  • then the member id is in the url:
  • https://oknesset.org/member/943/
  • member/943/

Getting MK Committee Membership

http://data.obudget.org/queries/640/source

  • Original date is in a hard to use format in sql queries.
  • This table reorders the data as committee, member, and is_member column with true/false value
  • UNION of data from MK membership table and MK chairperson-ship table
Clone this wiki locally