Querying: Difference between revisions

Revision as of 16:42, 24 August 2023

SPARQL is a powerful query language, but it is also quite complex. New users may find it easiest to use some of the sample queries below and adapt them to their needs.

Namespace

Run SPARQL queries via the Ottgaz Query Service.

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

Status count

Which regions have the most status changes entered in Ottgaz? run

SELECT (COUNT (DISTINCT ?statement) AS ?statementCount) (COUNT (DISTINCT ?status) AS ?statusCount) ?regionLabel
WHERE 
{
 ?region ogp:P15 ?statement.
 ?statement ogps:P15 ?status.
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}
GROUP BY ?regionLabel
ORDER BY desc(?statementCount)
LIMIT 100

Sancak seats by vilayet and year

This shows a tree map with the Ottoman language names of sancak seats within each vilayet in the year 1700. Seat names direct to items. Change the year in line 23. run

#defaultView:TreeMap
SELECT  ?vilayetLabel ?sancakSeat_ota ?sancakSeat
WHERE 
{
 ?sancak ogp:P15 ?statement.
 ?statement ogps:P15 og:Q4.
 ?statement ogpq:P7 ?starttime.
 ?statement ogpq:P9 ?vilayet.
 ?sancak ogt:P14 ?sancakSeat.
 OPTIONAL{?statement ogpq:P8 ?endtime.}
 OPTIONAL{?statement ogpq:P22 ?endtime.}
 ?sancakSeat rdfs:label ?sancakSeat_ota filter (lang(?sancakSeat_ota) = "ota")
 #enter same year twice
 FILTER(YEAR(?starttime) <= 1700 && YEAR(?endtime) > 1700)
 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
  }
}

Fetch missing geocoordinates from Wikidata

This query is not working yet. [test og:Q287 og:Q497 og:Q6421]

Help

After trying the samples, if you need more help formulating queries, try:

working through the Wikidata SPARQL tutorial
searching Wikidata sample queries and adapting them to Ottgaz
asking for help on the discussion page
asking for help on Stack Overflow or the wikibase Telegram group

Cleanup queries

These queries are useful to find information gaps to be filled when tidying the dataset.

Missing labels

Returns labels in English, Ottoman, and Arabic. Sort results to find blanks. run

SELECT ?seat ?seat_en ?seat_ota ?seat_ar
WHERE 
{
 ?seat ogt:P6 og:Q6421.
 
 OPTIONAL {?seat rdfs:label ?seat_en filter (lang(?seat_en) = "en")}.
 OPTIONAL {?seat rdfs:label ?seat_ota filter (lang(?seat_ota) = "ota")}.  
 OPTIONAL {?seat rdfs:label ?seat_ar filter (lang(?seat_ar) = "ar")}.  
 
}

Seats without geolocation

Wikidata ID can be used to supply geolocation. run

SELECT ?sancak ?sancakLabel ?seat ?seatLabel ?wikidata 
WHERE 
{
 ?sancak ogt:P15 og:Q4.
 ?sancak ogt:P14 ?seat.
 MINUS {?seat ogt:P10 ?geo.}
 OPTIONAL {?seat ogt:P3 ?wikidata.}  
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Seats without regions

During the initial setup, certain regions listed seats, but there was no reciprocal listing of region for the seat items. This query includes date qualifiers, where present. run

SELECT ?region ?regionLabel ?seat ?seatLabel ?starttime ?endtime ?latestdate
WHERE 
{
 ?region ogt:P14 ?seat.
 MINUS {?seat ogt:P11 ?region.}
 OPTIONAL {
   ?region ogp:P14 ?statement.
   ?statement ogps:P14 ?seat.
   ?statement ogpq:P7 ?starttime.
      OPTIONAL { ?statement ogpq:P8 ?endtime.}
      OPTIONAL { ?statement ogpq:P22 ?latestdate.}
 }
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

Regions without seats

Inverse of the above. run

Regions without status statements

Useful for finding errors in bulk uploads run

SELECT ?region ?regionLabel
WHERE 
{
 ?region ogt:P6 og:Q1.
 MINUS {?region ogp:P15 ?statement.}
 SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en" }
}

NSF ID geolocation

run

@@ Line 191: / Line 191: @@
     }
   }
+=== Fetch missing geocoordinates from Wikidata ===
+This query is not working yet. [test og:Q287 og:Q497 og:Q6421]
 == Help ==

Querying: Difference between revisions

Revision as of 16:42, 24 August 2023

Contents

Namespace

Sample queries

Label

Duration, start and end dates

Number of kazas created each year

Count status types in a given year

Number of kazas between 1800 and 1900

Sancak status ending between 1830 and 1840

Map of sanjak seats

Status count

Sancak seats by vilayet and year

Federated query with Wikidata

Fetch missing geocoordinates from Wikidata

Help

Cleanup queries

Missing labels

Seats without geolocation

Seats without regions

Regions without seats

Regions without status statements

NSF ID geolocation

Navigation menu

Querying: Difference between revisions

Revision as of 16:42, 24 August 2023

Namespace

Sample queries

Label

Duration, start and end dates

Number of kazas created each year

Count status types in a given year

Number of kazas between 1800 and 1900

Sancak status ending between 1830 and 1840

Map of sanjak seats

Status count

Sancak seats by vilayet and year

Federated query with Wikidata

Fetch missing geocoordinates from Wikidata

Help

Cleanup queries

Missing labels

Seats without geolocation

Seats without regions

Regions without seats

Regions without status statements

NSF ID geolocation

Navigation menu

Search