W3C home > Mailing lists > Public > public-hcls-coi@w3.org > October to December 2008

Re: Re: COI protocol #8 demo progress

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.



Received on Saturday, 11 October 2008 16:13:41 GMT

This archive was generated by hypermail 2.2.0+W3C-0.50 : Saturday, 11 October 2008 16:13:41 GMT