Jump to content

User:Andrew Gray/scratch

From Wikipedia, the free encyclopedia
Image Name Born Gender Party Constituency Country First day in Parliament Last day in Parliament Notes
[img] Rosemary McKenna 8 May 1941 Female Labour Cumbernauld and Kilsyth (1997-2005); Cumbernauld, Kilsyth and Kirkintilloch East (2005-2010) Scotland 1 May 1997 12 Apr 2010 --
[img] Kelvin Hopkins 22 Aug 1941 Male Labour (1997-2017) Luton North England 1 May 1997 6 Nov 2019 --
Independent (2017-2019)
[img] Angela Watkinson 18 Nov 1941 Female Conservative Upminster (2001-2010); Hornchurch and Upminster (2010-2017) England 7 Jun 2001 3 May 2017 --

Main SPARQL generator

[edit]
# all currently living former MPs with seats and parties - should be 1076 as of 25/3/2021
select distinct ?item ?image ?wikilink ?sortkey ?born ?genderLabel
?parties ?seats ?country ?earliest ?latest ?notes ?partycount ?seatcount where
{
  { select distinct ?item ?born ?gender ?wikilink ?image where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     optional { { ?item wdt:P569 ?born . ?item p:P569/psv:P569 [ wikibase:timePrecision ?precision ; wikibase:timeValue ?born ] .
       FILTER( ?precision = "11"^^xsd:integer ) } union # day to day
     { ?item wdt:P569 ?b . ?item p:P569/psv:P569 [ wikibase:timePrecision ?precision ; wikibase:timeValue ?b ] .
       FILTER( ?precision = "10"^^xsd:integer ) . bind(year(?b) as ?born) } union # month to year
     { ?item wdt:P569 ?b . ?item p:P569/psv:P569 [ wikibase:timePrecision ?precision ; wikibase:timeValue ?b ] .
       FILTER( ?precision = "9"^^xsd:integer ) . bind(year(?b) as ?born) } } # year to year
     filter not exists { ?item wdt:P570 ?died } # is still alive
     optional { ?item wdt:P21 ?gender } 
     optional { ?item wdt:P18 ?image } 
     ?enwp schema:about ?item . ?enwp schema:isPartOf <https://en.wikipedia.org/> . ?enwp schema:name ?wikilink .
   } }
  optional { select distinct ?item (concat(?snl, ", ", ?fnl) as ?sortkey) ?forenamecount ?surnamecount where {
     { select distinct ?item (count(distinct ?name) as ?forenamecount) 
                     where { ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . 
                             ?term wdt:P279 wd:Q16707842 . ?item wdt:P735 ?name } group by ?item }
     { select distinct ?item (count(distinct ?name) as ?surnamecount) 
                     where { ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . 
                             ?term wdt:P279 wd:Q16707842 . ?item wdt:P734 ?name } group by ?item }
     ?item wdt:P735 ?fn . ?fn rdfs:label ?fnl . filter(lang(?fnl) = "en") . filter(?forenamecount = 1)
     ?item wdt:P734 ?sn . ?sn rdfs:label ?snl . filter(lang(?snl) = "en") . filter(?surnamecount = 1)
    } }
  optional { select distinct ?item (min(?start) as ?earliest) where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     ?positionStatement pq:P580 ?start .
   } group by ?item } 
  optional { select distinct ?item (max(?end) as ?latest) where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     optional { ?positionStatement pq:P582 ?e } .  bind(COALESCE(?e, NOW()) as ?end)
   } group by ?item } 
  optional { select distinct ?item (GROUP_CONCAT(DISTINCT ?sLabel;separator="; ") AS ?seats)
             (count(distinct ?sLabel) as ?seatcount) (GROUP_CONCAT(DISTINCT ?cLabel;separator="; ") AS ?country) where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     ?positionStatement pq:P768 ?s . ?s rdfs:label ?sLabel . filter(lang(?sLabel) = 'en').
     ?s wdt:P131* ?c . ?c wdt:P31 wd:Q3336843 . ?c rdfs:label ?cLabel . filter(lang(?cLabel) = 'en').
   } group by ?item } 
  optional { select distinct ?item (GROUP_CONCAT(DISTINCT ?pLabel;separator="; ") AS ?parties) (count(distinct ?p) as ?partycount)  where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     ?positionStatement pq:P4100 ?p . ?p rdfs:label ?pLabel . filter(lang(?pLabel) = 'en').
   } group by ?item }  
  optional { select distinct ?item (GROUP_CONCAT(DISTINCT ?note;separator="; ") AS ?notes) where {
     ?item wdt:P31 wd:Q5 . ?item p:P39 ?positionStatement . ?positionStatement ps:P39 ?term . ?term wdt:P279 wd:Q16707842 . 
     { ?item p:P39 ?ps2 . ?ps2 ps:P39 ?t2 . ?t2 wdt:P279 wd:Q1711695 . bind("Member of the Scottish Parliament" as ?note) } union 
     { ?item p:P39 ?ps3 . ?ps3 ps:P39 ?t3 . ?t3 wdt:P279 wd:Q3406079 . bind("Member of the Senedd" as ?note) } union
     { ?item p:P39 ?ps4 . ?ps4 ps:P39 ?t4 . ?t4 wdt:P279 wd:Q3272410 . bind("Member of the Northern Ireland Assembly" as ?note) } union
     { ?item p:P39 ?ps5 . ?ps5 ps:P39 wd:Q18952564 . bind("Member of the House of Lords" as ?note) } union
     { ?item p:P39 ?ps6 . ?ps6 ps:P39 wd:Q27169 . bind("Member of the European Parliament" as ?note) } 
   } group by ?item } 
  filter(?latest < now()) . 
 SERVICE wikibase:label { bd:serviceParam wikibase:language 'en' }
} order by ?earliest ?sortkey

