Koetai SPARQL examples

Community-curated SPARQL example queries for multiple life-science endpoints.

View the Project on GitHub Koetai/sparql-examples

give_mappings_to_other_code_systems_to_the_pathology_identifier

rq turtle/ttl

With NCIt as a bridge and MONDO as a subsequent bridge, this query suggests mapping to other code systems

Use at

PREFIX ome:      <https://ld.openmicroscopy.org/core/>
PREFIX rdfs:     <http://www.w3.org/2000/01/rdf-schema#>
PREFIX skos:     <http://www.w3.org/2004/02/skos/core#>
PREFIX ncit:     <http://purl.obolibrary.org/obo/NCIT_>
PREFIX oboInOwl: <http://www.geneontology.org/formats/oboInOwl#>

SELECT DISTINCT ?value ?icdo ?ncit ?ncit_label ?umls ?system ?code
WHERE {

  # ── IDR side: collapse to the 70 distinct codes BEFORE federating ──
  {
    SELECT DISTINCT ?value WHERE {
      ?m ome:key "Pathology Identifier" ;
         ome:value ?value .
    }
  }

  BIND(REPLACE(?value, "^M-", "") AS ?raw)
  BIND(IF(STRLEN(?raw) = 5,
          CONCAT(SUBSTR(?raw, 1, 4), "/", SUBSTR(?raw, 5, 1)),
          ?raw) AS ?icdo)

  # ── Federation: one SERVICE round-trip for all 70 codes ──
  SERVICE <https://rdfportal.org/bioportal/sparql> {
    ?ncit ncit:P334 ?icdo ;
          rdfs:label ?ncit_label .
    OPTIONAL { ?ncit ncit:P207 ?umls }

    OPTIONAL {
      ?bridge skos:exactMatch ?ncit ;
              oboInOwl:hasDbXref ?xref .
      BIND(REPLACE(STR(?xref), ":.*",    "") AS ?system)
      BIND(REPLACE(STR(?xref), "^[^:]+:", "") AS ?code)
      FILTER(?system IN
        ("DOID","MONDO","MESH","SCTID","UMLS","Orphanet",
         "OMIM","EFO","HP","MEDGEN","ICDO","MedDRA","GARD"))
    }
  }
}

graph TD
classDef projected fill:lightgreen;
classDef literal fill:orange;
classDef iri fill:yellow;
  v9("?bridge")
  v11("?code"):::projected 
  v4("?icdo"):::projected 
  v1("?m")
  v6("?ncit"):::projected 
  v7("?ncit_label"):::projected 
  v3("?raw")
  v11("?system"):::projected 
  v8("?umls"):::projected 
  v2("?value"):::projected 
  v10("?xref")
  c2(["Pathology Identifier"]):::literal 
  v1 --https://ld.openmicroscopy.org/core/key-->  c2
  v1 --https://ld.openmicroscopy.org/core/value-->  v2
  bind0[/"replace(?value,'^M-','')"/]
  v2 --o bind0
  bind0 --as--o v3
  bind1[/"if(string-length(?raw) = '5^^xsd:integer',concat(substring(?raw,'1^^xsd:integer','4^^xsd:integer'),'/',substring(?raw,'5^^xsd:integer','1^^xsd:integer')),?raw)"/]
  v3 --o bind1
  bind1 --as--o v4
  subgraph s1["https://rdfportal.org/bioportal/sparql"]
    style s1 stroke-width:4px;
    v6 --http://purl.obolibrary.org/obo/NCIT_P334-->  v4
    v6 --"rdfs:label"-->  v7
    subgraph optional0["(optional)"]
    style optional0 fill:#bbf,stroke-dasharray: 5 5;
      v6 -.http://purl.obolibrary.org/obo/NCIT_P207.->  v8
    end
    subgraph optional1["(optional)"]
    style optional1 fill:#bbf,stroke-dasharray: 5 5;
      v9 -."skos:exactMatch".->  v6
      v9 --"oboInOwl:hasDbXref"-->  v10
      bind2[/"replace(str(?xref),':.*','')"/]
      v10 --o bind2
      bind2 --as--o v11
      bind3[/"replace(str(?xref),'^#91;^:#93;+:','')"/]
      v10 --o bind3
      bind3 --as--o v11
    end
  end