User:Stefan Kühn/Persondata

Allgemeine Abfragen edit

Person suchen, Lemma bekannt edit

#defaultView:Table
SELECT distinct ?item ?itemLabel ?itemDescription ?date_of_birth ?date_of_death

WITH {
  SELECT ?item
  WHERE
  {
    ?item rdfs:label "Arthur Goldschmidt"@en .
    ?item wdt:P31 ?instance_of .
  }
} AS %person

WHERE {
  INCLUDE %person.
 
  #OPTIONAL { ?item wdt:P19 ?place_of_birth }. # Geburtsort  
  #filter (?place_of_birth = wd:Q64)          # Geburtsort ist Berlin
  #OPTIONAL { ?item wdt:P18 ?image. }        # Bild
  OPTIONAL { ?item wdt:P569 ?date_of_birth }. # Geburtstag
  #OPTIONAL { ?item wdt:P570 ?date_of_death }. # Todestag 
  #FILTER (?date_of_birth >= "1902-02-13T00:00:00Z"^^xsd:dateTime)  # geboren nach  
  #FILTER (?date_of_birth <= "1902-02-13T00:00:00Z"^^xsd:dateTime)  # geboren vor  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en". }
}

order by ?date_of_birth

Try it!

Person suchen, fuzzy search with cirrussearch edit

SELECT ?item ?itemLabel ?itemDescription ?place_of_birthLabel ?date_of_birth ?date_of_death
WHERE
{
  SERVICE wikibase:mwapi
  {
    bd:serviceParam wikibase:endpoint "www.wikidata.org" .
    bd:serviceParam wikibase:api "Generator" .
    bd:serviceParam mwapi:generator "search" .
    bd:serviceParam mwapi:gsrsearch "inlabel:'Arthur Goldschmidt'" .
    bd:serviceParam mwapi:gsrlimit "max" .
    ?item wikibase:apiOutputItem mwapi:title.
  }
  ?item wdt:P31 wd:Q5 . # Only humans
  OPTIONAL { ?item wdt:P19 ?place_of_birth }
  OPTIONAL { ?item wdt:P569 ?date_of_birth }.
  OPTIONAL { ?item wdt:P570 ?date_of_death }.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en". }
}

Try it!

Person suchen, Vor- und Nachname über Liste edit

SELECT ?item ?itemLabel ?itemDescription ?born ?died
{
  VALUES (?first ?last)
  {
   ("Robert"@en "Darwin"@en)
   ("Rachel"@en "Carson"@en)
   ("Thomas"@en "Smith"@en)
  }
  BIND (STRLANG(STR(?first), "mul") AS ?first_mul)
  { ?first_name_item wdt:P1705 ?first }
  UNION
  { ?first_name_item wdt:P1705 ?first_mul }

  BIND (STRLANG(STR(?last), "mul") AS ?last_mul)
  { ?last_name_item wdt:P1705 ?last }
  UNION
  { ?last_name_item wdt:P1705 ?last_mul }

  ?item wdt:P31 wd:Q5 . # ?item is human
  ?item wdt:P735 ?first_name_item .
  ?item wdt:P734 ?last_name_item .

  OPTIONAL { ?item wdt:P569 ?born }
  OPTIONAL { ?item wdt:P570 ?died }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Try it!

Alle Personen mit gleichem Geburtstag (Monat, Tag) edit

#defaultView:Table,ImageGrid
SELECT ?item ?itemLabel ?itemDescription ?image ?date ?tod
WHERE
{
	?item wdt:P31 wd:Q5 .         # Mensch
	?item wdt:P569 ?date .        # Geburtstag
        ?item wdt:P19 wd:Q64.         # Geburtsort ist Berlin  (Limitierung auf Stadt notwendig sonst zu viele)
	OPTIONAL {?item wdt:P570 ?tod } # Todestag
	FILTER (month(?date) =  4 && day(?date) = 2)  # 
    OPTIONAL { ?item wdt:P18 ?image. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
order by ?date

Try it!

Alle Personen mit gleichem Geburtstag (Jahr, Monat, Tag) edit

#defaultView:ImageGrid
SELECT ?item ?itemLabel ?itemDescription ?image #?date ?dod
WHERE
{
	?item wdt:P31 wd:Q5 .         # Mensch
	?item wdt:P569 ?date .        # Geburtstag
	OPTIONAL {?item wdt:P570 ?dod } # Todestag
	FILTER (?date = "1960-04-14T00:00:00Z"^^xsd:dateTime)  # 
    #FILTER (!bound(?dod))
    OPTIONAL { ?item wdt:P18 ?image. }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]". }
}
order by ?date
LIMIT 100

Try it!

Personen geboren in einer Stadt + in einem Zeitraum edit

#defaultView:ImageGrid;Table
SELECT ?item ?itemLabel ?itemDescription ?image ?date_of_birth WHERE {
  ?item wdt:P31 wd:Q5.                      # Mensch
  ?item wdt:P19 ?place_of_birth             # Geburtsort     
  filter (?place_of_birth = wd:Q1731)       # Geburtsort ist Dresden
  OPTIONAL { ?item wdt:P18 ?image. }        # Bild
  OPTIONAL { ?item wdt:P569 ?date_of_birth. } # Geburtstag
  OPTIONAL { ?item wdt:P570 ?date_of_death. } # Todestag
  FILTER (?date_of_birth >= "1974-06-01T00:00:00Z"^^xsd:dateTime)  # geboren nach  
  FILTER (?date_of_birth <= "1975-07-01T00:00:00Z"^^xsd:dateTime)  # geboren vor
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }
}
order by ?date_of_birth
LIMIT 100

Try it!

Personenliste - Alle ukrainischen Handballspieler mit deutschen Artikeln edit

PREFIX schema: <http://schema.org/>
#defaultView:Table;ImageGrid;
SELECT DISTINCT ?item ?itemLabel ?itemDescription ?article  ?image WHERE {
  ?item wdt:P31 wd:Q5.               # ist ein = Mensch
  ?item wdt:P27 wd:Q212.             # Staatsangehörgigkeit = Ukrainer
  ?item wdt:P106 wd:Q13365117.       # Tätigkeit = Handballer

  ?article schema:about ?item.
  ?article schema:isPartOf <https://de.wikipedia.org/>.    # Artikel in der Deutschen

  OPTIONAL { ?item wdt:P18 ?image. }
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }
}
order by ?itemLabel

