Project:SPARQL/examples: Difference between revisions

From Protist-Prokaryote Symbiosis Database
Jump to navigation Jump to search
Line 405: Line 405:
</sparql>
</sparql>


===Generate QuickStatements to create items for DOIs found in references without corresponding items===
===Generate QuickStatements to create items for DOIs found in references without corresponding items (DEPRECATED)===


This checks for items in Wikidata with the same DOI. If references are not in Wikidata, consider adding them first with Scholia or Sourcemd.
This checks for items in Wikidata with the same DOI. If references are not in Wikidata, consider adding them first with Scholia or Sourcemd.
Use ppsdb-utils instead.


<sparql tryit="1">
<sparql tryit="1">

Revision as of 08:51, 18 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". }
}

Try it!


Draw taxonomy tree for Eukaryota (9-rank PR2 v5.0.0 taxonomy)

#defaultView:Tree
PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>

SELECT ?root ?rootLabel ?item ?itemLabel ?item2 ?item2Label ?item3 ?item3Label ?item4 ?item4Label ?item5 ?item5Label ?item6 ?item6Label ?item7 ?item7Label ?item8 ?item8Label ?item9 ?item9Label WHERE {
  BIND(pp:Q609 AS ?root)
  BIND(ppt:P29 AS ?property)
  ?item ?property ?root.
  OPTIONAL {
    ?item2 ?property ?item.
    OPTIONAL {
      ?item3 ?property ?item2.
      OPTIONAL {
        ?item4 ?property ?item3.
        OPTIONAL {
          ?item5 ?property ?item4.
          OPTIONAL {
            ?item6 ?property ?item5.
            OPTIONAL {
              ?item7 ?property ?item6.
              OPTIONAL {
                ?item8 ?property ?item7.
                OPTIONAL {
                  ?item9 ?property ?item8.
                }
              }
            }
          }
        }
      }
    }
  }
  SERVICE wikibase:label {
    bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en".
  }
}
ORDER BY (?itemLabel) (?item2Label) (?item3Label) (?item4Label) (?item5Label) (?item6Label) (?item7Label) (?item8Label) (?item9Label)

Try it!


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

Try it!


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

Try it!


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

Try it!


List Alphaproteobacterial symbionts localized in host nucleus

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 ?localization ?localizationLabel 
WITH {
  SELECT DISTINCT ?wditem WHERE {
    SERVICE <https://query.wikidata.org/sparql> {
      # taxon within Alphaproteobacteria
      ?wditem wdt:P171* wd:Q306579.
    }
  }
} AS %wd
WHERE {
  INCLUDE %wd
  ?host pps:P19 ?interaction.
  ?interaction ppss:P19 ?symbiont.
  ?symbiont ppt:P29 ?parent.
  # parent taxon is in Alphaproteobacteria
  ?parent ppt:P2 ?wditem.
  ?interaction ppsq:P20 ?localization.
  # nucleus or subclass of nucleus
  ?localization ppt:P24* pp:Q78
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} ORDER BY ?hostLabel

Try it!


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.
  }
}

Try it!


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". }
}

Try it!


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". }
}

Try it!


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

Try it!


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". }
}

Try it!


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". }
}

Try it!


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". }
}

Try it!


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". }
}

Try it!


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

Try it!


Maintenance queries

Generate QuickStatements to link interaction hosts to their genera (DEPRECATED)

Split labels, e.g. "Paramecium caudatum" on whitespace, take the first word as a putative genus name, and look for items with taxon rank "genus" that match the putative genus name. Have to avoid assigning labels like "Cryptotermes symbiont", instead use "symbiont of Cryptotermes" to avoid false matches. 

