W3C home > Mailing lists > Public > public-semweb-lifesci@w3.org > February 2009

[COI] Re: COI protocol #8 demo progress

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 GMT

This archive was generated by hypermail 2.3.1 : Tuesday, 26 March 2013 18:00:54 GMT