User:Idieh3/queries
People
editSearch person with NTA number in Wikidata
editSELECT ?item ?itemLabel
WHERE
{
?item wdt:P1006 "068899513" . # Search NTA author in Wikidata
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
WQS: Try it
Search persons with NTA number in Wikidata and show the entire formatted URL
editSELECT ?person ?ntaValue
WHERE
{
?person wdt:P1006 ?ntaId .
wd:P1006 wdt:P1630 ?formatterUrl .
BIND(IRI(REPLACE(?formatterUrl, "\\$", ?ntaId)) AS ?ntaValue)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
} LIMIT 10
WQS: Try it
Awards received for author
editSELECT ?p ?pLabel ?award ?awardLabel (YEAR(?when) as ?year)
WHERE
{
?p wdt:P50 wd:Q110731965 ; # author = Georgia Beers
p:P166 ?awardnode . # award received node
?awardnode ps:P166 ?award ; # award received
pq:P585 ?when . # point in time qualifier of award received
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
order by ?pLabel
WQS: Try it
Timeline of 1st women practising a given sports discipline
editTimeline of 1st women practising a given sports discipline (for each sport profession, the earliest-born woman with that occupation) - also a nice showcase what subqueries can do.
#defaultView:Timeline
select ?sportswoman ?sportswomanLabel ?professionLabel ?first with {
select distinct ?profession where {
?profession wdt:P279* wd:Q2066131 .
} } as %sport_professions with {
select ?sportswoman ?profession ?date_of_birth where {
include %sport_professions
?sportswoman wdt:P21 wd:Q6581072 .
?sportswoman wdt:P569 ?date_of_birth .
?sportswoman wdt:P106 ?profession .
} } as %sportswomen with {
select ?profession (MIN(?date_of_birth) as ?first) where {
include %sportswomen
} group by ?profession } as %world_firsts with {
select ?sportswoman ?profession ?first where {
include %sportswomen
include %world_firsts
FILTER( ?first = ?date_of_birth && BOUND(?profession))
} } as %final_results where {
include %final_results
SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
WQS: Try it
Query by: Vojtěch Dostál Source: https://twitter.com/medi_cago/status/1487549749830078471
title:musical works/compositions with titles that are female given names
edit# title:musical works/compositions with titles that are female given names
SELECT ?song ?female_name ?title (LANG(?title) AS ?title_language) ?note
WITH
{
SELECT DISTINCT ?song ?title
{
?song wdt:P31 / wdt:P279 * wd:Q105543609 .
?song wdt:P1476 ?title .
}
} AS %songs
{
{
INCLUDE %songs
?female_name wdt:P1705 ?title .
?female_name wdt:P31 wd:Q11879590 .
}
UNION
{
INCLUDE %songs
FILTER (LANG(?title) != "mul")
BIND (STRLANG(STR(?title), "mul") AS ?title_mul)
?female_name wdt:P1705 ?title_mul .
?female_name wdt:P31 wd:Q11879590 . hint:Prior hint:runLast true.
BIND ("match one to multiple" AS ?note)
}
}
WQS: Try it
Query by: Moebeus Source: https://twitter.com/exmusica/status/1573308311784521728
LGBT
editList of LGBT Publishers
editSELECT ?pub ?pubLabel
WHERE
{
?pub wdt:P31 wd:Q61755026 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
List of LGBT Bookshops
editSELECT ?shop ?shopLabel ?countryLabel
WHERE
{
?shop wdt:P31 wd:Q61696039 ;
wdt:P17 ?country .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
List of LGBT Film Festivals
editSELECT ?film ?filmLabel ?countryLabel
WHERE
{
?film wdt:P31 wd:Q62018250 ;
wdt:P17 ?country .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
ORDER BY ?countryLabel
WQS: Try it
List of LGBT Rights Activists - Female
editSELECT ?human ?humanLabel ?occupationLabel
WHERE
{
?human wdt:P31 wd:Q5 ; # instance of Human
wdt:P106 wd:Q19509201 ; # occupation LGBTI rights activist
wdt:P21 wd:Q6581072 ; # gender female
wdt:P106 ?occupation . # all occupations, easy to find someone similar
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en
}
WQS: Try it
List of LGBT Literary Awards
editSELECT ?item ?itemLabel
WHERE
{
?item wdt:P279 wd:Q107627866. # subclass of = LGBT literary award
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
WQS: Try it
Fictional
editEpisodes of the TV series "Doctor Who"
editSELECT ?episode_nb ?item ?itemLabel ?original_title ?season_nb ?episode_nb_in_season ?season ?sl ?st
WHERE
{
?item wdt:P31 wd:Q21191270 ; wdt:P4908 ?season ; wdt:P179 wd:Q34316 .
OPTIONAL { ?season p:P179 [ pq:P1545 ?season_nb ] }
OPTIONAL { ?item p:P4908 [ pq:P1545 ?episode_nb_in_season ] }
OPTIONAL { ?item p:P179 [ pq:P1545 ?episode_nb ] }
OPTIONAL { ?item wdt:P1476 ?original_title . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
OPTIONAL { ?item wikibase:sitelinks ?sl ; wikibase:statements ?st }
}
ORDER BY xsd:integer(?season_nb) xsd:integer(?episode_nb)
WQS: Try it
Grey's Anatomy: Q438406
Based on: Query: Episodes of the TV series "The Mentalist"
DC Comics items that have the official website, but not the DC comics ID
editSELECT ?item ?itemLabel ?node ?charid
WHERE
{
?item p:P856 [ ps:P856 ?node ] . # get the node (literal inside) of the official website
OPTIONAL { ?item wdt:P9851 ?charid } # get the DC character ID
FILTER(CONTAINS(STR(?node), "dccomics.com/characters")) . # Only ask for official websites of dccomics characters
FILTER(!BOUND(?charid)) . # And only when the character ID is not yet set
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
LIMIT 10
WQS: Try it
Non-binary fictional characters
edit#non-binay fictional characters
SELECT ?item ?itemLabel
WHERE
{
?item wdt:P31 wd:Q15632617 ; # Must be of a fictional human
wdt:P21 wd:Q48270 . # gender non-binary
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
LIMIT 100
WQS: [failed Try it]
Narrative locations for author
editSELECT ?p ?pLabel ?year ?loc ?locLabel ?coord
WHERE
{
?p wdt:P50 wd:Q110731965 ; # author = Georgia Beers
wdt:P577 ?d . # Publication date
BIND (YEAR(?d) as ?year) . # publication date as year
OPTIONAL {
?p wdt:P840 ?loc . # narrative location
OPTIONAL { ?loc wdt:P625 ?coord . } # coordinate location
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } # Helps get the label in your language, if not, then en language
}
order by ?year
WDS: https://w.wiki/563H
Geo
editPublic art in Amsterdam
edit# Public artworks
#defaultView:Map
# SAMPLE (needs GROUP BY): only show one of the values of these properties. Otherwise the same piece of art is shown multiple times in the list.
SELECT DISTINCT ?item ?itemLabel (SAMPLE(?instance_ofLabel) as ?instance_ofLabel) (SAMPLE(?creatorLabel) as ?creatorLabel) (SAMPLE(?image) as ?image) ?coordinates WHERE {
?item (wdt:P31/(wdt:P279*)) wd:Q838948 ; # instance of/subclass: work of art
(wdt:P136/(wdt:P279*)) wd:Q557141 . # genre/subclass: public art
?item wdt:P17 wd:Q55 . # country Netherlands
{ ?item wdt:P131 wd:Q9899 . } # municipality amsterdam
UNION # or
{ ?item wdt:P131 wd:Q727 . } # capital Amsterdam
SERVICE wikibase:label { bd:serviceParam wikibase:language "nl,en". }
OPTIONAL {
?item wdt:P31 ?instance_of.
?item wdt:P18 ?image.
?item wdt:P170 ?creator.
?item wdt:P625 ?coordinates .
}
}
GROUP BY ?item ?itemLabel ?coordinates
WQS: Try it
Based on: Query: all public artworks in Armenia on Wikidata
Mountains in Austria mapped with colours that correspond to the mountain height
editMountains in Austria mapped with colours that correspond to the mountains height: from red for the lowest through green to black for the highest.
# The heights of mountains in Switzerland
#defaultView:Map{"hide":["?x_coords","?rgb"]}
SELECT ?x ?xLabel ?description ?x_coords ?rgb WITH {
# extract the data
SELECT ?data ?x ?x_coords WHERE {
?x wdt:P31 wd:Q8502 .
?x p:P2044/psn:P2044 [
wikibase:quantityAmount ?data
] .
?x wdt:P17 wd:Q40 .
?x wdt:P625 ?x_coords .
}
} AS %totals
# determine the max and min values (used to calculate the spread
WITH {
SELECT (MAX(?data) AS ?max_data) (MIN(?data) AS ?min_data) WHERE {
INCLUDE %totals
}} AS %min_max
WHERE {
INCLUDE %totals
INCLUDE %min_max
?x rdfs:label ?xLabel. FILTER (LANG(?xLabel) = "en") .
BIND (CONCAT("The height of ", ?xLabel, " is about ", STR(ROUND(?data)), " metres" ) AS ?description)
BIND ( ?max_data - ?min_data AS ?spread )
BIND ( xsd:integer( 100 * (?data - ?min_data) / ?spread) AS ?percent ) # 0 -> 100
# some example hues 0:red, 20:orange, 50:yellow, 90:green, 180:cyan, 220:blue, 280:indigo, 330:violet, 340:red
# varying hue
# BIND ( 255 * ?percent / 100 + 0 AS ?hue )
# BIND (1 AS ?value) #
# BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's value
BIND ( (1 - ?percent / 100 + 0) AS ?value ) # to black
BIND ( 255 * ?percent / 100 + 0 AS ?hue )
BIND (1 AS ?saturation) . # 0 -> 1
# varying the hue's saturation
# BIND ( (1 - ?percent / 100 + 0) AS ?saturation ) # to -> white
# BIND (0 AS ?hue) # red
# BIND (1 AS ?value) . # 0 -> 1
# the following algorithm based on https://stackoverflow.com/questions/3018313/algorithm-to-convert-rgb-to-hsv-and-hsv-to-rgb-in-range-0-255-for-both
BIND ( ?hue / 60 AS ?hh )
BIND ( FLOOR (?hh) AS ?i ) .
BIND ( ?hh - ?i AS ?ff) .
BIND ( ?value * ( 1 - ?saturation ) AS ?p) .
BIND ( ?value * ( 1 - ( ?saturation * ?ff)) AS ?q ) .
BIND ( ?value * ( 1 - ( ?saturation * (1 - ?ff ))) AS ?t ) .
BIND ( COALESCE(
IF(?i = 0, ?value, 1/0),
IF(?i = 1, ?q, 1/0),
IF(?i = 2, ?p, 1/0),
IF(?i = 3, ?p, 1/0),
IF(?i = 4, ?t, 1/0),
IF(?i = 5, ?value, 1/0),
?value
) AS ?r )
BIND ( COALESCE(
IF(?i = 0, ?t, 1/0),
IF(?i = 1, ?value, 1/0),
IF(?i = 2, ?value, 1/0),
IF(?i = 3, ?q, 1/0),
IF(?i = 4, ?p, 1/0),
IF(?i = 5, ?p, 1/0),
?p
) AS ?g )
BIND ( COALESCE(
IF(?i = 0, ?p, 1/0),
IF(?i = 1, ?p, 1/0),
IF(?i = 2, ?t, 1/0),
IF(?i = 3, ?value, 1/0),
IF(?i = 4, ?value, 1/0),
IF(?i = 5, ?q, 1/0),
?q
) AS ?b )
BIND (FLOOR(255 * ?r) AS ?red)
BIND (FLOOR(255 * ?g) AS ?green)
BIND (FLOOR(255 * ?b) AS ?blue)
# red
BIND ( FLOOR (?red / 16) AS ?red_1 )
BIND ( COALESCE(
IF(?red_1 < 10, STR(?red_1), 1/0),
IF(?red_1 = 10, "a", 1/0),
IF(?red_1 = 11, "b", 1/0),
IF(?red_1 = 12, "c", 1/0),
IF(?red_1 = 13, "d", 1/0),
IF(?red_1 = 14, "e", 1/0),
IF(?red_1 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex1 )
BIND (FLOOR(?red - (16 * xsd:integer( ?red / 16 ))) AS ?red_2)
BIND ( COALESCE(
IF(?red_2 < 10, STR(?red_2), 1/0),
IF(?red_2 = 10, "a", 1/0),
IF(?red_2 = 11, "b", 1/0),
IF(?red_2 = 12, "c", 1/0),
IF(?red_2 = 13, "d", 1/0),
IF(?red_2 = 14, "e", 1/0),
IF(?red_2 = 15, "f", 1/0),
"ERROR"
) AS ?red_hex2 )
BIND (CONCAT(STR(?red_hex1), STR(?red_hex2)) AS ?red_hex )
# green
BIND ( FLOOR (?green / 16) AS ?green_1 )
BIND ( COALESCE(
IF(?green_1 < 10, STR(?green_1), 1/0),
IF(?green_1 = 10, "a", 1/0),
IF(?green_1 = 11, "b", 1/0),
IF(?green_1 = 12, "c", 1/0),
IF(?green_1 = 13, "d", 1/0),
IF(?green_1 = 14, "e", 1/0),
IF(?green_1 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex1 )
BIND (FLOOR(?green - (16 * xsd:integer( ?green / 16 ))) AS ?green_2)
BIND ( COALESCE(
IF(?green_2 < 10, STR(?green_2), 1/0),
IF(?green_2 = 10, "a", 1/0),
IF(?green_2 = 11, "b", 1/0),
IF(?green_2 = 12, "c", 1/0),
IF(?green_2 = 13, "d", 1/0),
IF(?green_2 = 14, "e", 1/0),
IF(?green_2 = 15, "f", 1/0),
"ERROR"
) AS ?green_hex2 )
BIND (CONCAT(STR(?green_hex1), STR(?green_hex2)) AS ?green_hex )
# blue
BIND ( FLOOR (?blue / 16) AS ?blue_1 )
BIND ( COALESCE(
IF(?blue_1 < 10, STR(?blue_1), 1/0),
IF(?blue_1 = 10, "a", 1/0),
IF(?blue_1 = 11, "b", 1/0),
IF(?blue_1 = 12, "c", 1/0),
IF(?blue_1 = 13, "d", 1/0),
IF(?blue_1 = 14, "e", 1/0),
IF(?blue_1 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex1 )
BIND (FLOOR(?blue - (16 * xsd:integer( ?blue / 16 ))) AS ?blue_2)
BIND ( COALESCE(
IF(?blue_2 < 10, STR(?blue_2), 1/0),
IF(?blue_2 = 10, "a", 1/0),
IF(?blue_2 = 11, "b", 1/0),
IF(?blue_2 = 12, "c", 1/0),
IF(?blue_2 = 13, "d", 1/0),
IF(?blue_2 = 14, "e", 1/0),
IF(?blue_2 = 15, "f", 1/0),
"ERROR"
) AS ?blue_hex2 )
BIND (CONCAT(STR(?blue_hex1), STR(?blue_hex2)) AS ?blue_hex )
BIND (CONCAT(STR(?red_hex), STR(?green_hex), STR(?blue_hex)) AS ?rgb)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Query by: Chris Groves Source: https://twitter.com/piecesofuk/status/1303969222222065664