User:Andrew Gray/scratch
Appearance
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
# 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
# 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
# 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