query-30378203b2047c03a2aee1638cf14a50
HASC Data to SQL-Command
Use at
- https://query.wikidata.org/sparql
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX bd: <http://www.bigdata.com/rdf#>
#-----------------------------------------------------
# HASC Data to SQL-Command
#-----------------------------------------------------
#defaultView:Table
SELECT ?hasc ?item ?itemLabel ?itemDescription ?countryLabel ?osm
(CONCAT("update hasc set wikidata='",?item_str,"' where hasc_code='", ?hasc,"';") as ?sql_cmd)
WHERE
{
BIND( STRAFTER(str(?item), 'http://www.wikidata.org/entity/') AS ?item_str) . # QID in Text umgewandeln
?item wdt:P8119 ?hasc . # hasc
FILTER(REGEX(STR(?hasc), "^US.[A-Z]{2}$","i"))
#OPTIONAL { ?item wdt:P625 ?coordinate. } # coordinate
OPTIONAL { ?item wdt:P17 ?country. } # country
OPTIONAL { ?item wdt:P402 ?osm. } # osm
SERVICE wikibase:label { bd:serviceParam wikibase:language "de". }
}
order by ?hasc
Query found at
graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
v4("?country")
v1("?hasc"):::projected
v3("?item"):::projected
v2("?item_str"):::projected
v5("?osm"):::projected
v6("?sql_cmd")
c9(["de"]):::literal
c7(["bd:serviceParam"]):::iri
f0[["regex(str(?hasc),'^US.#91;A-Z#93;{2}$','i')"]]
f0 --> v1
bind1[/"substring-after(str(?item),'http://www.wikidata.org/entity/')"/]
v3 --o bind1
bind1 --as--o v2
v3 --"wdt:P8119"--> v1
subgraph optional0["(optional)"]
style optional0 fill:#bbf,stroke-dasharray: 5 5;
v3 -."wdt:P17".-> v4
end
subgraph optional1["(optional)"]
style optional1 fill:#bbf,stroke-dasharray: 5 5;
v3 -."wdt:P402".-> v5
end
subgraph s1["http://wikiba.se/ontology#label"]
style s1 stroke-width:4px;
c7 --"wikibase:language"--> c9
end
bind2[/"concat('update hasc set wikidata='',?item_str,'' where hasc_code='',?hasc,'';')"/]
v2 --o bind2
v1 --o bind2
bind2 --as--o v6