User:Bouzinac/Fréquentations

Useful tools to edit patronages on airports elements

A tabular tool: https://en.wikipedia.org/wiki/ru:%D0%92%D0%9F:WE-F

A plain text data importer: https://www.wikidata.org/wiki/Help:QuickStatements

Modèle d'importation
!Item wikidata concerné	Clientèle	Valeur Clientèle	Date	Valeur Date	Source	Valeur Source
Q3661908	P3872	37181907	P585	+2021-00-00T00:00:00Z/9	S854	"https://www.dhmi.gov.tr/Lists/IstatisliklerDiger/Attachments/18/T%C3%9CRK%C4%B0YE%20GENEL%C4%B0%20%C4%B0STAT%C4%B0ST%C4%B0KLER%C4%B0.xlsx"
Q1433143	P3872	52	P585	+2021-00-00T00:00:00Z/9	S854	"https://www.dhmi.gov.tr/Lists/IstatisliklerDiger/Attachments/18/T%C3%9CRK%C4%B0YE%20GENEL%C4%B0%20%C4%B0STAT%C4%B0ST%C4%B0KLER%C4%B0.xlsx"

A plain text data importer with some integrated tools to identify which ID stands for the airport: https://www.wikidata.org/wiki/Wikidata:Tools/OpenRefine/

Patronage of (x) airport(s)

