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

COI protocol #8 demo update: COI patient DB -> HL7 concept mapping

From: <helen.chen@agfa.com>
Date: Tue, 7 Oct 2008 10:57:49 -0400
To: public-hcls-coi@w3.org
Message-ID: <OF34335162.B5A766EF-ON852574DB.0050E106-852574DB.00523416@agfa.com>
As suggested by Eric, I took a look at how the concepts used in Eric's 
Query statement below can be expressed in HL7 vocabulary. 

Here is my first attempt.   Will fill more details during our Tcon. 

http://esw.w3.org/topic/HCLS/ClinicalObservationsInteroperability/DB2HL7Mapping.html

I also discussed with Eric about embed a SW reasoner 'Euler' into the demo 
to do inferencing, but unfortunately, there is no current C++ version of 
euler.  We have a C# or java, prolog vresion though, will discuss with 
Eric how to proceed on that front.

Helen





Eric Prud'hommeaux <eric@w3.org> 
Sent by: public-hcls-coi-request@w3.org
10/01/2008 05:45 AM

To
public-hcls-coi@w3.org
cc

Subject
Re: COI protocol #8 demo progress







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.



Received on Tuesday, 7 October 2008 14:58:55 GMT

This archive was generated by hypermail 2.2.0+W3C-0.50 : Tuesday, 7 October 2008 14:58:55 GMT