User:CamelCaseNick/Stolpersteine
Stolperstein items whose photos don't have a depicts (P180) edit
#by CamelCaseNick
prefix commons:<http://commons.wikimedia.org/wiki/Special:FilePath/>
select ?stolperstein ?image with {
select ?stolperstein ?image ?title where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P131/wdt:P131 wd:Q1055;
wdt:P18 ?image.
bind(wikibase:decodeUri(replace(str(?image), str(commons:), "")) as ?title)
filter(regex(?title, "^(?:Stolpersteine? )?((?:(?:\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?(?: ?\\d+(?: ?[a-zA-Z]|[\\-–]\\d+)?)?)) \\( *((?:[\\p{L}\\d]+[\\-\\.,]? ?)*[\\p{L}\\d]+) *\\)(?: ?[Ii]n)? ([\\p{L}\\. \\-]+?)(?: ?\\(\\d+\\)| ?\\d+)?(?: ShiftN)?\\.(jpg|JPG)$")
|| regex(?title, "^((?:[\\p{L}\\d\\.]+[\\- ])*[\\p{L}\\d]+) ?[\\-–] ((?:(?:\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?(?: \\d+(?:[a-z]| ?[\\-–] ?\\d+)?)?)) \\(([\\p{L}\\- \\.]+)\\)(?:\\.| )Stolperstein(e| 20\\d{2}|\\-\\d{4}\\-\\d{2}\\-\\d{2})?(\\.\\d+)?(\\.(nnw|(crop\\.)?ajb|ccn))?\\.jpg$"))
}
order by asc(xsd:integer(replace(str(?stolperstein), str(wd:Q), "")))
limit 400
offset 2400
} as %stolpersteine where {
include %stolpersteine.
bind(concat("intitle:\"", ?title, "\" -haswbstatement:P180=", replace(str(?stolperstein), str(wd:), "")) as ?fileSearch)
service wikibase:mwapi {
bd:serviceParam wikibase:api "Search";
wikibase:endpoint "commons.wikimedia.org";
mwapi:srsearch ?fileSearch;
mwapi:srlimit "1"^^xsd:integer;
mwapi:srnamespace "6".
?fileMatch wikibase:apiOutput mwapi:title.
}
}
order by asc(xsd:integer(replace(str(?stolperstein), str(wd:Q), "")))
Stolpersteine with fields extrapolatable from regular photo file title structure and not matching up edit
#by CamelCaseNick
prefix commons:<http://commons.wikimedia.org/wiki/Special:FilePath/>
select (?stolperstein as ?item) ?nameImage ?addressImage ?quarterImage with {
select distinct ?stolperstein ?nameImage ?addressImage ?quarterImage where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P131+ wd:Q1055;
wdt:P18 ?image.
bind(wikibase:decodeUri(replace(str(?image), str(commons:), "")) as ?title)
values (?nameGroup ?addressGroup ?quarterGroup ?regex) {
("$1" "$2" "$3" "^((?:[\\p{L}\\d\\.]+[\\- ])*[\\p{L}\\d]+) ?[\\-–] ((?:(?:\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?(?: \\d+(?:[a-z]| ?[\\-–] ?\\d+)?)?)) \\(([\\p{L}\\- \\.]+)\\)(?:\\.| )Stolperstein(e| 20\\d{2}|\\-\\d{4}\\-\\d{2}\\-\\d{2})?(\\.\\d+)?(\\.(nnw|(crop\\.)?ajb|ccn))?\\.jpg$")
("$2" "$1" "$3" "^(?:Stolpersteine? )?((?:(?:\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?(?: ?\\d+(?: ?[a-zA-Z]|[\\-–]\\d+)?)?)) \\( *((?:[\\p{L}\\d]+[\\-\\.,]? ?)*[\\p{L}\\d]+) *\\)(?: ?[Ii]n)? ([\\p{L}\\. \\-]+?)(?: ?\\(\\d+\\)| ?\\d+)?(?: ShiftN)?\\.(jpg|JPG)$")
}
filter(regex(?title, ?regex))
bind(replace(?title, ?regex, ?nameGroup) as ?nameImage)
bind(replace(?title, ?regex, ?addressGroup) as ?addressImage)
bind(replace(?title, ?regex, ?quarterGroup) as ?quarterImage)
}
} as %matches with {
select ?stolperstein ?nameImage ?addressImage ?quarterImage where {
include %matches.
filter exists {
?stolperstein skos:altLabel|rdfs:label ?alias.
filter(lang(?alias) = "de")
filter(regex(?alias, "Stolperstein für (?!den|die|das)"))
filter(?alias = strlang(concat("Stolperstein für ", ?nameImage), "de"))
}
filter exists {
?stolperstein p:P6375/ps:P6375 ?address.
filter(regex(?address, "^((\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?( \\d+( [a-z])?(( – |/)\\d+( [a-z])?)?)?), \\d{5} Hamburg$"))
bind(replace(?address, "^((\\p{L}+\\.?[ \\-/])*\\p{L}+\\.?( \\d+( [a-z])?(( – |/)\\d+( [a-z])?)?)?), \\d{5} Hamburg$", "$1") as ?addr)
filter(?addr = strlang(?addressImage, "de"))
}
filter exists {
?stolperstein p:P276/ps:P276 ?quarter.
?quarter wdt:P31 wd:Q15830667;
skos:altLabel|rdfs:label ?alias.
filter(lang(?alias) = "de")
filter(?alias = strlang(replace(?quarterImage, "Hamburg ", "Hamburg-"), "de"))
}
}
} as %perfect where {
include %matches.
minus {
include %perfect.
}
}
visualization on map of Stolpersteine with same photo edit
#by CamelCaseNick
#defaultView:Map{"hide": "?line"}
select ?line ?stolpersteinA ?stolpersteinB where {
?stolpersteinA wdt:P31 wd:Q26703203;
wdt:P18 ?image;
p:P625/psv:P625 ?geoA.
?geoA wikibase:geoLatitude ?latA;
wikibase:geoLongitude ?lonA.
?stolpersteinB wdt:P31 wd:Q26703203;
wdt:P18 ?image;
p:P625/psv:P625 ?geoB.
?geoB wikibase:geoLatitude ?latB;
wikibase:geoLongitude ?lonB.
bind(strdt(concat('Linestring(',str(?lonA), " ", str(?latA), ",",
str(?lonB), " ", str(?latB),")"), geo:wktLiteral) as ?line)
filter(str(?stolpersteinA) < str(?stolpersteinB))
}
irregular Stolperstein labels edit
#by CamelCaseNick
select ?stolperstein ?label ?language where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?label.
bind(lang(?label) as ?language)
filter(
(!regex(?label, "^Snublesten for [‚A-Z]") && ?language = "da")
|| (!regex(?label, "^Stolperstein für [‚A-Z]") && ?language = "de")
|| (!regex(?label, "^Stolperstein dedicated to ([‚A-Z]|(the|an|a|deserters)($| ))") && ?language = "en")
|| (!regex(?label, "^Stumbliga ŝtoneto pri [‚A-Z]") && ?language = "eo")
|| (!regex(?label, "^Stolperstein en memoria de ([‚A-Z]|desertores$)") && ?language = "es")
|| (!regex(?label, "^Stolperstein à la mémoire d([’'][‚AEIOUYH]|e [B-DF-HJ-NP-TV-Z])") && ?language = "fr")
|| (!regex(?label, "^snublestein til minne om [‚A-Z]") && ?language = "nb")
|| (!regex(?label, "^Stolperstein voor [‚A-Z]") && ?language = "nl")
|| (!regex(?label, "^Stolperstein em memória de ([‚A-Z]|desertores$)") && ?language = "pt")
# || ?language not in ("da", "de", "en", "eo", "en", "es", "fr", "nb", "nl", "pt")
)
filter(?language not in ("de", "nl"))
}
missing P1801 for commemorated where P1801 is already set edit
#by CamelCaseNick
select distinct ?stolperstein ?stolpersteinLabel ?image ?commemorated ?commemoratedLabel where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P18 ?image;
wdt:P547 ?commemorated. filter(!wikibase:isSomeValue(?commemorated))
minus { ?commemorated wdt:P1801 ?image. }
[wdt:P31 wd:Q26703203] wdt:P131+ wd:Q1055; wdt:P547 ?commemorated.
service wikibase:label { bd:serviceParam wikibase:language "de,en". }
}
missing P1801 for commemorated edit
#by CamelCaseNick
prefix commons:<http://commons.wikimedia.org/wiki/Special:FilePath/>
select (strafter(str(?commemorated), str(wd:)) as ?s) ("P1801" as ?p) (concat('"', wikibase:decodeUri(strafter(str(?image), str(commons:))), '"') as ?o)
("S3452" as ?p2) (strafter(str(?stolperstein), str(wd:)) as ?o2) where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P18 ?image;
wdt:P547 ?commemorated. filter(!wikibase:isSomeValue(?commemorated))
minus { ?commemorated wdt:P1801 []. }
}
Hamburg Stolperstein descriptions edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ("Dde" as ?pid) (concat("\"", ?description, "\"") as ?vid) ?de where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P131+ wd:Q1055;
wdt:P276 ?quarter.
filter exists {
?quarter wdt:P31 wd:Q15830667.
}
?quarter rdfs:label ?qDe.
filter(lang(?qDe) = "de")
bind(concat("Stolperstein in ", if(strstarts(?qDe, "Hamburg-"), "", "Hamburg-"), ?qDe) as ?description)
optional {
?stolperstein schema:description ?de.
filter(lang(?de) = "de")
}
filter not exists {
?stolperstein schema:description ?de.
filter(lang(?de) = "de" && (strstarts(?de, ?description) || strstarts(?de, concat("ehemaliger ", ?description))))
}
}
Stolperstein descriptions edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ("Dde" as ?pid) (concat("\"", ?description, "\"") as ?vid) where {
values (?city ?quarterClass ?prefix ?conn) {
(wd:Q1055 wd:Q15830667 "Hamburg-" wdt:P276)
(wd:Q64 wd:Q35034452 "Berlin-" wdt:P276)
(wd:Q24879 wd:Q17278559 "Bremen-" wdt:P131)
(wd:Q365 wd:Q15632166 "Köln-" wdt:P131)
(wd:Q1718 wd:Q1852178 "Düsseldorf-" wdt:P131)
(wd:Q1022 wd:Q2740635 "Stuttgart-" wdt:P131)
(wd:Q1720 wd:Q26703203 "Mainz-" wdt:P131)
(wd:Q1794 wd:Q79416466 "Frankfurt-" wdt:P131)
}
?stolperstein wdt:P31 wd:Q26703203; wdt:P131+ ?city; ?conn ?quarter.
filter exists { ?quarter wdt:P31 ?quarterClass. }
?quarter rdfs:label ?qDe. filter(lang(?qDe) = "de")
bind(concat("Stolperstein in ", if(strstarts(?qDe, ?prefix), "", ?prefix), ?qDe) as ?description)
optional { ?stolperstein schema:description ?de. filter(lang(?de) = "de") }
filter not exists { ?stolperstein schema:description ?de. filter(lang(?de) = "de") }
}
extrapolation of German labels from English ones edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?Lde where {
?stolperstein wdt:P31 wd:Q26703203.
filter not exists {
?stolperstein rdfs:label ?de.
filter(lang(?de) = "de")
}
?stolperstein rdfs:label ?en.
filter(lang(?en) = "en")
filter(strstarts(?en, "Stolperstein dedicated to "))
bind(replace(?en, "Stolperstein dedicated to ", "Stolperstein für ") as ?Lde)
}
Stolperstein labels missing the academic title edit
#by CamelCaseNick
select ?stolperstein where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P131+ wd:Q1055;
wdt:P1684 ?inscription;
rdfs:label ?de.
filter(lang(?de) = "de")
filter(contains(?inscription, "Dr."))
filter(!contains(?de, " Dr. "))
}
infer commemorated from image usage edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) (replace(str(?commemorated), str(wd:), "") as ?P547) (replace(str(?commemorated), str(wd:), "") as ?S3452) where {
?stolperstein wdt:P31 wd:Q26703203;
wdt:P18 ?image.
?commemorated wdt:P31 wd:Q5;
wdt:P1801 ?image.
filter not exists {
?stolperstein wdt:P547 [].
}
}
aliasses without middle initials edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?Ade where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?de.
filter(lang(?de) = "de")
filter(regex(?de, "^Stolperstein für (\\p{L}+) \\p{L}\\. (\\p{L}+)$"))
bind(replace(?de, "^Stolperstein für (\\p{L}+) \\p{L}\\. (\\p{L}+)$", "$1 $2") as ?name)
bind(concat("Stolperstein für ", ?name) as ?Ade)
filter not exists {
?stolperstein skos:altLabel ?alias.
filter(?alias = strlang(?Ade, "de"))
}
}
aliasses without the titles of the commemorated edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?Ade where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?de.
filter(lang(?de) = "de")
filter(regex(?de, "^Stolperstein für ?(Prof\\.( ?Dr\\.)?|Dr\\.( ?med\\.( ?vet\\.)?| ?[ji]ur\\.| ?phil\\.)?)"))
bind(replace(?de, "^Stolperstein für ?(Prof\\.( ?Dr\\.)?|Dr\\.( ?med\\.( ?vet\\.)?| ?[ji]ur\\.| ?phil\\.)?) ", "") as ?name)
bind(concat("Stolperstein für ", ?name) as ?Ade)
filter not exists {
?stolperstein skos:altLabel ?alias.
filter(?alias = strlang(?Ade, "de"))
}
}
aliasses without the nicknames edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?Ade where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?de.
filter(lang(?de) = "de")
filter(regex(?de, "^Stolperstein für (\\p{L}+) („\\p{L}+“|‚\\p{L}+‘|\"\\p{L}+\"|'\\p{L}+'|\\(\\p{L}+\\)) (\\p{L}+)$"))
bind(replace(?de, "^Stolperstein für (\\p{L}+) („\\p{L}+“|‚\\p{L}+‘|\"\\p{L}+\"|'\\p{L}+'|\\(\\p{L}+\\)) (\\p{L}+)$", "$1 $3") as ?name)
bind(concat("Stolperstein für ", ?name) as ?Ade)
filter not exists {
?stolperstein skos:altLabel ?alias.
filter(?alias = strlang(?Ade, "de"))
}
}
French labels edit
#by CamelCaseNick
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?Lfr where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?fr.
filter(lang(?fr) = "fr")
filter(regex(?fr, "Stolperstein à la mémoire de [AEIOUY]", "i"))
bind(replace(?fr, "Stolperstein à la mémoire de ", "Stolperstein à la mémoire d’") as ?Lfr)
}
mendatory properties edit
select (replace(str(?stolperstein), str(wd:), "") as ?qid) ?pid ?vid where {
?stolperstein wdt:P31 ?class. values ?class { wd:Q26703203 wd:Q43638585 }
# ?stolperstein rdfs:label ?Lde. filter(lang(?Lde) = "de")
{
values (?p ?v) {
(wdt:P170 wd:Q195796)
(wdt:P186 wd:Q39782)
(wdt:P361 wd:Q314003)
}
filter not exists {
?stolperstein ?p ?v.
}
bind(replace(str(?p), str(wdt:), "") as ?pid)
bind(replace(str(?v), str(wd:), "") as ?vid)
} union {
?stolperstein wdt:P131 ?adminstrative.
?adminstrative wdt:P17 ?country.
filter not exists {
?stolperstein wdt:P17 [].
}
bind("P17" as ?pid)
bind(replace(str(?country), str(wd:), "") as ?vid)
} union {
?stolperstein wdt:P276 ?place.
?place wdt:P131 ?adminstrative.
filter not exists {
?stolperstein wdt:P131 [].
}
bind("P131" as ?pid)
bind(replace(str(?adminstrative), str(wd:), "") as ?vid)
}
}
Stolperstein labels edit
select (replace(str(?stolperstein), str(wd:), "") as ?qid) (concat("L", ?lang) as ?pid) (concat("\"", ?prefix, ?name, "\"") as ?label) where {
?stolperstein wdt:P31 wd:Q26703203;
rdfs:label ?Lde.
filter(lang(?Lde) = "de")
bind(replace(?Lde, "Stolperstein für ", "") as ?name)
values (?lang ?prefix ?cond) {
("en" "Stolperstein dedicated to " "")
("pt" "Stolperstein em memória de " "")
("es" "Stolperstein en memoria de " "")
("fr" "Stolperstein à la mémoire de " "^[^AEIOUY]")
("fr" "Stolperstein à la mémoire d’" "^[AEIOUY]")
}
filter(strstarts(?Lde, "Stolperstein für "))
filter(!regex(?name, "^(den|die|das|Unbekannt|Zwangsarbeiter|\\d)"))
filter(regex(?name, ?cond, "i"))
filter not exists {
?stolperstein rdfs:label ?l.
filter(lang(?l) = ?lang)
}
}