- 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.
Attachments
- application/octet-stream attachment: signature.asc
Received on Tuesday, 7 October 2008 14:58:55 UTC