User:Smalyshev (WMF)/Countries and States

Regions

Get administrative subdivisions of certain country with their: label, flag and wikipedia page.

• Items used: United States of America (Q30), province (Q34876), state (Q7275)

• Properties used: located in the administrative territorial entity (P131), instance of (P31), subclass of (P279), country (P17), flag image (P41)

SELECT REDUCED ?id ?idLabel 
	   (SAMPLE(?flagImg) as ?flagImg)
	   (SAMPLE(?page) as ?page)
WHERE {
  # Configurable parameters: 
  # ?country is a country
  BIND(wd:Q30 as ?country)
  # ?types is administrative subdivisions we seek for this country
  VALUES ?types { wd:Q34876 wd:Q7275 }
  # No serviceable parts beyond this point
  # Located in the administrative territorial entity of the whole country, but not sub-entity
  ?id wdt:P131 ?country .
  # We want it to be of this type in a country, and have a capital
  FILTER EXISTS { ?id (wdt:P31/wdt:P279*) ?types ;
                          wdt:P17 ?country . }
  # Excluding the country itself
  FILTER(?id != ?country)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?id wdt:P41 ?flagImg }
  OPTIONAL {
      ?page schema:about ?id .
      ?page schema:isPartOf <https://en.wikipedia.org/> .
  }
} GROUP BY ?id ?idLabel

Try it!

Regional Capitals

Get capitals of the administrative subdivisions of given country, with: label, population, location, image and wikipedia page. Note that one administrative entity can have more than one capital (rare, but happens)

• Items used: South Africa (Q258), province (Q34876), state (Q7275)

• Properties used: located in the administrative territorial entity (P131), instance of (P31), subclass of (P279), country (P17), capital (P36), coordinate location (P625), image (P18), population (P1082)

SELECT REDUCED ?id ?idLabel
       (SAMPLE(?location) as ?location)
       (SAMPLE(?image) as ?image)
       (SAMPLE(?population) as ?population)
       (SAMPLE(?page) as ?page)
WHERE {
  # Configurable parameters: 
  # ?country is a country
  BIND(wd:Q258 as ?country)
  # ?types is administrative subdivisions we seek for this country
  VALUES ?types { wd:Q34876 wd:Q7275 }
  # No serviceable parts beyond this point
  # Located in the administrative territorial entity of the whole country, but not sub-entity
  ?region wdt:P131 ?country .
  # We want it to be of this type in a country, and have a capital
  FILTER EXISTS { ?region (wdt:P31/wdt:P279*) ?types ;
                          wdt:P17 ?country . }
  # Excluding the country itself
  FILTER(?region != ?country)
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  ?region wdt:P36 ?id .
  ?id wdt:P625 ?location .
  OPTIONAL { ?id wdt:P18 ?image }
  OPTIONAL { ?id wdt:P1082 ?population }
  OPTIONAL {
      ?page schema:about ?id .
      ?page schema:isPartOf <https://en.wikipedia.org/> .
  }
} GROUP BY ?id ?idLabel

Try it!

Neighbors

Get countries that given country borders, with their: label, flag image, and Wikipedia page. The result includes the country itself.

• Items used: South Africa (Q258), historical country (Q3024240), country (Q6256)

• Properties used: shares border with (P47), instance of (P31), dissolved, abolished or demolished date (P576), flag image (P41)

SELECT REDUCED ?id ?idLabel 
	   (SAMPLE(?flagImg) as ?flagImg)
	   (SAMPLE(?page) as ?page)
WHERE {
  # Configurable parameters: 
  # ?country is a country
  BIND(wd:Q258 as ?country)
  # No serviceable parts beyond this point
  ?country wdt:P47? ?id .
  # exclude former countries
  FILTER NOT EXISTS { ?id wdt:P31 wd:Q3024240 }
  FILTER NOT EXISTS { ?id wdt:P576 ?date }
  FILTER EXISTS { ?id wdt:P31 wd:Q6256 }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  OPTIONAL { ?id wdt:P41 ?flagImg }
  OPTIONAL {
      ?page schema:about ?id .
      ?page schema:isPartOf <https://en.wikipedia.org/> .
  }
} GROUP BY ?id ?idLabel

Try it!

Neighboring capitals

Capitals of this and neighboring countries. Note that country can have more than one capital (rare but happens).

• Items used: Germany (Q183), historical country (Q3024240), country (Q6256)

• Properties used: shares border with (P47), instance of (P31), dissolved, abolished or demolished date (P576), capital (P36), coordinate location (P625), image (P18), population (P1082)

SELECT REDUCED ?id ?idLabel
       (SAMPLE(?location) as ?location)
       (SAMPLE(?image) as ?image)
       (SAMPLE(?population) as ?population)
       (SAMPLE(?page) as ?page)
WHERE {
  # Configurable parameters: 
  # ?country is a country
  BIND(wd:Q183 as ?country)
  # No serviceable parts beyond this point
  ?country wdt:P47? ?idObj .
  # exclude former countries
  FILTER NOT EXISTS { ?idObj wdt:P31 wd:Q3024240 }
  FILTER NOT EXISTS { ?idObj wdt:P576 ?date }
  FILTER EXISTS { ?idObj wdt:P31 wd:Q6256 }
  ?idObj wdt:P36 ?id .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
  ?id wdt:P625 ?location .
  OPTIONAL { ?id wdt:P18 ?image }
  OPTIONAL { ?id wdt:P1082 ?population }
  OPTIONAL {
      ?page schema:about ?id .
      ?page schema:isPartOf <https://en.wikipedia.org/> .
  }
} GROUP BY ?id ?idLabel

Try it!

Population by year

Population of a given place by year. Each year will have only one population figure.

• Items used: London (Q84)

• Properties used: population (P1082), point in time (P585)

SELECT (MAX(?population) as ?population) ?year WHERE {
    # set the item here - e.g. city, country
	BIND(wd:Q84 as ?item)
    ?item p:P1082 ?popstatement .
    ?popstatement ps:P1082 ?population .
    OPTIONAL {
      ?popstatement pq:P585 ?date .
      BIND(year(?date) as ?year)
    }
} GROUP BY ?year ORDER BY DESC(?year)

Try it!