Try it!

Personenliste - Alle ukrainischen Handballspieler mit Artikeln in einer von 4 Sprachen edit

PREFIX schema: <http://schema.org/>
#defaultView:Table;Map
SELECT DISTINCT ?item ?itemLabel ?itemDescription 
  ?article_de 
  ?article_en 
  ?article_uk
  ?article_ru
  ?image 
WHERE {
  ?item wdt:P31 wd:Q5.               # ist ein = Mensch
  ?item wdt:P27 wd:Q212.             # Staatsangehörgigkeit = Ukrainer
  ?item wdt:P106 wd:Q13365117.       # Tätigkeit = Handballer
  optional {  # Artikel in der de.wikipedia
    ?article_de schema:about ?item.
    ?article_de schema:isPartOf <https://de.wikipedia.org/>.   
  }
  optional { # Artikel in der en.wikipedia
    ?article_en schema:about ?item.
    ?article_en schema:isPartOf <https://en.wikipedia.org/>.    
  }
  optional { # Artikel in der uk.wikipedia
    ?article_uk schema:about ?item.
    ?article_uk schema:isPartOf <https://uk.wikipedia.org/>.    
  }
  optional { # Artikel in der ru.wikipedia
    ?article_ru schema:about ?item.
    ?article_ru schema:isPartOf <https://ru.wikipedia.org/>.    
  }
  OPTIONAL { ?item wdt:P18 ?image. }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de,en,uk,ru". }
}
order by ?itemLabel

Try it!

Personenliste - Alle Personen, die an COVID-19 verstorben sind edit

# Alle Personen, die an COVID-19 verstorben sind 
SELECT ?person ?personLabel ?personDescription ?image ?date_birth ?date_death ?age 
WHERE
{
  ?person wdt:P31 wd:Q5 .                         # Human
  ?person wdt:P570 ?date_death .                  # has date of death
  ?person wdt:P509 wd:Q84263196.                  # COVID-19
  OPTIONAL { ?person wdt:P569 ?date_birth. }      # get date of birth
  Bind ( round((?date_death - ?date_birth)/365.2425) as ?age )  # calculate age 
  OPTIONAL { ?person wdt:P18 ?image . }           # image
  SERVICE wikibase:label {
		bd:serviceParam wikibase:language "de,en,[AUTO_LANGUAGE]" .
	}
}
order by ?age

