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.