User:Stefan Kühn/Abfragen

From Wikidata
Jump to navigation Jump to search

Allgemeien Abfragen

[edit]

Alle Subinstanzen von Verkehrsinfrastrukut

[edit]
SELECT ?s ?desc_en ?desc_de
WHERE
{
  ?s wdt:P279 wd:Q376799 . # Transport Infrastructure
  OPTIONAL {
     ?s rdfs:label ?desc_en filter (lang(?desc_en) = "en").
   }
  OPTIONAL {
     ?s rdfs:label ?desc_de filter (lang(?desc_de) = "de").
   }
}
Try it!

Alles von einem Item

[edit]
PREFIX entity: <http://www.wikidata.org/entity/>

#  In addition to the original query this one comes with some advantages:
#  - You will get only literals as results, (even if the values are stored as IRI in wikibase)
#  - That means you will also get properties as birth date, alphanumeric identifier and so on.
#  - The list is ordered numerically by property number. (So P19 comes before P100) 
#  - All label, altLabel and description in a given Language are included.
#  You may open a separate column ?valUrl if you need also the IRI
#
#  Please advise, if there is an option to put the Q-Number  and/or the Language 
#  code into a runtime variable. 

SELECT ?propNumber ?propLabel ?val
WHERE
{
	hint:Query hint:optimizer 'None' .
    # NAME
	{	BIND(entity:Q1697056 AS ?valUrl) .
		BIND("N/A" AS ?propUrl ) .
		BIND("Name"@de AS ?propLabel ) .
       entity:Q1697056 rdfs:label ?val .
      
        FILTER (LANG(?val) = "de") 
	}
    UNION
    {   # alle Labels
		BIND(entity:Q1697056 AS ?valUrl) .
      
        BIND("AltLabel"@de AS ?propLabel ) .
        optional{entity:Q42 skos:altLabel ?val}.
        FILTER (LANG(?val) = "de") 
    }
    UNION
    {   # Description
		BIND(entity:Q1697056 AS ?valUrl) .
      
        BIND("Beschreibung"@de AS ?propLabel ) .
        optional{entity:Q1697056 schema:description ?val}.
        FILTER (LANG(?val) = "de") 
    }
   	UNION
	{	# komplexere Datentypen (Zeit, Adresse)
	    entity:Q1697056 ?propUrl ?valUrl .
		?property ?ref ?propUrl .
		?property rdf:type wikibase:Property .
		?property rdfs:label ?propLabel.
     	FILTER (lang(?propLabel) = 'de' )
        filter  isliteral(?valUrl) 
        BIND(?valUrl AS ?val)
	}
	UNION
	{	# einfache Datentypen (Text)
	     entity:Q1697056 ?propUrl ?valUrl .
		?property ?ref ?propUrl .
		?property rdf:type wikibase:Property .
		?property rdfs:label ?propLabel.
     	FILTER (lang(?propLabel) = 'de' ) 
        filter  isIRI(?valUrl) 
        ?valUrl rdfs:label ?valLabel 
		FILTER (LANG(?valLabel) = "de") 
         BIND(CONCAT(?valLabel) AS ?val)
	}
        BIND( SUBSTR(str(?propUrl),38, 250) AS ?propNumber)
}
ORDER BY xsd:integer(?propNumber)
Try it!

Hauptstädte