Try it!

Personenliste - Alle Personen, die Maler in der Renaissance waren (Tätigkeitliste, Epochenliste) edit

SELECT DISTINCT ?item ?itemLabel 
(GROUP_CONCAT(DISTINCT ?occupation; SEPARATOR = ",") AS ?occupationUID) 
(GROUP_CONCAT(DISTINCT ?occupationLabel; SEPARATOR = ",") AS ?occupations) 
(GROUP_CONCAT(DISTINCT ?movementLabel; SEPARATOR = ",") AS ?movements) 

WHERE {
  ?item wdt:P106 ?o;              # constrain what items are returned by predicate values in the filters
        wdt:P135 ?m.
  FILTER(?m IN(wd:Q4692, wd:Q1472236, wd:Q1474884, wd:Q979160))
  FILTER(?o IN(wd:Q1028181))

  ?item wdt:P106 ?occupation;    # find out all values of these two predicates
      wdt:P135 ?movement.
#  FILTER(?occupation NOT IN(wd:Q1028181))

  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
    ?item rdfs:label ?itemLabel.
    ?occupation rdfs:label ?occupationLabel.
    ?movement rdfs:label ?movementLabel.
  }
}
GROUP BY ?item ?itemLabel

Try it!

Wartungslisten edit

Beschreibung mit doppeltem Leerzeichen edit

#----------------------------------------------------------
# Deutsche Beschreibung enthält doppeltes Leerzeichen
#----------------------------------------------------------
#defaultView:Table
SELECT ?item ?itemLabel ?desc_de
WHERE 
{
  ?item wdt:P31 wd:Q5.               # ist ein = Mensch
  ?item wdt:P27 wd:Q183.             # Staatsangehörgigkeit = Deutscher
  #?item wdt:P106 wd:Q1734662 .       # Tätigkeit = Handballer

  # Regex-Filter für deutsche Beschreibung
  ?item schema:description ?desc_de . filter (lang(?desc_de) = "de").
  FILTER(REGEX(STR(?desc_de), "  "))
            
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }
}
#order by ?itemLabel  
limit 10

Try it!

Personen ohne deutsche Beschreibung, aber mit deutschem Artikel edit

#-------------------------------------------------------------------------------
# Personen ohne deutsche Beschreibung, aber mit deutschem Artikel
#-------------------------------------------------------------------------------
#defaultView:Table
SELECT ?item ?itemLabel ?article WHERE {
  hint:Query hint:optimizer "None" .               # Enable top-to-bottom execution
  SERVICE bd:slice {
    ?item wdt:P31 wd:Q5 . # is human    
    bd:serviceParam bd:slice.offset 0 .      # Offset einstellen
    bd:serviceParam bd:slice.limit 50000 .   # X Datensätze abrufen
  }      
  ?article schema:about ?item.                 
  ?article schema:isPartOf <https://de.wikipedia.org/>.   # has article in de.wikipedia.org
  filter not exists {?item schema:description ?desc . filter(lang(?desc)="de") } # Beschreibung auf Deutsch ist leer 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de" }     # Beschreibung in anderen Sprachen anzeigen

}
order by ?itemLabel
Limit 20

Try it!

Alle Familiennamen edit

#----------------------------------------------------------
# alle Familiennamen
#----------------------------------------------------------
SELECT ?item ?itemLabel 
WHERE 
{
  SERVICE bd:slice {
    ?item wdt:P31 wd:Q101352  .
    bd:serviceParam bd:slice.offset 0 .
    bd:serviceParam bd:slice.limit 2000 .
  }      
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],de". } 
}

Try it!

Personen mit fehlenden Familiennamen edit

