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

Re: [COI] Re: COI protocol #8 demo progress

From: <helen.chen@agfa.com>
Date: Tue, 24 Feb 2009 21:11:59 -0500
To: eric@w3.org
Cc: public-semweb-lifesci@w3.org
Message-ID: <OF4FA479CA.60D95244-ON85257568.000B2842-85257568.000C1595@agfa.com>
Eric

Thanks for continuing working on SWtranformer program.  It is indeed an 
important piece of work. 

We have been trying to compile the program without much success. 

My colleague Jos De Roo is using Linux and your r398 compiles and works on 
his machine, but not the latest version. 
Holger and I use cygwin, and we can not compile it at all, see some of the 
compile log in the attached file.
Holger also made a window exe file but I can not run on my computer, 
although we are using the same Window XP system, may due to missing some 
vc++ library.

Will you please take a quick look at the log and see if you can give us 
some advises?

Thanks for your help.


Helen








Eric Prud'hommeaux <eric@w3.org> 
Sent by: public-semweb-lifesci-request@w3.org
02/21/2009 10:27 AM

To
public-semweb-lifesci@w3.org
cc

Subject
[COI] Re: COI protocol #8 demo progress







* 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 Wednesday, 25 February 2009 02:12:50 GMT

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