|
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
| Line 184: | Line 184: | ||
PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/> | PREFIX wbt: <https://metabase.wikibase.cloud/prop/direct/> | ||
#defaultView:Map | #defaultView:Map | ||
SELECT ?item ?itemLabel ?place ?placeLabel ?coords | SELECT DISTINCT ?item ?itemLabel ?place ?placeLabel ?coords | ||
WHERE | WHERE | ||
{ | { | ||
| Line 198: | Line 198: | ||
} | } | ||
</SPARQL> | </SPARQL> | ||
== Documents authored by employees of Wikimedia Sverige (via Wikidata)== | == Documents authored by employees of Wikimedia Sverige (via Wikidata)== | ||
<SPARQL tryit="1"> | <SPARQL tryit="1"> | ||
Revision as of 11:40, 10 May 2023
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")}.
}
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)
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". }
}
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". }
}