GOTExample2022

Game of Thrones / Jon Snow Example[edit]

A starting point for analyzing the data in a triplestore such as wikidata might be a single item of interest such as the character Jon Snow instance ofGame of thrones character

GOTCountQuery[edit]

SPARQL Query to count Game of Thrones character items in Wikidata

SELECT (COUNT(DISTINCT ?item) AS ?count) WHERE 
{
  # Q20086263:Game of Thrones character
  ?item wdt:P31 wd:Q20086263;
}

try it! The above SPARQL query will let us know the total amount of similar items we might find in Wikidata. which is 117 at the time of writing this work.

A quite natural idea is to create a query now to get a table of all Game of thrones characters with columns for the most interesting aspects.

As a starting point we might use the Wikidata Query Service and select the sample "Cats" and refine it for our purposes with some properties we find on Jon Snows page to create a "naive" query:

NaiveGOTQuery1[edit]

# Game of Thrones Characters
SELECT
  ?item ?itemLabel
  ?genderLabel
  ?countryLabel
  ?placeOfBirthLabel
  ?placeOfDeathLabel
WHERE
{
  ?item wdt:P31 wd:Q20086263. # must be a game of thrones character
  ?item wdt:P21 ?gender.
  ?item wdt:P27 ?country.
  ?item wdt:P19 ?placeOfBirth.
  ?item wdt:P20 ?placeOfDeath.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

try it!

result[edit]

The result of this query is nicely tabular and can be exported to CSV format to be imported to our favorite relational database or spreadsheet - but it does not contain 117 entries any more but only 28. SPARQL queries will not return "NULL" values as our relational database query language SQL would. The query needs to be amended with the "OPTIONAL" keyword as shown below.

NaiveGOTQuery2[edit]

Naive Game of Thrones Character tabular query with OPTIONAL

# Game of Thrones Characters
SELECT
  ?item ?itemLabel
  ?genderLabel
  ?countryLabel
  ?placeOfBirthLabel
  ?placeOfDeathLabel
WHERE
{
  ?item wdt:P31 wd:Q20086263. # must be a game of thrones character
  OPTIONAL { ?item wdt:P21 ?gender}.
  OPTIONAL { ?item wdt:P27 ?country}.
  OPTIONAL { ?item wdt:P19 ?placeOfBirth}.
  OPTIONAL { ?item wdt:P20 ?placeOfDeath}.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

try it!

This table looks promising it has 117 entries and the columns are filled as expected.

The real surprise for a novice will be the result of the Query below when three more columns have been added. All over sudden the query result has 304 rows for our 117 characters. There are duplicate rows for each character and strange combinations of entries. A notorious example seems to be Jorah Mormont having 8 different values for the "occupation" column.

NaiveGOTQuery3[edit]

Naive Game of Thrones Character tabular query with duplicate rows for some characters

# Game of Thrones Characters
SELECT
  ?item ?itemLabel
  ?genderLabel
  ?countryLabel
  ?placeOfBirthLabel
  ?placeOfDeathLabel
  ?killedByLabel
  ?familyLabel
  ?occupationLabel
WHERE
{
  ?item wdt:P31 wd:Q20086263. # must be a game of thrones character
  OPTIONAL { ?item wdt:P21 ?gender}.
  OPTIONAL { ?item wdt:P27 ?country}.
  OPTIONAL { ?item wdt:P19 ?placeOfBirth}.
  OPTIONAL { ?item wdt:P20 ?placeOfDeath}.
  OPTIONAL { ?item wdt:P157 ?killedBy}.
  OPTIONAL { ?item wdt:P53 ?family}.
  OPTIONAL { ?item wdt:P106 ?occupation}.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}

try it!