Click here to launch the Wikidata query

Second SPARQL generator - seats and dates

[edit]
# query to find periods in each seat

SELECT distinct ?mp ?mpLabel ?seatLabel ?start (min(?end) as ?end2) 
(concat(?seatLabel, ' (', str(year(?start)), '-', str(year(?end2)), ")") as ?string)
where { 
  VALUES ?mp { wd:Q391520
wd:Q333487
wd:Q261001
wd:Q271935
wd:Q334313
wd:Q332483

 } # set MP here
  # find all seat-start pairs for each continuing period of office
  { SELECT distinct ?mp ?mpLabel ?seatLabel ?start
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. 
      ?ps pq:P768 ?seat . ?ps pq:P580 ?start. 
      filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . 
      ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
      ?ps0 pq:P768 ?seat . ?ps0 pq:P1534 wd:Q741182 . }
      filter not exists { ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term . ?ps2 pq:P768 ?seat . ?ps2 pq:P582 ?start } 
      # no earlier term ending this day
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY (?mp) ?start }
  # and all corresponding seat-party-end pairs
  { SELECT distinct ?mp ?mpLabel ?seatLabel ?end
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842.
      ?ps pq:P768 ?seat . ?ps pq:P582 ?end.
      filter not exists { ?ps pq:P1534 wd:Q741182 . 
      ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
      ?ps2 pq:P768 ?seat . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . }
      filter not exists { ?mp p:P39 ?ps3 . ?ps3 ps:P39 ?term . ?ps3 pq:P768 ?seat . ?ps3 pq:P580 ?end } 
      # no later term starting this day in same seat
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY (?mp) ?end }
  filter(?end > ?start) . # note > not >= 
} group by ?mp ?mpLabel ?seatLabel ?start order by ?mp ?start

Click here to launch the Wikidata query

Third SPARQL generator - parties and dates

[edit]
# query to find periods in each party

SELECT distinct ?mp ?mpLabel ?partyLabel ?start (min(?end) as ?end2) 
(concat(?partyLabel, ' (', str(year(?start)), '-', str(year(?end2)), ")") as ?string)
where { 
  VALUES ?mp { wd:Q391520
wd:Q333487
wd:Q261001
wd:Q271935
wd:Q334313
wd:Q332483

 } # set MP here
  # find all party-start pairs for each continuing period of office
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?start
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842. 
      ?ps pq:P4100 ?party . ?ps pq:P580 ?start. 
      filter not exists { ?ps pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . 
      ?mp p:P39 ?ps0 . ?ps0 ps:P39 ?term0 . ?term0 wdt:P156 ?term .
      ?ps0 pq:P4100 ?party . ?ps0 pq:P1534 wd:Q741182 . }
      filter not exists { ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term . ?ps2 pq:P4100 ?party . ?ps2 pq:P582 ?start } 
      # no earlier term ending this day in same party
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY (?mp) ?start }
  # and all corresponding party-end pairs
  { SELECT distinct ?mp ?mpLabel ?partyLabel ?end
    WHERE {
      ?mp p:P39 ?ps. ?ps ps:P39 ?term . ?term wdt:P279 wd:Q16707842.
      ?ps pq:P4100 ?party . ?ps pq:P582 ?end.
      filter not exists { ?ps pq:P1534 wd:Q741182 . 
      ?mp p:P39 ?ps2 . ?ps2 ps:P39 ?term2 . ?term2 wdt:P155 ?term . 
      ?ps2 pq:P4100 ?party . ?ps2 pq:P2715 ?elec . ?elec wdt:P31 wd:Q15283424 . }
      filter not exists { ?mp p:P39 ?ps3 . ?ps3 ps:P39 ?term . ?ps3 pq:P4100 ?party . ?ps3 pq:P580 ?end } 
      # no later term starting this day in same party
      filter not exists { ?ps pq:P1534 wd:Q50393121 } # omit any double-return seats which were not taken up
      SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  } ORDER BY (?mp) ?end }
  filter(?end > ?start) . # note > not >= 
} group by ?mp ?mpLabel ?partyLabel ?start order by ?mp ?start

Click here to launch the Wikidata query

Retrieved from "https://en.wikipedia.org/w/index.php?title=User:Andrew_Gray/scratch&oldid=1058929034"