GOTExample2022

From BITPlan Wiki
Jump to navigation Jump to search

Game of Thrones / Jon Snow Example

A starting point for analyzing the data in a triplestore such as wikidata might be a single item of interest such as the 160px-Game_of_Thrones_2011_logo.svg.png character Jon Snow instance ofGame of thrones character

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

# 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
item itemLabel genderLabel countryLabel placeOfBirthLabel placeOfDeathLabel
http://www.wikidata.org/entity/Q259818 Eddard Stark male Seven Kingdoms Winterfell King's Landing
http://www.wikidata.org/entity/Q1120793 Theon Greyjoy male Seven Kingdoms Pyke Winterfell
http://www.wikidata.org/entity/Q2941743 Catelyn Stark female Seven Kingdoms Riverrun Twins
http://www.wikidata.org/entity/Q3183235 Jon Snow male Seven Kingdoms Tower of Joy Castle Black
http://www.wikidata.org/entity/Q3665163 Cersei Lannister female Seven Kingdoms Casterly Rock Red Keep
http://www.wikidata.org/entity/Q3806180 Jaime Lannister male Seven Kingdoms Casterly Rock Red Keep
http://www.wikidata.org/entity/Q11680202 Beric Dondarrion male Seven Kingdoms The Stormlands Winterfell
http://www.wikidata.org/entity/Q12056060 Stannis Baratheon male Seven Kingdoms Storm's End The North
http://www.wikidata.org/entity/Q12815803 Rickon Stark male Seven Kingdoms Winterfell The North
http://www.wikidata.org/entity/Q12900597 Joffrey Baratheon male Seven Kingdoms King's Landing King's Landing
http://www.wikidata.org/entity/Q12900933 Margaery Tyrell female Seven Kingdoms Highgarden Great Sept of Baelor
http://www.wikidata.org/entity/Q12902445 Tywin Lannister male Seven Kingdoms Casterly Rock King's Landing
http://www.wikidata.org/entity/Q12902594 Viserys Targaryen male Seven Kingdoms King's Landing Vaes Dothrak
http://www.wikidata.org/entity/Q13634884 Robb Stark male Seven Kingdoms Riverrun Twins
http://www.wikidata.org/entity/Q13634885 Robert Baratheon male Seven Kingdoms Storm's End King's Landing
http://www.wikidata.org/entity/Q17355556 Janos Slynt male Seven Kingdoms King's Landing The North
http://www.wikidata.org/entity/Q18920137 Loras Tyrell male Seven Kingdoms Highgarden Great Sept of Baelor
http://www.wikidata.org/entity/Q19791067 Lysa Arryn female Seven Kingdoms Riverrun Eyrie
http://www.wikidata.org/entity/Q23730371 Hoster Tully male Seven Kingdoms Riverrun Winterfell
http://www.wikidata.org/entity/Q23747242 Shireen Baratheon female Seven Kingdoms Dragonstone The North
http://www.wikidata.org/entity/Q23759020 Rickard Stark male Seven Kingdoms Winterfell King's Landing
http://www.wikidata.org/entity/Q23759067 Benjen Stark male Seven Kingdoms Winterfell Beyond the Wall
http://www.wikidata.org/entity/Q23759071 Lyanna Stark female Seven Kingdoms Winterfell Tower of Joy
http://www.wikidata.org/entity/Q23782360 Brynden Tully male Seven Kingdoms Riverrun Riverrun
http://www.wikidata.org/entity/Q23782368 Walder Frey male Seven Kingdoms Twins Twins
http://www.wikidata.org/entity/Q24814689 Aemon Targaryen male Seven Kingdoms King's Landing Castle Black
http://www.wikidata.org/entity/Q28468107 Doran Martell male Seven Kingdoms Sunspear Dorne
http://www.wikidata.org/entity/Q55433701 Jojen Reed male Seven Kingdoms Greywater Watch Beyond the Wall

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

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.

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!