SELECT ?item ?itemLabel ?year
(sample(?number) as ?number)
WHERE {
&nbsp;values ?item {wd:Q17430 wd:Q642313 wd:Q17431} .
&nbsp;?item p:P3872 ?statement.
&nbsp;?statement pq:P585 ?time.
&nbsp;?statement ps:P3872 ?number.
&nbsp;bind (YEAR(?time) AS ?year)
 FILTER(?year>=1979) .
&nbsp;SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
&nbsp;MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel
order by ?item desc (?year)

Try it!

Patronage of (x) airport(s) in a country

SELECT ?item ?itemLabel ?year
(sample(?number) as ?number)
WHERE {
&nbsp;values ?item {wd:Q17430 wd:Q642313 wd:Q17431} .
&nbsp;?item p:P3872 ?statement.
&nbsp;?statement pq:P585 ?time.
&nbsp;?statement ps:P3872 ?number.
&nbsp;BIND (YEAR(?time) AS ?year)
 FILTER(?year>=1979) .
&nbsp;SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
&nbsp;MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel
order by ?item desc (?year)

Try it!

Fréquentation/Patronage of (x) airport(s) in an administrative région

SELECT ?item ?itemLabel ?year
(sample(?number) as ?number)
WHERE {
 ?item p:P131/ps:P131 wd:Q30971.
 ?item p:P3872 ?statement.
 ?statement pq:P585 ?time.
 ?statement ps:P3872 ?number.
 BIND (YEAR(?time) AS ?year)
 FILTER(?year>=1979) .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
 MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel
order by ?item desc (?year)

Try it!

Fréquentation/patronage of (x) airports with international ,domestic, total stat

SELECT ?item ?itemLabel ?year ?whereLabel
(sample(?number) as ?number)
WHERE {
 values ?item {wd:Q1049719} .
 ?item p:P3872 ?statement.
 ?statement pq:P585 ?time.
 ?statement pq:P518 ?where.
 ?statement ps:P3872 ?number.
 bind (YEAR(?time) AS ?year)
 FILTER(?year>=1979) .
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
 MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel ?where ?whereLabel
order by ?item desc (?year)

Try it!

patronage of airport with specific IATA code

SELECT ?item ?itemLabel (max(?number) as ?passengers) 
?year
with {
  select ?item ?statement ?year ?timevalue ?numberperperiod{
    ?item wdt:P238 ?IATA
 VALUES ?IATA {"ICN"}.
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue
               ; ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date .
    optional { ?statement pq:P518 ?applies .}
filter(bound(?applies)=false || ?applies = wd:Q2165236 )
bind(if(bound(?applies)=false,"no applies","everywhere") as ?where )
MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
bind (YEAR(?date) AS ?year)
FILTER (?year >1949).
FILTER (?year < year(now())).
}
} as %airport
where {
  {
select ?item ?year (sum(?numberperperiod) as ?number) {
include %airport .
?timevalue wikibase:timePrecision ?prec filter ( ?prec > 9 )
} group by ?item  ?year
  } union {
      ?timevalue wikibase:timePrecision 9 .
    bind (?numberperperiod as ?number)
      include %airport
  } .
    SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
} group by ?item ?itemLabel ?year
order by ?item desc (?year)

Try it!

Fréquentation/patronage of (x) airports with only total passenger stat

SELECT ?item ?itemLabel ?year ?where
(sample(?number) as ?number)
WHERE {
 values ?item {wd:Q1049719} .
 ?item p:P3872 ?statement.
 ?statement pq:P585 ?time.
 ?statement pq:P518 ?where.
 ?statement ps:P3872 ?number.
 bind (YEAR(?time) AS ?year)
 FILTER(?year>=1979) .
 filter(?where= wd:Q2165236).
 SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
 MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
} group by ?year ?item ?itemLabel ?where ?whereLabel
order by ?item desc (?year)

Try it!

Fréquentation patronage of x airports that have data for 2017 but not 2018

SELECT ?item ?itemLabel ?pays ?paysLabel (SUM(?number) AS ?numbers) WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784;
    p:P3872 ?statement.
  ?statement pq:P585 ?value.
   ?statement ps:P3872 ?number.
  FILTER(("2016-12-31"^^xsd:dateTime < ?value) && ("2018-00-00"^^xsd:dateTime > ?value))
  MINUS {
    ?item p:P3872 ?statement2.
    ?statement2 pq:P585 ?value2.
    FILTER(("2017-12-31"^^xsd:dateTime < ?value2) && ("2019-00-00"^^xsd:dateTime > ?value2))
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
  OPTIONAL { ?item wdt:P17 ?pays. }
}
GROUP BY ?item ?itemLabel ?pays ?paysLabel
ORDER BY DESC(?numbers)

Try it!

Patronage of airport(s) that have data for 2018 but not 2019

SELECT ?item ?itemLabel ?pays ?paysLabel (max(?value) as ?DateMax) (SUM(?number) AS ?numbers) WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784;
    p:P3872 ?statement.
  ?statement pq:P585 ?value.
   ?statement ps:P3872 ?number.
  FILTER(("2017-12-31"^^xsd:dateTime < ?value) && ("2019-00-00"^^xsd:dateTime > ?value))
  MINUS {
    ?item p:P3872 ?statement2.
    ?statement2 pq:P585 ?value2.
    FILTER(("2018-12-31"^^xsd:dateTime < ?value2) && ("2020-00-00"^^xsd:dateTime > ?value2))
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
  OPTIONAL { ?item wdt:P17 ?pays. }
}
GROUP BY ?item ?itemLabel ?pays ?paysLabel
ORDER BY DESC(?paysLabel) desc(?numbers)

Try it!

Patronage of airport(s) that have data for 2019 but not 2020

SELECT ?item ?itemLabel ?pays ?paysLabel (max(?value) as ?DateMax) (SUM(?number) AS ?numbers) WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784;
    p:P3872 ?statement.
  ?statement pq:P585 ?value.
   ?statement ps:P3872 ?number.
  FILTER(("2018-12-31"^^xsd:dateTime < ?value) && ("2020-00-00"^^xsd:dateTime > ?value))
  MINUS {
    ?item p:P3872 ?statement2.
    ?statement2 pq:P585 ?value2.
    FILTER(("2019-12-31"^^xsd:dateTime < ?value2) && ("2021-00-00"^^xsd:dateTime > ?value2))
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
  OPTIONAL { ?item wdt:P17 ?pays. }
}
GROUP BY ?item ?itemLabel ?pays ?paysLabel
ORDER BY DESC(?paysLabel) desc(?numbers)

Try it!

Patronage of airport(s) that have data for 2020 but not 2021

SELECT ?item ?itemLabel ?pays ?paysLabel (max(?value) as ?DateMax) (SUM(?number) AS ?numbers) WHERE {
  ?item wdt:P31/wdt:P279* wd:Q1248784;
         wdt:P5817 wd:Q55654238;
    p:P3872 ?statement.
  ?statement pq:P585 ?value; ps:P3872 ?number.
  FILTER(("2019-12-31"^^xsd:dateTime < ?value) && ("2021-00-00"^^xsd:dateTime > ?value))
  MINUS {
    ?item p:P3872 ?statement2.
    ?statement2 pq:P585 ?value2.
    FILTER(("2020-12-31"^^xsd:dateTime < ?value2) && ("2022-00-00"^^xsd:dateTime > ?value2))
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],fr,en". }
  OPTIONAL { ?item wdt:P17 ?pays. }
}
GROUP BY ?item ?itemLabel ?pays ?paysLabel
ORDER BY DESC(?paysLabel) desc(?numbers)

Try it!

Patronage of airport(s) that have data for 2021 but not 2022

https://w.wiki/6WQq

Has every airport 12 month values?

SELECT ?year ?item ?itemLabel ?number_of_months ?first_month ?last_month ?have_value_for_year
WITH
{
  SELECT ?item ?statement ?date ?year ?timevalue ?numberperperiod ?reference_URL
  WHERE
  {
?item (wdt:P31/wdt:P279*) wd:Q62447.
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue;
               ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date.
    OPTIONAL { ?statement pq:P518 ?applies. }
    OPTIONAL { ?statement prov:wasDerivedFrom / (pr:P854|pr:P4656) ?reference_URL. }
    FILTER (BOUND(?applies)=false || ?applies = wd:Q2165236 )
    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    BIND (YEAR(?date) AS ?year)
    FILTER (?year=2020).
  }
} AS %airport
WHERE
{
  {
    SELECT ?item (COUNT(DISTINCT ?date) AS ?number_of_months) (MAX(?date) AS ?last_month) (MIN(?date) AS ?first_month)
    WHERE
    {
      INCLUDE %airport
      ?timevalue wikibase:timePrecision 10 . # Precicision is month
    }
    GROUP BY ?item
    HAVING (?number_of_months != 12)
  }
  OPTIONAL
  {
    {
      SELECT ?item
      WHERE
      {
        INCLUDE %airport
        ?timevalue wikibase:timePrecision 9 . # Precicision is year
      }
      GROUP BY ?item
    }
    BIND ("yes" AS ?have_value_for_year)
  } 
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}

Try it!

2020 rank (known values)

# Scroll down and hit blue arrow down to run and see the results + the sources
SELECT ?item ?itemLabel (MAX(?number) AS ?passengers)
WITH
{
  SELECT ?item ?statement ?date ?year ?timevalue ?numberperperiod ?reference_URL
  WHERE
  {
    ?item wdt:P238 ?airport_code.
    ?item p:P3872 ?statement.
    ?statement pqv:P585 ?timevalue;
               ps:P3872 ?numberperperiod.
    ?timevalue wikibase:timeValue ?date.
    OPTIONAL { ?statement pq:P518 ?applies. }
    OPTIONAL { ?statement prov:wasDerivedFrom / (pr:P854|pr:P4656) ?reference_URL. }
    FILTER (BOUND(?applies)=false || ?applies = wd:Q2165236 )
    MINUS { ?statement wikibase:rank wikibase:DeprecatedRank }
    BIND (YEAR(?date) AS ?year)
    FILTER (?year =2020).
  }
} AS %airport
WHERE
{
  {
    # Get the sum of monthly values within a year
    SELECT ?item ?year (SUM(?max_numberperperiod) AS ?number) (SAMPLE(?monthly_reference_URL) AS ?monthly_reference_URL2)
    WHERE
    {
      # Get the maximal value and a sample reference URL for each unique month
      {
        SELECT ?item ?year (MAX(?numberperperiod) AS ?max_numberperperiod) (SAMPLE(?reference_URL) AS ?monthly_reference_URL)
        WHERE
        {
          INCLUDE %airport
          ?timevalue wikibase:timePrecision ?prec.
          FILTER (?prec > 9)# precision more precise or equal to month
        }
        GROUP BY ?item ?year ?date
      }
    }
    GROUP BY ?item ?year
  }
  UNION
  {
    ?timevalue wikibase:timePrecision 9 .
    BIND (?numberperperiod AS ?number)
    BIND (?reference_URL AS ?sample_reference_URL)
    INCLUDE %airport
  }
  SERVICE wikibase:label { bd:serviceParam wikibase:language "fr". }
}
GROUP BY ?item ?itemLabel ?year
ORDER BY DESC (?passengers)
limit 20

Try it!