HCLSIG/RDB2RDF Use Cases
< HCLSIG
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 |