[edit]
#------------------------------------------------
# Hauptstädte der Erde
#-----------------------------------------------
#defaultView:Map
SELECT DISTINCT ?country ?countryLabel ?capital ?capitalLabel ?coordinates
WHERE
{
  ?country wdt:P31 wd:Q3624078 .
  #not a former country
  FILTER NOT EXISTS {?country wdt:P31 wd:Q3024240}
  #and no an ancient civilisation (needed to exclude ancient Egypt)
  FILTER NOT EXISTS {?country wdt:P31 wd:Q28171280}
  OPTIONAL { ?country wdt:P36 ?capital  .
  ?capital wdt:P625 ?coordinates.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en" }
}
ORDER BY ?countryLabel
Try it!


Straßenbahn in Ulm

[edit]
#defaultView:Map{"hide":["?coordinates", "?line", "?rgb"]}
SELECT DISTINCT ?coordinates ?line ?item ?itemLabel ?connectLabel ?image ?code ?rgb

WITH {
  SELECT ?item (SAMPLE(?coordinates) AS ?coordinates) (SAMPLE(?image) AS ?image) (sample(?lat1) as ?lat1) (sample(?lon1) as ?lon1) (sample(?code) as ?code) WHERE {
    ?item wdt:P31 wd:Q2175765.
    ?item wdt:P131 wd:Q3012.
    ?item wdt:P625 ?coordinates  .
    ?item p:P625 / psv:P625 / wikibase:geoLatitude ?lat1 .
    ?item p:P625 / psv:P625 / wikibase:geoLongitude ?lon1 .
    OPTIONAL { ?item wdt:P18 ?image }.
    OPTIONAL { ?item wdt:P5696 ?code }.
  } GROUP BY ?item
} AS %stations

WITH {
  SELECT ?nextstation (sample(?lat2) as ?lat2) (sample(?lon2) as ?lon2) WHERE {
    ?nextstation wdt:P31 wd:Q2175765 .
    ?nextstation wdt:P131 wd:Q3012 .
    ?nextstation p:P625 / psv:P625 / wikibase:geoLatitude ?lat2 .
    ?nextstation p:P625 / psv:P625 / wikibase:geoLongitude ?lon2 .
  } GROUP BY ?nextstation
} AS %nextstations

WITH {
  SELECT ?line ?connect ("9B0058" as ?rgb) WHERE {
    INCLUDE %stations .
    INCLUDE %nextstations .
    ?item p:P197 ?nextstationstatement .
    ?nextstationstatement ps:P197 ?nextstation .
    ?nextstationstatement pq:P81 ?connect . 
    FILTER(STR(?item) < STR(?nextstation)) .    
    BIND(CONCAT('LINESTRING (', STR(?lon1), ' ', STR(?lat1), ',', STR(?lon2), ' ', STR(?lat2), ')') AS ?str) .
    BIND(STRDT(?str, geo:wktLiteral) AS ?line) 
  }
} AS %lines 
 
WHERE {
  { INCLUDE %stations }  UNION { INCLUDE %lines  } .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Try it!

Kreisebene

[edit]

Alle Landkreise und kreisfreie Städte in Deutschland

[edit]
#------------------------------------------------
# alle Landkreise und kreisfreie Städte 
# 294 Landkreise             --> de:Liste_der_Landkreise_in_Deutschland
# 107 Kreisfreie Städte      --> de:Liste der kreisfreien Städte in Deutschland
# Summe 401 Stand 2019-03-27 
#-----------------------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription ?kreisschluessel 
       (SAMPLE(?coordinate) AS ?coord)              # nur eine Koordinate soll genutzt werden
WHERE {
  { ?item wdt:P31/wdt:P279* wd:Q106658.             # alle Landkreise (294 von 294)
    MINUS { ?item wdt:P576 _:b1. }                  # ohne Auflösungsdatum
  }
  union
  {  ?item p:P31 ?statement .
     ?statement ps:P31/wdt:P279* wd:Q22865 .        # alle Kreisfreie Städte in Deutschland  (107 von 107)
     MINUS { ?statement pq:P582 [] }                # ohne Auflösungsdatum
  }
  
  OPTIONAL { ?item wdt:P440 ?kreisschluessel}       # Kreise mit Kreisschlüssel (399 von 401)
  MINUS { ?item wdt:P576 _:b2. }                    # ohne Auflösungsdatum   
  OPTIONAL { ?item wdt:P625 ?coordinate. }          # Koordinate für Kartenansicht
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
}
GROUP BY ?item ?itemLabel ?itemDescription ?coord ?kreisschluessel
order by ?itemLabel
Try it!

alle französischen Départements

[edit]
#--------------------------------
# alle französischen Départements 
# (101 Stand 2019-03-29)
# https://de.wikipedia.org/wiki/Département
#--------------------------------
#defaultView:Map
SELECT distinct ?item ?itemLabel (SAMPLE(?coordinate) AS ?coord)
where {
  ?item wdt:P31/wdt:P279* wd:Q6465.           # alle Departments (294 von 294)
  MINUS { ?item wdt:P576 _:b1. }              # ohne Auflösungsdatum
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel
Try it!

Alle Provinzen Spaniens

[edit]
#--------------------------------
# alle Provinzen Spaniens 
# (50 Stand 2019-03-29)
# https://de.wikipedia.org/wiki/Liste_der_Provinzen_Spaniens
#--------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription (SAMPLE(?coordinate) AS ?coord)
where {
  ?item p:P31 ?statement .
  ?statement ps:P31 wd:Q162620.        # alle Provinzen Spaniens /wdt:P279*
  MINUS { ?statement pq:P582 [] }                # ohne Endzeit an der Eigenschaft
  MINUS { ?item wdt:P576 [].} #_:b1. }           # ohne Auflösungsdatum am Objekt
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel ?itemDescription
order by ?itemDescription
Try it!

Alle Powiate Polens

[edit]
#--------------------------------
# alle Powiate Polens
# 314 sogenannte Landkreise (powiat ziemski) und 65 Stadtkreise (powiat grodzki).  (Stand  2019-03-29)
# https://de.wikipedia.org/wiki/Powiat
#--------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription (SAMPLE(?coordinate) AS ?coord)
where {
  ?item p:P31 ?statement .
  ?statement ps:P31/wdt:P279* wd:Q247073.        # Powiate Polens
  MINUS { ?statement pq:P582 [] }                # ohne Endzeit an der Eigenschaft
  MINUS { ?item wdt:P576 [].} #_:b1. }           # ohne Auflösungsdatum am Objekt
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel ?itemDescription
order by ?itemDescription
Try it!

Alle Bezirke und Statutarstädte von Österreich

[edit]
#--------------------------------
# alle Bezirke und Statutarstädte von Österreich
# 15 Statutarstädte und 79 Bezirke 
# 94 Gesamt (Stand  2019-03-29)
# https://de.wikipedia.org/wiki/Liste_der_Bezirke_und_Statutarst%C3%A4dte_in_%C3%96sterreich
#--------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription (SAMPLE(?coordinate) AS ?coord)
where {
  ?item p:P31 ?statement .
  ?statement ps:P31/wdt:P279* wd:Q871419.        # Bezirk in Österreich
  MINUS { ?statement pq:P582 [] }                # ohne Endzeit an der Eigenschaft
  MINUS { ?item wdt:P576 [].} #_:b1. }           # ohne Auflösungsdatum am Objekt
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel ?itemDescription
order by ?itemDescription
Try it!

Alle Bezirk in der Schweiz

[edit]
#--------------------------------
# alle Bezirk in der Schweiz
# https://de.wikipedia.org/wiki/Bezirk_(Schweiz)
#--------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription (SAMPLE(?coordinate) AS ?coord)
where {
  ?item p:P31 ?statement .
  ?statement ps:P31/wdt:P279* wd:Q662914.        # Bezirk in der Schweiz
  MINUS { ?statement pq:P582 [] }                # ohne Endzeit an der Eigenschaft
  MINUS { ?item wdt:P576 [].} #_:b1. }           # ohne Auflösungsdatum am Objekt
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel ?itemDescription
order by ?itemDescription
Try it!


Alle Okres in Tschechien

[edit]
#--------------------------------
# alle Okres in Tschechien
# https://de.wikipedia.org/wiki/Verwaltungsgliederung_Tschechiens
#--------------------------------
#defaultView:Map,Table
SELECT distinct ?item ?itemLabel ?itemDescription (SAMPLE(?coordinate) AS ?coord)
where {
  ?item p:P31 ?statement .
  ?statement ps:P31/wdt:P279* wd:Q548611.        # Okres in Tschechien
  MINUS { ?statement pq:P582 [] }                # ohne Endzeit an der Eigenschaft
  MINUS { ?item wdt:P576 [].} #_:b1. }           # ohne Auflösungsdatum am Objekt
  OPTIONAL { ?item wdt:P625 ?coordinate. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
} group by ?item ?itemLabel ?itemDescription
order by ?itemDescription
Try it!

Alle Stadtteile einer Stadt für Commons-Suche

[edit]
#---------------------------------------------------------------------------------------
# Alle Stadtteile einer Stadt für Commons-Suche
# ==> Weismark OR Pallien OR Trier-Süd incategory:"All media needing categories as of 2019"
#---------------------------------------------------------------------------------------
#defaultView:Table

    SELECT distinct ?group ?itemLabel
    WHERE {    
      BIND("OR " AS ?group ) .
      ?item wdt:P131 wd:Q1055.     # liegt in Verwaltungseinheit der Stadt
      VALUES ?instance_of { 
        wd:Q2740635                 # Stadtbezirk in Deutschland
        wd:Q4286337                 # Stadtbezirk
        wd:Q253019                  # Ortsteil
      } 
     ?item wdt:P31/wdt:P279* ?instance_of    # ist Teil der Stadt
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en,de". }
     }
Try it!


Gemeinde mit dem Namen Reims

[edit]
select distinct ?item ?itemLabel ?itemDescription
where {
  ?item (wdt:P31/wdt:P279*) wd:Q484170.
  #?item wdt:P131/wdt:P279* wd:Q142.
  ?item rdfs:label ?name .
  FILTER(REGEX(STR(?name), "Reims")) 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
limit 5
Try it!

Nachnamen und dänische Verben

[edit]
# Abfrage von Finn Årup Nielsen, die nach Personen mit Nachnamen sucht, die der Vergangenheitsform eines dänischen Verbs entsprechen.
SELECT 
  (COUNT(?person) AS ?count)
  ?lexeme ?lemma ?surname
  (SAMPLE(?person) AS ?example_person)
  (GROUP_CONCAT(?person_labels; separator=", ") AS ?names)
WHERE {
  hint:Query hint:optimizer "None".     

  ?lexeme dct:language wd:Q9035 .
  ?lexeme ontolex:lexicalForm ?form .
  ?form wikibase:grammaticalFeature wd:Q52434448 .
  ?lexeme wikibase:lemma ?lemma .
  ?form ontolex:representation ?word .
  BIND(STRLANG(CONCAT(UCASE(SUBSTR(STR(?word), 1, 1)), SUBSTR(STR(?word), 2)), "en") AS ?surname)
  ?surname_item rdfs:label ?surname .
  ?person wdt:P734 ?surname_item  .
  ?person rdfs:label ?person_labels . FILTER(LANG(?person_labels) = "en")
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?lexeme ?lemma ?surname
ORDER BY DESC(?count) 
LIMIT 100
Try it!

Große Städte mit weiblichen Bürgermeister

[edit]
#Largest cities with female mayor
SELECT DISTINCT ?city ?cityLabel ?mayor ?mayorLabel
WHERE
{
  BIND(wd:Q6581072 AS ?sex)
  BIND(wd:Q515 AS ?c)

	?city wdt:P31/wdt:P279* ?c .  # find instances of subclasses of city
	?city p:P6 ?statement .            # with a P6 (head of goverment) statement
	?statement ps:P6 ?mayor .          # ... that has the value ?mayor
	?mayor wdt:P21 ?sex .       # ... where the ?mayor has P21 (sex or gender) female
	FILTER NOT EXISTS { ?statement pq:P582 ?x }  # ... but the statement has no P582 (end date) qualifier
	
	# Now select the population value of the ?city
	# (wdt: properties use only statements of "preferred" rank if any, usually meaning "current population")
	?city wdt:P1082 ?population .
	# Optionally, find English labels for city and mayor:
	SERVICE wikibase:label {
		bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" .
	}
}
ORDER BY DESC(?population)
LIMIT 10
Try it!


Objekte in einem Bundsland (mit Unterabfrage)

[edit]
#Objekte in einem Bundsland (mit Unterabfrage)
#defaultView:Map,Table

select ?object ?objectLabel ?objectDescription ?coord ?image
where {
  ?object wdt:P131/wdt:P279* ?item.      
  ?object (wdt:P31/wdt:P279*) wd:Q33506 .  # ist ein Museum (oder Unterklasse)
  #?object (wdt:P31/wdt:P279*) wd:Q55488 .   # ist ein Bahnhof (oder Unterklasse) 
  #OPTIONAL { ?object wdt:P18 ?image . }     
  OPTIONAL { ?object wdt:P625 ?coord. }  

# hier kommt die Unterabfrage mit den Kreisen und Städten des Bundeslandes
{  
SELECT distinct ?item 
#?itemLabel ?itemDescription ?kreisschluessel 
       (SAMPLE(?coordinate) AS ?coord)              # nur eine Koordinate soll genutzt werden
WHERE {
  { ?item wdt:P31/wdt:P279* wd:Q106658.             # alle Landkreise (294 von 294)
    MINUS { ?item wdt:P576 _:b1. }                  # ohne Auflösungsdatum
  }
  union
  {  ?item p:P31 ?statement .
     ?statement ps:P31/wdt:P279* wd:Q22865 .        # alle Kreisfreie Städte in Deutschland  (107 von 107)
     MINUS { ?statement pq:P582 [] }                # ohne Auflösungsdatum
  }
  
  #OPTIONAL { ?item wdt:P440 ?kreisschluessel}       # Kreise mit Kreisschlüssel (399 von 401)
  MINUS { ?item wdt:P576 _:b2. }                    # ohne Auflösungsdatum   
  #OPTIONAL { ?item wdt:P625 ?coordinate. }          # Koordinate für Kartenansicht
  
  
  ?item wdt:P131 wd:Q1205.                           # liegt in Thüringen
  #?item wdt:P131 wd:Q1202.                           # liegt in Sachsen

  
}
GROUP BY ?item #?itemLabel ?itemDescription ?coord ?kreisschluessel
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" }
}
Try it!


Commons-Subkategorien auf Karte darstellen

[edit]

Es sollen alle Unterkategorien von c:Category:Villages_in_Saale-Holzland-Kreis auf einer Karte angezeigt werden. Lösung: SPARQL fragt erst in Commons ab und dann mit der Info die Koordinaten in Wikidata. 

#defaultView:Map
SELECT ?item (SAMPLE(?coords) AS ?coord) WITH {
  SELECT DISTINCT ?out ?depth WHERE {
    SERVICE <https://query.wikidata.org/bigdata/namespace/categories/sparql> {
      SERVICE mediawiki:categoryTree {
        bd:serviceParam mediawiki:start <https://commons.wikimedia.org/wiki/Category:Villages_in_Saale-Holzland-Kreis> .
        bd:serviceParam mediawiki:direction 'Reverse' .
        bd:serviceParam mediawiki:depth 1 .  # direct subcategories only
      }
    }
  }
} AS %subquery WHERE {
  INCLUDE %subquery .
  FILTER(?depth = 1) .  # remove the host category from results
  OPTIONAL {
    ?out schema:about/wdt:P301? ?item .
    FILTER NOT EXISTS { ?item wdt:P31 wd:Q4167836 }  # remove category items from results set
    OPTIONAL {
      ?item wdt:P625 ?coords .
    }
  }
} GROUP BY ?item  # to aggregate sample coordinates in case there are multiple P625 values in items
Try it!


Flächengrößen vergleichen

[edit]
#---------------------------------------------------------------------------------------
# prefecture level cities sorted by area
#---------------------------------------------------------------------------------------
#defaultView:Table
SELECT distinct ?item ?itemLabel ?itemDescription ?area ?normalizedSuperficie ?normalizedUnitLabel ?originalSuperficie ?originalUnitLabel ?normalizedSuperficieKM2 
WHERE {    
  ?item wdt:P31 wd:Q748149 .    # is prefecture level cities
  ?item p:P2046 [ psn:P2046 [ wikibase:quantityAmount ?normalizedSuperficie; wikibase:quantityUnit ?normalizedUnit ] ] .
  ?item p:P2046 [ psv:P2046 [ wikibase:quantityAmount ?originalSuperficie; wikibase:quantityUnit ?originalUnit ] ] .
  BIND(?normalizedSuperficie/1000000 AS ?normalizedSuperficieKM2) .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE]". }
}
ORDER BY desc(?normalizedSuperficieKM2)
Try it!
Retrieved from "https://www.wikidata.org/w/index.php?title=User:Stefan_Kühn/Abfragen&oldid=2270010000"