User:Aeroid/SPARQL Notes

Nationalparks

[1]

Fragments

without german articles

  	# look for articles (sitelinks) in English ("de")
  	OPTIONAL { ?sitelink schema:about ?item . ?sitelink schema:inLanguage "de" } 
	# but select items with no such article
	FILTER (!BOUND(?sitelink))
 

within Hamburg

The upwards and downwards tree. It collects all territories that have a claim via P131-chains up to Hamburg or(!) via P150-chain down from any of them.

  ?adminTree (wdt:P131)* wd:Q1055 .
  ?adminTree (wdt:P150)* ?territory .
  ?statement wdt:P131 ?territory .
 

The straight forward way. Only those that explicitly claim P131-chains up to Hamburg (ignoring teh reverse via P150). This seems to be very expensive and causes timeout in many combinations.

  ?statement wdt:P131* wd:Q1055 .
 

Alterative notation but also straight forward. Same as the one directly above, but seems not cause performance issues.

  ?statement wdt:P131/wdt:P131* wd:Q1055 .
 

beware of Ranks

The Query Service will make use of ranks. Using one of the latter two options allows you to rely on ranking in the P131*-tree to ommit former relations with normal ranks, if the current one is set to preferred. (see Statement types)

See also Alternative query, via Q30 and P6.

Items with claims using qualifiers about Hamburg and its subdivisions

The following query uses these:

• Items: Hamburg (Q1055)   