#----------------------------------------------------------
#  Personen mit fehlenden Familiennamen (Result copy to QuickStatement)
#----------------------------------------------------------
SELECT DISTINCT ?item_output2 ?property ?lastname_item_output2 ?c ?itemLabel ?lastname ?itemDescription
WITH {
  SELECT ?lastname (SAMPLE(?item) AS ?lastname_item) {
    ?item wdt:P31 wd:Q101352 ;                     # family names
          wdt:P407 wd:Q188 ;                       # in German
          rdfs:label ?lastname FILTER(LANG(?lastname) = "de")
  } GROUP BY ?lastname HAVING(COUNT(?item) = 1)    # non-ambiguous, just in case
} AS %SURNAMES
{
  hint:Query hint:optimizer "None" .               # Enable top-to-bottom execution
  SERVICE bd:slice {
    ?item wdt:P31 wd:Q5 .                          # only human
    FILTER NOT EXISTS { ?item wdt:P734 [] } .      # no family name
    bd:serviceParam bd:slice.offset 500000 .
    bd:serviceParam bd:slice.limit 10000 .
  }                 

  ?item rdfs:label ?itemLabel .              
  FILTER(LANG(?itemLabel) = 'de') .                 # german label 
  FILTER NOT EXISTS { ?item wdt:P734 [] } .         # no family name
  FILTER NOT EXISTS { ?item wdt:P1950 [] } .        # no first family name in Portuguese name
  FILTER NOT EXISTS { ?item wdt:P742 [] } .         # pseudonym
  
  ?item schema:description ?itemDescription . 
  filter (lang(?itemDescription) = "de").
  FILTER(REGEX(STR(?itemDescription), "deutsch|Schweizer|österreichisch")).         # only German Person
  
  FILTER(REGEX(STR(?itemLabel), "^\\p{Lu}[\\p{Ll}-]+( \\p{Lu}[\\p{Ll}-]+)+$")) .    # like: Aaaa Bbbb Cccc 
  BIND(REPLACE(?itemLabel, "^.* ([^\\s]+)$", "$1") AS ?lastname)                    # get lastname
  FILTER(REGEX(STR(?lastname), "...")) .                                            # minimum 3 letters
  
  INCLUDE %SURNAMES                                                                 # hashjoin!

  # OUTPUT
  BIND(STR(?item) as ?item_output) .
  BIND(REPLACE(?item_output, "http://www.wikidata.org/entity/", "", "i") AS ?item_output2) .
  BIND(STR("P734") as ?property) .
  BIND(STR("#") as ?c) .
  BIND(STR(?lastname_item) as ?lastname_item_output) .
  BIND(REPLACE(?lastname_item_output, "http://www.wikidata.org/entity/", "", "i") AS ?lastname_item_output2) .
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }  
} GROUP BY ?item_output2 ?property ?lastname_item_output2 ?c ?itemLabel ?lastname ?itemDescription
order by ?lastname ?itemLabel

Try it!

Daten-Download edit

#----------------------------------------------------------
# Daten-Download in 25.000er Schritten (mit offset)
#----------------------------------------------------------

SELECT DISTINCT
  ?item
  ?label_en
  ?label_de
  ?desc_en
  ?desc_de
  ?dewiki
  (SAMPLE(?dob) AS ?date_of_birth)
  (SAMPLE(?pob) AS ?place_of_birth)
  (SAMPLE(?dod) AS ?date_of_death)
  (SAMPLE(?pod) AS ?place_of_death)
  (SAMPLE(?img) AS ?image)
  (SAMPLE(?coc) AS ?citizenship)
  (SAMPLE(?occ) AS ?occupation)
WHERE {
  SERVICE bd:slice {
    ?item wdt:P31 wd:Q5 .
    bd:serviceParam bd:slice.offset 0 .
    bd:serviceParam bd:slice.limit 25000 .
  }                 

  OPTIONAL {
    ?item rdfs:label ?label_en .
    FILTER(LANG(?label_en) = 'en') .
  }
  OPTIONAL {
    ?item rdfs:label ?label_de .
    FILTER(LANG(?label_de) = 'de') .
  }
  OPTIONAL {
    ?item schema:description ?desc_en .
    FILTER(LANG(?desc_en) = 'en') .
  }
  OPTIONAL {
    ?item schema:description ?desc_de .
    FILTER(LANG(?desc_de) = 'de') .
  }
  OPTIONAL {
    ?dewiki schema:about ?item; schema:isPartOf <https://de.wikipedia.org/>
  }
  OPTIONAL { ?item wdt:P569 ?dob }
  OPTIONAL { ?item wdt:P570 ?dod }
  OPTIONAL { ?item wdt:P19 ?pob }
  OPTIONAL { ?item wdt:P20 ?pod }

  OPTIONAL { ?item wdt:P27 ?coc }
  OPTIONAL { ?item wdt:P106 ?occ }
  OPTIONAL { ?item wdt:P18 ?img }
} GROUP BY ?item ?label_en ?label_de ?desc_en ?desc_de ?dewiki

Try it!