Wikidata:WikiProject British Politicians/Sample Queries

From Wikidata
Jump to navigation Jump to search

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 data[edit]

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|pq:P102 ?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|pq:P102 ?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|pq:P102 ?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!

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!


Careers[edit]

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!

Constituencies[edit]

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 seven or more constituencies


# MPs who have sat for seven (or more?) seats, and the relevant constituencies

SELECT distinct ?mp ?mpLabel ?born ?died ?constituencyLabel
WHERE
{
  {
    SELECT ?mp (COUNT (distinct ?constituency) AS ?count) WHERE {
      ?position wdt:P279 wd:Q16707842. # every UK MP (currently post-1832)
      ?mp p:P39 ?positionStatement . ?positionStatement ps:P39 ?position ; pq:P768 ?constituency .
    }
    GROUP BY ?mp
  }
  ?position wdt:P279 wd:Q16707842.
  ?mp p:P39 ?positionStatement . ?positionStatement ps:P39 ?position ; pq:P768 ?constituency .  
  filter (?count > 6) # only those with seven distinct seats - change this to alter the limits
  OPTIONAL { ?mp wdt:P569 ?dob . BIND(YEAR(?dob) as ?born) }
  OPTIONAL { ?mp wdt:P570 ?dod . BIND(YEAR(?dod) as ?died) }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?mp

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!

Elections[edit]

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!

Biographical information[edit]

Names[edit]

Most popular first names for MPs
SELECT ?nameLabel (count(distinct ?person1) as ?count)
WHERE {
 { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q16707842 ] } # UK MP
 UNION { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q18015642 ] } # British MP
 UNION { ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q18018860 ] } # English MP
 ?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 {
    { ?mpStatement ps:P39/wdt:P279? wd:Q16707842. } UNION
    { ?mpStatement ps:P39/wdt:P279? wd:Q18015642. } UNION
    { ?mpStatement ps:P39/wdt:P279? wd:Q18018860. }
    ?mpStatement pq:P580 ?date.
    ?mp p:P39 ?mpStatement;
        wdt:P735 ?givenName.
  }
  GROUP BY ?givenName
} AS %givenNames WHERE {
  INCLUDE %givenNames.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
ORDER BY ?firstDate ?givenNameLabel

Try it!

Places[edit]

MSPs born outside Scotland
SELECT DISTINCT ?person ?personLabel ?born ?bornLabel ?coord {
  ?position wdt:P279* wd:Q1711695 .          # 1. find all the MSP positions
  ?person wdt: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 death[edit]

Most and least common birthdays among MPs
SELECT DISTINCT ?birthday (count(distinct ?item) as ?total)
{
 ?item wdt:P39 [ wdt:P279* wd:Q16707842 ] . # all (UK) MPs
 ?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:P39 [ wdt:P279* wd:Q16707842 ] .  # all 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 .
    ?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 .
    ?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!

Ancestry and families[edit]

Married couples who were both MPs
SELECT DISTINCT ?person1 ?person1Label ?person2 ?person2Label
WHERE {
           ?person1 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q16707842 ] .  # person1 is an MP
           ?person2 wdt:P31 wd:Q5 ; wdt:P39 [ wdt:P279* wd:Q16707842 ] .  # so is person2
           ?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 ?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)
  } 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
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!

Other achievements[edit]

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!

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 connections[edit]

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

Complete trivia[edit]

Heads of state / government who came to office during the lifetime of the person who was the incumbent when they were born
# this query
# - checks every head of state/government for a country
# - finds out who held that role on the day they were born
# - finds out if that person was still alive on the day the new one started
#
# this is a deeply trivial query
# but as it turns out to be true for Macron, it's an interesting footnote
# and we should see if it's true for anyone else
#
# This one has nothing to do with British MPs, but is presented for inspiration.

SELECT ?roleLabel ?countryLabel ?newperson ?newpersonLabel ?newstart ?oldperson ?oldpersonLabel ?died # ?newbirth ?start ?end 
WHERE {
  { ?role wdt:P279* wd:Q48352 . } UNION { ?role wdt:P279* wd:Q2285706 . } # the position is head of state or head of government
  ?role wdt:P1001 ?country . ?country wdt:P31 wd:Q6256 . FILTER NOT EXISTS { ?role wdt:P279* wd:Q116 } .  # a country, but is not a monarchy
  
  ?newperson wdt:P31 wd:Q5 ; wdt:P39 ?role . ?newperson wdt:P569 ?newbirth . ?newperson p:P39 [ ps:P39 wd:Q191954; pq:P580 ?newstart ]. 
  # now, find all incumbents (as ?newperson) with their accession dates and birthdates
  
  ?oldperson wdt:P31 wd:Q5 ; wdt:P39 ?role . OPTIONAL { ?oldperson wdt:P570 ?died . } 
  ?oldperson p:P39 [ ps:P39 ?role; pq:P580 ?start ] . ?oldperson p:P39 [ ps:P39 ?role; pq:P582 ?end ] .
  # then, find all incumbents again (as ?oldperson) with their start/finish dates and deathdates
  
  FILTER ( ?start < ?newbirth ) .  # when the new person was born, the old person had begun their term
  FILTER ( ?end > ?newbirth ) .    # when the new person was born, the old person had not finished their term
                                   # therefore combined, this was presumably the person on the day they were born
                                   # can uncomment ?newbirth ?start ?end to check
  { FILTER ( ?newstart < ?died ) } UNION { FILTER NOT EXISTS{ ?oldperson wdt:P570 ?died } } .
                                   # this complete mess of a query finds the cases
                                   # a) where the new person's start date is before the old person's death
                                   # b) where the old person has not yet died
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }  # get their names
}

Try it!