- From: Eric Prud'hommeaux <eric@w3.org>
- Date: Wed, 1 Oct 2008 05:45:07 -0400
- To: public-hcls-coi@w3.org
- Message-ID: <20081001094507.GB4276@w3.org>
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 Wednesday, 1 October 2008 15:13:43 UTC