Open main menu

Wikidata:WikiProject British Politicians/Sample Queries

This is a collection of sample queries to demonstrate what can be done with Wikidata and the MPs dataset. Suggestions or refinements are always welcome.

Parliamentary dataEdit

All political terms for a single person
# parliamentary terms of a single person
# here, Winston Churchill
SELECT DISTINCT ?constituencyLabel ?partyLabel ?start ?electionLabel ?end ?causeLabel {
 wd:Q8016 p:P39 ?positionStatement . # all positions held by this person
  ?positionStatement ps:P39 [wdt:P279* wd:Q16707842] . # filter to positions which are a subclass of UK MP
 OPTIONAL { ?positionStatement pq:P768 ?constituency . }  # then find various specific values for each term
 OPTIONAL { ?positionStatement pq:P4100 ?party . }
 OPTIONAL { ?positionStatement pq:P580 ?start . }
 OPTIONAL { ?positionStatement pq:P2715 ?election . }
 OPTIONAL { ?positionStatement pq:P582 ?end . }
 OPTIONAL { ?positionStatement pq:P1534 ?cause . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?start

Try it!

All members in a specific parliamentary term
# members of the 2010-15 Parliament.
SELECT DISTINCT ?item ?itemLabel ?constituencyLabel ?partyLabel ?start ?electionLabel ?end ?causeLabel {
 ?item p:P39 ?positionStatement .
 ?positionStatement ps:P39 wd:Q35494253 . 
 OPTIONAL { ?positionStatement pq:P768 ?constituency . }
 OPTIONAL { ?positionStatement pq:P4100 ?party . }
 OPTIONAL { ?positionStatement pq:P580 ?start . }
 OPTIONAL { ?positionStatement pq:P2715 ?election . }
 OPTIONAL { ?positionStatement pq:P582 ?end . }
 OPTIONAL { ?positionStatement pq:P1534 ?cause . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?start

Try it!

All incumbents for a single constituency
# incumbents for a single constituency
# here, Edinburgh South
SELECT DISTINCT ?person ?personLabel ?partyLabel ?start ?electionLabel ?end ?causeLabel {
 ?person p:P39 ?positionStatement .
 ?positionStatement ps:P39 [wdt:P279* wd:Q16707842] .  # all people who held an MP position
 ?positionStatement pq:P768 wd:Q1070093 .              # filter only to those with Edinburgh South as the constituency
 OPTIONAL { ?positionStatement pq:P4100 ?party . }
 OPTIONAL { ?positionStatement pq:P580 ?start . }
 OPTIONAL { ?positionStatement pq:P2715 ?election . }
 OPTIONAL { ?positionStatement pq:P582 ?end . }
 OPTIONAL { ?positionStatement pq:P1534 ?cause . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?start

Try it!

All party changes in a single Parliament
SELECT DISTINCT ?item ?itemLabel ?party1Label ?reasonLabel ?date ?party2Label {
 ?item p:P39 ?positionStatement1 .
 ?positionStatement1 ps:P39 wd:Q30524710 . 
 ?positionStatement1 pq:P4100 ?party1 .
 ?positionStatement1 pq:P582 ?date .
 OPTIONAL { ?positionStatement1 pq:P1534 ?reason . }
 ?item p:P39 ?positionStatement2 .
 ?positionStatement2 ps:P39 wd:Q30524710 . 
 ?positionStatement2 pq:P4100 ?party2 .
 ?positionStatement2 pq:P580 ?date .  filter ( ?party1 != ?party2 ) 
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?date

Try it!

All members in Parliament on a single date
# # # generate all members serving on a specific date
#
# see https://www.wikidata.org/wiki/Wikidata:WikiProject_British_Politicians
# as of Feb 2019 this query will work comprehensively for any date since the 1886 election
# including, where known, constituency, party, start & end dates, election, & reason term ended
#
# 1886 onwards = data precise to the day (a few minor exceptions wrt resignation dates etc)
#                (taken from Historic Hansard/MySociety then manually cleaned & validated) 
# 1832-1886 = data from Historic Hansard but not yet validated, known to be somewhat incomplete
# 1386-1421, 1509-1604 = can only say "served at some point during the term" so 
#                        there will be some ambiguities (note last column for relevant data)
# 
# data for earliest periods is taken from History of Parliament but due to gaps
# in the historical record is inevitably not complete - eg 1401 has only 40 MPs.
# 
# 1640-1832 is completely blank, and 1421-1509, 1604-1640 are v patchy. 
#
# To run this query, change the date ("2016-05-10T00:00:00Z") below - keep the same format
# then scroll down and click the blue [>] button to the lower left

SELECT DISTINCT ?item ?itemLabel ?constituencyLabel ?partyLabel ?start ?electionLabel ?end ?endcauseLabel ?noteLabel
where {
  # # # #     # # # #     # # # # #
  # # # # #     # # # #     # # # # # 
  # # # # # # sub in value here # # # # 
    bind(("2016-05-10T00:00:00Z"^^xsd:dateTime) as ?date) . 
  # # # # # # sub in value here # # # #
  # # # # #     # # # #     # # # # #
  # # # #     # # # #     # # # # #
  ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . 
  { ?term wdt:P279 wd:Q16707842 } union { ?term wdt:P279 wd:Q18015642 } union { ?term wdt:P279 wd:Q18018860 } 
  OPTIONAL { ?positionStatement pq:P768 ?constituency . }
  OPTIONAL { ?positionStatement pq:P4100 ?party . }
  OPTIONAL { ?positionStatement pq:P2715 ?election . }
  OPTIONAL { ?positionStatement pq:P1534 ?endcause . }
  OPTIONAL { ?positionStatement pq:P580 ?start . }
  OPTIONAL { ?positionStatement pq:P582 ?end . }
  OPTIONAL { ?positionStatement pq:P793 ?note . }
  ?term wdt:P571 ?termstart . optional { ?term wdt:P576 ?termend . }
  filter (COALESCE(?start, ?termstart) <= ?date ) . filter (COALESCE(?end, ?termend, NOW()) >= ?date ) .
  # use qualifier dates; if not known use term dates; if no end date use now
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by desc(?start) ?end

Try it!

Party breakdown on a single date
# # # generate all members serving on a specific date
#
# see https://www.wikidata.org/wiki/Wikidata:WikiProject_British_Politicians
# as of Feb 2019 this query will work comprehensively for any date since the 1886 election
# including, where known, constituency, party, start & end dates, election, & reason term ended
#
# 1886 onwards = data precise to the day (a few minor exceptions wrt resignation dates etc)
#                (taken from Historic Hansard/MySociety then manually cleaned & validated) 
# 1832-1886 = data from Historic Hansard but not yet validated, known to be somewhat incomplete
# 1386-1421, 1509-1604 = can only say "served at some point during the term" so 
#                        there will be some ambiguities (note last column for relevant data)
# 
# data for earliest periods is taken from History of Parliament but due to gaps
# in the historical record is inevitably not complete - eg 1401 has only 40 MPs.
# 
# 1640-1832 is completely blank, and 1421-1509, 1604-1640 are v patchy. 
#
# To run this query, change the date ("2016-05-10T00:00:00Z") below - keep the same format
# then scroll down and click the blue [>] button to the lower left

SELECT DISTINCT ?party1 ?name ?rgb (count(DISTINCT(?item)) as ?count) 
where {
  # # # #     # # # #     # # # # #
  # # # # #     # # # #     # # # # # 
  # # # # # # sub in value here # # # # 
    bind(("2016-05-10T00:00:00Z"^^xsd:dateTime) as ?date) . 
  # # # # # # sub in value here # # # #
  # # # # #     # # # #     # # # # #
  # # # #     # # # #     # # # # #
  ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . 
  { ?term wdt:P279 wd:Q16707842 } union { ?term wdt:P279 wd:Q18015642 } union { ?term wdt:P279 wd:Q18018860 } 
  OPTIONAL { ?positionStatement pq:P768 ?constituency . }
  { ?positionStatement pq:P4100 ?party . } # nb PARTY IS REQUIRED
  OPTIONAL { ?positionStatement pq:P580 ?start . }
  OPTIONAL { ?positionStatement pq:P582 ?end . }
  ?term wdt:P571 ?termstart . optional { ?term wdt:P576 ?termend . }
  filter (COALESCE(?start, ?termstart) <= ?date ) . filter (COALESCE(?end, ?termend, NOW()) >= ?date ) .
  # use qualifier dates; if not known use term dates; if no end date use now
 BIND(IF(?party = wd:Q6467393, wd:Q9630 , ?party) AS ?party1) .
 optional { ?party1 wdt:P465 ?rgb } . # colour if known
  
  ?party1 rdfs:label ?longname FILTER (LANG(?longname) = "en") .
 OPTIONAL { ?party1 wdt:P1813 ?shortname FILTER (LANG(?shortname) = "en") } 
 BIND(IF(BOUND(?shortname), ?shortname, ?longname) AS ?name)
  
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} group by ?party1 ?name ?rgb order by desc(?count)
#defaultView:BubbleChart

Try it!

Total experience in the Commons by party
# This returns the total number of days experience in Parliament for each party grouping.
# MPs are counted with their current group regardless of any historical affiliations.
# Day counts are only when they are serving as elected MPs - election day to dissolution - 
# and do not include any periods outside the Commons (eg defeated and later returned)
# Labour Co-Op is counted as Labour; Change UK as independent.

SELECT distinct ?groupLabel
  (count(distinct ?mp) as ?current_members)
  (round(sum(?duration)) as ?cumulative_days_experience)
  (round(?cumulative_days_experience/?current_members) as ?average_days_experience)
  ((round(?average_days_experience/36.525)/10) as ?average_in_years) #fiddly count to make it decimal
   WHERE {
     { ?mp p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start . ?ps pq:P582 ?end . 
       ?term wdt:P279 wd:Q16707842 . BIND((?end-?start) AS ?duration) }
     # period for all ended terms for all MPs, regardless of party
     union
     { ?mp p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start . ?term wdt:P279 wd:Q16707842 . 
       filter not exists { ?ps pq:P582 ?end } . BIND(floor((NOW() - ?start)) as ?duration) }
     # time elapsed in a term which is still ongoing; floor rounds down to whole days
     # these two clauses count all the days served by MPs, both finished & current terms
     
       ?mp p:P39 ?currps . ?currps ps:P39 ?currterm . ?currterm wdt:P279 wd:Q16707842 .
       ?currps pq:P4100 ?party . filter not exists { ?currps pq:P582 ?currend } .
     # our MP must hold a current term, ie has no end date, and have a party affiliation
     
     BIND(IF(?party = wd:Q6467393, wd:Q9630 ,
             (IF(?party = wd:Q61751194 , wd:Q327591 , 
                (IF(?party = wd:Q67153570 , wd:Q327591 , ?party)))
             )
            )             
          AS ?group) .
     # merge Labour/Co-Op, Independent/Change UK/The Independents
     
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} group by ?groupLabel order by desc(?current_members)

Try it!

Total experience on a given date
# # # generate all members serving on a specific date
# # # with their constituency, party group (amalgamated) and experience in days
# # # seems valid back to 1997 but perhaps unreliable before that

SELECT DISTINCT ?item ?itemLabel ?constituencyLabel ?groupLabel ?experience_in_days 
where 
{  
  { select ?item (sum(round(?duration)) as ?experience_in_days)
    WHERE {
     { bind(("1997-05-07T00:00:00Z"^^xsd:dateTime) as ?date) .
       ?item p:P39 ?ps . ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
       ?ps pq:P580 ?start . ?ps pq:P582 ?end . 
       filter ( ?end < ?date ) . 
       BIND((?end - ?start) AS ?duration) . }
     # period for all previous terms
     union
     { bind(("1997-05-07T00:00:00Z"^^xsd:dateTime) as ?date) .
       ?item p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start .  ?ps pq:P582 ?end .
       ?term wdt:P279 wd:Q16707842 . filter ( ?start <= ?date ) .filter ( ?end >= ?date ) .
       BIND((?date - ?start) AS ?duration) } 
     # period for the term containing this date with an end 
     union
     { bind(("1997-05-07T00:00:00Z"^^xsd:dateTime) as ?date) .
       ?item p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start .  
       ?term wdt:P279 wd:Q16707842 . filter ( ?start <= ?date ) .filter not exists { ?ps pq:P582 ?end } .
       BIND((?date - ?start) AS ?duration) }     
     # period for an ongoing term containing this date 
   } group by ?item
  }  
  { select ?item ?constituency ?group
    where {
      bind(("1997-05-07T00:00:00Z"^^xsd:dateTime) as ?date) .
      ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 .
      ?positionStatement pq:P580 ?start . ?positionStatement pq:P768 ?constituency . 
      OPTIONAL { ?positionStatement pq:P582 ?end . } filter (?start <= ?date ) . filter (COALESCE(?end, NOW()) > ?date ) .
      OPTIONAL { ?positionStatement pq:P4100 ?party . 
        BIND(IF(?party = wd:Q6467393, wd:Q9630 ,
               (IF(?party = wd:Q61751194 , wd:Q327591 , 
                  (IF(?party = wd:Q67153570 , wd:Q327591 , ?party)))  )  )  AS ?group) . }
      # use qualifier dates; if no end date use now ; also merge independent groups & labour co-op
      # touch wood this should give numbers for TODAY, so if they have changed today it gives the newest grouping
   }
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

People who were simultaneously MPs and MSPs
# people who were both MPs and MSPs at the same time
SELECT DISTINCT ?person ?personLabel ?mpStart ?mpEnd ?mspStart ?mspEnd
WHERE {
  ?person p:P39 ?mspStatement, ?mpStatement.
  ?mspStatement ps:P39/wdt:P279* wd:Q1711695.
  ?mpStatement ps:P39/wdt:P279* wd:Q16707842.            # find all MSPs and MPs
  ?mspStatement pq:P580 ?mspStart.                       # find their MSP term
  OPTIONAL { ?mspStatement pq:P582 ?mspEnd_. }
  BIND(COALESCE(?mspEnd_, NOW()) AS ?mspEnd)
  ?mpStatement pq:P580 ?mpStart.                         # find their MP term
  OPTIONAL { ?mpStatement pq:P582 ?mpEnd_. }
  BIND(COALESCE(?mpEnd_, NOW()) AS ?mpEnd)
  FILTER ( ?mpStart <= ?mspEnd && ?mspStart <= ?mpEnd )  # check they overlapped
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
ORDER BY ?mpStart

Try it!

MPs expelled from or suspended by their party
# MPs who were expelled from, or suspended by, their party
SELECT DISTINCT ?person ?personLabel ?causeLabel ?partyLabel ?date
WHERE {

  ?person p:P39 [ ps:P39 [ wdt:P279* wd:Q16707842 ] ;  
          pq:P1534 ?cause ; pq:P582 ?date ; pq:P4100 ?party ].                  # find the reason their term of office ended
  { ?cause wdt:P279* wd:Q30580630 . } UNION { ?cause wdt:P279* wd:Q35855188 . } # filter to expulsion or suspension
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }          # get their names
}
ORDER BY ?date

Try it!

People who were returned for two seats at the same election
# MPs returned for more than one seat at the same election
SELECT DISTINCT ?item ?itemLabel ?electionLabel ?constituencyLabel ?constituency2Label  {
  ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P2715 ?election . 
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' } 
  ?item p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?term2 . ?positionStatement2 pq:P2715 ?election2 . ?positionStatement2 pq:P768 ?constituency2 . 
  filter (?constituency != ?constituency2 ) . filter (str(?constituency) < str(?constituency2) ) 
  filter (?term = ?term2 ) . filter (?election = ?election2 ) 
  optional {?election wdt:P585 ?date }
  ?election wdt:P31 wd:Q15283424 . ?election2 wdt:P31 wd:Q15283424 .
  ?term wdt:P279* wd:Q16707842 . ?term wdt:P279* wd:Q16707842 .
}
ORDER BY DESC(?date) ?itemLabel

Try it!


CareersEdit

MPs who began their careers at by-elections
# MPs who began their career at a by-election since 1945
# note that party data is at time of election, & is not usually available before 1997 (yet!)
# some are tied to a specific by-election, others generic; this is being updated as we go along
SELECT DISTINCT ?MP ?MPLabel ?start ?constituencyLabel ?partyLabel ?electionLabel 
 {
 ?membership wdt:P279 wd:Q16707842 .
 ?MP p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
 ?positionStatement pq:P768 ?constituency .
 optional { ?positionStatement pq:P4100 ?party } . 
 ?positionStatement pq:P580 ?start .
 ?positionStatement pq:P2715 ?election . filter not exists { ?election wdt:P31 wd:Q15283424 } 
 # this finds all people who held an MP role but were not elected at a general election
 filter not exists { ?membership2 wdt:P279 wd:Q16707842 . 
    ?MP p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?membership2 . 
    ?positionStatement2 pq:P580 ?start2 . FILTER (?start2 < ?start) . } 
 # ...and where they are not recorded as having ever served in Parliament before
 FILTER (?start >= "1945-07-05T00:00:00Z"^^xsd:dateTime) . 
 # limits it to only after the date of the 1945 general election
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?start

Try it!

The reasons MPs left office for the last time
# This query identifies MPs and the final ends to their careers
# It looks for 
# - the last time someone left office as an MP
# - what the cause of their leaving office was

SELECT DISTINCT (count (?item) as ?count) ?causeLabel 
{
 ?membership wdt:P279 wd:Q16707842 .  # find all MP positions
 ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership .  # and people who held such a position
 OPTIONAL { ?positionStatement pq:P768 ?constituency . } # constituency if known
 OPTIONAL { ?positionStatement pq:P4100 ?party . } # party if known
 ?positionStatement pq:P580 ?start . # all members who have a start date (need to block out any without full dates)
 ?positionStatement pq:P582 ?end . # all members who have an end date 
 ?positionStatement pq:P1534 ?cause .
 FILTER NOT EXISTS {
 ?membership2 wdt:P279 wd:Q16707842 .
 ?item p:P39 ?positionStatement2 . 
 ?positionStatement2 ps:P39 ?membership2 . 
 ?positionStatement2 pq:P580 ?start2 .
 FILTER (?start2 >= ?end) .  } # filter out any where they came back to office at a later date
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
GROUP BY ?causeLabel
ORDER BY ?count

Try it!

Number of distinct terms served by a single MP
# MPs who have sat in twelve or more terms
SELECT distinct ?terms ?mp ?mpLabel ?article
WHERE
{
  {
    SELECT ?mp (COUNT (distinct ?position) AS ?terms) WHERE {
      ?position wdt:P279 wd:Q16707842. # every UK MP (currently post-1832)
      ?mp p:P39 ?positionStatement . ?positionStatement ps:P39 ?position . 
    }
    GROUP BY ?mp
  }
  optional { ?article schema:about ?mp . ?article schema:inLanguage "en" .
             ?article schema:isPartOf <https://en.wikipedia.org/> . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  filter (?terms >= 12 ) . # twelve or more terms
}   
ORDER BY desc(?terms)

Try it!

ConstituenciesEdit

List of all constituencies which have been represented by any known MP
SELECT DISTINCT ?constituencyLabel ?typeLabel ?start ?end ?partofLabel {
 ?item p:P39 ?positionStatement .
 ?positionStatement ps:P39 [ wdt:P279 wd:Q16707842 ] . 
 ?positionStatement pq:P768 ?constituency .
 OPTIONAL { ?constituency wdt:P31 ?type .}
 OPTIONAL { ?constituency wdt:P571 ?start .}
 OPTIONAL { ?constituency wdt:P576 ?end .}
 OPTIONAL { ?constituency wdt:P131 ?partof .}
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?start

Try it!

MPs who have sat for five or more constituencies
# MPs, and the different seats they sat for
# counting on label takes account of the fact that some seats are split by Wikidata
# and this is slightly counterintuitive (a reader might reasonably treat them as the same place)
# see eg Ian Mikardo at Reading; the constituency 1885-1950 & 1955-1974 are distinct items
# if we counted by ?constituency then he would total six, but really two are the "same" seat

select ?mp ?mpLabel ?seatcount ?seats
WHERE {
       {
        SELECT ?mp ?mpLabel (COUNT(DISTINCT ?constituencyLabel) AS ?seatcount) (GROUP_CONCAT(DISTINCT(?constituencyLabel); separator=", ") as ?seats)
         WHERE {
         ?position wdt:P279 wd:Q16707842.
         ?mp p:P39 ?positionStatement . ?positionStatement ps:P39 ?position ; pq:P768 ?constituency .
         SERVICE wikibase:label {
               bd:serviceParam wikibase:language "en" .
               ?mp rdfs:label ?mpLabel .
               ?constituency rdfs:label ?constituencyLabel }
        } GROUP BY ?mp ?mpLabel 
       }
  filter(?seatcount >4)
} 
order by desc(?seatcount)

Try it!


MPs who sat for three different seats in a single parliamentary term
# MPs who sat for three constituencies in the same parliament
SELECT DISTINCT ?item ?itemLabel ?membership1Label ?constituency1Label ?elected1 ?constituency2Label ?elected2 ?constituency3Label ?elected3
WHERE {
 ?membership1 wdt:P279 wd:Q16707842 . 
 ?membership2 wdt:P279 wd:Q16707842 . 
 ?membership3 wdt:P279 wd:Q16707842 . 
 ?item p:P39 ?positionStatement1 . ?positionStatement1 ps:P39 ?membership1 ; pq:P768 ?constituency1 ; pq:P580 ?elected1 .
 ?item p:P39 ?positionStatement2 . ?positionStatement2 ps:P39 ?membership2 ; pq:P768 ?constituency2 ; pq:P580 ?elected2 .
 ?item p:P39 ?positionStatement3 . ?positionStatement3 ps:P39 ?membership3 ; pq:P768 ?constituency3 ; pq:P580 ?elected3 .
 FILTER ( ?membership1 = ?membership2 ) . FILTER ( ?membership2 = ?membership3 ) . # check they are all in the same Parliament
 FILTER(STR(?constituency1) > STR(?constituency2)) . # 
 FILTER(STR(?constituency2) > STR(?constituency3)) . # this stops us returning several lines, and also confirms they are different seats
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' } }

Try it!


People who were returned for two seats at the same election
# MPs returned for more than one seat at the same election
SELECT DISTINCT ?item ?itemLabel ?electionLabel ?constituencyLabel ?constituency2Label  {
 ?item p:P39 ?positionStatement .
 ?positionStatement ps:P39 ?term . 
 ?positionStatement pq:P768 ?constituency . 
 ?positionStatement pq:P2715 ?election . 
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' } 
 ?item p:P39 ?positionStatement2 .
 ?positionStatement2 ps:P39 ?term2 . 
 ?positionStatement2 pq:P2715 ?election2 . 
 ?positionStatement2 pq:P768 ?constituency2 . 
  filter (?constituency != ?constituency2 ) 
  filter (str(?constituency) < str(?constituency2) ) 
  filter (?term = ?term2 ) 
  filter (?election = ?election2 ) 
  optional {?election wdt:P585 ?date }
  ?election wdt:P31 wd:Q15283424 . ?election2 wdt:P31 wd:Q15283424 .
  ?term wdt:P279* wd:Q16707842 . ?term wdt:P279* wd:Q16707842 .
}
ORDER BY DESC(?date) ?itemLabel

Try it!

Number of men and women elected in various constituencies
# UK Parliament constituencies and the number of male & female MPs they have elected since 1997

SELECT DISTINCT ?constituency ?constituencyLabel ?created ?dissolved ?men_elected ?women_elected ?total
{
 ?constituency wdt:P31 wd:Q27971968 . 
  filter not exists { ?constituency wdt:P576 ?end . FILTER (?end <= "1997-05-01T00:00:00Z"^^xsd:dateTime) . }
  optional { ?constituency wdt:P576 ?end . bind(year(?end) as ?dissolved) . }
  optional { ?constituency wdt:P571 ?start . bind(year(?start) as ?created) . }
 # find all constituencies, omit any dissolved before 1997
  optional { SELECT ?constituency (COUNT (distinct ?man) AS ?men_elected) WHERE {
      ?constituency wdt:P31 wd:Q27971968 . 
      ?man p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P580 ?start . 
      ?man wdt:P21 wd:Q6581097 . FILTER (?start >= "1997-05-01T00:00:00Z"^^xsd:dateTime) . 
    } GROUP BY ?constituency }  
  # find and count all men elected (optional in case 0), omit any elected before 1997
  optional { SELECT ?constituency (COUNT (distinct ?woman) AS ?women_elected) WHERE {
      ?constituency wdt:P31 wd:Q27971968 . 
      ?woman p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P580 ?start . 
      ?woman wdt:P21 wd:Q6581072 . FILTER (?start >= "1997-05-01T00:00:00Z"^^xsd:dateTime) . 
    } GROUP BY ?constituency }
  # find and count all women elected (optional in case 0), omit any elected before 1997
  { SELECT ?constituency (COUNT (distinct ?mp) AS ?total) WHERE {
      ?constituency wdt:P31 wd:Q27971968 . 
      ?mp p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P580 ?start . 
      FILTER (?start >= "1997-05-01T00:00:00Z"^^xsd:dateTime) . 
    } GROUP BY ?constituency }
  # find and count all people elected, omit any elected before 1997
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
ORDER BY ?men_elected ?women_elected

Try it!

ElectionsEdit

Number of men and women returned at general elections
SELECT distinct ?election ?electionLabel ?members ?women ?men where
{
  { SELECT distinct ?election ?electiondate where 
    { ?election wdt:P31 wd:Q15283424 . ?election wdt:P585 ?electiondate . }
  } # find all general elections and their date (for sorting)
  
  { SELECT ?election (count(distinct ?item) as ?members) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
    } group by ?election
  } # number of people elected in this election
    # this code finds anyone who served the parliamentary term linked to this election
    # with a P39 value which started with this election

  { SELECT ?election (count(distinct ?item) as ?women) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
      ?item wdt:P21 wd:Q6581072 .
    } group by ?election
  } # as above, count only women

  { SELECT ?election (count(distinct ?item) as ?men) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
      ?item wdt:P21 wd:Q6581097 .
    } group by ?election
  } # as above, count only men

  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by desc(?electiondate)

Try it!

MPs returned at a general election with parliamentary experience
# Number of veteran MPs and completely new MPs returned at each general election
# Note that "veteran" includes any previous UK Parliament experience, which may not have been in the previous term
# Thus an MP who leaves, spends some time out of Parliament, and returns, will count as "veteran" not "new"
# Also includes the number who held office at the dissolution immediately preceding the election
# ie those who had recent experience and no break in service
# and those who have any experience in a devolved assembly
# new MPs are defined as having neither UK nor devolved experience

SELECT distinct ?election ?electionLabel ?members ?westminster_experience ?has_devolved_experience ?devolved_experience_only ?held_at_dissolution ?new

WITH {
   SELECT DISTINCT ?item ?membership ?election ?electiondate ?membership2 ?positionStatement2 ?devolvedmembership ?positionStatement3 ?reason WHERE {
     
     # find all general elections and their date
     ?election wdt:P31 wd:Q15283424 . ?election wdt:P585 ?electiondate . 
     filter (?electiondate >= "1832-01-01T00:00:00Z"^^xsd:dateTime) . 
  
     # item holds a term which began with the election
     ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
     ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election . 
 
     OPTIONAL {
         # find other positions held
         ?item p:P39 ?positionStatement2 .
         ?positionStatement2 ps:P39 ?membership2 .
         ?positionStatement2 pq:P580 ?start . filter ( ?start < ?electiondate ) .
         ?membership2 wdt:P279 wd:Q16707842 .
         optional {?positionStatement2 pq:P1534 ?reason .}
         # optionally find whether they held office during a previous term, and if so why they left
     }
     OPTIONAL {
         # find other positions held
         ?item p:P39 ?positionStatement3 .
         ?positionStatement3 ps:P39 ?devolvedmembership .
         ?positionStatement3 pq:P580 ?start3 . filter ( ?start3 < ?electiondate ) .
         { ?devolvedmembership wdt:P279 wd:Q1711695 . } union
         { ?devolvedmembership wdt:P279 wd:Q3406079 . } union
         { ?devolvedmembership wdt:P279 wd:Q3272410. } 
         # ... where they previously were an MSP, MLA, or AM; only the "modern" NI assembly 
         # is counted (1998-date), as previous incarnations do yet not have term dates available
     }
   }
} AS %members

WHERE {
  
  { SELECT ?election ?electiondate (count(distinct ?item) as ?members) where 
    { INCLUDE %members .
    } group by ?election ?electiondate 
  } # count all members for each election

  { SELECT ?election ?electiondate (count(distinct ?item) as ?westminster_experience) where 
    { INCLUDE %members .
      FILTER (BOUND (?membership2)) .
    } group by ?election ?electiondate
  } # count all peoiple with any earlier term, for each election
  
  { SELECT ?election ?electiondate (count(distinct ?item) as ?held_at_dissolution) where 
    { INCLUDE %members .
      FILTER (BOUND (?positionStatement2)) .
      FILTER EXISTS {?membership wdt:P155 ?membership2} .
      filter(?reason=wd:Q741182)
    } group by ?election ?electiondate
  } # count all people who served in the immediately previous term, and left at dissolution
   
  # people with devolved experience
  optional { SELECT ?election ?electiondate (count(distinct ?item) as ?has_devolved_experience) where 
    { INCLUDE %members .
      FILTER (BOUND (?positionStatement3)) .
    } group by ?election ?electiondate
  } 
  
  # people with devolved experience but not Westminster experience
  optional { SELECT ?election ?electiondate (count(distinct ?item) as ?devolved_experience_only) where 
    { INCLUDE %members .
      FILTER (BOUND (?positionStatement3)) .
      FILTER (!BOUND (?positionStatement2)) .
    } group by ?election ?electiondate
  } 
  bind(if(bound(?devolved_experience_only),?devolved_experience_only,0) as ?deo)
  bind((?members-?westminster_experience-?deo) as ?new)
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by desc(?electiondate)

Try it!

Average ages (approx.) of MPs at general elections, by party
SELECT distinct ?election ?electiondate ?electionLabel ?members ?labour ?conservative 
((?electionyear - ?all_birth) as ?all_age)
((?electionyear - ?lab_birth) as ?lab_age)
((?electionyear - ?con_birth) as ?con_age) where
# nb some strange rounding here - find average year of birth, then subtract from year of election
# still need to find a more sophisticated way of doing this!
{
  { SELECT distinct ?election ?electiondate ?electionyear where 
    { ?election wdt:P31 wd:Q15283424 . ?election wdt:P585 ?electiondate . 
      bind(year(?electiondate) as ?electionyear) . filter (?electionyear >= 1970 ) }
  } # find all general elections and their date (for sorting)
  
  { SELECT ?election (count(distinct ?item) as ?members) (round(avg(year(?birth))) as ?all_birth) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
      optional { ?item wdt:P569 ?birth . }
    } group by ?election
  } # all MPs
  
  { SELECT ?election (count(distinct ?item) as ?labour) (round(avg(year(?birth))) as ?lab_birth) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
      { ?positionStatement pq:P4100 wd:Q9630 } union { ?positionStatement pq:P4100 wd:Q6467393 }
      optional { ?item wdt:P569 ?birth . }
    } group by ?election
  } # as above, count only labour

  { SELECT ?election (count(distinct ?item) as ?conservative) (round(avg(year(?birth))) as ?con_birth) where 
    { ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership . 
      ?membership wdt:P2715 ?election . ?positionStatement pq:P2715 ?election .
      ?positionStatement pq:P4100 wd:Q9626 .
      optional { ?item wdt:P569 ?birth . }
    } group by ?election
  } # as above, count only conservative

  
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by desc(?electiondate)

Try it!

Number of person-days per party in the 2017- Parliament
SELECT distinct ?partyLabel (sum(?duration) as ?total)
   WHERE {
     { ?mp p:P39 ?ps . ?ps ps:P39 wd:Q30524710 . ?ps pq:P580 ?start . ?ps pq:P582 ?end . 
       ?ps pq:P4100 ?party1 . 
       BIND((?end-?start) AS ?duration) }
     # period for all ended terms
     union
     { ?mp p:P39 ?ps . ?ps ps:P39 wd:Q30524710 . ?ps pq:P580 ?start .
       ?ps pq:P4100 ?party1 . 
       filter not exists { ?ps pq:P582 ?end } . BIND(floor((NOW() - ?start)) as ?duration) }
     # time elapsed in a term which is still ongoing; floor rounds down to whole days 
     BIND(IF(?party1 = wd:Q6467393, wd:Q9630 , ?party1) AS ?party) .
     # merges co-op into Labour
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} group by ?partyLabel order by desc(?total)

Try it!

Total number of days served by MPs
SELECT distinct ?mp ?mpLabel (sum(?duration) as ?total)
   WHERE {
     { ?mp p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start . ?ps pq:P582 ?end . 
       ?term wdt:P279 wd:Q16707842 . 
       BIND((?end-?start) AS ?duration) }
     # period for all ended terms
     union
     { ?mp p:P39 ?ps . ?ps ps:P39 ?term . ?ps pq:P580 ?start .
       ?term wdt:P279 wd:Q16707842 .
       filter not exists { ?ps pq:P582 ?end } . BIND(floor((NOW() - ?start)) as ?duration) }
     # time elapsed in a term which is still ongoing; floor rounds down to whole days
     SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
} group by ?mp ?mpLabel 
# blank/zero values showing up because of start/end novalue, but we can work on those

Try it!

Biographical informationEdit

NamesEdit

Most popular first names for MPs
SELECT ?nameLabel (count(distinct ?person1) as ?count)
WHERE {
 { ?person1 wdt:P31 wd:Q5 ; p:P39 ?ps . ?ps ps:P39 ?term .
   {?term wdt:P279* wd:Q16707842 } # UK MP
    UNION { ?term wdt:P279* wd:Q18015642 } # British MP
    UNION { ?term wdt:P279* wd:Q18018860 } # English MP
   }
 union { ?person1 wdt:P31 wd:Q5 ; wdt:P1614 ?hop } # or in HoP but without an item
 ?person1 wdt:P735 ?name
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?nameLabel
ORDER BY desc(?count)
#defaultView:BubbleChart

Try it!

Number of female MPs and of ones named "John" over time
# UK parliaments with count of Johns and count of women
#defaultView:LineChart
SELECT ?date ?count ?label WITH {
  # all MPs with their associated parliament
  SELECT DISTINCT ?mp ?parliament WHERE {
    ?mp wdt:P31 wd:Q5.
    {
      # new data model: position held – Member of the nth Parliament of the United Kingdom
      ?mp p:P39/ps:P39 ?position.
      ?position wdt:P279 wd:Q16707842;
                p:P279/pq:P2937|wdt:P2937 ?parliament.
    } UNION {
      # old data model: member of – nth Parliament of the United Kingdom
      ?mp p:P463/ps:P463 ?parliament.
      ?parliament wdt:P31 wd:Q21094819.
    }
  } 
} AS %MPsWithParliament WITH {
  # count of Johns in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?johns) WHERE {
    INCLUDE %MPsWithParliament.
    ?mp wdt:P735 wd:Q4925477.
  }
  GROUP BY ?parliament
} AS %johns WITH {
  # count of women in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?women) WHERE {
    INCLUDE %MPsWithParliament.
    ?mp wdt:P21/wdt:P279* wd:Q6581072. # (P279*: include transgender females – non yet, but will probably happen in the future)
  }
  GROUP BY ?parliament
} AS %women WHERE {
  # get ?johns and ?women
  INCLUDE %johns.
  INCLUDE %women.
  # fan out single result
  # ?parliament ?johns ?women
  # into two results
  # ?parliament "Johns" ?johns
  # ?parliament "women" ?women
  # so the line chart works
  VALUES ?label { "Johns"@en "women"@en }
  BIND(IF(?label = "Johns"@en, ?johns, ?women) AS ?count)
  # get parliament date for the chart
  ?parliament wdt:P571|wdt:P580 ?date.
  # add labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?date

Try it!


Given names of MPs with date when first MP with that name entered parliament
SELECT ?givenName ?givenNameLabel ?firstDate WITH {
  SELECT ?givenName (MIN(?date) AS ?firstDate) WHERE {
    { ?term wdt:P279* wd:Q16707842. } UNION
    { ?term wdt:P279* wd:Q18015642. } UNION
    { ?term wdt:P279* wd:Q18018860. }
    ?mp p:P39 ?mpStatement . ?mpStatement ps:P39 ?term .
    optional { ?mpStatement pq:P580 ?start.  FILTER (!isBLANK(?start)) }
    ?mp wdt:P735 ?givenName.
    ?term wdt:P571 ?termstart .
  bind(COALESCE(?start, ?termstart) as ?date ) . 
  # use qualifier dates; if not known use term dates
  }
  GROUP BY ?givenName
} AS %givenNames WHERE {
  INCLUDE %givenNames.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?firstDate ?givenNameLabel

Try it!

MPs with the same surname as their constituency
# items holding a position for an electoral district with a label matching their family name
SELECT DISTINCT ?item ?name ?label WHERE {
  ?item p:P39/pq:P768/rdfs:label ?label;
         wdt:P734/rdfs:label ?label.
  filter(lang(?label)="en")
  ?item rdfs:label ?name .  filter(lang(?name)="en")
}

Try it!

PlacesEdit

MSPs born outside Scotland
SELECT DISTINCT ?person ?personLabel ?born ?bornLabel ?coord {
  ?position wdt:P279* wd:Q1711695 .          # 1. find all the MSP positions
  ?person p:P39 ?ps . ?ps ps:P39 ?position . # 2. did they hold any MSP position
  ?person wdt:P31 wd:Q5 .                    # 2a. check they were actually people (in case we have any fictional MSPs)
  ?person wdt:P19 ?born .                    # 3. where were they born
  MINUS { ?born wdt:P131* wd:Q22 . }         # 4. this bit filters down to just "not in Scotland"
  OPTIONAL { ?born wdt:P625 ?coord . }       # 5. coordinates if available
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
#defaultView:Map

Try it!

Birth and deathEdit

Most and least common birthdays among MPs
SELECT DISTINCT ?birthday (count(distinct ?item) as ?total) WHERE
{
 ?item wdt:P31 wd:Q5 ; p:P39 ?ps . ?ps ps:P39 ?term .
 ?term wdt:P279* wd:Q16707842 .      # UK MP (let's only look at post-1801 for now)
 ?item wdt:P569 ?born .              # with a known birthday
 BIND( DAY(?born) AS ?day )          # identify the month of birth
 BIND( MONTH(?born) AS ?month )      # identify the month of birth
 bind(concat(str(?day),"/",str(?month)) as ?birthday)
 ?item p:P569/psv:P569 ?date_node . ?date_node wikibase:timePrecision "11"^^xsd:integer .
                                     # filter only day-precision records
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
GROUP BY ?birthday
ORDER BY ?total

Try it!

All MPs born since 1945, sorted by month of birth
SELECT DISTINCT ?month (count(?item) as ?total)
{
 ?item wdt:P31 wd:Q5 ; p:P39 ?ps . ?ps ps:P39 ?term .
 ?term wdt:P279* wd:Q16707842 .     # all UK MPs
 ?item wdt:P569 ?born .             # with a known birthday
 BIND( MONTH(?born) AS ?month )     # identify the month of birth
 BIND( YEAR(?born) AS ?year )       # identify the year of birth
 FILTER (?year > 1945 ) 
 ?item p:P569/psv:P569 ?date_node .
 ?date_node wikibase:timePrecision "11"^^xsd:integer .  # filter only day-precision records
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}
GROUP BY ?month
ORDER BY ?month
#defaultView:LineChart

Try it!

Cause of death for MPs who died in office
# what on earth happened to MPs who died in office? Here, 1854...
SELECT ?mp ?mpLabel ?died ?mannerLabel ?causeLabel ?casualtyLabel ?placeLabel
WHERE {
    ?membership wdt:P279 wd:Q16707842 . # all UK MPs
    ?mp p:P39 ?positionStatement .  ?positionStatement ps:P39 ?membership . 
    ?positionStatement pq:P1534 wd:Q5247364 .  # ie only those coded as end cause: death in office
    ?mp wdt:P570 ?died .
    optional { ?mp wdt:P509 ?cause . }
    optional { ?mp wdt:P1196 ?manner . }
    optional { ?mp wdt:P1347 ?casualty . } # military deaths are coded strangely
    optional { ?mp wdt:P20 ?place . }
  FILTER (?died >= "1854-01-01T00:00:00Z"^^xsd:dateTime) . 
  FILTER (?died <= "1854-12-31T00:00:00Z"^^xsd:dateTime) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Graph of the number of MPs who died in office by year
# How many MPs died in office, each year 1832-date?
# Data pre-WW2 is still a bit flaky on exact death dates,
# probably a bit more reliable after that

SELECT ?year ?diedcount
#defaultView:BarChart
WITH {
  # count of MPs who died in office in each parliament
  SELECT ?year (COUNT(DISTINCT ?mp) AS ?died) WHERE {
    ?membership wdt:P279 wd:Q16707842 . # all UK MPs
    ?mp p:P39 ?positionStatement .  ?positionStatement ps:P39 ?membership . 
    ?positionStatement pq:P1534 wd:Q5247364 .  # ie only those coded as end cause: death in office
    ?mp wdt:P570 ?died .
    BIND(STR(YEAR(?died)) AS ?year)
  }
  GROUP BY ?year
} AS %died 
WHERE {
  INCLUDE %died
  BIND(?died AS ?diedcount)
  # add labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?year

Try it!

Constituencies with a high number of deaths in office
# UK Parliament constituencies and the number of MPs who died in office
# analysis restricted to "since 1929"
#
SELECT DISTINCT ?constituency ?constituencyLabel ?startyear ?endyear ?died ?total ?percent_died_in_office
{
 ?constituency wdt:P31 wd:Q27971968 . 
  optional { ?constituency wdt:P571 ?start . bind(year(?start) as ?startyear)}
  optional { ?constituency wdt:P576 ?end . bind(year(?end) as ?endyear)}
 # find all constituencies
  { SELECT ?constituency (COUNT (distinct ?member) AS ?died) WHERE {
      ?constituency wdt:P31 wd:Q27971968 . 
      ?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency . ?positionStatement pq:P1534 wd:Q5247364 .
      ?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) . 
    } GROUP BY ?constituency }  
  # find and count all people who died in office 
  { SELECT ?constituency (COUNT (distinct ?member) AS ?total) WHERE {
      ?constituency wdt:P31 wd:Q27971968 . 
      ?member p:P39 ?positionStatement . ?positionStatement pq:P768 ?constituency .
      ?positionStatement pq:P580 ?start . FILTER (?start >= "1929-01-01T00:00:00Z"^^xsd:dateTime) . 
    } GROUP BY ?constituency }
  # find and count all people elected 
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
  bind ((?died/?total)*100 as ?percent_died_in_office)
}
ORDER BY desc(?percent_died_in_office)

Try it!

(Former) MPs who died in the past calendar year
# All recently deceased MPs (ie this calendar year)
# giving date of birth, last constituency, last date in Parliament, and reason for leaving
SELECT DISTINCT ?item ?itemLabel ?wikipedia ?born ?constituencyLabel ?left ?died
{
 ?membership wdt:P279 wd:Q16707842 .  # find all MP positions
 ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?membership .  # and people who held such a position
 OPTIONAL { ?positionStatement pq:P768 ?constituency . } # constituency if known
 OPTIONAL { ?positionStatement pq:P4100 ?party . } # party if known
 ?positionStatement pq:P580 ?start . # all members who have a start date (need to block out any without full dates)
 optional { ?positionStatement pq:P582 ?left . }
 ?positionStatement pq:P1534 ?cause .
 FILTER NOT EXISTS {
 ?membership2 wdt:P279 wd:Q16707842 .
 ?item p:P39 ?positionStatement2 . 
 ?positionStatement2 ps:P39 ?membership2 . 
 ?positionStatement2 pq:P580 ?start2 .
 FILTER (?start2 >= ?left) .  } 
 # filter out any where they came back to office at a later date
 ?item wdt:P570 ?died . 
 # find all MPs who have died
 BIND(YEAR(NOW()) AS ?now) . FILTER (YEAR(?died) = ?now ) . 
 # find all MPs who died this year
 # "BIND(YEAR(NOW())-1 AS ?now)" for last year, etc
 optional { ?item wdt:P569 ?born } . 
 optional { ?wikipedia schema:about ?item . ?wikipedia schema:isPartOf <https://en.wikipedia.org/>. } .
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by desc(?died)

Try it!

Ancestry and familiesEdit

Married couples who were both MPs
SELECT DISTINCT ?person1 ?person1Label ?person2 ?person2Label
WHERE {
 ?person1 wdt:P31 wd:Q5 ; p:P39 ?ps . ?ps ps:P39 ?term1 .
 ?term1 wdt:P279* wd:Q16707842 .                                     # person 1 is a UK MP
 ?person2 wdt:P31 wd:Q5 ; p:P39 ?ps . ?ps ps:P39 ?term2 .
 ?term2 wdt:P279* wd:Q16707842 .                                     # person 2 is a UK MP
 ?person2 wdt:P26 ?person1 .                                         # person 2 is married to person1
 FILTER(STR(?person2) > STR(?person1))                               # person 1's ID is smaller than person 2's
                                                                     # (this means we don't have both A-B and B-A showing up)
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  # get their names
}

Try it!

People who had four or more children who became MPs
# People with multiple MPs as children
SELECT distinct ?person ?personLabel ?born ?died ?count WHERE {
  { select distinct ?person (count(distinct ?child) as ?count) where {
  ?person wdt:P40 ?child . 
  ?child p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . 
  { ?term wdt:P279 wd:Q16707842 } union { ?term wdt:P279 wd:Q18015642 } union { ?term wdt:P279 wd:Q18018860 } 
  } group by ?person } 
  optional { ?person wdt:P569 ?born }
  optional { ?person wdt:P570 ?died }
  filter (?count > 3 ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } 
} order by desc(?count)

Try it!


Political ancestry - MPs with nine direct ancestors who were also MPs, with no breaks
Note that this query is computationally very intensive, and may sometimes time out. I am working on refining it.
This query is too long for the SPARQL template, but can be found at this link
MPs descended from people who were in the UCL Legacy of British Slaveowners database
This query is too long for the SPARQL template, but can be found at this link
Members of the current parliament who Wikidata thinks have mythical ancestors
# MPs with identified mythical ancestors
SELECT ?mp ?mpLabel ?person ?personLabel ?born ?died ?typeLabel WHERE {
  ?mp p:P39 ?positionStatement .
  ?positionStatement ps:P39 wd:Q30524710 .  # this person is a member of the current parliament
  { ?mp (wdt:P25|wdt:P22)* ?person . } # mp's mother or father, or their mother or father, and so on
  filter not exists { ?person wdt:P31 wd:Q5 } . # the ancestor is not "a human"
  ?person wdt:P31 ?type .
  OPTIONAL { ?person wdt:P569 ?born } .
  OPTIONAL { ?person wdt:P570 ?died } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

Try it!

MPs descended from William the Conqueror
# MPs descended from William I 
SELECT distinct ?mp ?mpLabel ?born ?died WHERE {
  ?mp p:P39 ?positionStatement .
  ?positionStatement ps:P39 ?membership .  # this person is a member of parliament
        { ?membership wdt:P279* wd:Q16707842 }  # person was a UK MP
  UNION { ?membership wdt:P279* wd:Q18015642 }  # or a British MP (to 1801)
  UNION { ?membership wdt:P279* wd:Q18018860 }  # or an English MP (to 1707)
  OPTIONAL { ?mp wdt:P569 ?born } .
  OPTIONAL { ?mp wdt:P570 ?died } .
  {  wd:Q37594 wdt:P40* ?mp . } # person's child, or their child's child, and so on
  union
  { ?mp (wdt:P25|wdt:P22)* wd:Q37594 . } # person's parent, or their parent's parent, and so on
  #
  # must replace Q37594 in both of the above lines to make this work properly
  # 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". } }

Try it!

Social mediaEdit

MPs and their twitter accounts
select distinct ?item ?itemLabel ?username ?followers ?date 
where {
  ?item p:P39 ?ps . ?ps ps:P39 wd:Q30524710 . 
  ?item wdt:P2002 ?username . # select best only
  ?item p:P2002 ?ts . ?ts ps:P2002 ?username . optional { ?ts pq:P3744 ?followers . }
  optional {   ?ts prov:wasDerivedFrom [ pr:P813 ?date ] . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  }

Try it!

Other achievementsEdit

MPs who have competed in the Olympics
select distinct ?person ?personLabel ?thingLabel WHERE 
{
 ?person wdt:P1344 ?thing . # person did a thing
 { ?thing wdt:P31 wd:Q159821 } UNION { ?thing wdt:P31 wd:Q82414 }  # thing was the olympics
 ?person wdt:P39 [ wdt:P279* wd:Q16707842 ] .  # person was an MP 
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
}

Try it!

MPs who have also received major scientific awards
#MPs with science awards
SELECT DISTINCT ?personLabel ?person ?awardLabel ?award WHERE {
 ?person wdt:P166 ?award .                  # this person had won some kind of award
 ?award wdt:P31/wdt:P279* wd:Q11448906 .    # and that award is a type of science award
  
         { ?person wdt:P39 [ wdt:P279* wd:Q16707842 ] }  # UK MP
   UNION { ?person wdt:P39 [ wdt:P279* wd:Q18015642 ] }  # British MP (1707-1800)
   UNION { ?person wdt:P39 [ wdt:P279* wd:Q18018860 ] }  # English MP (pre-1707)
  
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}

Try it!

Gallery of MPs who have won a Nobel Prize
#MPs with Nobels
SELECT DISTINCT ?personLabel ?person ?awardLabel ?year ?image WHERE {
 ?person p:P166 ?statement .                  # this person had won some kind of award  
 ?statement ps:P166 ?award . ?award wdt:P31/wdt:P279* wd:Q7191 .    # and that award is a Nobel
 optional { ?statement pq:P585 ?time . bind(year(?time) as ?year) . }
 ?person wdt:P39 [ wdt:P279* wd:Q16707842 ] .
 optional { ?person wdt:P18 ?image . }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
} order by ?year
#defaultView:ImageGrid

Try it!

People known to have been killed by an MP
SELECT DISTINCT ?victim ?victimLabel ?killer ?killerLabel ?died
WHERE {
          ?victim wdt:P31 wd:Q5 ; wdt:P157 ?killer .    # person was killed by a known person
  
        { ?killer wdt:P39 [ wdt:P279* wd:Q16707842 ] }  # killer was a UK MP
  UNION { ?killer wdt:P39 [ wdt:P279* wd:Q18015642 ] }  # or a British MP (to 1801)
  UNION { ?killer wdt:P39 [ wdt:P279* wd:Q18018860 ] }  # or an English MP (to 1707)
  
  OPTIONAL { ?victim wdt:P570 ?died }                   # date of death if known
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  # get their names
}

Try it!

People who sat in the Commons and in a foreign parliament
SELECT DISTINCT ?person ?personLabel ?positionLabel ?countryLabel
WHERE {
        { ?person wdt:P39 [ wdt:P279* wd:Q16707842 ] }  # person was a UK MP
  UNION { ?person wdt:P39 [ wdt:P279* wd:Q18015642 ] }  # or a British MP (to 1801)
  UNION { ?person wdt:P39 [ wdt:P279* wd:Q18018860 ] }  # or an English MP (to 1707)
  ?person wdt:P39 ?position .          # who held some position
  ?position wdt:P279* wd:Q486839 .       # which is a subclass of member of parliament
  ?position wdt:P17 ?country .           # and which applies to a country which is
  FILTER ( ?country != wd:Q145 )           # not UK & NI
  FILTER ( ?country != wd:Q174193 )        # not UK & Ireland
  FILTER ( ?country != wd:Q161885 )        # not GB
  FILTER ( ?country != wd:Q215530 )        # not Kingdom of Ireland
  FILTER ( ?country != wd:Q844250 )        # not Lordship of Ireland
  FILTER ( ?country != wd:Q179876 )        # not Kingdom of England
  FILTER ( ?country != wd:Q230791 )        # not Kingdom of Scotland
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "en" .
  }
}

Try it!

Database connectionsEdit

Number of MPs in the ODNB, by parliament
# For a given UK parliament, find
# - how many MPs sat in it
# - how many of those have died
# - how many of those who have died are in the ODNB
# - how many *overall* are in the ODNB
#   (remember the ODNB only adds people three years after their death)
#

SELECT ?start ?percent_died_in_odnb 
#defaultView:LineChart

WITH {
  # all MPs with their associated parliament
  SELECT DISTINCT ?mp ?parliament WHERE {
    ?mp wdt:P31 wd:Q5.
      # using the new data model - so limits how far back it goes
      ?mp p:P39/ps:P39 ?position . 
      ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament.
    }
} AS %MPsWithParliament 

WITH {
  # count of ODNB entries in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?odnb) WHERE {
    INCLUDE %MPsWithParliament.
    ?mp wdt:P1415 ?odnb
  }
  GROUP BY ?parliament
} AS %odnb 

WITH {
  # count of dead MPs in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?died) WHERE {
    INCLUDE %MPsWithParliament.
    ?mp wdt:P570 ?died . 
  }
  GROUP BY ?parliament
} AS %died 

WITH {
  # count of members entries in each parliament
  SELECT ?parliament (COUNT(DISTINCT ?mp) AS ?mps) WHERE {
    ?mp wdt:P31 wd:Q5.
      ?mp p:P39/ps:P39 ?position.
      ?position wdt:P279 wd:Q16707842; p:P279/pq:P2937|wdt:P2937 ?parliament.
  }
  GROUP BY ?parliament
} AS %mpcount 

WHERE {
  # get ?odnb
  INCLUDE %odnb
  INCLUDE %mpcount
  INCLUDE %died
  BIND(?odnb AS ?odnbcount)
  BIND(?mps AS ?mpcount)
  BIND(ROUND(100*(?odnbcount/?mpcount)) AS ?percent_in_odnb) 
  BIND(?died AS ?diedcount)
  BIND(ROUND(100*(?odnbcount/?diedcount)) AS ?percent_died_in_odnb) 

  # get parliament dates
  ?parliament wdt:P571|wdt:P580 ?start.
  ?parliament wdt:P576|wdt:P582 ?end.
  # add labels
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?start

Try it!

MPs matched to the Rush database, by parliament
This query is too long for the SPARQL template, but can be found at this link


Federated queriesEdit

Working notes at Wikidata:WikiProject British Politicians/parlidata

Querying Parliament Data and recovering labels from Wikidata
link at api.parliament.uk
Querying Wikidata and recovering coordinates from Parliament Data
# compare lat/long of Parliament and Wikidata constituency records
PREFIX parliament:<https://id.parliament.uk/schema/>

SELECT DISTINCT ?constituency ?parlcoord ?item ?itemLabel ?wdcoord ?dist ?line WHERE {
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  SERVICE <https://api.parliament.uk/sparql> 
    { ?constituency parliament:constituencyGroupHasConstituencyArea ?area .
      ?area parliament:latitude ?lat . ?area parliament:longitude ?long . 
      bind(SUBSTR(str(?constituency),26) as ?parlid) . }
  BIND(concat("Point(",str(?long)," ",str(?lat),")"^^geo:wktLiteral) as ?parlcoord) 
  # get constituencies from Parliament with coordinates
  ?item wdt:P6213 ?parlid . ?item wdt:P31 wd:Q27971968 . ?item wdt:P625 ?wdcoord . 
  # now get them from Wikidata with coordinates
  BIND(geof:distance(?parlcoord, ?wdcoord) as ?dist) . filter (?dist >= 10)
  # now find out the distance (in kms) and show those where distance > 10km
  ?item p:P625 ?statementnode. ?statementnode psv:P625 ?valuenode.
  ?valuenode wikibase:geoLatitude ?wikilat . ?valuenode wikibase:geoLongitude ?wikilon.
  BIND(CONCAT('LINESTRING (', STR(?wikilon), ' ', STR(?wikilat), ',', STR(?long), ' ', STR(?lat), ')') AS ?str) .
  BIND(STRDT(?str, geo:wktLiteral) AS ?line) 
}
#defaultView:Map

Try it!