PREFIX pp: <https://ppsdb.wikibase.cloud/entity/>
PREFIX ppt: <https://ppsdb.wikibase.cloud/prop/direct/>
PREFIX wikibase: <http://wikiba.se/ontology#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX bd: <http://www.bigdata.com/rdf#>
SELECT DISTINCT ?qid ?P29 WHERE {
  {
    SELECT DISTINCT ?item ?qid ?itemLabel ?maybeGenus WHERE {
      ?item ppt:P19 ?symbiont.
      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".
        ?item rdfs:label ?itemLabel.
      }
      BIND(STRBEFORE(?itemLabel, " ") AS ?maybeGenus)
    }
  }
  {
    SELECT DISTINCT ?item2 ?item2Label WHERE {
      ?item2 ppt:P32 pp:Q601;
        rdfs:label ?item2Label.
      FILTER((LANG(?item2Label)) = "en")
    }
  }
  FILTER(?item2Label = ?maybeGenus)
  BIND(ENCODE_FOR_URI(REPLACE(STR(?item), ".*Q", "Q")) AS ?qid)
  BIND(ENCODE_FOR_URI(REPLACE(STR(?item2), ".*Q", "Q")) AS ?P29)
}

Try it!


Generate QuickStatements to create items for DOIs found in references without corresponding items (DEPRECATED)

This checks for items in Wikidata with the same DOI. If references are not in Wikidata, consider adding them first with Scholia or Sourcemd.

Use ppsdb-utils instead. 

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 ?qid ?Len ?Aen ?P18 ?P13 ?P2
WITH {
  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. 
    }
  }
} AS %dois
WHERE {
  INCLUDE %dois
  SERVICE <https://query.wikidata.org/sparql> {
    # find wikidata items with this DOI
    ?wdref wdt:P356 ?DOI;
           rdfs:label ?wdlabel.
    FILTER ( LANG(?wdlabel) = "en" )
  }
  BIND ("" AS ?qid)
  # Labels and aliases do not need to be surrounded by quotes
  # unless they contain double quotes or commas
  # Replace double quotes and commas in label because lazy
  BIND (REPLACE(REPLACE(STR(?wdlabel), '\"', ""), ",", "") AS ?Len) 
  BIND (STR(?DOI) AS ?Aen)
  # Strings, external identifiers, and URLs must be surrounded by three double quotes
  # The Query Service CSV export automagically converts single quotes to triple
  BIND (CONCAT('\"', STR(?DOI), '\"') AS ?P13)
  BIND (CONCAT('\"', STR(?wdref), '\"') AS ?P2)
  BIND ("Q3" as ?P18) # instance of reference
}

Try it!


Generate QuickStatements to link taxa to Wikidata items by matching NCBI taxon ID

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 ?qid ?P2
WITH {
  SELECT DISTINCT ?ppitem ?qid ?ncbi ?pplabel
  WHERE {
    ?ppitem ppt:P11 ?ncbi.
    ?ppitem rdfs:label ?pplabel.
    FILTER NOT EXISTS { ?ppitem ppt:P2 ?wikidata_mapping. }
    BIND (ENCODE_FOR_URI(REPLACE(STR(?ppitem), ".*Q", "Q")) AS ?qid)
  }
} AS %ncbis
WHERE {
  INCLUDE %ncbis
  SERVICE <https://query.wikidata.org/sparql> {
    ?wditem wdt:P685 ?ncbi;
           rdfs:label ?wdlabel.
    FILTER ( LANG(?wdlabel) = "en" )
    BIND (CONCAT('\"', STR(?wditem), '\"') AS ?P2)
  }
}

Try it!


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

Try it!


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

Try it!


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". }
}

Try it!


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". }
}

Try it!


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". }
}

Try it!


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.
}

Try it!


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". }
}

Try it!


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. 
  }
}

Try it!


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
  { SELECT (COUNT (DISTINCT ?host) AS ?hostCount) (COUNT (DISTINCT ?s) AS ?statementCount) WHERE { ?host pps:P19 ?s. } }
  # distinct named symbiont items
  { SELECT (COUNT (DISTINCT ?symbiont) AS ?symbiontCount) WHERE { ?h ppt:P19 ?symbiont . FILTER (!ISBLANK (?symbiont)) } }
  # statements where symbiont is unknown
  { SELECT (COUNT (DISTINCT ?symbiont) AS ?unknownCount) WHERE { ?h ppt:P19 ?symbiont . FILTER (ISBLANK (?symbiont)) } }
  # reference items
  { SELECT (COUNT (DISTINCT ?ref) AS ?refCount) WHERE { ?ref ppt:P18 pp:Q3 } }
}

Try it!


Host taxa sorted by number of interactions

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)

Try it!


Symbiont taxa sorted by number of interactions

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)

Try it!


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

Try it!

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