• Properties: located in the administrative territorial entity (P131)   

  SELECT ?item ?itemLabel ?p WHERE {
    ?statement pq:P131/wdt:P131* wd:Q1055 .
    ?item ?p ?statement
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

The following query uses these:

• Items: Hamburg (Q1055)   

• Properties: located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   

  SELECT DISTINCT ?item ?itemLabel ?p WHERE {
    ?adminTree (wdt:P131)* wd:Q1055 .
    ?adminTree (wdt:P150)* ?territory .
    ?statement pq:P131 ?territory .
    ?item ?p ?statement
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Lists about Hamburg and its subdivisions

The following query uses these:

• Items: Hamburg (Q1055)   , Wikimedia list article (Q13406463)   

• Properties: located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   , instance of (P31)   

  SELECT DISTINCT ?item ?itemLabel WHERE {
    ?adminTree (wdt:P131)* wd:Q1055 .
    ?adminTree (wdt:P150)* ?territory .
    ?statement pq:P131 ?territory .
    ?item ?p ?statement .
    ?item wdt:P31 wd:Q13406463
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Categories about Hamburg and its subdivisions

The following query uses these:

• Items: Hamburg (Q1055)   , Wikimedia category (Q4167836)   

• Properties: located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   , category combines topics (P971)   , instance of (P31)   

  SELECT DISTINCT ?item ?itemLabel WHERE {
    ?adminTree (wdt:P131)* wd:Q1055 .
    ?adminTree (wdt:P150)* ?territory .
    ?item wdt:P971 ?territory .
    ?item wdt:P31 wd:Q4167836
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Located in Harburg

The following query uses these:

• Items: Harburg (Q5907)   

• Properties: located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   , applies to jurisdiction (P1001)   

  SELECT DISTINCT ?item ?itemLabel WHERE {  
    ?adminTree (wdt:P131)* wd:Q5907 .
    ?adminTree (wdt:P150)* ?territory .
    {
      ?item wdt:P131 ?territory . 
    } UNION { 
      ?item wdt:P1001 ?territory .
    } 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Categories and Lists about Hamburg and subdivisons

The following query uses these:

• Items: Hamburg (Q1055)   , Wikimedia category (Q4167836)   , Wikimedia list article (Q13406463)   

• Properties: located in the administrative territorial entity (P131)   , instance of (P31)   , category's main topic (P301)   , category combines topics (P971)   

  SELECT DISTINCT ?item ?itemLabel WHERE {
    ?territory (wdt:P131)* wd:Q1055 .
    {
      ?item wdt:P31 wd:Q4167836 .
      {
        ?item wdt:P301 ?territory
      } UNION {
        ?item wdt:P971 ?territory
      }
    } UNION {
      ?item wdt:P31 wd:Q13406463 .
      ?item ?p ?statement .
      ?statement pq:P131 ?territory
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Located in and Categories and Lists about Harburg and subdivisons

The following query uses these:

• Items: Harburg (Q5907)   , Wikimedia category (Q4167836)   , Wikimedia list article (Q13406463)   

• Properties: located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   , applies to jurisdiction (P1001)   , instance of (P31)   , category's main topic (P301)   , category combines topics (P971)   

  SELECT DISTINCT ?item ?itemLabel WHERE {
    ## only upward relation P131 which might miss items if the P131-tree is incomplete
    ?territory (wdt:P131)* wd:Q5907 .
    ## alternative: also use downward relation P150 which might lead to unwanted items from former adminstrative relations
    # ?adminTree (wdt:P131)* wd:Q5907 .
    # ?adminTree (wdt:P150)* ?territory .
    ##
    {  
      ?item wdt:P131 ?territory # anything located in 
    } UNION { 
      ?item wdt:P1001 ?territory # or within territorial jurisdiction
    } UNION {
      ?item wdt:P31 wd:Q4167836 . # categories
      {
        ?item wdt:P301 ?territory # with topic
      } UNION {
        ?item wdt:P971 ?territory # or combining topic with something
      }
    } UNION {
      ?item wdt:P31 wd:Q13406463 . #lists
      ?item ?p ?statement .
      ?statement pq:P131 ?territory # with statements having qualifiers located in 
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

Cultural heritage monuments in Hamburg

The following query uses these:

• Items: Wikimedia category (Q4167836)   , cultural heritage monument in Germany (Q11691318)   , Wikimedia list article (Q13406463)   , Hamburg (Q1055)   , heritage site (Q358)   

• Properties: instance of (P31)   , category combines topics (P971)   , is a list of (P360)   , applies to jurisdiction (P1001)   , located in the administrative territorial entity (P131)   , contains the administrative territorial entity (P150)   , heritage designation (P1435)   

  SELECT DISTINCT ?item ?itemLabel WHERE {
    {
      { 
        ?item wdt:P31 wd:Q4167836.
        ?item wdt:P971 wd:Q11691318.
        ?item wdt:P971 ?territory.
      } UNION  {
        ?item wdt:P31 wd:Q13406463.
        ?item wdt:P360 wd:Q11691318 .
        ?item wdt:P1001 wd:Q1055 .
      }
    } UNION  { 
      ?adminTree (wdt:P131)* wd:Q1055 .
      ?adminTree (wdt:P150)* ?territory .
      ?item wdt:P131 ?territory
      {
        ?item wdt:P1435 wd:Q11691318 
      } UNION  {
        ?item wdt:P1435 wd:Q358
      }
    }
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?itemLabel )
  

Try it!

German adminstrative units that where abolished in the last 365 days

The following query uses these:

• Items: administrative divisions of Germany (Q387917)   

• Properties: instance of (P31)   , subclass of (P279)   , inception (P571)   , dissolved, abolished or demolished date (P576)   

  SELECT DISTINCT ?event ?eventLabel ?date
  WHERE
  {
  	# find admin territories of Germany
  	?event wdt:P31/wdt:P279* wd:Q387917.
          # with founding/inception date
  	#OPTIONAL { ?event wdt:P571 ?date. }
          # abolished date
  	OPTIONAL { ?event wdt:P576 ?date. }
  	# but at least one of those
  	FILTER(BOUND(?date) && DATATYPE(?date) = xsd:dateTime).
  	# calculate age (days, negative are in the future)
  	BIND(NOW() - ?date AS ?age).
  	FILTER(0 <= ?age && ?age < 365).
  	# and get a label as well
  	SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?eventLabel )
  # limit to 100 results so we don't timeout
  LIMIT 100
  

Try it!

Solving the problem of former P131 to interfere with lists

P131 with No Value and have been abolished

The following query uses these:

• Items: administrative territorial entity (Q56061)   

• Properties: instance of (P31)   , subclass of (P279)   , dissolved, abolished or demolished date (P576)   , located in the administrative territorial entity (P131)   

  SELECT DISTINCT ?territory ?territoryLabel ?date WHERE
  {
    # administrative territorial entity
    ?territory wdt:P31/wdt:P279* wd:Q56061 .
    # with a statement defined to be "no value" as P131
    ?territory rdf:type wdno:P131 .
    # abolished date
    ?territory wdt:P576 ?date . 
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  } ORDER BY ( ?territoryLabel )
  # avoid timeout
  LIMIT 100
  

Try it!

Gemeindeschlüssel DE-NI

The following query uses these:

• Properties: German municipality key (P439)   , located in the administrative territorial entity (P131)   , instance of (P31)   , country (P17)   

  SELECT DISTINCT ?_Amtlicher_Gemeindeschluessel ?itemLabel ?typeLabel ?liegtinLabel ?stateLabel ?item WHERE {
    ?item wdt:P439 ?_Amtlicher_Gemeindeschluessel.
    FILTER(REGEX(?_Amtlicher_Gemeindeschluessel, "^03"))
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
    OPTIONAL {
      ?item wdt:P131 ?liegtin .
      ?item wdt:P31 ?type .
      ?item wdt:P17 ?state .
    }
  } ORDER BY ( ?_Amtlicher_Gemeindeschluessel )
  

Try it!

siehe [2], [3], [4] (LSN-Online: Tabelle A100001G Landesamt für Statistik Niedersachsen, Bevölkerung und Katasterfläche 1) in Niedersachsen (Gebietsstand: 1.1.2015))

Einwohnerzahlen mit Datum

The following query uses these:

• Items: Brunswick (Q2773)   

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

  SELECT ?population ?pointInTime WHERE {
     wd:Q2773 p:P1082 ?statement.
     OPTIONAL {
       ?statement psv:P1082/wikibase:quantityAmount  ?population.
       ?statement pq:P585 ?pointInTime.
     }
     SERVICE wikibase:label {
       bd:serviceParam wikibase:language "en" .
     }
  } ORDER BY ( ?pointInTime )
  

Try it!

Brücken in Deutschland sortiert nach normalisierter Länge

siehe [5]

The following query uses these:

• Items: bridge (Q12280)   

• Properties: instance of (P31)   , subclass of (P279)   , date of official opening (P1619)   , inception (P571)   , start time (P580)   , length (P2043)   , longest span (P2787)   

  SELECT DISTINCT (ROUND(?normalizedLengthMeter) AS ?length) (ROUND(?normalizedLongestSpanMeter) AS ?span) ?opening ?inception ?start ?itemLabel ?item WHERE {
    ?item wdt:P31/wdt:P279* wd:Q12280.
    ?item p:P2043/psn:P2043/wikibase:quantityAmount ?normalizedLengthMeter.
    OPTIONAL { ?item p:P2787/psn:P2787/wikibase:quantityAmount ?normalizedLongestSpanMeter. }
    OPTIONAL { ?item wdt:P1619 ?opening. }.
    OPTIONAL { ?item wdt:P571 ?inception. }.
    OPTIONAL { ?item wdt:P580 ?start. }.
    SERVICE wikibase:label { bd:serviceParam wikibase:language "de" }
  }
  ORDER BY DESC( ?normalizedLengthMeter )
  

Try it!

Städte mit normalisierter Fläche

siehe List vun de gröttsten Städer in Mekelnborg-Vörpommern. wikibase:BestRank sorgt hier für "Truthy" Properties, da es hier durchaus Mehrfachangaben zu den geänderten Flächen gibt. Über das Row-Template werden aus Quadratmetern Quadratkilometer und Einheit. Leider noch mal Dezimalpunkt, statt -komma.

The following query uses these:

• Items: Mecklenburg-Western Pomerania (Q1196)   , municipality in Germany (Q262166)   

• Properties: located in the administrative territorial entity (P131)   , instance of (P31)   , subclass of (P279)   , population (P1082)   , area (P2046)   

  SELECT DISTINCT ?item ?itemLabel ?_population ?normalizedAreaSquaremeter WHERE {
   ?item wdt:P131 ?territory.
   ?territory wdt:P131* wd:Q1196.
   ?item (wdt:P31/wdt:P279*) wd:Q262166.
   ?item wdt:P1082 ?_population.
   SERVICE wikibase:label { bd:serviceParam wikibase:language "nds". }
   OPTIONAL {
     ?item p:P2046 [
       a wikibase:BestRank;
       psn:P2046/wikibase:quantityAmount ?normalizedAreaSquaremeter
     ].
   }
  }
  ORDER BY DESC(?_population)
  LIMIT 25
  

Try it!