0

I am trying to get a list countries including the english short names:

# get a list countries with the corresponding ISO code
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
SELECT ?country ?countryLabel ?shortName (MAX(?pop) as ?population) ?coord ?isocode
WHERE 
{
  # instance of country
  ?country wdt:P31 wd:Q3624078.
  OPTIONAL {
     ?country rdfs:label ?countryLabel filter (lang(?countryLabel) = "en").
   }
  OPTIONAL {
    # https://www.wikidata.org/wiki/Property:P1813
    ?country wdt:P1813 ?shortName.
  }   
  OPTIONAL { 
    # get the population
     # https://www.wikidata.org/wiki/Property:P1082
     ?country wdt:P1082 ?pop. 
  }
  # get the iso countryCode
  { ?country wdt:P297 ?isocode }.
  # get the coordinate
  OPTIONAL { ?country wdt:P625 ?coord }.
} 
GROUP BY ?country ?countryLabel ?shortName ?population ?coord ?isocode 
ORDER BY ?countryLabel

try it!

Unfortunately also flags and non english versions of "shortName" are returned. I tried using a subquery but that timed out. I'd like to avoid using the wikibase label service since I need to run the query on my local wikidata copy which uses Apache Jena

How could i get the english shortnames of countries? E.g. China for People's republic of china and USA for United States of America?

Wolfgang Fahl
  • 15,016
  • 11
  • 93
  • 186
  • why can't you use a `filter (lang(?shortName) = "en")` in the `optional` pattern then? Is there something unexpected or missing then? – UninformedUser Sep 07 '20 at 08:00
  • @UninformedUser - your hint is already very helpful. Some results will then still contain flags or be ambigous but still the result is better than my attempt. – Wolfgang Fahl Sep 07 '20 at 08:02
  • Hm, can you point me to the still wrong results? Some do have multiple names, yes (in that case you could use aggregate function `max` or `sample` – UninformedUser Sep 07 '20 at 08:06
  • ah, ok. `Maldives` - well, that's weird, but the flag has unfortunately an English language tag – UninformedUser Sep 07 '20 at 08:06
  • Try this pattern instead please: `OPTIONAL { # https://www.wikidata.org/wiki/Property:P1813 ?country p:P1813 ?shortNameStmt. # get the statement ?shortNameStmt ps:P1813 ?shortName filter (lang(?shortName) = "en") # ignore emojis aka flags filter not exists {?shortNameStmt pq:P31 wd:Q28840786} } ` - it will omit flags by checking for statement qualifiers of type `P31 wd:Q28840786` – UninformedUser Sep 07 '20 at 08:12
  • could you please post your comment as a complete answer so that i can upvote it. – Wolfgang Fahl Sep 07 '20 at 08:13

1 Answers1

3

There are two issues here:

  1. we need to filter for the English short names only, i.e. we need a filter (lang(?shortName) = "en") clause inside the second OPTIONAL pattern
  2. for some reason, there are some flags having an English language tag, so we have to ignore those somehow - the good thing, there is a statement qualifier that helps here: an instance of (P31) relation to the Wikidata entity emoji flag sequence (Q28840786)

So, overall, we replace

OPTIONAL {
    # https://www.wikidata.org/wiki/Property:P1813
    ?country wdt:P1813 ?shortName.
}

by

OPTIONAL {
  ?country p:P1813 ?shortNameStmt. # get the short name statement
  ?shortNameStmt ps:P1813 ?shortName # the the short name value from the statement
  filter (lang(?shortName) = "en") # filter for English short names only
  filter not exists {?shortNameStmt pq:P31 wd:Q28840786} # ignore flags (aka emojis)
}

Still, there will be multiple entries for some countries because of multiple short names. One way to workaround this is to use some aggregate function like sample or min/max and pick just a single short name per country.

UninformedUser
  • 8,397
  • 1
  • 14
  • 23
  • you are using prefixes that are not defined in the original query. When I tried to adapt the prefixes the query would not work on my apache jena. – Wolfgang Fahl Sep 08 '20 at 06:42
  • see https://stackoverflow.com/questions/63788703/query-works-on-wikidata-query-service-but-not-on-apache-jena-copy – Wolfgang Fahl Sep 08 '20 at 07:04