- From: Eric Prud'hommeaux <eric@w3.org>
- Date: Sat, 21 Feb 2009 10:27:00 -0500
- To: public-semweb-lifesci@w3.org
- Message-ID: <20090221152700.GA14185@w3.org>
* Eric Prud'hommeaux <eric@w3.org> [2008-10-11 12:11-0400]
> 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/
The latest from the const-happy branch (390) pushes filters down to SQL
sdtm: FILTER (?code = 6809 || ?code = 6810)
becomes rim: FILTER ( ?code = 6809 || ?code = 6810 )
becomes sql: WHERE (indicCode_gen0.ingredient = 6809 OR indicCode_gen0.ingredient = 6810)
This should build with [[
svn co https://swobjects.svn.sourceforge.net/svnroot/swobjects/branches/const-happy const-happy
cd const-happy
make bin/SWtransformer
]]
and then you can test it à la:
[[
eric@mouni:/tmp/const-happy$ bin/SWtransformer -q tests/healthCare/in-list/sdtm.rq tests/healthCare/in-list/hl7-sdtm.rq | bin/SWtransformer -q - tests/healthCare/in-list/db-hl7.rq -s http://hospital.example/DB/ | mysql -u root DiabeticPatientsDataSet
patient dob sex takes indicDate
1517441 1964-09-01 00:00:00 Female Glucophage TABS 2007-07-23 00:00:00
1517441 1964-09-01 00:00:00 Female Glucophage TABS 2007-07-23 00:00:00
1517000 1958-01-01 00:00:00 Male Metformin HCl 500 MG Tablet 2007-06-29 00:00:00
1517000 1958-01-01 00:00:00 Male Metformin HCl 500 MG Tablet 2007-06-29 00:00:00
1517403 1981-05-11 00:00:00 Male Metformin HCl 500 MG Tablet 2008-01-03 00:00:00
1505583 1950-02-25 00:00:00 Female Metformin HCl 500 MG Tablet 2008-01-14 00:00:00
1234561 1983-01-02 00:00:00 Male GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 2007-09-28 00:00:00
1234561 1983-01-02 00:00:00 Male GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 2007-09-28 00:00:00
1234562 1963-12-27 00:00:00 Female GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 2007-09-28 00:00:00
1234562 1963-12-27 00:00:00 Female GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 2008-07-28 00:00:00
1234563 1983-02-25 00:00:00 Male GlipiZIDE-Metformin HCl 2.5-250 MG Tablet 2007-07-28 00:00:00
1517441 1964-09-01 00:00:00 Female Glucophage TABS 2007-07-23 00:00:00
1517441 1964-09-01 00:00:00 Female Glucophage TABS 2007-07-23 00:00:00
1516986 1995-06-13 00:00:00 Male Metformin HCl 1000 MG Tablet 2007-12-04 00:00:00
]] — (sample run, using healthCare/in-list tests)
> * 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.
> 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 ;
>
> # nothing by ingredient in sdtm/caBIG/...
> spl:activeIngredient [ spl:classCode 6809 ] ;
> sdtm:startDateTimeOfMedication ?indicDate
> # ; sdtm:endDateTimeOfMedication ?indicEnd
> ] .
> # OPTIONAL {
> # [ sdtm:subject ?patient ;
> # sdtm:standardizedMedicationName ?contra ;
> # spl:activeIngredient [ spl:classCode 11289 ]
> # sdtm:effectiveTime [
> # sdtm:startDateTimeOfMedication ?contraDate
> # # ; sdtm:endDateTimeOfMedication ?contraEnd
> # ] .
> # }
> } LIMIT 30
> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
> PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
> PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
>
> PREFIX sdtm: <http://www.sdtm.org/vocabulary#>
> PREFIX bridg: <>
> PREFIX caBIG: <>
> PREFIX CDASH: <>
> PREFIX MHONG: <>
> PREFIX dam: <>
>
> CONSTRUCT {
> ?patient
> a sdtm:Patient ;
> sdtm:middleName ?middleName ;
> sdtm:dateTimeOfBirth ?dob ;
> sdtm:sex ?sex .
> [ a sdtm:ConcomitantMedication ;
> sdtm:subject ?patient ;
> sdtm:standardizedMedicationName ?takes ;
>
> # nothing by ingredient in sdtm/caBIG/...
> spl:activeIngredient [ spl:classCode ?ingred ] ;
> sdtm:startDateTimeOfMedication ?start
> # ; sdtm:endDateTimeOfMedication ?end
> ] .
> } WHERE {
> ?patient
> a hl7:Person ;
> hl7:entityName ?middleName ;
> hl7:livingSubjectBirthTime ?dob ;
> hl7:administrativeGenderCodePrintName ?sex ;
> hl7:substanceAdministration [
> a hl7:SubstanceAdministration ;
> hl7:consumable [
> hl7:displayName ?takes ;
> spl:activeIngredient [
> spl:classCode ?ingred
> ]
> ] ;
> hl7:effectiveTime [
> hl7:start ?start
> # hl7:end ?end
> ]
> ] .
> }
> 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#>
>
> PREFIX hl7: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
> PREFIX spl: <http://www.hl7.org/v3ballot/xml/infrastructure/vocabulary/vocabulary#>
>
> CONSTRUCT {
> ?person
> a hl7:Person ;
> hl7:entityName ?middleName ;
> hl7:livingSubjectBirthTime ?dob ;
> hl7:administrativeGenderCodePrintName ?sex ;
> hl7:substanceAdministration [
> a hl7:SubstanceAdministration ;
> hl7:consumable [
> hl7:displayName ?takes ;
> spl:activeIngredient [
> spl:classCode ?ingred
> ]
> ] ;
> hl7:effectiveTime [
> hl7:start ?indicDate
> # hl7:end (?indicDate + ?indicDuration)
> ]
> ] .
> } WHERE {
> ?person Person:MiddleName ?middleName ;
> Person:DateOfBirth ?dob ;
> Person:SexDE ?sexEntry .
>
> OPTIONAL {
> ?sexEntry Sex_DE:EntryName ?sex .
> }
>
> OPTIONAL {
> ?indicItem Item_Medication:PatientID ?person ;
> Item_Medication:PerformedDTTM ?indicDate ;
> Item_Medication:EntryName ?takes .
> ?indicMed Medication:ItemID ?indicItem ;
> Medication:DaysToTake ?indicDuration ;
> Medication:MedDictDE ?indicDE .
> ?indicDE Medication_DE:NDC ?indicNDC .
> }
>
> OPTIONAL {
> ?indicCode NDCcodes:NDC ?indicNDC ;
> NDCcodes:ingredient ?ingred .
> }
> } LIMIT 30
--
-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.
There are subtle nuances encoded in font variation and clever layout
which can only be seen by printing this message on high-clay paper.
Received on Saturday, 21 February 2009 15:27:12 UTC