[Gnumed-devel] import patient data - basic procedure

[Jerzy Luszawski]

My situation:
I had a simple list of patients with comments and dates of first and
last contact, which I needed imported to bare Gnumed database as initial
data.
Records were like this:
(firstname, lastname, start_date, due_date, description, ...)
They were just exported from my task manager / calendar application :)

Looking around database and code, no available importers were suitable
(my data are not HL7 formatted, or xDT etc.). Did I miss something? All
existing importers were so customized to accept specific data, that I
decided to carry out the procedure manually.

It meant:
1. create demographic patient data
2. create encounter with some narratives describing patient's problem

Unfortunately, neither inserting basic patient's demographic data nor
creating encounter is an easy one line SQL statement (why?). 
To make things harder, Postgres in version 9.1, which is still default
for current Ubuntu distribution, does not support updatable views yet.

I think many people wanting to work with Gnumed face the same problem
of importing existing data, which are not necessary HL7 compliant.
Providing simple guide for minimal data import may save lots of time
required to figure out internal details of database schema, eg. which
fields are mandatory and which constraints must be preserved. So I
wrote this guide, which is directed rather to some IT professional
helping with migration, than to end user. Maybe you will consider it
worth posting in wiki pages for developers?

Background details:

1. Creating usable patient's data requires inserting records to both
dem.identity and dem.names tables.

2. Creating an encounter which is properly placed in EMR tree requires
adding an episode. But you cannot add episode until you have encounter. 
Moreover text data for encounter are mainly stored as narratives in
clin_narrative table (well, you might use assessment_of_encounter field,
but it is not meant for long, multi-line text). And encounter is not
linked directly to episode, only through narratives. So, you have to
touch three tables to import one encounter. (I know it could be
simplified and still valid in the database schema, but it would not
create proper EMR tree and made further management difficult)

Here is my solution:
1. created a function to add a minimal person's data dem.add_person and
a function to add episode with encounter clin.insert_full_episode (both
included as attachments). They use named parameters (supported by
Postgres v. 9.1 does anyone use older version?). One may enhance them,
but they were meant to accept NULL values wherever possible and insert
only minimal data. I hope you will find them useful. Comments welcome.

2. imported external data to temporary table for further processing.
The important fact is that only one record for a patient existed.
3. added column fk_identity referencing dem.identity(pk) to this table
for further reference.
4. imported demographic data by calling dem.add_person storing
dem.identity.pk in temporary_import_table.fk_identity
like this:
UPDATE temporary_import_table set fk_identity = dem.add_person(
  temporary_import_table.lastname,
  temporary_import_table.firstname,
  temporary_import_table.gender  );

5. created unattributed episode with one encounter with one narrative
containing "description" from temporary file.
SELECT clin.insert_full_episode(
  fk_identity,          --  _pk_identity integer, 
  start_date,           --  _date  timestamp with time zone
  NULL,                 --  _health_issue integer
  'data import',        --  _episode_d text
  'data import',        --  _encounter_RFE text
  NULL,                 --  _encounter_AOE text
  (SELECT pk FROM clin.encounter_type 
    WHERE description = 'administrative encounter'),
                        --  _encounter_type integer
  Coalesce(description, 'no information'),      --  _s text
  NULL,                 --  _o text
  NULL,                 --  _a text
  NULL                  --  _p text
)
FROM temporary_import_table ;

Based on additional data available, I created some health_issues and
attached episodes to them, but this is specific to my data.

-- 
Regards,
Jerzy Luszawski

clin.insert_full_episode.sql
Description: Text Data

dem.add_person.sql
Description: Text Data