User:Hannes Röst/Queries

• User:Hannes Röst/Queries/Swiss Municipalities
• User:Hannes Röst/Queries/HLS

General

Query by property name

SELECT DISTINCT ?item ?itemLabel ?coord
WHERE 
{
  ?item wdt:P381 ?refnr . # PCP Reference number (KGS Nummer)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  FILTER(?refnr IN ("2806")  )
}

Try it!

Select an item

SELECT DISTINCT ?item ?itemLabel ?height
WHERE 
{
   VALUES ?item { wd:Q804983 }
  ?item p:P2793/psn:P2793/wikibase:quantityAmount ?height .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Select a statement

# Select Member of Swiss National Council statement
SELECT DISTINCT *
WHERE 
{
  VALUES ?item { wd:Q117117 } # example item

  # Select items that have P39 -> Q18510612, then select corresponding *statements* 
  ?item wdt:P39 wd:Q18510612 .  
  ?item p:P39 ?statement .
  FILTER EXISTS {?statement ?v  wd:Q18510612 .  }

  OPTIONAL { ?statement pq:P768 ?canton . }
  OPTIONAL { ?statement pq:P580 ?start_time . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Select all subclasses

SELECT DISTINCT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q662914. # Is instance of or subclass of 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Select alt labels

SELECT DISTINCT ?item ?label_en ?label_de ?label_fr ?label_it ?altLabel
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q662914. # Is instance of or subclass of 
  OPTIONAL { ?item skos:altLabel ?altLabel . FILTER (lang(?altLabel) IN ("en", "de", "fr", "it")) }
  OPTIONAL {?item rdfs:label ?label_en filter (lang(?label_en) = "en"). }
  OPTIONAL { ?item rdfs:label ?label_de filter (lang(?label_de) = "de"). }
  OPTIONAL { ?item rdfs:label ?label_fr filter (lang(?label_fr) = "fr"). }
  OPTIONAL { ?item rdfs:label ?label_it filter (lang(?label_it) = "it"). }
}

Try it!

Qualifiers used with a statement

• which qualifiers are used with this item: https://w.wiki/UhV

Improve descriptions

SELECT ?item ?itemDescription (GROUP_CONCAT(year(?dob)) as ?dob_y) (GROUP_CONCAT(year(?dod)) as ?dod_y) (COUNT(?item) as ?nr)
WHERE
{
    ?item wdt:P39 wd:Q18510612 .  
    ?item wdt:P569 ?dob .
    ?item wdt:P570 ?dod .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?item ?itemDescription

Try it!

Duplicate entries

# Find duplicate HLS entries
SELECT ?hls (COUNT(?hls) AS ?nr_items)  (GROUP_CONCAT(?item; separator=";") AS ?all_items) 
WHERE
{
    ?item wdt:P902 ?hls. # DOB
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
GROUP BY ?hls
HAVING(COUNT(?hls) > 1)

Try it!

Duplicate references

# Find people with duplicate award entries
SELECT ?item ?itemLabel ?value ?ref  ?refLabel  ?prop (COUNT(?ref) AS ?nr_items)
WHERE
{
    ?item wdt:P39 wd:Q18510612 .  
    # ?item p:P214 ?statement. # VIAF
    ?item p:P569 ?statement. # DOB
    # ?item ?prop ?statement. 
  
    ?statement ps:P569 ?value.
    ?statement prov:wasDerivedFrom ?refnode.
    # ?refnode   pr:P902 ?ref. ## HLS identifier
    ?refnode   pr:P248 ?ref.   ## ref name
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

GROUP BY ?item ?itemLabel ?value ?statement ?ref ?refLabel ?prop
HAVING(COUNT(?ref) > 1)

Try it!

Duplicate entries in a list

# Find people with duplicate award entries
SELECT ?item ?itemLabel ?value ?valueLabel  (COUNT(?value) AS ?nr_items)
WHERE
{
    ?item wdt:P39 wd:Q3307529 .  # NHL General Managers
    ?item p:P166 ?statement. # list all awards  
    ?statement ps:P166 ?value. # get values
    SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

GROUP BY ?item ?itemLabel ?value ?valueLabel

Try it!

Journals

Journals 1

Chemistry journals: https://w.wiki/StN

# Select publishers of chemistry journals by number of journals they have
# -- and filter out those where the publishers already has the tag "publisher"
SELECT ?publisher ?publisherLabel ?journals
WHERE
{
  ## Perform selections 
  #  ?publisher wdt:P31 wd:Q2085381 . 
  
  ### START INNER QUERY
  {
    SELECT DISTINCT ?publisher ?publisherLabel (COUNT(?publisher) AS ?journals) 
    WHERE
    {
      ?journal wdt:P31 wd:Q5633421 .         # find scientific journal 
      ?journal wdt:P921 wd:Q2329 .          # in chemistry
      # select inception, publisher and NLM identifier (optional)
      OPTIONAL { ?journal wdt:P571 ?inception }
      OPTIONAL { ?journal wdt:P123 ?publisher . ?publisher wdt:P31 ?publisher_instance }
      OPTIONAL { ?journal wdt:P1055 ?nlm_id }
     SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
    }
    GROUP BY ?publisher ?publisherLabel 
    ORDER BY ?journals  
  }               
  ### END INNER QUERY

  ## Now filter results
  FILTER NOT EXISTS{ ?publisher p:P31/ps:P31/wdt:P279* wd:Q2085381 } # only keep those without publishers
}

Journals 2

Find journals without main topic and update them:

SELECT   ?journal ?journalLabel ?title
#SELECT   (COUNT(?journal) AS ?fow_cnt) 
WHERE
{
  ?journal wdt:P31 wd:Q5633421 .         #find scientific journal
  ?journal wdt:P1476 ?title # get title
  FILTER NOT EXISTS{ ?journal wdt:P921 ?publ } # filter out those that already have "main subject"
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" . }
  FILTER(REGEX(STR(?title), ".*iochemistry.*")) # match title to Biochemistry
} 

Then use https://quickstatements.toolforge.org as follows

Q2183997	P921	Q7094	S887	Q69652283

(which means)

Acta Biochimica et Biophysica Sinica [Q2183997]
ADD
Sources to
main subject [P921]
biochemistry [Q7094]
based on heuristic [P887]
deduced from title [Q69652283]

Tunnels

https://w.wiki/TX9

## all tunnels in Switzerland 
SELECT DISTINCT ?item ?itemLabel ?coord ?osm_id ?geo 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q44377; # Is instance of or subclass of 
  wdt:P17 wd:Q39 . #in Switzerland
  ?item wdt:P625 ?coord .
  OPTIONAL { ?item wdt:P3896 ?geo . }
  OPTIONAL {?item wdt:P402 ?osm_id . }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Bridges

https://query.wikidata.org/

# All bridges (architectural features) crossing the rhine
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q811979; # Is instance of or subclass of : architectural feature
        wdt:P177 wd:Q584. # crosses Rhine
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

# All bridges (architectural features) crossing the Thur
SELECT ?item ?itemLabel 
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q811979; # Is instance of or subclass of : architectural feature
        wdt:P177 wd:Q14339. # crosses Thur
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Heritage sites

Should be empty: https://w.wiki/ToH

Swiss municipalities

https://w.wiki/TuV

Count

SELECT (COUNT(DISTINCT(?item)) as ?cnt)
WHERE 
{
  ?item wdt:P31/wdt:P279* wd:Q12280. # Is instance of or subclass of : bridge
  ?item wdt:P17 wd:Q39 . # Switzerland
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

All bridges over Thur

https://w.wiki/TTP

Lexemes

Combine items and lexemes https://w.wiki/VGj

Politicians

# Alle Thurgauer Nationalrate
SELECT DISTINCT ?item ?itemLabel ?cantonLabel
WHERE 
{
  ?item wdt:P39 wd:Q18510612 .  
  ?item p:P39 ?statement .
  ?statement pq:P768 ?canton . # get Canton for which they served and filter
  FILTER EXISTS {?statement pq:P768 wd:Q12713.  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

# Alle Nationalrate nach Kanton und Amtszeit
# SELECT DISTINCT ?item ?itemLabel ?cantonLabel ?start_time
SELECT DISTINCT ?item (COUNT(?start_time) AS ?nr_times) 
  (GROUP_CONCAT(year(?start_time); separator=";") AS ?start_times) 
  (GROUP_CONCAT(?cantonLabel; separator=";") AS ?cantons)
  (GROUP_CONCAT(?periodLabel; separator=";") AS ?periods)
WHERE 
{
  ?item wdt:P39 wd:Q18510612 .  
  ?item p:P39 ?statement .
  FILTER EXISTS {?statement ?v  wd:Q18510612 .  }

  OPTIONAL { ?statement pq:P768 ?canton . }
  OPTIONAL { ?statement pq:P580 ?start_time . }
  OPTIONAL { ?statement pq:P2937 ?period . }
 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". 
                          ?canton rdfs:label ?cantonLabel  .
                          ?period rdfs:label ?periodLabel  .
                         }
}
GROUP BY ?item

Try it!

OpenStreetMap

Find all items with a given OSM tag:

SELECT ?item ?itemLabel ?osm_tag
WHERE 
{
  ?item wdt:P1282 ?osm_tag .
  FILTER regex(?osm_tag, ".*:sport.*", "i") 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!