Querying: Difference between revisions

From ottgaz.org
Jump to navigation Jump to search
(map of sancak seats)
(8 intermediate revisions by the same user not shown)
Line 18: Line 18:


=== Label ===
=== Label ===
All items that are vilayets, as well as their English language label. [https://tinyurl.com/2pren5e2 run]
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 ogt:P6 og:Q5 .  
   ?vilayet ogt:P15 og:Q5.
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
  }
  }
Line 78: Line 78:
  ORDER BY desc(?count)
  ORDER BY desc(?count)


===How many kazas between 1800 and 1900?===
===Number of kazas between 1800 and 1900===
Not working yet. 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]
*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]


  SELECT ?number (count(*) as ?count)  {
PREFIX wd: <http://www.wikidata.org/entity/>
  ?year ogt:P6 og:Q15677 ; ogt:P24 ?number
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
  FILTER (?number >= 1800 && ?number <= 1900)
  SELECT ?number (count(*) as ?count)  {
  ?kaza ogt:P15 og:Q3.
  SERVICE <https://query.wikidata.org/sparql> {
  ?kaza ogp:P15 ?statement.
  ?year wdt:P31 wd:Q21199 ; wdt:P1181 ?number
  ?statement ogps:P15 og:Q3.
  FILTER (?number >= 1750 && ?number <= 1760)
}
  ?vilayet ogt:P15 og:Q5.
  ?vilayet ogp:P15 ?statement.
  ?statement ogps:P15 og:Q5.
  ?statement ogpq:P7 ?starttime.
  ?statement ogpq:P7 ?starttime.
  OPTIONAL{?statement ogpq:P8 ?endtime.}
  OPTIONAL{?statement ogpq:P8 ?endtime.}
Line 93: Line 97:
  BIND (YEAR(COALESCE(?endtime, NOW())) AS ?year2)
  BIND (YEAR(COALESCE(?endtime, NOW())) AS ?year2)
  FILTER (?number >= ?year1 && ?number <= ?year2)
  FILTER (?number >= ?year1 && ?number <= ?year2)
  }  
  }
  group by ?number
  group by ?number
  order 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 ==
== 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/2ehssccv run]
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 og: <https://ottgaz.org/entity/>
Line 108: Line 143:
  SELECT ?sancak ?sancakLabel ?vilayet ?vilayetLabel
  SELECT ?sancak ?sancakLabel ?vilayet ?vilayetLabel
  WHERE {
  WHERE {
   ?sancak ogt:P6 og:Q4 . # item must be a sanjak
   ?sancak ogt:P15 og:Q4 . # item must be a sanjak
   ?sancak ogt:P4 ?c . # finds reference URL to wikidata
   ?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
   BIND(IRI(REPLACE(REPLACE(REPLACE(STR(?c), "Property:", ""), "/wiki/", "/entity/"), "https://", "http://")) AS ?wd_c) # produces proper URL for query form

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
  }
}