Wikidata

User:Idieh3/queries

< User:Idieh3

People

edit

Search person with NTA number in Wikidata

edit 
SELECT ?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

edit 
SELECT ?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

edit 
SELECT ?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

edit

Timeline 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

edit

List of LGBT Publishers

edit 
SELECT ?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

edit 
SELECT ?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

edit 
SELECT ?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

edit 
SELECT ?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

edit 
SELECT ?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

edit

Episodes of the TV series "Doctor Who"

edit 
SELECT ?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

edit 
SELECT ?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

edit 
SELECT ?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

edit

Public 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

edit

Mountains 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

Retrieved from "https://www.wikidata.org/w/index.php?title=User:Idieh3/queries&oldid=1744442672"