- From: Eric Prud'hommeaux <eric@w3.org>
- Date: Sat, 11 Oct 2008 12:11:36 -0400
- To: public-hcls-coi@w3.org
- Message-ID: <20081011161136.GB13414@w3.org>
I've created an sdtm query — sdtm.rq
to query data mapped from hl7 — hl7-sdtm.rq
which is mapped from the db — db-hl7.rq
I poked around to look for things like the relationship between a
patient and a medication, but am not super confident that I've aligned
with existing structures (say, in XML).
You can try this at home by downloading an executable for your
operating system from http://www.w3.org/2008/04/SPARQLfed/ and running
cat sdtm.rq | \
SWtransformer -q - hl7-sdtm.rq | \
SWtransformer -q - db-hl7.rq -s http://hospital.example/DB/ | \
mysql -u root DiabeticPatientsDataSet --table
where the db comes from http://www.w3.org/2008/04/DiabeticPatientsDataSet/
* Eric Prud'hommeaux <eric@w3.org> [2008-10-01 05:45-0400]
> I've extended these queries to include (contra)indications by ingredient:
> [[
> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
> PREFIX Person: <http://hospital.example/DB/Person#>
> PREFIX Sex_DE: <http://hospital.example/DB/Sex_DE#>
> 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#>
>
> SELECT ?person ?dob ?sex ?takes ?contraItem WHERE {
> ?person Person:MiddleName ?middleName ;
> Person:DateOfBirth ?dob ;
> Person:SexDE ?sexEntry .
> ?sexEntry Sex_DE:EntryName ?sex .
>
> ?indicItem Item_Medication:PatientID ?person ;
> Item_Medication:EntryName ?takes .
> ?indicMed Medication:ItemID ?indicItem ;
> Medication:MedDictDE ?indicDE .
> ?indicDE Medication_DE:NDC ?indicNDC .
> ?indicCode NDCcodes:NDC ?indicNDC ;
> NDCcodes:ingredient 6809 .
>
> OPTIONAL {
> ?contraItem Item_Medication:PatientID ?person .
> ?contraMed Medication:ItemID ?contraItem ;
> Medication:MedDictDE ?contraDE .
> ?contraDE Medication_DE:NDC ?contraNDC .
> ?contraCode NDCcodes:NDC ?contraNDC ;
> NDCcodes:ingredient 11289
> }
> FILTER (?dob < "1984-01-01T00:00:00"^^xsd:dateTime && !BOUND(?contraItem))
> } LIMIT 30
> ]]
>
> yielding:
> [[
> +---------+---------------------+--------+-------------------------------------------+
> | person | dob | sex | takes |
> +---------+---------------------+--------+-------------------------------------------+
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS |
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS |
> | 1517000 | 1958-01-01 00:00:00 | Male | Metformin HCl 500 MG Tablet |
> | 1517000 | 1958-01-01 00:00:00 | Male | Metformin HCl 500 MG Tablet |
> | 1517403 | 1981-05-11 00:00:00 | Male | Metformin HCl 500 MG Tablet |
> | 1505583 | 1950-02-25 00:00:00 | Female | Metformin HCl 500 MG Tablet |
> | 1234561 | 1983-01-02 00:00:00 | Male | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet |
> | 1234561 | 1983-01-02 00:00:00 | Male | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet |
> | 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet |
> | 1234562 | 1963-12-27 00:00:00 | Female | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet |
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS |
> | 1517441 | 1964-09-01 00:00:00 | Female | Glucophage TABS |
> +---------+---------------------+--------+-------------------------------------------+
> ]]
>
>
> The birthdate limitation is set for < 1984-01-01 in order to eliminate
> +---------+---------------------+------+-------------------------------------------+
> | person | dob | sex | takes |
> +---------+---------------------+------+-------------------------------------------+
> | 1234563 | 1983-02-25 00:00:00 | Male | GlipiZIDE-Metformin HCl 2.5-250 MG Tablet |
> +---------+---------------------+------+-------------------------------------------+
>
> The dupes appear to come from identical Medication.ItemID for probably
> different dates:
> [[
> mysql> select * from Medication where ItemID=10276300039;
> +-------------+-------------+--------------------+-------------+------+--------+------------------------+--------------------+------------+----------------+-----------+
> | ID | ItemID | FormOfMedicationDE | FreqUnitsDE | Dose | Refill | RoutOfAdministrationDE | QuantityToDispense | DaysToTake | PrescribedByID | MedDictDE |
> +-------------+-------------+--------------------+-------------+------+--------+------------------------+--------------------+------------+----------------+-----------+
> | 10276300041 | 10276300039 | 0 | 2 | 1 | 0 | 15 | 0 | 0 | 1524566 | 90346 |
> | 10307800003 | 10276300039 | 0 | 2 | 1 | 0 | 15 | 0 | 0 | 1524566 | 90346 |
> +-------------+-------------+--------------------+-------------+------+--------+------------------------+--------------------+------------+----------------+-----------+
> ]]
> Fixing date ranges in the query will help this, I expect.
>
>
> * Eric Prud'hommeaux <eric@w3.org> [2008-10-01 01:32-0400]
> > Given some data that Helen added to db:
> > <http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockPerson.csv>
> > <http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockMedication.csv>
> > <http://www.w3.org/2008/04/DiabeticPatientsDataSet/MockItem_Medication.csv>
> > and the latest version of SWObjects:
> > <http://www.w3.org/2008/04/SPARQLfed/>
> > I tested the SPARQL-SQL mapping on a sample query
> > tests/execute_HealthCare1 tests/query_stem:Person.rq -s http://hospital.example/DB/
> > <http://www.w3.org/2008/04/SPARQLfed/tests/query_stem:Person.rq>
> >
> > got an SQL query:
> > [[
> > SELECT person.id AS person, person.DateOfBirth AS dob, sexEntry.EntryName AS sex, medication.id AS medication, opt1.contraIndic AS contraIndic
> > FROM Person AS person
> > INNER JOIN Sex_DE AS sexEntry ON sexEntry.id=person.SexDE
> > INNER JOIN Item_Medication AS medication ON medication.PatientID=person.id AND medication.EntryName="GlipiZIDE-Metformin HCl 2.5-250 MG Tablet"
> > LEFT OUTER JOIN (
> > SELECT contraIndic.PatientID AS person, contraIndic.id AS contraIndic
> > FROM Item_Medication AS contraIndic
> > WHERE contraIndic.EntryName="Warfarin Sodium 5 MG Tablet"
> > ) AS opt1 ON opt1.person=person.id
> > WHERE (person.DateOfBirth < "1980-01-01 00:00:00" AND !(opt1.contraIndic IS NOT NULL))
> > LIMIT 10
> > ]]
> >
> > executed it, and got:
> > +---------+---------------------+--------+-------------+-------------+
> > | person | dob | sex | medication | contraIndic |
> > +---------+---------------------+--------+-------------+-------------+
> > | 1234562 | 1963-12-27 00:00:00 | Female | 99999999003 | NULL |
> > | 1234562 | 1963-12-27 00:00:00 | Female | 99999999004 | NULL |
> > +---------+---------------------+--------+-------------+-------------+
> >
> > I encourage folks to play more. Also, probably time to start working on
> > the construct rules that allow the SPARQL query to work over HL7 data.
> >
> > Holger, could you build a DOS/WIN version of the code and distribute it
> > or make it available to folks who want to help?
> > --
> > -eric
> >
> > office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
> > mobile: +1.617.599.3509
> >
> > (eric@w3.org)
> > Feel free to forward this message to any list for any purpose other than
> > email address distribution.
>
>
>
> --
> -eric
>
> office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
> mobile: +1.617.599.3509
>
> (eric@w3.org)
> Feel free to forward this message to any list for any purpose other than
> email address distribution.
--
-eric
office: +1.617.258.5741 32-G528, MIT, Cambridge, MA 02144 USA
mobile: +1.617.599.3509
(eric@w3.org)
Feel free to forward this message to any list for any purpose other than
email address distribution.
Attachments
- text/plain attachment: sdtm.rq
- text/plain attachment: hl7-sdtm.rq
- text/plain attachment: db-hl7.rq
Received on Saturday, 11 October 2008 16:13:41 UTC