User:Stefan Kühn/Abfragen
Jump to navigation
Jump to search
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Allgemeien Abfragen
Alle Subinstanzen von Verkehrsinfrastrukut
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").
}
}
Alles von einem Item
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)
Hauptstädte
#------------------------------------------------
# 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
Straßenbahn in Ulm
#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". }
}
Kreisebene
Alle Landkreise und kreisfreie Städte in Deutschland
#------------------------------------------------
# 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
alle französischen Départements
#--------------------------------
# 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
Alle Provinzen Spaniens
#--------------------------------
# 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
Alle Powiate Polens
#--------------------------------
# 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
Alle Bezirke und Statutarstädte von Österreich
#--------------------------------
# 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
Alle Bezirk in der Schweiz
#--------------------------------
# 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
Alle Okres in Tschechien
#--------------------------------
# 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
Alle Stadtteile einer Stadt für Commons-Suche
#---------------------------------------------------------------------------------------
# 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". }
}
Gemeinde mit dem Namen Reims
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
Nachnamen und dänische Verben
# 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
Große Städte mit weiblichen Bürgermeister
#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
Objekte in einem Bundsland (mit Unterabfrage)
#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]" }
}
Commons-Subkategorien auf Karte darstellen
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
Flächengrößen vergleichen
#---------------------------------------------------------------------------------------
# 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)