X-Ray Image Management

Objective

Create a specialized system for managing X-ray radiographic images with support for multiple views, anatomical regions, and diagnostic annotations.

Step 1: Create X-Ray Patients Table

Store patient information for X-ray studies.

CREATE TABLE xray_patients (
    id INTEGER PRIMARY KEY,
    patient_id VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200),
    date_of_birth DATE,
    gender VARCHAR(20),
    height_cm INTEGER,
    weight_kg DECIMAL(5, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create X-Ray Exams Table

Store X-ray examination records.

CREATE TABLE xray_exams (
    id INTEGER PRIMARY KEY,
    patient_id INTEGER NOT NULL,
    exam_date DATE NOT NULL,
    exam_time TIME,
    body_region VARCHAR(100) NOT NULL,
    exam_type VARCHAR(100),
    clinical_indication TEXT,
    referring_physician VARCHAR(200),
    radiologist VARCHAR(200),
    technologist VARCHAR(200),
    exam_status VARCHAR(50) DEFAULT 'acquired',
    report_status VARCHAR(50) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES xray_patients(id)
);

Step 3: Create X-Ray Images Table

Store individual X-ray images.

CREATE TABLE xray_images (
    id INTEGER PRIMARY KEY,
    exam_id INTEGER NOT NULL,
    image_file IMAGE(PNG),
    thumbnail IMAGE(JPEG),
    view_position VARCHAR(50) NOT NULL,
    laterality VARCHAR(20),
    patient_orientation VARCHAR(50),
    exposure_kvp INTEGER,
    exposure_mas DECIMAL(6, 2),
    distance_cm INTEGER,
    image_quality VARCHAR(50),
    rows INTEGER,
    columns INTEGER,
    bits_allocated INTEGER,
    file_size BIGINT,
    acquisition_time TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES xray_exams(id)
);

Step 4: Create Annotations Table

Store radiologist markings and findings.

CREATE TABLE xray_annotations (
    id INTEGER PRIMARY KEY,
    image_id INTEGER NOT NULL,
    annotation_type VARCHAR(50),
    x_position DECIMAL(6, 2),
    y_position DECIMAL(6, 2),
    width DECIMAL(6, 2),
    height DECIMAL(6, 2),
    label VARCHAR(255),
    description TEXT,
    severity VARCHAR(50),
    annotated_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (image_id) REFERENCES xray_images(id)
);

Step 5: Create Reports Table

Store radiology reports.

CREATE TABLE xray_reports (
    id INTEGER PRIMARY KEY,
    exam_id INTEGER NOT NULL,
    findings TEXT,
    impression TEXT,
    recommendations TEXT,
    report_status VARCHAR(50) DEFAULT 'draft',
    reported_by VARCHAR(200),
    reported_at TIMESTAMP,
    verified_by VARCHAR(200),
    verified_at TIMESTAMP,
    FOREIGN KEY (exam_id) REFERENCES xray_exams(id)
);

Step 6: Insert Sample Patients

Add patient records.

INSERT INTO xray_patients (id, patient_id, name, date_of_birth, gender, height_cm, weight_kg) VALUES
    (1, 'XR-PAT-001', 'James Wilson', '1958-05-20', 'Male', 178, 82.5),
    (2, 'XR-PAT-002', 'Emily Chen', '1985-11-12', 'Female', 165, 58.0),
    (3, 'XR-PAT-003', 'Robert Taylor', '1972-03-08', 'Male', 182, 95.0),
    (4, 'XR-PAT-004', 'Lisa Martinez', '1990-07-25', 'Female', 160, 55.5),
    (5, 'XR-PAT-005', 'David Brown', '1965-09-30', 'Male', 175, 78.0);

Step 7: Insert X-Ray Exams

Add examination records.

INSERT INTO xray_exams (id, patient_id, exam_date, exam_time, body_region, exam_type, clinical_indication, referring_physician, radiologist, technologist, exam_status, report_status) VALUES
    (1, 1, '2024-01-15', '09:30:00', 'CHEST', 'Chest X-Ray', 'Shortness of breath, rule out pneumonia', 'Dr. Anderson', 'Dr. Miller', 'Tech. Wilson', 'completed', 'final'),
    (2, 1, '2024-01-15', '09:45:00', 'HAND', 'Hand X-Ray', 'Right hand pain after fall', 'Dr. Anderson', 'Dr. Miller', 'Tech. Wilson', 'completed', 'final'),
    (3, 2, '2024-01-16', '10:15:00', 'SPINE', 'Lumbar Spine X-Ray', 'Lower back pain', 'Dr. Thompson', 'Dr. Garcia', 'Tech. Brown', 'completed', 'final'),
    (4, 3, '2024-01-17', '14:00:00', 'CHEST', 'Chest X-Ray', 'Pre-operative evaluation', 'Dr. White', 'Dr. Miller', 'Tech. Wilson', 'completed', 'pending'),
    (5, 4, '2024-01-18', '11:30:00', 'KNEE', 'Knee X-Ray', 'Left knee pain, possible fracture', 'Dr. Lee', 'Dr. Garcia', 'Tech. Brown', 'completed', 'final'),
    (6, 5, '2024-01-19', '08:45:00', 'ABDOMEN', 'Abdominal X-Ray', 'Abdominal pain, constipation', 'Dr. Anderson', 'Dr. Miller', 'Tech. Davis', 'completed', 'draft');

Step 8: Insert X-Ray Images

Add image records with views.

INSERT INTO xray_images (id, exam_id, view_position, laterality, patient_orientation, exposure_kvp, exposure_mas, distance_cm, image_quality, rows, columns, bits_allocated, file_size, acquisition_time) VALUES
    -- Chest X-Ray (PA and Lateral)
    (1, 1, 'PA', NULL, 'STANDING', 120, 2.5, 180, 'good', 3000, 2500, 14, 15000000, '2024-01-15 09:31:00'),
    (2, 1, 'LATERAL', 'LEFT', 'STANDING', 125, 4.0, 180, 'good', 3000, 2500, 14, 15000000, '2024-01-15 09:33:00'),
    -- Hand X-Ray (3 views)
    (3, 2, 'PA', 'RIGHT', 'PRONE', 55, 3.0, 100, 'excellent', 2048, 2048, 12, 8388608, '2024-01-15 09:46:00'),
    (4, 2, 'OBLIQUE', 'RIGHT', 'PRONE', 55, 3.0, 100, 'excellent', 2048, 2048, 12, 8388608, '2024-01-15 09:47:00'),
    (5, 2, 'LATERAL', 'RIGHT', 'LATERAL', 55, 3.0, 100, 'good', 2048, 2048, 12, 8388608, '2024-01-15 09:48:00'),
    -- Lumbar Spine (AP and Lateral)
    (6, 3, 'AP', NULL, 'SUPINE', 80, 40.0, 100, 'good', 2500, 2000, 14, 10000000, '2024-01-16 10:16:00'),
    (7, 3, 'LATERAL', NULL, 'LATERAL', 90, 80.0, 100, 'acceptable', 2500, 2000, 14, 10000000, '2024-01-16 10:18:00'),
    -- Knee X-Ray (3 views)
    (8, 5, 'AP', 'LEFT', 'STANDING', 60, 5.0, 100, 'excellent', 2500, 2000, 12, 10000000, '2024-01-18 11:31:00'),
    (9, 5, 'LATERAL', 'LEFT', 'LATERAL', 65, 6.0, 100, 'excellent', 2500, 2000, 12, 10000000, '2024-01-18 11:33:00'),
    (10, 5, 'SUNRISE', 'LEFT', 'PRONE', 60, 4.0, 100, 'good', 2500, 2000, 12, 10000000, '2024-01-18 11:35:00');

Step 9: Insert Annotations

Add radiologist findings.

INSERT INTO xray_annotations (id, image_id, annotation_type, x_position, y_position, width, height, label, description, severity, annotated_by) VALUES
    -- Chest findings
    (1, 1, 'region', 45.5, 60.2, 15.0, 12.0, 'Right Lower Lobe Opacity', 'Patchy opacity consistent with pneumonia', 'moderate', 'Dr. Miller'),
    (2, 1, 'measurement', 50.0, 30.0, NULL, NULL, 'CTR 0.48', 'Cardiothoracic ratio within normal limits', 'normal', 'Dr. Miller'),
    -- Hand findings
    (3, 3, 'point', 35.0, 45.0, NULL, NULL, 'Fracture', 'Non-displaced fracture of 5th metacarpal neck', 'moderate', 'Dr. Miller'),
    (4, 4, 'region', 33.0, 43.0, 8.0, 10.0, 'Soft Tissue Swelling', 'Soft tissue swelling around 5th MCP', 'mild', 'Dr. Miller'),
    -- Knee findings
    (5, 8, 'region', 48.0, 55.0, 12.0, 15.0, 'Joint Effusion', 'Small joint effusion', 'mild', 'Dr. Garcia'),
    (6, 8, 'measurement', 50.0, 40.0, NULL, NULL, 'Joint Space', 'Mild joint space narrowing medial compartment', 'mild', 'Dr. Garcia');

Step 10: Insert Reports

Add radiology reports.

INSERT INTO xray_reports (id, exam_id, findings, impression, recommendations, report_status, reported_by, reported_at, verified_by, verified_at) VALUES
    (1, 1, 'Heart size normal. Patchy airspace opacity in the right lower lobe. No pleural effusion. No pneumothorax.', 'Right lower lobe pneumonia. Recommend clinical correlation and follow-up chest radiograph after treatment.', 'Clinical correlation recommended. Follow-up imaging in 4-6 weeks.', 'final', 'Dr. Miller', '2024-01-15 11:00:00', 'Dr. Senior', '2024-01-15 14:00:00'),
    (2, 2, 'Non-displaced fracture of the 5th metacarpal neck (boxers fracture). Soft tissue swelling. No other acute osseous abnormality.', 'Boxers fracture of right 5th metacarpal. No significant angulation.', 'Orthopedic consultation for splinting. Follow-up radiograph in 2 weeks.', 'final', 'Dr. Miller', '2024-01-15 11:30:00', 'Dr. Senior', '2024-01-15 14:30:00'),
    (3, 3, 'Lumbar lordosis preserved. Mild disc space narrowing at L4-L5 and L5-S1. Osteophyte formation at multiple levels. No fracture or subluxation.', 'Degenerative disc disease and mild spondylosis. No acute abnormality.', 'Conservative management. Consider MRI if symptoms persist.', 'final', 'Dr. Garcia', '2024-01-16 12:00:00', 'Dr. Senior', '2024-01-16 15:00:00'),
    (4, 5, 'Small joint effusion. Mild tricompartmental osteoarthritis with medial compartment predominance. No fracture.', 'Mild osteoarthritis of left knee with small effusion. No acute fracture.', 'Weight-bearing as tolerated. Consider orthopedic referral if symptoms persist.', 'final', 'Dr. Garcia', '2024-01-18 14:00:00', 'Dr. Senior', '2024-01-18 16:00:00');

Step 11: Get Exam with All Views

Retrieve complete exam images.

SELECT
    xe.body_region,
    xe.exam_type,
    xi.view_position,
    xi.laterality,
    xi.exposure_kvp || 'kVp / ' || xi.exposure_mas || 'mAs' as technique,
    xi.image_quality
FROM xray_exams xe
INNER JOIN xray_images xi ON xe.id = xi.exam_id
WHERE xe.id = 2
ORDER BY xi.acquisition_time;

Step 12: Get Image with Annotations

View findings on image.

SELECT
    xi.view_position,
    xa.annotation_type,
    xa.label,
    xa.description,
    xa.severity,
    xa.annotated_by
FROM xray_images xi
INNER JOIN xray_annotations xa ON xi.id = xa.image_id
WHERE xi.exam_id = 1
ORDER BY xa.severity DESC;

Step 13: Exams by Body Region

Analyze exam distribution.

SELECT
    body_region,
    COUNT(*) as exam_count,
    COUNT(CASE WHEN report_status = 'final' THEN 1 END) as reported,
    COUNT(CASE WHEN report_status = 'pending' THEN 1 END) as pending
FROM xray_exams
GROUP BY body_region
ORDER BY exam_count DESC;

Step 14: Pending Reports List

Find exams needing reports.

SELECT
    xp.name as patient,
    xe.exam_date,
    xe.body_region,
    xe.exam_type,
    xe.referring_physician,
    xe.report_status
FROM xray_exams xe
INNER JOIN xray_patients xp ON xe.patient_id = xp.id
WHERE xe.report_status IN ('pending', 'draft')
ORDER BY xe.exam_date;

Cleanup (Optional)

DROP TABLE IF EXISTS xray_reports;
DROP TABLE IF EXISTS xray_annotations;
DROP TABLE IF EXISTS xray_images;
DROP TABLE IF EXISTS xray_exams;
DROP TABLE IF EXISTS xray_patients;

Expected Outcomes

• X-ray exams organized
• Multiple views stored
• Annotations linked
• Reports tracked
• Quality documented

Common View Positions

Key Concepts Learned

• X-ray view management
• Technical parameters tracking
• Annotation storage
• Report workflow
• Quality assessment