User:Fnielsen/SPARQL
Jump to navigation
Jump to search
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
Try it!
For: https://query.wikidata.org
Status: https://searchdata.wmflabs.org/wdqs/
Creative works[edit]
Creative works with geo locations[edit]
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX v: <http://www.wikidata.org/prop/statement/>
SELECT ?work ?workLabel ?location ?locationLabel ?geo ?short_isbn ?long_isbn WHERE {
?work wdt:P31/wdt:P279* wd:Q386724 . # work
?work wdt:P840 ?location . # location
?location wdt:P625 ?geo . # Geo coordinates
?location wdt:P17 wd:Q35 . # Location in Denmark
OPTIONAL {?work wdt:P957 ?short_isbn }
OPTIONAL {?work wdt:P212 ?long_isbn }
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
Literary works with citation for reference to place of action[edit]
#defaultView:Map
prefix pr: <http://www.wikidata.org/prop/reference/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX v: <http://www.wikidata.org/prop/statement/>
PREFIX q: <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX prov: <http://www.w3.org/ns/prov#>
SELECT ?workLabel ?authorLabel ?locationLabel ?geo ?citat WHERE {
{ ?work (wdt:P31/wdt:P279*) wd:Q7725634 } union { ?work wdt:P31 wd:Q571 }
?work wdt:P50 ?author .
?work p:P840 ?location_statement .
?location_statement v:P840 ?location .
?location wdt:P17 wd:Q35 .
?location wdt:P625 ?geo .
OPTIONAL {
?location_statement prov:wasDerivedFrom ?ref .
?ref pr:P1683 ?citat .
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
Narrative location around the center of France[edit]
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>
PREFIX hint: <http://www.bigdata.com/queryHints#>
SELECT ?place ?placeLabel
?workLabel ?geo
WHERE {
hint:Query hint:optimizer "Runtime" .
wd:Q670930 wdt:P625 ?qgeo .
SERVICE wikibase:around {
?place wdt:P625 ?geo .
bd:serviceParam
wikibase:center ?qgeo ;
wikibase:radius "500" .
}
?work wdt:P840 ?place .
{ ?work wdt:P31/wdt:P279* wd:Q7725634 . } union { ?work wdt:P31 wd:Q571 . }
SERVICE wikibase:label {
bd:serviceParam
wikibase:language "en,fr" . } }
Residence of authors from Denmark[edit]
#defaultView:Map
select ?place_label ?place ?geo (?author_label as ?layer) ?author where {
?author wdt:P27 wd:Q35 .
?work wdt:P50 ?author .
?author wdt:P551 ?place .
?place wdt:P625 ?geo .
?author rdfs:label ?author_label . filter (lang(?author_label) = 'da')
?place rdfs:label ?place_label . filter (lang(?place_label) = 'da')
}
Neuroinformatics[edit]
Ventricular-brain ratios[edit]
prefix pr: <http://www.wikidata.org/prop/reference/>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX v: <http://www.wikidata.org/prop/statement/>
PREFIX q: <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX prov: <http://www.w3.org/ns/prov#>
SELECT ?value ?year ?referenced ?referencedLabel WHERE {
wd:Q17141282 p:P1181 ?value_statement .
?value_statement v:P1181 ?value .
?value_statement prov:wasDerivedFrom ?ref .
?ref pr:P248 ?referenced .
?referenced wdt:P577 ?year .
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
}
Short link: preview.tinyurl.com/ncny58y
Papers with external data[edit]
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX pq: <http://www.wikidata.org/prop/qualifier/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
select * where {
?paper wdt:P31 wd:Q13442814 .
?paper rdfs:label ?title filter (lang(?title) = 'en') .
?paper wdt:P1325 ?resource .
} limit 10
Patients and those they are treated by[edit]
select ?patient ?patientLabel ?treater ?treaterLabel ?medical_condition ?medical_conditionLabel where {
?patient wdt:P3205 ?treater .
optional { ?patient wdt:P1050 ?medical_condition . }
SERVICE wikibase:label {
bd:serviceParam wikibase:language "da" .
}
}
Wiki research[edit]
The full monty[edit]
# Wikipedia research et al.
select ?paper ?paper_label ?class_labels ?authors ?venueLabel ?topics ?date ?full_text
where {
{
select
?paper ?paper_label
(group_concat(distinct ?classes_label; separator=", ") as ?class_labels)
(group_concat(distinct ?author_label; separator=", ") as ?authors)
(sample(?published_ins) as ?venue)
(group_concat(distinct ?topic_label; separator=", ") as ?topics)
(min(?dates_of_publication) as ?date)
(sample(?full_texts) as ?full_text)
where {
{ ?paper wdt:P921 wd:Q52 } # Wikipedia
union { ?paper wdt:P921 wd:Q195951 } # reliability
union { ?paper wdt:P921 wd:Q870337 } # academic studies about Wikipedia
union { ?paper wdt:P921 wd:Q23038345 } # Wikipedian
union { ?paper wdt:P921 ?wiki . ?wiki wdt:P31 wd:Q10876391 } # Language-version Wikipedia
union { ?paper wdt:P921 wd:Q2013 } # Wikidata
union { ?paper wdt:P921 wd:Q17002416 } # gender bias on Wikipedia
optional {
?paper rdfs:label ?paper_label .
filter (lang(?paper_label) = "en")
}
optional {
?paper wdt:P31 ?classes .
?classes rdfs:label ?classes_label .
filter (lang(?classes_label) = "en")
}
{ ?paper wdt:P921 ?topic . }
optional { ?topic rdfs:label ?topic_label . filter (lang(?topic_label) = "en") }
optional {
?paper wdt:P50 ?author .
?author rdfs:label ?author_label .
filter (lang(?author_label) = "en")
}
optional { ?paper wdt:P1433 ?published_ins }
optional { ?paper wdt:P577 ?dates_of_publication }
optional { ?paper wdt:P953 ?full_texts }
} group by ?paper ?paper_label
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "en" . }
} order by desc(?date)
Wikidata research[edit]
select ?work ?workLabel where {
?work wdt:P921 wd:Q2013 .
SERVICE wikibase:label {
bd:serviceParam wikibase:language "da" .
}
}
Works mentioning Wikidata properties[edit]
select ?work ?workLabel ?property ?propertyLabel where {
?work wdt:P3176 ?property
service wikibase:label { bd:serviceParam wikibase:language "da" . }
}
Danish Wikipedia research[edit]
Python with SPARQL query[edit]
#!/usr/bin/env python
import pandas as pd
import sparql
service = sparql.Service('https://query.wikidata.org/sparql', method="GET")
statement = """
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX v: <http://www.wikidata.org/prop/statement/>
SELECT ?author ?authorLabel ?work ?workLabel ?journal ?journalLabel ?doi ?date WHERE {
?danishAuthor wdt:P27 wd:Q35 .
?work wdt:P50 ?danishAuthor .
?work wdt:P31/wdt:P279* wd:Q17538129 .
?work wdt:P921 wd:Q52 .
?work wdt:P50 ?author .
OPTIONAL { ?work wdt:P356 ?doi . }
OPTIONAL { ?work wdt:P577 ?date . }
OPTIONAL { ?work wdt:P1433 ?journal . }
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
"""
result = service.query(statement)
df = pd.DataFrame(result.fetchall(), columns=result.variables)
columns = df.columns
for column in columns:
elements = []
for element in df[column]:
if type(element) == sparql.IRI or type(element) == sparql.Literal:
elements.append(element.value)
else:
elements.append(element)
df[column] = elements
def merge(df, column):
if column == 'doi':
return ", ".join(set("[http://dx.doi.org/%s %s]" % (doi, doi) for doi in df.doi if doi is not None))
else:
column_label = column + 'Label'
if column_label in df.columns:
elements = []
for uri, label in zip(df[column].values, df[column_label].values):
elements.append("[[%s|%s]]" % (uri[31:], unicode(label)))
return ", ".join(set(elements))
else:
return ", ".join(set(unicode(element) for element in df[column].values))
grouped = df.groupby('work')
uris = grouped.groups.keys()
columns = ['author', 'work', 'journal', 'date', 'doi']
collected = pd.DataFrame(index=uris, columns=columns)
for uri in grouped.groups:
for column in columns:
collected.ix[uri, column] = merge(grouped.get_group(uri), column)
collected.index = [element[31:] for element in collected.index]
pd.set_option('display.max_colwidth', -1)
html = collected.to_html(index_names=False)
html = html.replace('border="1" class="dataframe"', 'class="wikitable sortable"')
html = html.replace('<thead>', '').replace('</thead>', '').replace('<tbody>', '').replace('</tbody>', '')
print(html)
Result[edit]
Gives the following:
author | work | journal | date | doi | |
---|---|---|---|---|---|
Q21174060 | Tønnes Bekker-Nielsen | Historie på Wikipedia | Noter | 2011-03-01T00:00:00Z | |
Q21172296 | Morten Rask | The reach and richness of Wikipedia: Is Wikinomics only for rich countries? | First Monday | 2008-06-01T00:00:00Z | |
Q21172284 | Finn Årup Nielsen | Scientific citations in Wikipedia | First Monday | 2007-08-01T00:00:00Z | |
Q21172700 | Michael Andreas Etter, Finn Årup Nielsen | Collective remembering of organizations: Co-construction of organizational pasts in Wikipedia | Corporate Communications | 2015-10-05T00:00:00Z | 10.1108/CCIJ-09-2014-0059 |
"Awesome" people born on Wikidata birthday[edit]
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
SELECT ?human ?humanLabel (count(*) as ?count) WHERE {
?human wdt:P31 wd:Q5 ;
wdt:P569 ?date .
?thing wdt:P138 ?human .
FILTER (
datatype(?date) = xsd:dateTime &&
month(?date) = 10 &&
day(?date) = 29
)
SERVICE wikibase:label { bd:serviceParam wikibase:language "en,de,da,no,fr,sv" . }
}
GROUP BY ?human ?humanLabel
ORDER BY DESC(?count)
Companies[edit]
Russian branch[edit]
select ?branch ?okved1 ?okvd2 ?branchLabel where {
{ ?branch wdt:P3246 ?okvd2 . }
union { ?branch wdt:P3243 ?okved1 . }
service wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
order by ?code
Graphs[edit]
Road connections[edit]
#defaultView:Graph
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
select ?road ?roadLabel ?image ?connected_road ?connected_roadLabel where {
service gas:service {
gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ;
gas:in wd:Q3429049, wd:Q12325236;
gas:traversalDirection "Undirected" ;
gas:out ?connected_road ;
gas:out1 ?depth ;
gas:out2 ?road ;
gas:linkType wdt:P2789 ;
gas:maxIterations 7;
gas:maxVisited 200 .
}
?road wdt:P2789 ?connected_road .
OPTIONAL {?road wdt:P18 ?image }
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
A.P. Møller group ownership[edit]
#defaultView:Graph
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
select
?owner ?ownerLabel ?owned ?ownedLabel
where {
{
select
?owner1 ?owned1
where {
service gas:service {
gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ;
gas:in wd:Q12300043, wd:Q861295 ;
gas:traversalDirection "Undirected" ;
gas:out ?owned1 ;
gas:out1 ?depth ;
gas:out2 ?owner1 ;
gas:linkType wdt:P1830 ;
gas:maxIterations 5;
gas:maxVisited 200 .
}
}
}
?owner wdt:P1830 ?owned .
filter (?owner = ?owner1 || ?owner = ?owned1 || ?owned = ?owner1 || ?owned = ?owned1)
service wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
Scientific co-authors[edit]
#defaultView:Graph
SELECT ?author1 ?author1Label ?author2 ?author2Label WHERE {
?work wdt:P50 ?author1 .
?work wdt:P50 ?author2 .
?work wdt:P31 wd:Q13442814 .
filter (?author1 != ?author2)
SERVICE wikibase:label {
bd:serviceParam wikibase:language "en".
# ?item rdfs:label ?itemLabel.
}
}
LIMIT 100
Neuroinformatics co-citation author network[edit]
#defaultView:Graph
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX p: <http://www.wikidata.org/prop/>
PREFIX ps: <http://www.wikidata.org/prop/statement/>
PREFIX prov: <http://www.w3.org/ns/prov#>
select distinct ?paper_author ?paper_authorLabel ?cited_paper_author ?cited_paper_authorLabel where {
?paper wdt:P1325 ?resource .
?paper wdt:P50 ?paper_author .
filter ( strstarts(str(?resource), "https://openfmri.org/") || strstarts(str(?resource), "http://neurosynth.org/") || strstarts(str(?resource), "http://neurovault.org/") )
?paper wdt:P2860 ?cited_paper .
# ?cited_paper wdt:P1325 ?resource_for_cited .
# filter ( strstarts(str(?resource_for_cited), "https://openfmri.org/") || strstarts(str(?resource_for_cited), "http://neurosynth.org/") || strstarts(str(?resource_for_cited), "http://neurovault.org/") )
?cited_paper wdt:P50 ?cited_paper_author .
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
Author cocitation network[edit]
#defaultView:Graph
PREFIX wd: <http://www.wikidata.org/entity/>
PREFIX wdt: <http://www.wikidata.org/prop/direct/>
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
select ?citing_paper_author ?citing_paper_authorLabel ?paper_author ?paper_authorLabel where {
service gas:service {
gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.BFS" ;
gas:in wd:Q23992764 ,
wd:Q24684259 ;
gas:traversalDirection "Forward" ;
gas:out ?paper ;
gas:out1 ?depth ;
gas:out2 ?citing_paper ;
gas:linkType wdt:P2860 ;
gas:maxIterations 5;
gas:maxVisited 200 .
}
?citing_paper wdt:P2860 ?paper .
?paper wdt:P50 ?paper_author .
?citing_paper wdt:P50 ?citing_paper_author .
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
}
Board members for Danish organizations[edit]
# Board members for Danish organizations
SELECT ?person ?personLabel ?image ?organization ?organizationLabel (CONCAT("7F0000") AS ?rgb) WHERE {
{
?person p:P39 ?position_statement.
OPTIONAL { ?person wdt:P18 ?image. }
?position_statement ps:P39 wd:Q2824523.
?position_statement pq:P642 ?organization.
?organization wdt:P17 wd:Q35.
}
UNION
{
?organization wdt:P3320 ?person.
?organization wdt:P17 wd:Q35.
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en". }
}
Controlling and ownerships in Denmark[edit]
#defaultView:Graph
# Controlling Denmark
SELECT ?controller ?controllerLabel ?controlled ?controlledLabel WHERE {
{
?controller p:P39 ?position_statement.
?position_statement ps:P39 wd:Q2824523.
?position_statement pq:P642 ?controlled.
?controlled wdt:P17 wd:Q35.
}
UNION {
?controlled wdt:P3320|wdt:P127|wdt:P749|wdt:P1037|wdt:P169 ?controller.
?controlled wdt:P17 wd:Q35.
}
UNION {
?controller wdt:P1830|wdt:P355 ?controlled.
?controlled wdt:P17 wd:Q35
}
minus { ?controlled wdt:P570 ?date_of_death }
OPTIONAL { ?controller wdt:P18 ?controller_image. }
OPTIONAL { ?controlled wdt:P18 ?controlled_image. }
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en". }
}
Scientometrics[edit]
Most cited Dane[edit]
SELECT ?author1 ?author1Label (count(?citing_work) AS ?number_of_citations) (group_concat(distinct ?citing_work_label; separator=" // ") as ?citing_works) WHERE {
{
select distinct ?author1 where {
{ ?author1 wdt:P27 wd:Q35 . }
union { ?author1 wdt:P1416 ?organization . ?organization wdt:P17 wd:Q35 }
union { ?author1 wdt:P108 ?organization . ?organization wdt:P17 wd:Q35 }
}
}
?work wdt:P50 ?author1 .
?citing_work wdt:P2860 ?work .
service wikibase:label { bd:serviceParam wikibase:language "en" . }
?citing_work rdfs:label ?citing_work_label . filter (lang(?citing_work_label) = 'en')
}
GROUP BY ?author1 ?author1Label
ORDER BY DESC(?number_of_citations)
Most cited with a SoundCloud ID[edit]
SELECT ?author1 ?author1Label (count(?citing_work) AS ?number_of_citations) (group_concat(?citing_work_label; separator=" // ") as ?citing_works) WHERE {
?work wdt:P50 ?author1 .
?author1 wdt:P3040 ?soundcloud .
?citing_work wdt:P2860 ?work .
service wikibase:label { bd:serviceParam wikibase:language "en" . }
?citing_work rdfs:label ?citing_work_label . filter (lang(?citing_work_label) = 'en')
}
GROUP BY ?author1 ?author1Label
ORDER BY DESC(?number_of_citations)
Most self-citing author[edit]
SELECT ?author1 ?author1Label (count(?citing_work) AS ?number_of_citations) (group_concat(?citing_workLabel; separator=" // ") as ?citing_works) WHERE {
?work wdt:P50 ?author1 .
?citing_work wdt:P2860 ?work .
?citing_work wdt:P50 ?author2 .
filter (?author1 = ?author2 )
service wikibase:label { bd:serviceParam wikibase:language "en" . }
?citing_work rdfs:label ?citing_workLabel . filter (lang(?citing_workLabel) = 'en')
}
GROUP BY ?author1 ?author1Label
ORDER BY DESC(?number_of_citations)
Author affiliated with DTU Compute with most works in Wikidata[edit]
SELECT ?person ?personLabel (COUNT(?work) AS ?number_of_works) (GROUP_CONCAT(?work_label; separator=" // ") AS ?work_labels) WHERE {
?person wdt:P1416 ?section .
?section wdt:P361 wd:Q23048689 .
?work wdt:P50 ?person .
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
?work rdfs:label ?work_label . filter (lang(?work_label) = 'en')
}
GROUP BY ?person ?personLabel
ORDER BY DESC(?number_of_works)
Most cited work from DTU Compute-affiliated author based on recorded citations in Wikidata[edit]
SELECT distinct ?work ?work_label (COUNT(?citing_work) AS ?number_of_citing_works) (GROUP_CONCAT(?citing_work_label; separator=" // ") AS ?citing_work_labels) WHERE {
{ SELECT distinct ?work ?work_label ?citing_work ?citing_work_label WHERE {
?person wdt:P1416 ?section .
?section wdt:P361 wd:Q23048689 .
?work wdt:P50 ?person .
?citing_work wdt:P2860 ?work .
SERVICE wikibase:label { bd:serviceParam wikibase:language "da,en" . }
?work rdfs:label ?work_label . filter (lang(?work_label) = 'en')
?citing_work rdfs:label ?citing_work_label . filter (lang(?citing_work_label) = 'en')
}
}
}
GROUP BY ?work ?work_label
ORDER BY DESC(?number_of_citing_works)