User:Herzi Pinki/Sparql/Coordinate Me
< User:Herzi Pinki | Sparql
Objekte ohne Koordinaten in Österreich[edit]
Einschränkung auf instance of (P31) möglich - nicht alle Objekte haben sinnvollerweise Koordinaten
#title: items by country without coordinates
SELECT ?item ?itemLabel ?itemDescription WHERE {
?item wdt:P17 wd:Q40 . # Österreich
?item wdt:P131*/wdt:P279* wd:Q40 . # liegt in einer Verwaltungseinheit in Österreich
?item wdt:P31*/wdt:P279* ?isA .
VALUES ?isA {wd:Q271669 wd:Q486972} # landform settlement
#{?item wdt:P31*/wdt:P279* wd:Q271669 .} union {?item wdt:P31*/wdt:P279* wd:Q486972 .} # -> timeout
filter not exists { ?item wdt:P625 [] .}
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de,en' }
} limit 10000
Todo:
- füge hinzu coordinate location (P625), überprüfe located in the administrative territorial entity (P131); überprüfe instance of (P31)
aus archINFORM importierte Gebäude ohne Koordinaten[edit]
~1800, User:Arch2all als Beiträger & Verursacher
#title: imported from archINFORM in Austria without coordinates
SELECT distinct ?item ?itemLabel ?itemDescription WHERE {
?item wdt:P17 wd:Q40 . # Österreich
?item wdt:P5383 [] .
#?item wdt:P131*/wdt:P279* wd:Q1741 . # liegt in einer Verwaltungseinheit in Österreich
#?item wdt:P131 wd:Q1741 . # liegt in einer Verwaltungseinheit in Österreich
#?item wdt:P31/wdt:P279* wd:Q41176 .
#{?item wdt:P31*/wdt:P279* wd:Q271669 .} union {?item wdt:P31*/wdt:P279* wd:Q486972 .} # -> timeout
#VALUES ?isA {wd:Q16970} # landform settlement
filter not exists { ?item wdt:P625 [] .}
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de,en' }
}
Objekte ohne Koordinaten in einer Region / Gemeinde[edit]
#title: items by region without coordinates
SELECT ?item ?itemLabel ?itemDescription WHERE {
?item wdt:P17 wd:Q40 . # Österreich
?item wdt:P131*/wdt:P279* wd:Q684847 . # liegt in einer Verwaltungseinheit in Österreich (hier Gemeinde Molln)
#?item wdt:P31*/wdt:P279* ?isA .
#VALUES ?isA {wd:Q271669 wd:Q486972} # landform settlement
#{?item wdt:P31*/wdt:P279* wd:Q271669 .} union {?item wdt:P31*/wdt:P279* wd:Q486972 .} # -> timeout
filter not exists { ?item wdt:P625 [] .}
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} limit 10000
Todo:
- füge hinzu coordinate location (P625), überprüfe located in the administrative territorial entity (P131); überprüfe instance of (P31)
Objekte mit Koordinaten im Gebiet von Österreich[edit]
#title: get objects located in the area of Austria without assignment to a state
#defaultView:Map{"hide":"?rgb"}
SELECT ?item ?itemLabel ?itemDescription (GROUP_CONCAT(DISTINCT ?whereLabel; SEPARATOR=', ') AS ?whereLabels) (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
#?item wdt:P31/wdt:P279* ?isA .
?item wdt:P31 ?isA .
VALUES ?isA {wd:Q355304 wd:Q3257686 wd:Q41176} # Fließgewässer # Ortschaft # Gebäude
FILTER NOT EXISTS { ?item wdt:P17 [] . }
?item p:P625 [
psv:P625 [
wikibase:geoLatitude ?lat ;
wikibase:geoLongitude ?lon ;
] ;
ps:P625 ?coord
]
FILTER (?lat > 46.3719 && ?lat < 49.022 && ?lon > 9.5604 && ?lon < 17.1618) # surrounding rectangle
minus {?item wdt:P31/wdt:P279* wd:Q15284}
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q169514 } # imported from Wikimedia project: Swedish Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q837615 } # imported from Wikimedia project: Cebuano Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P248 wd:Q1194038 } # stated in: GEOnet Names Server
OPTIONAL {
?item wdt:P131 ?where .
OPTIONAL {
?where rdfs:label ?whereLiteral .
FILTER(LANG(?whereLiteral) = 'de') .
}
}
BIND(IF(BOUND(?where), IF(BOUND(?whereLiteral), ?whereLiteral, STRAFTER(STR(?where), 'entity/')), 'no P131') AS ?whereLabel) .
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE],de,en' }
} GROUP BY ?item ?itemLabel ?itemDescription ?whereLabels ?coord ?rgb ?layer
Todo:
- füge hinzu country (P17) (sei nett zu den Nachbarn), überprüfe located in the administrative territorial entity (P131); überprüfe instance of (P31)
Objekte mit Koordinaten in einer Region, aber nicht dem Staat zugeordnet[edit]
#title: get objects located in P131 in Austria but not in Austria
#defaultView:Map{"hide":"?rgb"}
SELECT ?item ?itemLabel ?itemDescription (GROUP_CONCAT(DISTINCT ?whereLabel; SEPARATOR=', ') AS ?whereLabels) (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
#?item wdt:P31/wdt:P279* ?isA .
?item wdt:P131*/wdt:P279* ?bundesland .
values ?bundesland {wd:Q43210 wd:Q37985 wd:Q42497 wd:Q41967 wd:Q43325 wd:Q41358 wd:Q42880 wd:Q38981 wd:Q1741}. # alle 9
FILTER NOT EXISTS { ?item wdt:P17 [] . }
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q169514 } # imported from Wikimedia project: Swedish Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q837615 } # imported from Wikimedia project: Cebuano Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P248 wd:Q1194038 } # stated in: GEOnet Names Server
OPTIONAL {
?item wdt:P131 ?where .
OPTIONAL {
?where rdfs:label ?whereLiteral .
FILTER(LANG(?whereLiteral) = 'de') .
}
}
BIND(IF(BOUND(?where), IF(BOUND(?whereLiteral), ?whereLiteral, STRAFTER(STR(?where), 'entity/')), 'no P131') AS ?whereLabel) .
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?whereLabels ?coord ?rgb ?layer
Todo:
- füge hinzu country (P17) (sei nett zu den Nachbarn), überprüfe das Objekt
Alle Objekte in AT mit Koordinaten[edit]
#defaultView:Map{"hide":"?rgb"}
#title: all items in Austria with coordinates
SELECT ?item ?itemLabel ?itemDescription (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
?item wdt:P17 wd:Q40 .
?item wdt:P131* wd:Q40 .
?item wdt:P625 [] .
minus {?item wdt:P31 wd:Q35779580.} # möglicherweise ungültiger Eintrag
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P625 ?coord.
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?coord ?rgb ?layer ORDER BY ASC(?item)
Alle Objekte in AT mit Koordinaten und ohne instance of (P31)[edit]
#defaultView:Map{"hide":"?rgb"}
#title: all items in Austria with coordinates
SELECT ?item ?itemLabel ?itemDescription (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
?item wdt:P17 wd:Q40 .
?item wdt:P131* wd:Q40 .
?item wdt:P625 [] .
filter not exists {?item wdt:P31 []}
minus {?item wdt:P31 wd:Q35779580.} # möglicherweise ungültiger Eintrag
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P625 ?coord.
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?coord ?rgb ?layer ORDER BY ASC(?item)
Objekte ohne Label in Deutsch[edit]
(mit und ohne Koordinaten)
#title: label de missing
SELECT DISTINCT ?item ?itemLabel ?itemDescription ?labelDe ?labelAt ?labelEn WHERE {
?item wdt:P17 wd:Q40 .
?item wdt:P625 []. # with coordinates
minus {
?item rdfs:label ?labelDe .
FILTER(LANG(?labelDe) = 'de') .
}
minus {
?item rdfs:label ?labelAt .
FILTER(LANG(?labelAt) = 'de-at') .
}
OPTIONAL {
?item rdfs:label ?labelEn .
FILTER(LANG(?labelEn) = 'en') .
}
}
todo:
- Label deutsch ergänzen (übersetzen)
Suche nach Label & Description[edit]
#defaultView:Map{"hide":"?rgb"}
#title: wildcard search in labels & description
SELECT ?item ?itemLabel ?itemDescription (GROUP_CONCAT(DISTINCT ?whereLabel; SEPARATOR=', ') AS ?whereLabels) (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
bind ("steinbach" as ?pattern).
?item wdt:P17 wd:Q40 .
#?item wdt:P31/wdt:P279* wd:Q355304. # Fließgewässer
?item wdt:P625 [] .
?item rdfs:label ?itemLabel;
schema:description ?description;
FILTER (LANG(?itemLabel) = "de").
FILTER (LANG(?description) = "de").
FILTER (regex(?itemLabel, ?pattern, "i") || regex(?description, ?pattern, "i")).
MINUS {?item wdt:P31 wd:Q261543 }.
Minus {?item wdt:P31 wd:Q406957 }.
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q169514 } # imported from Wikimedia project: Swedish Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q837615 } # imported from Wikimedia project: Cebuano Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P248 wd:Q1194038 } # stated in: GEOnet Names Server
OPTIONAL {
?item wdt:P131 ?where .
OPTIONAL {
?where rdfs:label ?whereLiteral .
FILTER(LANG(?whereLiteral) = 'de') .
}
}
BIND(IF(BOUND(?where), IF(BOUND(?whereLiteral), ?whereLiteral, STRAFTER(STR(?where), 'entity/')), 'no P131') AS ?whereLabel) .
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?whereLabels ?coord ?rgb ?layer
Objekte die keiner Gemeinde zugeordnet sind[edit]
mit Koordinaten (in Kärnten) und ohne sinnvollen Artikel[edit]
#defaultView:Map{"hide":"?rgb"}
#title: items in Carinthia with coordinates and missing municipality without WP:de articles, but WP:sv or WP:ceb
SELECT ?item ?itemLabel ?itemDescription (GROUP_CONCAT(DISTINCT ?whereLabel; SEPARATOR=', ') AS ?whereLabels) (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
?item wdt:P17 wd:Q40 .
?item wdt:P131* wd:Q37985 . # Kärnten
#?item wdt:P131*/wdt:P279* wd:Q980 . # by
?item wdt:P31 [] .
?item wdt:P625 [] .
?item wdt:P131 ?wo .
minus {?item wdt:P31*/wdt:P279* wd:Q667509 .} # not is a gemeinde at
minus {?item wdt:P31 wd:Q35779580.} # möglicherweise ungültiger Eintrag
# minus {?item wdt:P31 wd:Q355304 .} # not a fließgewässer
minus {?wo wdt:P31 wd:Q667509 .} # not a municipality (e.g. statutarstadt) at
?sitelink schema:about ?item .
FILTER NOT EXISTS {
?article schema:about ?item .
?article schema:isPartOf <https://de.wikipedia.org/> . #Targeting Wikipedia language where subjects has no article.
}
FILTER EXISTS {
?article schema:about ?item .
{?article schema:isPartOf <https://sv.wikipedia.org/> .} #Targeting Wikipedia language where subjects has no article.
union
{?article schema:isPartOf <https://ceb.wikipedia.org/> .} #Targeting Wikipedia language where subjects has no article.
}
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q169514 } # imported from Wikimedia project: Swedish Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P143 wd:Q837615 } # imported from Wikimedia project: Cebuano Wikipedia
#MINUS { ?coordStatement prov:wasDerivedFrom/pr:P248 wd:Q1194038 } # stated in: GEOnet Names Server
OPTIONAL {
?item wdt:P131 ?where .
OPTIONAL {
?where rdfs:label ?whereLiteral .
FILTER(LANG(?whereLiteral) = 'de') .
}
}
BIND(IF(BOUND(?where), IF(BOUND(?whereLiteral), ?whereLiteral, STRAFTER(STR(?where), 'entity/')), 'no P131') AS ?whereLabel) .
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?whereLabels ?coord ?rgb ?layer ORDER BY ASC(?item)
done:
ohne Koordinaten[edit]
#title: items in Austria without coordinates and missing municipality
SELECT DISTINCT ?item ?itemLabel ?itemDescription WHERE {
?item wdt:P17 wd:Q40 .
#?item wdt:P131* wd:Q37985 . # Kärnten
#?item wdt:P131*/wdt:P279* wd:Q980 . # by
?item wdt:P31 [] .
FILTER NOT EXISTS { ?item wdt:P625 [] . }
?item wdt:P131 ?wo .
minus {?item wdt:P31*/wdt:P279* wd:Q667509 .} # not is a gemeinde at
minus {?item wdt:P31 wd:Q35779580.} # möglicherweise ungültiger Eintrag
# minus {?item wdt:P31 wd:Q355304 .} # not a fließgewässer
minus {?wo wdt:P31 wd:Q667509 .} # not a municipality (e.g. statutarstadt) at
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription
todo:
- located in the administrative territorial entity (P131) auf die Gemeinde setzen (ev. zwei bei Grenzobjekten), Bundesland und Bezirk entfernen (nur die unterste Verwaltungsebene und das ist in AT die Gemeinde)
Grenzlage[edit]
muss von beiden Seiten der Grenze geprüft werden um Objekte zu finden
- an der Grenze, die nicht beiden Gebieten zugeordnet sind (country (P17), located in the administrative territorial entity (P131))
- die innerhalb der Grenze liegen (können ignoriert werden)
- die außerhalb der eigenen Grenze liegen (falsches Land/Gebiet oder Duplikat)
- die außerhalb der fremden Grenze liegen (beim Gegencheck), wie oben
ganz Österreich[edit]
#defaultView:Map{"hide":"?rgb"}
#title: Border check (with distinct types, less timeout)
SELECT ?item ?itemLabel ?itemDescription ?coord ?anz ?rgb ?layer WITH {
SELECT DISTINCT ?item (COUNT(DISTINCT ?country) as ?anz) WHERE {
hint:Query hint:optimizer "None".
?item wdt:P17 wd:Q215 . #16
#?item wdt:P131*/wdt:P279* wd:Q980 . # in case of timelimit restrict to sub unit
?item wdt:P31 ?isA .
VALUES ?isA {wd:Q8502 wd:Q16887036 wd:Q2231510 wd:Q10862618 wd:Q35666 wd:Q23397 wd:Q133056} # distinct border related types
?item wdt:P625 [] .
?item wdt:P17 ?country .
}
GROUP BY ?item
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
BIND(IF(?anz = 1, '60ff60', 'ff0000') AS ?rgb) .
BIND(IF(?anz = 1, 'inside (single P17)', 'border (multiple P17)') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?coord ?anz ?rgb ?layer # ORDER BY ASC(?item)
Molln[edit]
Grenzlage Gemeinde Molln[edit]
#defaultView:Map{"hide":"?rgb"}
#title: Border check on municipality level
SELECT ?item ?itemLabel ?itemDescription ?coord ?anz ?rgb ?layer WITH {
SELECT DISTINCT ?item (COUNT(DISTINCT ?unit) as ?anz) WHERE {
hint:Query hint:optimizer "None".
?item wdt:P17 wd:Q40 . #16
?item wdt:P131*/wdt:P279* wd:Q684847.
?item wdt:P31 ?isA .
#VALUES ?isA {wd:Q8502 wd:Q16887036 wd:Q2231510 wd:Q10862618 wd:Q35666 wd:Q23397 wd:Q133056} # distinct border related types
?item wdt:P625 [] .
?item wdt:P131 ?unit .
}
GROUP BY ?item
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
BIND(IF(?anz = 1, '60ff60', 'ff0000') AS ?rgb) .
BIND(IF(?anz = 1, 'inside (single P17)', 'border (multiple P17)') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?coord ?anz ?rgb ?layer # ORDER BY ASC(?item)
Umgebung über Karthographer (sparql) / nicht in Molln (dann aber doch)[edit]
Diese Lösung um die Shape von Molln mitzusehen.
Objekte mit Koordinaten im Gemeindegebiet Molln[edit]
#defaultView:Map{"hide":"?rgb"}
SELECT ?item ?itemLabel ?itemDescription (GROUP_CONCAT(DISTINCT ?whereLabel; SEPARATOR=', ') AS ?whereLabels) (SAMPLE(?image) AS ?image) ?coord ?rgb ?layer WITH {
SELECT DISTINCT ?item WHERE {
?item wdt:P17|wdt:P131* wd:Q684847 .
}
} AS %subquery1 WHERE {
INCLUDE %subquery1 .
?item wdt:P31 [] .
?item p:P625 ?coordStatement .
?coordStatement ps:P625 ?coord .
OPTIONAL {
?item wdt:P131 ?where .
OPTIONAL {
?where rdfs:label ?whereLiteral .
FILTER(LANG(?whereLiteral) = 'de') .
}
}
BIND(IF(BOUND(?where), IF(BOUND(?whereLiteral), ?whereLiteral, STRAFTER(STR(?where), 'entity/')), 'no P131') AS ?whereLabel) .
OPTIONAL { ?item wdt:P18 ?image }
BIND(IF(EXISTS { ?item p:P18 [] }, '0000ff', 'ff0000') AS ?rgb) .
BIND(IF(EXISTS { ?item p:P18 [] }, 'With image', 'Without image') AS ?layer) .
SERVICE wikibase:label { bd:serviceParam wikibase:language '[AUTO_LANGUAGE,de,en]' }
} GROUP BY ?item ?itemLabel ?itemDescription ?whereLabels ?coord ?rgb ?layer ORDER BY ASC(?item)