HCLSIG/RDB2RDF Use Cases

From W3C Wiki
HCLS Home Discussions

HCLS RDB2RDF Use Cases

This is a collection of SPARQL queries performed over relational databases. Included is the database structure, sample data, a SPARQL query with results, and an equivalent SQL query.

COI Patient Recruitment Demo

Diabetic patient data from the University of Texas Health Science Center is expressed as RDF views. The views are drawn on HL7/RIM and CDISK SDTM data structures. Some columns have been omitted from this use case for brevity.

Namespace Prefixes

PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX Person: <http://hospital.example/DB/Person#>
PREFIX Sex_DE: <http://hospital.example/DB/Zex_DE#> # note typo required for wiki
PREFIX Item_Medication: <http://hospital.example/DB/Item_Medication#>
PREFIX Medication: <http://hospital.example/DB/Medication#>
PREFIX Medication_DE: <http://hospital.example/DB/Medication_DE#>
PREFIX NDCcodes: <http://hospital.example/DB/NDCcodes#>
PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>


Person

ID MiddleName SexDE DateOfBirth
1234561 2 1983-01-02 00:00:00
1234562 3 1963-12-27 00:00:00
1234563 2 1983-02-25 00:00:00 
<http://hospital.example/DB/Person/ID.1234561#record>
   a hl7:Person ;    hl7:administrativeGenderCodePrintName Sex_DE:M ;
   hl7:livingSubjectBirthTime "1983-01-02T00:00:00Z"^^xsd:dateTime .
<http://hospital.example/DB/Person/ID.1234562#record>
   a hl7:Person ;    hl7:administrativeGenderCodePrintName Sex_DE:F ;
   hl7:livingSubjectBirthTime "1963-12-27T00:00:00Z"^^xsd:dateTime .
<http://hospital.example/DB/Person/ID.1234563#record>
   a hl7:Person ;    hl7:administrativeGenderCodePrintName Sex_DE:M ;
   hl7:livingSubjectBirthTime "1983-02-25T00:00:00Z"^^xsd:dateTime .


Sex_DE

ID EntryCode EntryName
2 1 Male
3 2 Female 
<http://hospital.example/DB/Zex_DE/ID.2#record>
    hl7:administrativeGenderCodePrintName "Male"@en_us ;
    Sex_DE:EntryMnemonic "M"@en_us .
<http://hospital.example/DB/Zex_DE/ID.3#record>
    hl7:administrativeGenderCodePrintName "Female"@en_us ;
    Sex_DE:EntryMnemonic "F"@en_us .


Item_Medication

ID PatientID ItemType
99999999002 1234561 ME
99999999003 1234562 ME
99999999004 1234562 ME 
<http://hospital.example/DB/Person/ID.1234561#record>
    hl7:substanceAdministration <http://hospital.example/DB/Item_Medication/ID.99999999002#record> .
<http://hospital.example/DB/Item_Medication/ID.99999999002#record>
    a hl7:SubstanceAdministration ;
    hl7:effectiveTime _:t1 .
_:t1 hl7:start "2007-09-28 00:00:00"^^xsd:dateTime .
<http://hospital.example/DB/Person/ID.1234562#record>
    hl7:substanceAdministration <http://hospital.example/DB/Item_Medication/ID.99999999003#record> ;
    hl7:substanceAdministration <http://hospital.example/DB/Item_Medication/ID.99999999004#record> .
<http://hospital.example/DB/Item_Medication/ID.99999999003#record>
    a hl7:SubstanceAdministration ;
    hl7:effectiveTime _:t2 .
_:t2 hl7:start "2007-09-28 00:00:00"^^xsd:dateTime .
<http://hospital.example/DB/Item_Medication/ID.99999999004#record>
    a hl7:SubstanceAdministration ;
    hl7:effectiveTime _:t3 .
_:t3 hl7:start "2008-07-28 00:00:00"^^xsd:dateTime .


Medication

ID ItemID Dose Refill QuantityToDispense DaysToTake PrescribedByID
88888888002 99999999002 2 6 180 45 1004682
88888888003 99999999002 2 0 180 45 1004683
88888888004 99999999003 2 6 180 45 1004682
88888888005 99999999004 4 6 180 45 1004682 
<http://hospital.example/DB/Item_Medication/ID.99999999002#record>
    hl7:consumable <http://hospital.example/DB/Medication_DE/ID.132139#record> .
_:t1 hl7:durationInDays 45 .
<http://hospital.example/DB/Item_Medication/ID.99999999003#record>
    hl7:consumable <http://hospital.example/DB/Medication_DE/ID.132139#record> .
<http://hospital.example/DB/Item_Medication/ID.99999999004#record>
    hl7:consumable <http://hospital.example/DB/Medication_DE/ID.132139#record> .
