Wikimania2019 book icon.svg

Want to learn more about Metabase, share your ideas or get help? You're welcome to the Metabase Office Hours and chat with the Wikimedia Sverige team!

Friday, August 23, 9am Swedish time (convert time zone) – join in Google Meet

Friday, August 23, 4pm Swedish time (convert time zone) – join in Google Meet

Project:SPARQL/examples: Difference between revisions

From MetaBase
Jump to navigation Jump to search
Line 171: Line 171:
   ?item wbt:P31 ?video.
   ?item wbt:P31 ?video.
   SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
   SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
</SPARQL>
==Presentations without a speaker==
<SPARQL tryit="1">
PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
MINUS {?item wbt:P33 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
}
</SPARQL>
</SPARQL>

Revision as of 10:34, 31 January 2024

Overview queries

All types of documents and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?wikidata_iri
WHERE
{
?item wbt:P4* wb:Q21.
OPTIONAL {?item wbt:P1 ?wikidataQ}
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


All types of activities and events and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?wikidata_iri
WHERE
{
?item wbt:P4* wb:Q8.
OPTIONAL {?item wbt:P1 ?wikidataQ}
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


All index terms and their Wikidata equivalents

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabelEN ?itemLabelSV ?wikidata_iri
WHERE
{
?item wbt:P5 wb:Q12 .
OPTIONAL {?item wbt:P1 ?wikidataQ}
OPTIONAL {?item rdfs:label ?itemLabelEN filter (lang(?itemLabelEN) = "en")}.
OPTIONAL {?item rdfs:label ?itemLabelSV filter (lang(?itemLabelSV) = "sv")}.
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)
}

Try it!

All properties and items with labels and descriptions in English and Swedish

PREFIX wd: <https://metabase.wikibase.cloud/entity/>
PREFIX wdt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabelEN ?itemLabelSV ?itemDescEN ?itemDescSV
WHERE
{
  OPTIONAL {?item rdfs:label ?itemLabelEN filter (lang(?itemLabelEN) = "en")}.
  OPTIONAL {?item rdfs:label ?itemLabelSV filter (lang(?itemLabelSV) = "sv")}.
  OPTIONAL {?item schema:description ?itemDescEN filter (lang(?itemDescEN) = "en")}.
  OPTIONAL {?item schema:description ?itemDescSV filter (lang(?itemDescSV) = "sv")}.
}

Try it!

Content uploads

All content uploads

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?commonscat
WHERE
{
?item wbt:P5 wb:Q22186 .
OPTIONAL {?item wbt:P37 ?commonscat}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,sv,[AUTO_LANGUAGE]". }
}

Try it!


All resource types

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{
?item wbt:P5 wb:Q22211 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,[AUTO_LANGUAGE]". }
}

Try it!


Documents

Documents in languages other than Swedish

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?language ?languageLabel
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P20 ?language.
MINUS {?item wbt:P20 wb:Q31}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Documents published in 2020

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?year
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P18 ?published.
BIND(str(YEAR(?published)) AS ?year)
FILTER (?year = "2020")
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


People and how many documents they have authored

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?author ?authorLabel (COUNT(?item) AS ?count)
WHERE
{
?item wbt:P5/wbt:P4* wb:Q21.
?item wbt:P19 ?author.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?author ?authorLabel
ORDER BY DESC (?count)

Try it!


Events

Most common locations for WMSE events

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:BubbleChart
SELECT ?location ?locationLabel (COUNT(DISTINCT ?item) AS ?count)
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 .
?item wbt:P27 ?location.
?item wbt:P14 wb:Q9.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?location ?locationLabel
ORDER BY DESC(?count)

Try it!


Number of events 2021-2022 and total number of participants

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT (count(distinct ?item) as ?total_events) (sum(?participants) as ?total_participants)
WHERE
{
  ?item wbt:P23 ?when.
  FILTER (?when > "2021-01-01"^^xsd:dateTime && ?when < "2022-12-31"^^xsd:dateTime)
  OPTIONAL{?item wbt:P28 ?participants.}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Video recordings of events of Wikimedia Sverige

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel ?video
WHERE
{
  ?item wbt:P14 wb:Q9.
  ?item wbt:P31 ?video.
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Presentations without a speaker

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
MINUS {?item wbt:P33 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Projects

Timeline of all projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:Timeline
SELECT ?item ?itemLabel ?start ?end
WHERE
{
?item wbt:P5 wb:Q2 .
?item wbt:P6 ?start.
?item wbt:P7 ?end.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Most common programmatic areas for WMSE projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?program ?programLabel (COUNT(DISTINCT ?item) AS ?count)
WHERE
{
?item wbt:P5 wb:Q2 .
?item wbt:P14 wb:Q9.
?item wbt:P10 ?program.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
GROUP BY ?program ?programLabel
ORDER BY DESC(?count)

Try it!


Federated queries

Map of all event locations

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
#defaultView:Map
SELECT DISTINCT ?item ?itemLabel ?place ?placeLabel ?coords
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 .
?item wbt:P27 ?place.
?place wbt:P1 ?wikidataQ.
BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
?wikidata_iri wdt:P625 ?coords.
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Documents authored by employees of Wikimedia Sverige (via Wikidata)

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?document ?documentLabel ?person ?personLabel
WHERE
{
?document wbt:P19 ?person.
?person wbt:P1 ?wikidataQ.

BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
?wikidata_iri wdt:P108 wd:Q15279144.
}

SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Organizations and their social media and official website (from Wikidata)

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?wikidata_iri ?url ?twitter ?instagram
WHERE
{
?item wbt:P5 wb:Q13 .
?item wbt:P1 ?wikidataQ.

BIND(URI(concat("http://www.wikidata.org/entity/", ?wikidataQ)) AS ?wikidata_iri)

SERVICE <https://query.wikidata.org/sparql> {
 OPTIONAL {?wikidata_iri wdt:P856 ?url.}
 OPTIONAL {?wikidata_iri wdt:P2002 ?twitter.}
 OPTIONAL {?wikidata_iri wdt:P2003 ?instagram.}
}

SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Maintenance queries

Root items requiring Wikidata equivalent without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5 wb:Q10.
MINUS {?item wbt:P1 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Events without indication whether they're online, in-person or hybrid

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel
WHERE
{
?item wbt:P5/wbt:P4* wb:Q40 .
MINUS {?item wbt:P5 wb:Q81}
MINUS {?item wbt:P5 wb:Q80}
MINUS {?item wbt:P5 wb:Q79}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Organizations without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{
?item wbt:P5 wb:Q13 .
MINUS {?item wbt:P1 [].}

SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Items used as values of Main Subject that are not Index Terms

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?subject ?subjectLabel
WHERE
{
?item wb:P15 ?subject.
MINUS {?subject wb:P5 wbt:Q12}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Items with non-unique Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item1 ?item1Label ?item2 ?item2Label ?value WHERE {
  ?item1 wbt:P1 ?value.
  ?item2 wbt:P1 ?value.

  FILTER((?item1 != ?item2) && ((STR(?item1)) < (STR(?item2))))
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv". }
}

Try it!

Properties without usage example

PREFIX wd: <https://metabase.wikibase.cloud/entity/>
PREFIX wdt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>

SELECT ?item ?itemLabel
WHERE
{
  ?item rdf:type wikibase:Property.
  MINUS {?item wdt:P30 []}
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}

Try it!

Index terms without Wikidata ID

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE {
?item wbt:P5 wb:Q12.
MINUS {?item wbt:P1 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Organizers who are not Organizations or Persons

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel ?organizer ?organizerLabel
WHERE {
?item wbt:P14 ?organizer.
?organizer wbt:P5 ?organizerType.
FILTER(?organizerType NOT IN(wb:Q13, wb:Q23) ). # organizer type is not organization or person

SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

All Temporary Free Text Fields

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX wbp: <https://metabase.wikibase.cloud/prop/>
PREFIX wbpq: <https://metabase.wikibase.cloud/prop/qualifier/>
SELECT DISTINCT ?item ?itemLabel ?val ?qual ?qualLabel
WHERE
{
?item wbt:P38 ?val.
OPTIONAL {?item wbp:P38 ?statement.
?statement wbpq:P41 ?qual .}
     
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Temporary Free Text Fields without a qualifier saying which property they should be replaced with

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
PREFIX p:<https://metabase.wikibase.cloud/prop/>
PREFIX pq:<http://https://metabase.wikibase.cloud/prop/qualifier/>

SELECT ?item ?itemLabel
WHERE
{
	?item p:P38 ?statement .
  	VALUES ?pq { pq:P38 } .
	OPTIONAL {
		?statement ?pq ?qualif .
	} .
	FILTER( !BOUND( ?qualif ) ) .
  SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!


Subjects of Wikimedia Platforms Affected that are not Wikimedia Content Projects or Family of Wikimedia Projects

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT DISTINCT ?item ?itemLabel ?affectedPlatform ?affectedPlatformLabel
WHERE
{
?item wbt:P13 ?affectedPlatform.
?affectedPlatform wbt:P5 ?what.
 
MINUS
{
?affectedPlatform wbt:P5 wb:Q22.
}

MINUS
{
?affectedPlatform wbt:P5 wb:Q25.
}

SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Presentations without Part of

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
MINUS {?item wbt:P17 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Presentations that are Part of something that is not an activity

PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>

SELECT ?item ?itemLabel ?partOfInstance ?partOfInstanceLabel
WHERE
{?item wbt:P5/wbt:P4* wb:Q48.
?item wbt:P17 ?partOf
MINUS {?partOf wbt:P5/wbt:P4* wb:Q8}
?partOf wbt:P5 ?partOfInstance.
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}

Try it!

Retrieved from "https://metabase.wikibase.cloud/w/index.php?title=Project:SPARQL/examples&oldid=9858"