![]() |
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
Jump to navigation
Jump to search
(→Events) |
|||
Line 129: | Line 129: | ||
</SPARQL> | </SPARQL> | ||
=Topics= | |||
==Topics of the sessions at the GLAM Wiki 2023 conference== | |||
<SPARQL tryit="1"> | |||
#defaultView:BubbleChart | |||
PREFIX wb: <https://metabase.wikibase.cloud/entity/> | |||
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/> | |||
SELECT ?mainsubject ?mainsubjectLabel (COUNT(?item) AS ?count) | |||
WHERE | |||
{ | |||
?item wbt:P17 wb:Q22660. | |||
?item wbt:P15 ?mainsubject | |||
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". } | |||
} | |||
GROUP BY ?mainsubject ?mainsubjectLabel | |||
ORDER BY DESC(?count) | |||
</SPARQL> | |||
=Events= | =Events= | ||
==Most common locations for WMSE events== | ==Most common locations for WMSE events== |
Revision as of 07:41, 10 April 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". }
}
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". }
}
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)
}
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")}.
}
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]". }
}
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]". }
}
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". }
}
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". }
}
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)
Topics
Topics of the sessions at the GLAM Wiki 2023 conference
#defaultView:BubbleChart
PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?mainsubject ?mainsubjectLabel (COUNT(?item) AS ?count)
WHERE
{
?item wbt:P17 wb:Q22660.
?item wbt:P15 ?mainsubject
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?mainsubject ?mainsubjectLabel
ORDER BY DESC(?count)
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)
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". }
}
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". }
}
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". }
}
Workshops without a leader
PREFIX wb: <https://metabase.wikibase.cloud/entity/>
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/>
SELECT ?item ?itemLabel
WHERE
{?item wbt:P5 wb:Q37.
MINUS {?item wbt:P32 []}
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
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". }
}
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)
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". }
}
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". }
}
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". }
}
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". }
}
Events (excluding campaigns, photo contests) 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 . # is an event
MINUS {?item wbt:P5 wb:Q81} # is not an online event
MINUS {?item wbt:P5 wb:Q80} # is not an in-person event
MINUS {?item wbt:P5 wb:Q79} # is not a hybrid event
MINUS {?item wbt:P5 wb:Q22217} # is not a campaign
MINUS {?item wbt:P5 wb:Q142} # is not a photo contest
SERVICE wikibase:label { bd:serviceParam wikibase:language "sv,en". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}
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". }
}