_:t2 hl7:durationInDays 45 .
<http://hospital.example/DB/Item_Medication/ID.99999999005#record>
    hl7:consumable <http://hospital.example/DB/Medication_DE/ID.132139#record> .
_:t3 hl7:durationInDays 45 .


Medication_DE:

ID Entry EntryCode EntryName NDC Strength Form UnitOfMeasure DrugName
132139 131933 98630 GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 54868079500 2.5-250 TABS MG GlipiZIDE-Metformin HCl 
<http://hospital.example/DB/Medication_DE/ID.132139#record> .
    hl7:displayName "GlipiZIDE-Metformin HCl 2.5-250 MG Tablet" .


NDCcodes:

ingredient RxCUI labelType name
6809 351273 Clinical Glipizide 2.5 MG / Metformin 500 MG Oral Tablet 
<http://hospital.example/DB/Medication_DE/ID.132139#record> .
    spl:activeIngredient _:i1 .
_:i1 spl:classCode 54868079500 .


SPARQL (HL7): 

PREFIX sdtm: <http://www.sdtm.org/vocabulary#>
PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>

SELECT ?patient ?dob ?sex ?takes ?indicDate # ?indicEnd ?contra
 WHERE {
  ?patient a sdtm:Patient ;
          sdtm:middleName ?middleName ;
          sdtm:dateTimeOfBirth ?dob ;
          sdtm:sex ?sex .

  [	  sdtm:subject ?patient ;
	  sdtm:standardizedMedicationName ?takes ;

	  spl:activeIngredient [ spl:classCode 6809 ] ;
          sdtm:startDateTimeOfMedication ?indicDate
  ] .
  OPTIONAL {
  [	  sdtm:subject ?patient ;
	  sdtm:standardizedMedicationName ?disqual ;
	  spl:activeIngredient [ spl:classCode 11289 ]
          sdtm:startDateTimeOfMedication ?indicDate
  ] }
} LIMIT 30


SQL: 

SELECT R_patient.ID AS patient
  FROM Person AS R_patient
       INNER JOIN Sex_DE AS R_sexEntry
       INNER JOIN Item_Medication AS R_indicItem
       INNER JOIN Medication AS R_indicMed
       INNER JOIN Medication_DE AS R_indicDE
       INNER JOIN NDCcodes AS R_indicCode
       LEFT OUTER JOIN (
       SELECT 6 AS _DISJOINT_, R_disqualMed.MedDictDE AS disqualDE, R_disqualItem.PerformedDTTM AS disqualDate, R_disqualItem.ID AS disqualItem, R_disqualMed.ID AS disqualMed, R_disqualDE.NDC AS disqualNDC, R_disqualItem.PatientID AS patient, R_disqualItem.EntryName AS takes
         FROM Item_Medication AS R_disqualItem
              INNER JOIN Medication AS R_disqualMed
              INNER JOIN Medication_DE AS R_disqualDE
              INNER JOIN NDCcodes AS R_disqualCode
        WHERE (R_disqualCode.NDC=R_disqualDE.NDC)
          AND (R_disqualCode.ingredient=11289)
          AND (R_disqualDE.ID=R_disqualMed.MedDictDE)
          AND (R_disqualItem.EntryName IS NOT NULL)
          AND (R_disqualItem.PatientID IS NOT NULL)
          AND (R_disqualItem.PerformedDTTM IS NOT NULL)
          AND (R_disqualMed.ID IS NOT NULL)
          AND (R_disqualMed.ItemID=R_disqualItem.ID)
                  ) AS G_opt6 ON (G_opt6.patient=R_patient.ID)
   AND (G_opt6.takes=R_indicItem.EntryName)
 WHERE (R_indicCode.NDC=R_indicDE.NDC)
   AND (R_indicCode.ingredient=6809)
   AND (R_indicDE.ID=R_indicMed.MedDictDE)
   AND (R_indicItem.EntryName IS NOT NULL)
   AND (R_indicItem.PatientID=R_patient.ID)
   AND (R_indicItem.PerformedDTTM IS NOT NULL)
   AND (R_indicMed.ID IS NOT NULL)
   AND (R_indicMed.ItemID=R_indicItem.ID)
   AND (R_patient.DateOfBirth IS NOT NULL)
   AND (R_patient.MiddleName IS NOT NULL)
   AND (R_sexEntry.EntryName IS NOT NULL)
   AND (R_sexEntry.ID=R_patient.SexDE);


BioRDF 7tm Demo

HCLS Home Discussions
Retrieved from "https://www.w3.org/wiki/index.php?title=HCLSIG/RDB2RDF_Use_Cases&oldid=26932"