Project:SPARQL/examples: Difference between revisions
(4 intermediate revisions by the same user not shown) | |||
Line 533: | Line 533: | ||
SELECT ?hostCount ?statementCount ?symbiontCount ?unknownCount ?refCount WHERE { | SELECT ?hostCount ?statementCount ?symbiontCount ?unknownCount ?refCount WHERE { | ||
# distinct host taxa and 'interacts with' statements, including to unknown symbionts | # distinct host taxa and 'interacts with' statements, including to unknown symbionts | ||
{ SELECT (COUNT (DISTINCT ?host) AS ?hostCount) (COUNT (DISTINCT ?s) AS ?statementCount) WHERE { ?host pps:P19 ?s. } } | # alternative paths to capture both "interacts with" (P19) and "interacts experimentally with" (P41) | ||
{ SELECT (COUNT (DISTINCT ?host) AS ?hostCount) (COUNT (DISTINCT ?s) AS ?statementCount) WHERE { ?host pps:P19|pps:P41 ?s. } } | |||
# distinct named symbiont items | # distinct named symbiont items | ||
{ SELECT (COUNT (DISTINCT ?symbiont) AS ?symbiontCount) WHERE { ?h ppt:P19 ?symbiont . FILTER (!ISBLANK (?symbiont)) } } | { SELECT (COUNT (DISTINCT ?symbiont) AS ?symbiontCount) WHERE { ?h ppt:P19|ppt:P41 ?symbiont . FILTER (!ISBLANK (?symbiont)) } } | ||
# statements where symbiont is unknown | # statements where symbiont is unknown | ||
{ SELECT (COUNT (DISTINCT ?symbiont) AS ?unknownCount) WHERE { ?h ppt:P19 ?symbiont . FILTER (ISBLANK (?symbiont)) } } | { SELECT (COUNT (DISTINCT ?symbiont) AS ?unknownCount) WHERE { ?h ppt:P19|ppt:P41 ?symbiont . FILTER (ISBLANK (?symbiont)) } } | ||
# reference items | # reference items | ||
{ SELECT (COUNT (DISTINCT ?ref) AS ?refCount) WHERE { ?ref ppt:P18 pp:Q3 } } | { SELECT (COUNT (DISTINCT ?ref) AS ?refCount) WHERE { ?ref ppt:P18 pp:Q3 } } | ||
Line 558: | Line 559: | ||
WITH { | WITH { | ||
SELECT DISTINCT ?symb ?symbLabel ?wditem WHERE { | SELECT DISTINCT ?symb ?symbLabel ?wditem WHERE { | ||
?host ppt:P19 ?symb. | ?host ppt:P19|ppt:P41 ?symb. | ||
?symb ppt:P29 ?parent. | ?symb ppt:P29 ?parent. | ||
?parent ppt:P2 ?wditem. | ?parent ppt:P2 ?wditem. | ||
Line 585: | Line 586: | ||
===Host taxa sorted by number of interactions=== | ===Host taxa sorted by number of interactions=== | ||
Treat these counts with a pinch of salt, because species concepts differ. | Counting natural interactions only. Treat these counts with a pinch of salt, because species concepts differ. | ||
<sparql tryit="1"> | <sparql tryit="1"> | ||
Line 597: | Line 598: | ||
===Symbiont taxa sorted by number of interactions=== | ===Symbiont taxa sorted by number of interactions=== | ||
Counting natural interactions only. | |||
<sparql tryit="1"> | <sparql tryit="1"> |
Latest revision as of 11:26, 22 July 2024
Showcase visualizations
Graph (network) of all hosts connected to Megaira polyxenophila and their other interactions
Megaira polyxenophila Q141 is an intracellular bacterium in a wide diversity of host taxa. This query includes sub-species of Megaira polyxenophila.
#defaultView:Graph
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
SELECT DISTINCT ?host ?hostLabel ?pic ?linkto ?linktoLabel ?edgeLabel WHERE {
?host pps:P19 ?s.
?s ppss:P19/ppt:P29* pp:Q141 # include items with parent taxon Q141
OPTIONAL {
?host pps:P19 ?statement.
?statement ppss:P19 ?linkto.
OPTIONAL { ?statement ppsq:P20 ?edge }
}
OPTIONAL { ?host ppt:P33 ?pic }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Bubble chart of numbers of interactions by symbiont localization
#defaultView:BubbleChart
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?localization ?localizationLabel (COUNT (DISTINCT ?interaction) AS ?count) WHERE {
?host pps:P19 ?interaction.
?interaction ppss:P19 ?symbiont.
?interaction ppsq:P20 ?localization.
FILTER (!ISBLANK (?localization))
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?localization ?localizationLabel
List interactions where host is a member of Blattodea (cockroaches and termites), according to Wikidata
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?host ?hostLabel ?symb ?symbLabel ?wditem
WITH {
SELECT DISTINCT ?wditem WHERE {
SERVICE <https://query.wikidata.org/sparql> {
?wditem wdt:P171* wd:Q25309.
}
}
} AS %wd
WHERE {
INCLUDE %wd
?host ppt:P19 ?symb.
?host ppt:P29 ?parent.
?parent ppt:P2 ?wditem.
?host rdfs:label ?hostLabel.
?symb rdfs:label ?symbLabel.
} ORDER BY ?symbLabel ?hostLabel
List interactions where symbiont is member of Alphaproteobacteria according to Wikidata
Get items with a parent taxon that is mapped to a taxon in Wikidata, relying on the taxonomy that is given there.
We rely on parent taxon being mapped to Wikidata, rather than the taxon itself being mapped to Wikidata, because not all taxon items represented in this Wikibase are suitable for being added to Wikidata, e.g. informally named taxonomic units, even though there is some information on their taxonomic position.
Query works without timeout even with large taxa like Bacteria.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?host ?hostLabel ?symb ?symbLabel ?wditem
WITH {
SELECT DISTINCT ?wditem WHERE {
SERVICE <https://query.wikidata.org/sparql> {
?wditem wdt:P171* wd:Q306579.
}
}
} AS %wd
WHERE {
INCLUDE %wd
?host ppt:P19 ?symb.
?symb ppt:P29 ?parent.
?parent ppt:P2 ?wditem.
?host rdfs:label ?hostLabel.
?symb rdfs:label ?symbLabel.
} ORDER BY ?symbLabel ?hostLabel
List symbionts localized in host nucleus and their taxonomic class
Placing Wikidata search statement in the WITH or WHERE clause appears to depend on how large the expected results are. Want to avoid time-outs on the Wikidata query.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?host ?hostLabel ?symbiont ?symbiontLabel ?parentClass ?parentClassLabel ?localizationLabel
WITH {
SELECT DISTINCT ?host ?hostLabel ?symbiont ?symbiontLabel ?wditem ?localizationLabel WHERE {
?host pps:P19 ?interaction.
?interaction ppss:P19 ?symbiont.
?symbiont ppt:P29 ?parent.
?parent ppt:P2 ?wditem.
?interaction ppsq:P20 ?localization.
# nucleus or subclass of or part of nucleus
?localization ppt:P24*/ppt:P47* pp:Q78
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
?host rdfs:label ?hostLabel.
?symbiont rdfs:label ?symbiontLabel.
?localization rdfs:label ?localizationLabel.
}
}
} AS %wd
WHERE {
INCLUDE %wd
SERVICE <https://query.wikidata.org/sparql> {
OPTIONAL {
?wditem wdt:P171* ?parentClass.
?parentClass wdt:P105 wd:Q37517.
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
?parentClass rdfs:label ?parentClassLabel.
}
}
}
} ORDER BY ?parentClassLabel ?hostLabel
List interactions where publication co-authored by Vittorio Boscaro is cited
Example of using a federated query with Wikidata items.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?host ?hostLabel ?symb ?symbLabel ?statedin ?wdref
WITH {
SELECT DISTINCT ?host ?hostLabel ?symb ?symbLabel ?statedin ?wdref
WHERE {
?host pps:P19 ?interaction.
?host rdfs:label ?hostLabel.
?interaction ppss:P19 ?symb.
?symb rdfs:label ?symbLabel.
?interaction prov:wasDerivedFrom ?refnode.
?refnode ppsr:P23 ?statedin.
# Wikidata URI
?statedin ppt:P2 ?wdref.
}
} AS %refs
WHERE {
INCLUDE %refs
SERVICE <https://query.wikidata.org/sparql> {
# Wikidata items with author Q30513710
?wdref wdt:P50 wd:Q30513710.
}
}
List interactions characterized by FISH but not sequencing
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
SELECT DISTINCT ?host ?hostLabel ?symb ?symbLabel WHERE {
?host pps:P19 ?s.
?s ppsq:P22 pp:Q10. # FISH
?s ppss:P19 ?symb.
# Use this form because an interaction may be documented in multiple statements
FILTER NOT EXISTS {
?host pps:P19 ?t.
?t ppss:P19 ?symb.
?t ppsq:P22 pp:Q13. # phylogenetic marker sequencing
}
FILTER NOT EXISTS {
?host pps:P19 ?u.
?u ppss:P19 ?symb.
?u ppsq:P22 pp:Q12. # metagenome sequencing
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Lists
List all taxon items
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel WHERE {
?item ppt:P18/ppt:P24* pp:Q2.
# FILTER NOT EXISTS { ?item ppt:P18 pp:Q1488. } # optionally exclude higher taxa
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
List all symbiotic hosts and their environmental terms
Note that some entries have multiple claims for a single environment-related property, so the number of results will be inflated by the distinct combinations.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel ?envmat ?envmatLabel ?envloc ?envlocLabel ?envsys ?envsysLabel WHERE {
?item ppt:P19 ?symbiont.
OPTIONAL { ?item ppt:P36 ?envmat }
OPTIONAL { ?item ppt:P40 ?envloc }
OPTIONAL { ?item ppt:P38 ?envsys }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ?itemLabel
List all interaction types and RO mapping if available
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel ?roterm WHERE {
?item ppt:P18 pp:Q1.
OPTIONAL {
?item ppt:P16 ?roterm
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
List all body parts and GO mapping if available
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel ?goterm WHERE {
?item ppt:P18 pp:Q5.
OPTIONAL {
?item ppt:P17 ?goterm
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
List all analytical techniques and Wikidata mapping if available
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel ?wikidata WHERE {
?item ppt:P18 pp:Q6.
OPTIONAL {
?item ppt:P2 ?wikidata
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
List all interactions, optionally the localization, interaction type, and references
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?host ?hostLabel ?typeLabel ?symbiontLabel ?localizationLabel ?doi ?statedin WHERE {
?host pps:P19 ?interaction.
?interaction ppss:P19 ?symbiont.
OPTIONAL { ?interaction ppsq:P20 ?localization. }
OPTIONAL { ?interaction ppsq:P26 ?type. }
OPTIONAL {
?interaction prov:wasDerivedFrom ?refnode.
OPTIONAL { ?refnode ppsr:P27 ?doi }
OPTIONAL { ?refnode ppsr:P23 ?statedin }
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Count references and qualifier statements for each interaction
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX prov: <http://www.w3.org/ns/prov#>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT ?host ?hostLabel ?symbiont ?symbiontLabel (COUNT(DISTINCT ?refnode) AS ?refnodeCount) (COUNT(DISTINCT ?localization) AS ?localizationCount) (COUNT(DISTINCT ?type) AS ?typeCount) (COUNT(DISTINCT ?methobj) AS ?methobjCount) (COUNT(DISTINCT ?methsub) AS ?methsubCount) WHERE {
?host pps:P19 ?interaction.
?interaction ppss:P19 ?symbiont.
OPTIONAL { ?interaction ppsq:P22 ?methobj. }
OPTIONAL { ?interaction ppsq:P42 ?methsub. }
OPTIONAL { ?interaction ppsq:P20 ?localization. }
OPTIONAL { ?interaction ppsq:P26 ?type. }
OPTIONAL {
?interaction prov:wasDerivedFrom ?refnode.
OPTIONAL { ?refnode ppsr:P27 ?doi. }
OPTIONAL { ?refnode ppsr:P23 ?statedin. }
}
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?host ?hostLabel ?symbiont ?symbiontLabel
ORDER BY ?hostLabel
Maintenance queries
Generate QuickStatements to create new parent taxon items for symbionts based on Wikidata mapping
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?qid ?Len ?P18 ?P2
WITH {
SELECT DISTINCT ?symb ?symbLabel ?wditem
WHERE {
?host ppt:P19 ?symb.
FILTER NOT EXISTS { ?symb ppt:P29 ?parent. }
?symb ppt:P2 ?wditem.
?symb rdfs:label ?symbLabel.
}
} AS %in
WHERE {
INCLUDE %in
SERVICE <https://query.wikidata.org/sparql> {
?wditem wdt:P171 ?wdparent.
?wdparent rdfs:label ?wdparentlabel.
FILTER ( LANG(?wdparentlabel) = "en" )
}
# Check that a parent taxon item with the same wikidata mapping
# does not already exist
FILTER NOT EXISTS { ?qid ppt:P2 ?wdparent }
BIND (CONCAT('"', STR(?wdparent), '"') as ?P2)
BIND (STR("Q1488") AS ?P18) # instance of higher taxon
BIND (?wdparentlabel as ?Len)
} ORDER BY ?Len
Generate QuickStatements to link symbiont items to their parent taxa based on Wikidata mappings
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?qid ?P29
WITH {
SELECT DISTINCT ?symb ?symbLabel ?wditem ?qid
WHERE {
?host ppt:P19 ?symb.
FILTER NOT EXISTS { ?symb ppt:P29 ?parent. }
?symb ppt:P2 ?wditem.
?symb rdfs:label ?symbLabel.
BIND (ENCODE_FOR_URI(REPLACE(STR(?symb), ".*Q", "Q")) AS ?qid)
}
} AS %in
WHERE {
INCLUDE %in
SERVICE <https://query.wikidata.org/sparql> {
?wditem wdt:P171 ?wdparent.
?wdparent rdfs:label ?wdparentlabel.
FILTER ( LANG(?wdparentlabel) = "en" )
}
OPTIONAL {
?parent ppt:P2 ?wdparent.
?parent rdfs:label ?parentLabel.
BIND (ENCODE_FOR_URI(REPLACE(STR(?parent), ".*Q", "Q")) AS ?P29)
}
} ORDER BY ?Len
Graph of entity subclasses up to 2nd order
#defaultView:Graph
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX gas: <http://www.bigdata.com/rdf/gas#>
SELECT DISTINCT ?item ?itemLabel ?linkTo ?linkToLabel WHERE {
SERVICE gas:service {
gas:program gas:gasClass "com.bigdata.rdf.graph.analytics.SSSP" ;
gas:in pp:Q610;
gas:traversalDirection "Reverse" ;
gas:out ?item ;
gas:out1 ?depth ;
gas:maxIterations 2 ;
gas:linkType ppt:P24 .
}
OPTIONAL { ?item ppt:P24 ?linkTo }
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Taxon items without parent taxon
Results should be empty.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?qid ?itemLabel WHERE {
?item ppt:P18/ppt:P24* pp:Q2.
FILTER NOT EXISTS { ?item ppt:P18 pp:Q1488. } # not a higher taxon
FILTER NOT EXISTS {
?item ppt:P29 ?parent.
}
BIND (ENCODE_FOR_URI(REPLACE(STR(?item), ".*Q", "Q")) AS ?qid)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Formally named taxon items not mapped to Wikidata
Should map to Wikidata URI with Property:P2; create taxon item on Wikidata if necessary.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?qid ?itemLabel WHERE {
?item ppt:P18 pp:Q2206.
#FILTER NOT EXISTS { ?item ppt:P18 pp:Q1488. } # not a higher taxon
FILTER NOT EXISTS {
?item ppt:P2 ?parent.
}
BIND (ENCODE_FOR_URI(REPLACE(STR(?item), ".*Q", "Q")) AS ?qid)
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
Items with representative sequence record without reference
Representative sequence claims for a taxon should be referenced to ensure that the taxon concept is traceable.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
SELECT DISTINCT ?item ?itemLabel WHERE {
?item pps:P34 ?repseq. # SSU rRNA sequence record
# or P46 for genome sequence
FILTER NOT EXISTS { ?repseq prov:wasDerivedFrom ?refnode. }
?item rdfs:label ?itemLabel.
}
Items that are in a maintenance class
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT DISTINCT ?item ?itemLabel ?classLabel WHERE {
?item ppt:P18 ?class.
?class ppt:P24* pp:Q657
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
DOIs used in reference qualifiers but not in a reference item
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?host ?DOI
WHERE {
?host pps:P19 ?interaction.
?interaction prov:wasDerivedFrom ?refnode.
?refnode ppsr:P27 ?doi.
# check that no reference item already linked in this statement
FILTER NOT EXISTS { ?refnode ppsr:P23 ?statedin }
# wikidata standardizes DOI in uppercase
BIND (UCASE(STR(?doi)) AS ?DOI)
# check that no reference with this DOI already exists
FILTER NOT EXISTS {
?ref ppt:P18 pp:Q3;
ppt:P13 ?DOI.
}
}
Statistics
Summary: Counts of hosts, symbionts, interactions, references
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
SELECT ?hostCount ?statementCount ?symbiontCount ?unknownCount ?refCount WHERE {
# distinct host taxa and 'interacts with' statements, including to unknown symbionts
# alternative paths to capture both "interacts with" (P19) and "interacts experimentally with" (P41)
{ SELECT (COUNT (DISTINCT ?host) AS ?hostCount) (COUNT (DISTINCT ?s) AS ?statementCount) WHERE { ?host pps:P19|pps:P41 ?s. } }
# distinct named symbiont items
{ SELECT (COUNT (DISTINCT ?symbiont) AS ?symbiontCount) WHERE { ?h ppt:P19|ppt:P41 ?symbiont . FILTER (!ISBLANK (?symbiont)) } }
# statements where symbiont is unknown
{ SELECT (COUNT (DISTINCT ?symbiont) AS ?unknownCount) WHERE { ?h ppt:P19|ppt:P41 ?symbiont . FILTER (ISBLANK (?symbiont)) } }
# reference items
{ SELECT (COUNT (DISTINCT ?ref) AS ?refCount) WHERE { ?ref ppt:P18 pp:Q3 } }
}
Summary: Symbiont taxa by phylum, according to Wikidata
NB: Wikidata taxonomy is sometimes conflicting/incomplete, especially for eukaryote higher taxonomy.
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT DISTINCT ?parentPhylum ?parentPhylumLabel (COUNT (DISTINCT (?symb)) AS ?count )
WITH {
SELECT DISTINCT ?symb ?symbLabel ?wditem WHERE {
?host ppt:P19|ppt:P41 ?symb.
?symb ppt:P29 ?parent.
?parent ppt:P2 ?wditem.
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
?symb rdfs:label ?symbLabel.
}
}
} AS %wd
WHERE {
INCLUDE %wd
SERVICE <https://query.wikidata.org/sparql> {
OPTIONAL {
VALUES ?phylumOrDivision { wd:Q334460 wd:Q38348 }
?wditem wdt:P171* ?parentPhylum.
?parentPhylum wdt:P105 ?phylumOrDivision.
SERVICE wikibase:label {
bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
?parentPhylum rdfs:label ?parentPhylumLabel.
}
}
}
} GROUP BY ?parentPhylum ?parentPhylumLabel ORDER BY DESC(?count)
Host taxa sorted by number of interactions
Counting natural interactions only. Treat these counts with a pinch of salt, because species concepts differ.
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT ?host ?hostLabel (COUNT (DISTINCT ?symbiont) AS ?count) WHERE {
?host ppt:P19 ?symbiont.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?host ?hostLabel ORDER BY DESC(?count)
Symbiont taxa sorted by number of interactions
Counting natural interactions only.
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
SELECT ?symbiont ?symbiontLabel (COUNT (DISTINCT ?host) AS ?count) WHERE {
?host ppt:P19 ?symbiont.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?symbiont ?symbiontLabel ORDER BY DESC(?count)
Top 20 references cited in interaction statements
PREFIX pps: <https://ppsdb.wikibase.cloud/prop/>
PREFIX ppss: <https://ppsdb.wikibase.cloud/prop/statement/>
PREFIX ppsq: <https://ppsdb.wikibase.cloud/prop/qualifier/>
PREFIX ppsr: <https://ppsdb.wikibase.cloud/prop/reference/>
SELECT ?statedin ?statedinLabel (COUNT(DISTINCT ?interaction) AS ?count) WHERE {
?host pps:P19 ?interaction.
?interaction ppss:P19 ?symbiont.
?interaction prov:wasDerivedFrom ?refnode.
?refnode ppsr:P23 ?statedin.
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?statedin ?statedinLabel
ORDER BY DESC(?count)
LIMIT 20