Querying: Difference between revisions
No edit summary |
(map of sancak seats) |
||
(25 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
Run SPARQL queries via the | Run SPARQL queries via the [https://ottgaz.org/query/ Ottgaz Query Service]. | ||
'''Remember:''' SPARQL is currently case sensitive, as elastic search is disabled in wikibase.cloud. | '''Remember:''' SPARQL is currently case sensitive, as elastic search is disabled in wikibase.cloud. | ||
Line 7: | Line 7: | ||
Declare these namespaces at the start of every query: | Declare these namespaces at the start of every query: | ||
PREFIX | PREFIX og: <https://ottgaz.org/entity/> | ||
PREFIX | PREFIX ogs: <https://ottgaz.org/entity/statement/> | ||
PREFIX ogv: <https://ottgaz.org/value/> | |||
PREFIX ogt: <https://ottgaz.org/prop/direct/> | |||
PREFIX ogp: <https://ottgaz.org/prop/> | |||
PREFIX ogps: <https://ottgaz.org/prop/statement/> | |||
PREFIX ogpq: <https://ottgaz.org/prop/qualifier/> | |||
== Sample queries == | == Sample queries == | ||
=== Label === | === Label === | ||
All regions that had vilayet status, as well as their English language label. [https://tinyurl.com/2k4uq6u2 run] | |||
SELECT ?vilayet ?vilayetLabel | SELECT ?vilayet ?vilayetLabel | ||
WHERE | WHERE | ||
{ | { | ||
?vilayet wdt:P6 wd: | ?vilayet ogt:P15 og:Q5. | ||
?vilayet rdfs:label ?vilayetLabel . | SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } | ||
} | |||
=== Duration, start and end dates === | |||
Duration between start and end date of every region with a start date, as well as region type. [https://tinyurl.com/2k8h75bz run] | |||
SELECT ?regionLabel ?statusLabel (YEAR(?starttime) as ?start) (YEAR(?endtime) as ?end) ?duration | |||
WHERE | |||
{ | |||
?region ogt:P6 og:Q1. | |||
?region ogp:P15 ?statement. | |||
?statement ogps:P15 ?status. | |||
?statement ogpq:P7 ?starttime. | |||
OPTIONAL{?statement ogpq:P8 ?endtime.} | |||
OPTIONAL{?statement ogpq:P22 ?endtime.} | |||
BIND(year(?endtime)-year(?starttime) as ?duration ) | |||
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } | |||
} | |||
ORDER BY desc(?duration) | |||
=== Number of kazas created each year === | |||
In each year, how many kazas were inaugurated? [https://tinyurl.com/2z9gjrch run] | |||
SELECT ?year (COUNT (DISTINCT ?kaza) AS ?count) | |||
WHERE | |||
{ | |||
?kaza ogt:P15 og:Q3. | |||
?kaza ogp:P15 ?statement. | |||
?statement ogps:P15 og:Q3. | |||
?statement ogpq:P7 ?starttime. | |||
bind(year(?starttime) as ?year) | |||
} | |||
GROUP BY ?year | |||
ORDER BY desc(?count) | |||
===Count status types in a given year=== | |||
For a given year, how many regions of each status are found in the dataset? [https://tinyurl.com/2mqqhl8u run] | |||
SELECT (COUNT (DISTINCT ?region) AS ?count) ?statusLabel | |||
WHERE | |||
{ | |||
?region ogp:P15 ?statement. | |||
?statement ogps:P15 ?status. | |||
?statement ogpq:P7 ?starttime. | |||
OPTIONAL{?statement ogpq:P8 ?endtime.} | |||
OPTIONAL{?statement ogpq:P22 ?endtime.} | |||
#enter same year twice | |||
FILTER(YEAR(?starttime) <= 1600 && YEAR(?endtime) > 1600) | |||
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } | |||
} | |||
GROUP BY ?statusLabel | |||
ORDER BY desc(?count) | |||
===Number of kazas between 1800 and 1900=== | |||
*This query is not working yet.* The query below returns the number of vilayets between 1750 and 1760. Thanks to [https://stackoverflow.com/users/7879193/stanislav-kralin Stanislav Kralin] for [https://stackoverflow.com/questions/75137894/counting-items-over-time-using-sparql help]. [ run] | |||
PREFIX wd: <http://www.wikidata.org/entity/> | |||
PREFIX wdt: <http://www.wikidata.org/prop/direct/> | |||
SELECT ?number (count(*) as ?count) { | |||
SERVICE <https://query.wikidata.org/sparql> { | |||
?year wdt:P31 wd:Q21199 ; wdt:P1181 ?number | |||
FILTER (?number >= 1750 && ?number <= 1760) | |||
} | |||
?vilayet ogt:P15 og:Q5. | |||
?vilayet ogp:P15 ?statement. | |||
?statement ogps:P15 og:Q5. | |||
?statement ogpq:P7 ?starttime. | |||
OPTIONAL{?statement ogpq:P8 ?endtime.} | |||
OPTIONAL{?statement ogpq:P22 ?endtime.} | |||
BIND (YEAR(?starttime) AS ?year1) | |||
BIND (YEAR(COALESCE(?endtime, NOW())) AS ?year2) | |||
FILTER (?number >= ?year1 && ?number <= ?year2) | |||
} | |||
group by ?number | |||
order by ?number | |||
===Sancak status ending between 1830 and 1840=== | |||
This query will return the region more than once, if it undergoes status change more than once during this time period. [https://tinyurl.com/2ew6y2vw run] | |||
SELECT ?sancak ?sancakLabel (YEAR(?starttime) as ?start) (YEAR(?endtime) as ?end) | |||
WHERE | |||
{ | |||
?sancak ogt:P6 og:Q1. | |||
?sancak ogp:P15 ?statement. | |||
?statement ogps:P15 og:Q4. | |||
?statement ogpq:P7 ?starttime. | |||
OPTIONAL{?statement ogpq:P8 ?endtime.} | |||
OPTIONAL{?statement ogpq:P22 ?endtime.} | |||
FILTER(YEAR(?endtime) >= 1830 && YEAR(?endtime) <= 1840) | |||
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } | |||
} | |||
ORDER BY (?endtime) | |||
=== Map of sanjak seats === | |||
This shows only those sanjaks with seats designated and geolocated. [https://tinyurl.com/2hqpwoas run] | |||
#defaultView:Map | |||
SELECT ?sancakLabel ?seatLabel ?geo | |||
WHERE | |||
{ | |||
?sancak ogt:P15 og:Q4. | |||
?sancak ogt:P14 ?seat. | |||
OPTIONAL {?seat ogt:P10 ?geo.} | |||
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } | |||
} | |||
== Federated query with Wikidata == | |||
For every sanjak in Ottgaz, this query fetches the vilayet or eyalet to which Wikidata says it belongs. Thanks to Lucas Werkmeister for help. [https://tinyurl.com/2eokop65 run] | |||
PREFIX og: <https://ottgaz.org/entity/> | |||
PREFIX ogt: <https://ottgaz.org/prop/direct/> | |||
PREFIX wd: <http://www.wikidata.org/entity/> | |||
PREFIX wdt: <http://www.wikidata.org/prop/direct/> | |||
SELECT ?sancak ?sancakLabel ?vilayet ?vilayetLabel | |||
WHERE { | |||
?sancak ogt:P15 og:Q4 . # item must be a sanjak | |||
?sancak ogt:P4 ?c . # finds reference URL to wikidata | |||
BIND(IRI(REPLACE(REPLACE(REPLACE(STR(?c), "Property:", ""), "/wiki/", "/entity/"), "https://", "http://")) AS ?wd_c) # produces proper URL for query form | |||
SERVICE <https://query.wikidata.org/sparql> { | |||
?wd_c wdt:P131 ?vilayet . # finds containing vilayet in Wikidata | |||
SERVICE wikibase:label { | |||
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". | |||
?vilayet rdfs:label ?vilayetLabel. # finds label in Wikidata | |||
} | |||
} | |||
SERVICE wikibase:label { | |||
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". | |||
?sancak rdfs:label ?sancakLabel. # finds label in Ottgaz | |||
} | |||
} | } |
Revision as of 18:49, 15 March 2023
Run SPARQL queries via the Ottgaz Query Service.
Remember: SPARQL is currently case sensitive, as elastic search is disabled in wikibase.cloud.
Namespace
Declare these namespaces at the start of every query:
PREFIX og: <https://ottgaz.org/entity/> PREFIX ogs: <https://ottgaz.org/entity/statement/> PREFIX ogv: <https://ottgaz.org/value/> PREFIX ogt: <https://ottgaz.org/prop/direct/> PREFIX ogp: <https://ottgaz.org/prop/> PREFIX ogps: <https://ottgaz.org/prop/statement/> PREFIX ogpq: <https://ottgaz.org/prop/qualifier/>
Sample queries
Label
All regions that had vilayet status, as well as their English language label. run
SELECT ?vilayet ?vilayetLabel WHERE { ?vilayet ogt:P15 og:Q5. SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } }
Duration, start and end dates
Duration between start and end date of every region with a start date, as well as region type. run
SELECT ?regionLabel ?statusLabel (YEAR(?starttime) as ?start) (YEAR(?endtime) as ?end) ?duration WHERE { ?region ogt:P6 og:Q1. ?region ogp:P15 ?statement. ?statement ogps:P15 ?status. ?statement ogpq:P7 ?starttime. OPTIONAL{?statement ogpq:P8 ?endtime.} OPTIONAL{?statement ogpq:P22 ?endtime.} BIND(year(?endtime)-year(?starttime) as ?duration ) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } } ORDER BY desc(?duration)
Number of kazas created each year
In each year, how many kazas were inaugurated? run
SELECT ?year (COUNT (DISTINCT ?kaza) AS ?count) WHERE { ?kaza ogt:P15 og:Q3. ?kaza ogp:P15 ?statement. ?statement ogps:P15 og:Q3. ?statement ogpq:P7 ?starttime. bind(year(?starttime) as ?year) } GROUP BY ?year ORDER BY desc(?count)
Count status types in a given year
For a given year, how many regions of each status are found in the dataset? run
SELECT (COUNT (DISTINCT ?region) AS ?count) ?statusLabel WHERE { ?region ogp:P15 ?statement. ?statement ogps:P15 ?status. ?statement ogpq:P7 ?starttime. OPTIONAL{?statement ogpq:P8 ?endtime.} OPTIONAL{?statement ogpq:P22 ?endtime.} #enter same year twice FILTER(YEAR(?starttime) <= 1600 && YEAR(?endtime) > 1600) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } } GROUP BY ?statusLabel ORDER BY desc(?count)
Number of kazas between 1800 and 1900
- This query is not working yet.* The query below returns the number of vilayets between 1750 and 1760. Thanks to Stanislav Kralin for help. [ run]
PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> SELECT ?number (count(*) as ?count) { SERVICE <https://query.wikidata.org/sparql> { ?year wdt:P31 wd:Q21199 ; wdt:P1181 ?number FILTER (?number >= 1750 && ?number <= 1760) } ?vilayet ogt:P15 og:Q5. ?vilayet ogp:P15 ?statement. ?statement ogps:P15 og:Q5. ?statement ogpq:P7 ?starttime. OPTIONAL{?statement ogpq:P8 ?endtime.} OPTIONAL{?statement ogpq:P22 ?endtime.} BIND (YEAR(?starttime) AS ?year1) BIND (YEAR(COALESCE(?endtime, NOW())) AS ?year2) FILTER (?number >= ?year1 && ?number <= ?year2) } group by ?number order by ?number
Sancak status ending between 1830 and 1840
This query will return the region more than once, if it undergoes status change more than once during this time period. run
SELECT ?sancak ?sancakLabel (YEAR(?starttime) as ?start) (YEAR(?endtime) as ?end) WHERE { ?sancak ogt:P6 og:Q1. ?sancak ogp:P15 ?statement. ?statement ogps:P15 og:Q4. ?statement ogpq:P7 ?starttime. OPTIONAL{?statement ogpq:P8 ?endtime.} OPTIONAL{?statement ogpq:P22 ?endtime.} FILTER(YEAR(?endtime) >= 1830 && YEAR(?endtime) <= 1840) SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } } ORDER BY (?endtime)
Map of sanjak seats
This shows only those sanjaks with seats designated and geolocated. run
#defaultView:Map SELECT ?sancakLabel ?seatLabel ?geo WHERE { ?sancak ogt:P15 og:Q4. ?sancak ogt:P14 ?seat. OPTIONAL {?seat ogt:P10 ?geo.} SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" } }
Federated query with Wikidata
For every sanjak in Ottgaz, this query fetches the vilayet or eyalet to which Wikidata says it belongs. Thanks to Lucas Werkmeister for help. run
PREFIX og: <https://ottgaz.org/entity/> PREFIX ogt: <https://ottgaz.org/prop/direct/> PREFIX wd: <http://www.wikidata.org/entity/> PREFIX wdt: <http://www.wikidata.org/prop/direct/> SELECT ?sancak ?sancakLabel ?vilayet ?vilayetLabel WHERE { ?sancak ogt:P15 og:Q4 . # item must be a sanjak ?sancak ogt:P4 ?c . # finds reference URL to wikidata BIND(IRI(REPLACE(REPLACE(REPLACE(STR(?c), "Property:", ""), "/wiki/", "/entity/"), "https://", "http://")) AS ?wd_c) # produces proper URL for query form SERVICE <https://query.wikidata.org/sparql> { ?wd_c wdt:P131 ?vilayet . # finds containing vilayet in Wikidata SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?vilayet rdfs:label ?vilayetLabel. # finds label in Wikidata } } SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". ?sancak rdfs:label ?sancakLabel. # finds label in Ottgaz } }