Medical Image Storage

Objective

Create a foundational system for storing medical images with associated patient information and study metadata. This enables organized medical imaging workflows while maintaining data integrity.

Step 1: Create Patients Table

Store patient demographic information.

CREATE TABLE medical_patients (
    id INTEGER PRIMARY KEY,
    patient_id VARCHAR(50) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(20),
    medical_record_number VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Imaging Studies Table

Store imaging study information.

CREATE TABLE imaging_studies (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    study_uid VARCHAR(100) UNIQUE,
    study_date DATE NOT NULL,
    study_time TIME,
    modality VARCHAR(20) NOT NULL,
    study_description VARCHAR(255),
    referring_physician VARCHAR(200),
    performing_physician VARCHAR(200),
    institution_name VARCHAR(200),
    accession_number VARCHAR(50),
    series_count INTEGER DEFAULT 0,
    image_count INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'acquired',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES medical_patients(id)
);

Step 3: Create Image Series Table

Organize images into series.

CREATE TABLE image_series (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    series_uid VARCHAR(100) UNIQUE,
    series_number INTEGER,
    series_description VARCHAR(255),
    modality VARCHAR(20),
    body_part VARCHAR(100),
    patient_position VARCHAR(50),
    series_date DATE,
    series_time TIME,
    image_count INTEGER DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES imaging_studies(id)
);

Step 4: Create Medical Images Table

Store individual medical images.

CREATE TABLE medical_images (
    id INTEGER PRIMARY KEY,
    series_id INTEGER NOT NULL,
    instance_uid VARCHAR(100) UNIQUE,
    instance_number INTEGER,
    image_data IMAGE(PNG),
    image_thumbnail IMAGE(JPEG),
    acquisition_date DATE,
    acquisition_time TIME,
    pixel_spacing VARCHAR(50),
    slice_thickness DECIMAL(6, 2),
    slice_location DECIMAL(8, 2),
    window_center INTEGER,
    window_width INTEGER,
    rows INTEGER,
    columns INTEGER,
    bits_stored INTEGER,
    file_size BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (series_id) REFERENCES image_series(id)
);

Step 5: Create Study Notes Table

Store radiologist observations.

CREATE TABLE study_notes (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    note_type VARCHAR(50),
    note_text TEXT,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (study_id) REFERENCES imaging_studies(id)
);

Step 6: Insert Sample Patients

Add patient records.

INSERT INTO medical_patients (id, patient_id, first_name, last_name, date_of_birth, gender, medical_record_number) VALUES
    (1, 'PAT-001', 'John', 'Smith', '1965-03-15', 'Male', 'MRN-10001'),
    (2, 'PAT-002', 'Mary', 'Johnson', '1978-07-22', 'Female', 'MRN-10002'),
    (3, 'PAT-003', 'Robert', 'Williams', '1955-11-08', 'Male', 'MRN-10003'),
    (4, 'PAT-004', 'Sarah', 'Brown', '1982-04-30', 'Female', 'MRN-10004'),
    (5, 'PAT-005', 'Michael', 'Davis', '1970-09-12', 'Male', 'MRN-10005');

Step 7: Insert Imaging Studies

Add study records.

INSERT INTO imaging_studies (id, patient_id, study_uid, study_date, study_time, modality, study_description, referring_physician, performing_physician, institution_name, accession_number, series_count, image_count, status) VALUES
    (1, 1, '1.2.840.10001.1.1', '2024-01-15', '09:30:00', 'CT', 'CT Chest with Contrast', 'Dr. Anderson', 'Dr. Miller', 'City General Hospital', 'ACC-2024-001', 3, 450, 'completed'),
    (2, 1, '1.2.840.10001.1.2', '2024-01-20', '14:15:00', 'XR', 'Chest X-Ray PA and Lateral', 'Dr. Anderson', 'Tech. Wilson', 'City General Hospital', 'ACC-2024-002', 1, 2, 'completed'),
    (3, 2, '1.2.840.10001.2.1', '2024-01-18', '10:00:00', 'MR', 'MRI Brain without Contrast', 'Dr. Thompson', 'Dr. Garcia', 'City General Hospital', 'ACC-2024-003', 5, 180, 'completed'),
    (4, 3, '1.2.840.10001.3.1', '2024-01-22', '11:45:00', 'CT', 'CT Abdomen Pelvis', 'Dr. White', 'Dr. Miller', 'City General Hospital', 'ACC-2024-004', 2, 380, 'in_progress'),
    (5, 4, '1.2.840.10001.4.1', '2024-01-25', '08:30:00', 'US', 'Ultrasound Abdomen Complete', 'Dr. Lee', 'Tech. Brown', 'City General Hospital', 'ACC-2024-005', 1, 45, 'completed'),
    (6, 5, '1.2.840.10001.5.1', '2024-01-26', '15:00:00', 'XR', 'X-Ray Lumbar Spine', 'Dr. Anderson', 'Tech. Wilson', 'City General Hospital', 'ACC-2024-006', 1, 4, 'pending_review');

Step 8: Insert Image Series

Add series information.

INSERT INTO image_series (id, study_id, series_uid, series_number, series_description, modality, body_part, patient_position, image_count) VALUES
    -- CT Chest series
    (1, 1, '1.2.840.10001.1.1.1', 1, 'Axial Pre-Contrast', 'CT', 'CHEST', 'HFS', 150),
    (2, 1, '1.2.840.10001.1.1.2', 2, 'Axial Post-Contrast', 'CT', 'CHEST', 'HFS', 150),
    (3, 1, '1.2.840.10001.1.1.3', 3, 'Coronal Reconstruction', 'CT', 'CHEST', 'HFS', 150),
    -- Chest X-Ray series
    (4, 2, '1.2.840.10001.1.2.1', 1, 'PA and Lateral Views', 'XR', 'CHEST', 'STANDING', 2),
    -- MRI Brain series
    (5, 3, '1.2.840.10001.2.1.1', 1, 'Axial T1', 'MR', 'BRAIN', 'HFS', 30),
    (6, 3, '1.2.840.10001.2.1.2', 2, 'Axial T2', 'MR', 'BRAIN', 'HFS', 30),
    (7, 3, '1.2.840.10001.2.1.3', 3, 'Axial FLAIR', 'MR', 'BRAIN', 'HFS', 30),
    (8, 3, '1.2.840.10001.2.1.4', 4, 'Sagittal T1', 'MR', 'BRAIN', 'HFS', 45),
    (9, 3, '1.2.840.10001.2.1.5', 5, 'Coronal T2', 'MR', 'BRAIN', 'HFS', 45),
    -- Ultrasound series
    (10, 5, '1.2.840.10001.4.1.1', 1, 'Abdomen Survey', 'US', 'ABDOMEN', 'SUPINE', 45);

Step 9: Insert Sample Images

Add image metadata.

INSERT INTO medical_images (id, series_id, instance_uid, instance_number, acquisition_date, acquisition_time, slice_thickness, slice_location, window_center, window_width, rows, columns, bits_stored, file_size) VALUES
    -- CT Chest images (sample of series 1)
    (1, 1, '1.2.840.10001.1.1.1.1', 1, '2024-01-15', '09:31:00', 2.5, -200.0, 40, 400, 512, 512, 12, 524288),
    (2, 1, '1.2.840.10001.1.1.1.2', 2, '2024-01-15', '09:31:01', 2.5, -197.5, 40, 400, 512, 512, 12, 524288),
    (3, 1, '1.2.840.10001.1.1.1.3', 3, '2024-01-15', '09:31:02', 2.5, -195.0, 40, 400, 512, 512, 12, 524288),
    -- Chest X-Ray images
    (4, 4, '1.2.840.10001.1.2.1.1', 1, '2024-01-20', '14:16:00', NULL, NULL, 2048, 4096, 3000, 2500, 14, 15000000),
    (5, 4, '1.2.840.10001.1.2.1.2', 2, '2024-01-20', '14:17:00', NULL, NULL, 2048, 4096, 3000, 2500, 14, 15000000),
    -- MRI Brain images (sample)
    (6, 5, '1.2.840.10001.2.1.1.1', 1, '2024-01-18', '10:05:00', 5.0, -80.0, NULL, NULL, 256, 256, 12, 131072),
    (7, 5, '1.2.840.10001.2.1.1.2', 2, '2024-01-18', '10:05:03', 5.0, -75.0, NULL, NULL, 256, 256, 12, 131072),
    (8, 5, '1.2.840.10001.2.1.1.3', 3, '2024-01-18', '10:05:06', 5.0, -70.0, NULL, NULL, 256, 256, 12, 131072);

Step 10: Insert Study Notes

Add clinical notes.

INSERT INTO study_notes (id, study_id, note_type, note_text, created_by) VALUES
    (1, 1, 'clinical_indication', 'Persistent cough, evaluate for pulmonary pathology', 'Dr. Anderson'),
    (2, 1, 'preliminary_finding', 'No acute pulmonary abnormality identified. Small bilateral pleural effusions.', 'Dr. Miller'),
    (3, 3, 'clinical_indication', 'Headaches and dizziness for 2 weeks', 'Dr. Thompson'),
    (4, 3, 'preliminary_finding', 'No acute intracranial abnormality. Age-appropriate white matter changes.', 'Dr. Garcia'),
    (5, 6, 'clinical_indication', 'Lower back pain radiating to left leg', 'Dr. Anderson');

Step 11: Query Patient Studies

Get all studies for a patient.

SELECT
    mp.first_name || ' ' || mp.last_name as patient_name,
    mp.medical_record_number,
    ist.study_date,
    ist.modality,
    ist.study_description,
    ist.series_count,
    ist.image_count,
    ist.status
FROM medical_patients mp
INNER JOIN imaging_studies ist ON mp.id = ist.patient_id
WHERE mp.id = 1
ORDER BY ist.study_date DESC;

Step 12: Get Study Details with Series

View complete study structure.

SELECT
    ist.study_description,
    ist.modality as study_modality,
    ims.series_number,
    ims.series_description,
    ims.body_part,
    ims.image_count
FROM imaging_studies ist
INNER JOIN image_series ims ON ist.id = ims.study_id
WHERE ist.id = 1
ORDER BY ims.series_number;

Step 13: Studies by Modality Summary

Analyze imaging volume.

SELECT
    modality,
    COUNT(*) as study_count,
    SUM(image_count) as total_images,
    AVG(image_count) as avg_images_per_study
FROM imaging_studies
GROUP BY modality
ORDER BY study_count DESC;

Step 14: Recent Studies Pending Review

Find studies needing attention.

SELECT
    mp.first_name || ' ' || mp.last_name as patient_name,
    ist.study_date,
    ist.modality,
    ist.study_description,
    ist.referring_physician,
    ist.status
FROM imaging_studies ist
INNER JOIN medical_patients mp ON ist.patient_id = mp.id
WHERE ist.status IN ('pending_review', 'in_progress')
ORDER BY ist.study_date;

Cleanup (Optional)

DROP TABLE IF EXISTS study_notes;
DROP TABLE IF EXISTS medical_images;
DROP TABLE IF EXISTS image_series;
DROP TABLE IF EXISTS imaging_studies;
DROP TABLE IF EXISTS medical_patients;

Expected Outcomes

• Patient records organized
• Studies tracked by modality
• Series structure maintained
• Images stored with metadata
• Clinical notes attached

Imaging Modalities

Key Concepts Learned

• Medical image hierarchy (Study > Series > Image)
• Patient-study relationships
• Modality-specific metadata
• Clinical workflow status tracking
• Study notes and observations