Medical Image Annotation

Objective

Build a comprehensive annotation system for medical images supporting measurements, region markings, and structured findings documentation for radiological interpretation.

Step 1: Create Annotated Studies Table

Store studies available for annotation.

CREATE TABLE annotated_studies (
    id INTEGER PRIMARY KEY,
    study_uid VARCHAR(100) UNIQUE,
    patient_id VARCHAR(50),
    study_date DATE,
    modality VARCHAR(20),
    body_part VARCHAR(100),
    study_description VARCHAR(255),
    annotation_count INTEGER DEFAULT 0,
    last_annotated TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Annotated Images Table

Store images that can be annotated.

CREATE TABLE annotated_images (
    id INTEGER PRIMARY KEY,
    study_id INTEGER NOT NULL,
    image_uid VARCHAR(100) UNIQUE,
    series_description VARCHAR(255),
    instance_number INTEGER,
    image_data IMAGE(PNG),
    thumbnail IMAGE(JPEG),
    width INTEGER,
    height INTEGER,
    pixel_spacing_x DECIMAL(8, 4),
    pixel_spacing_y DECIMAL(8, 4),
    annotation_count INTEGER DEFAULT 0,
    FOREIGN KEY (study_id) REFERENCES annotated_studies(id)
);

Step 3: Create Annotation Types Table

Define available annotation types.

CREATE TABLE annotation_types (
    id INTEGER PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100),
    category VARCHAR(50),
    shape VARCHAR(50),
    requires_measurement BOOLEAN DEFAULT FALSE,
    color_hex VARCHAR(10) DEFAULT '#FF0000',
    description TEXT
);

Step 4: Create Annotations Table

Store individual annotations.

CREATE TABLE image_annotations (
    id INTEGER PRIMARY KEY,
    image_id INTEGER NOT NULL,
    annotation_type_id INTEGER NOT NULL,
    annotation_uid VARCHAR(100) UNIQUE,
    label VARCHAR(255),
    description TEXT,
    coordinates TEXT,
    area_cm2 DECIMAL(10, 4),
    perimeter_cm DECIMAL(10, 4),
    mean_hu DECIMAL(8, 2),
    std_hu DECIMAL(8, 2),
    status VARCHAR(50) DEFAULT 'active',
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_by VARCHAR(100),
    modified_at TIMESTAMP,
    FOREIGN KEY (image_id) REFERENCES annotated_images(id),
    FOREIGN KEY (annotation_type_id) REFERENCES annotation_types(id)
);

Step 5: Create Measurements Table

Store linear and volumetric measurements.

CREATE TABLE annotation_measurements (
    id INTEGER PRIMARY KEY,
    annotation_id INTEGER NOT NULL,
    measurement_type VARCHAR(50),
    measurement_name VARCHAR(100),
    value DECIMAL(10, 4),
    unit VARCHAR(20),
    start_point VARCHAR(50),
    end_point VARCHAR(50),
    comparison_value DECIMAL(10, 4),
    comparison_date DATE,
    change_percent DECIMAL(6, 2),
    FOREIGN KEY (annotation_id) REFERENCES image_annotations(id)
);

Step 6: Create Annotation History Table

Track annotation changes.

CREATE TABLE annotation_history (
    id INTEGER PRIMARY KEY,
    annotation_id INTEGER NOT NULL,
    action VARCHAR(50),
    previous_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    change_reason TEXT,
    FOREIGN KEY (annotation_id) REFERENCES image_annotations(id)
);

Step 7: Insert Annotation Types

Define standard annotation types.

INSERT INTO annotation_types (id, type_code, type_name, category, shape, requires_measurement, color_hex, description) VALUES
    -- Measurement types
    (1, 'LINEAR', 'Linear Measurement', 'measurement', 'line', TRUE, '#00FF00', 'Straight line distance measurement'),
    (2, 'BIDIMENSIONAL', 'Bidimensional Measurement', 'measurement', 'cross', TRUE, '#00FF00', 'Two perpendicular measurements'),
    (3, 'ANGLE', 'Angle Measurement', 'measurement', 'angle', TRUE, '#00FFFF', 'Angle between two lines'),
    (4, 'COBBS', 'Cobb Angle', 'measurement', 'cobbs', TRUE, '#00FFFF', 'Spinal curvature angle'),
    -- Region types
    (5, 'ROI_ELLIPSE', 'Elliptical ROI', 'region', 'ellipse', FALSE, '#FF0000', 'Elliptical region of interest'),
    (6, 'ROI_RECTANGLE', 'Rectangular ROI', 'region', 'rectangle', FALSE, '#FF0000', 'Rectangular region of interest'),
    (7, 'ROI_FREEHAND', 'Freehand ROI', 'region', 'polygon', FALSE, '#FF0000', 'Freehand drawn region'),
    (8, 'SEGMENTATION', 'Segmentation', 'region', 'polygon', FALSE, '#FF00FF', 'Organ or lesion segmentation'),
    -- Marker types
    (9, 'POINT', 'Point Marker', 'marker', 'point', FALSE, '#FFFF00', 'Single point annotation'),
    (10, 'ARROW', 'Arrow Pointer', 'marker', 'arrow', FALSE, '#FFFF00', 'Arrow pointing to finding'),
    (11, 'TEXT', 'Text Label', 'marker', 'text', FALSE, '#FFFFFF', 'Text annotation'),
    -- Finding types
    (12, 'NODULE', 'Pulmonary Nodule', 'finding', 'ellipse', TRUE, '#FF6600', 'Lung nodule measurement'),
    (13, 'LESION', 'Lesion', 'finding', 'ellipse', TRUE, '#FF0000', 'General lesion annotation'),
    (14, 'FRACTURE', 'Fracture Line', 'finding', 'line', FALSE, '#FF0000', 'Bone fracture marking'),
    (15, 'EFFUSION', 'Effusion', 'finding', 'polygon', FALSE, '#0000FF', 'Fluid collection');

Step 8: Insert Sample Studies

Add studies for annotation.

INSERT INTO annotated_studies (id, study_uid, patient_id, study_date, modality, body_part, study_description, annotation_count) VALUES
    (1, '1.2.840.10001.1', 'PAT-001', '2024-01-15', 'CT', 'CHEST', 'CT Chest with Contrast', 12),
    (2, '1.2.840.10001.2', 'PAT-002', '2024-01-16', 'MR', 'BRAIN', 'MRI Brain without Contrast', 8),
    (3, '1.2.840.10001.3', 'PAT-003', '2024-01-17', 'CT', 'ABDOMEN', 'CT Abdomen Pelvis', 15),
    (4, '1.2.840.10001.4', 'PAT-001', '2024-01-18', 'XR', 'SPINE', 'X-Ray Thoracic Spine', 5),
    (5, '1.2.840.10001.5', 'PAT-004', '2024-01-19', 'US', 'LIVER', 'Ultrasound Liver', 6);

Step 9: Insert Annotated Images

Add images for annotation.

INSERT INTO annotated_images (id, study_id, image_uid, series_description, instance_number, width, height, pixel_spacing_x, pixel_spacing_y, annotation_count) VALUES
    -- CT Chest images
    (1, 1, '1.2.840.10001.1.1.50', 'Axial Lung Window', 50, 512, 512, 0.7031, 0.7031, 4),
    (2, 1, '1.2.840.10001.1.1.75', 'Axial Lung Window', 75, 512, 512, 0.7031, 0.7031, 3),
    (3, 1, '1.2.840.10001.1.1.100', 'Axial Mediastinal Window', 100, 512, 512, 0.7031, 0.7031, 5),
    -- MRI Brain images
    (4, 2, '1.2.840.10001.2.1.15', 'Axial T2 FLAIR', 15, 256, 256, 0.9375, 0.9375, 4),
    (5, 2, '1.2.840.10001.2.1.16', 'Axial T2 FLAIR', 16, 256, 256, 0.9375, 0.9375, 4),
    -- CT Abdomen images
    (6, 3, '1.2.840.10001.3.1.120', 'Axial Portal Venous', 120, 512, 512, 0.7812, 0.7812, 6),
    (7, 3, '1.2.840.10001.3.1.150', 'Axial Portal Venous', 150, 512, 512, 0.7812, 0.7812, 5),
    -- X-Ray Spine
    (8, 4, '1.2.840.10001.4.1.1', 'AP View', 1, 2048, 2500, 0.1430, 0.1430, 3),
    (9, 4, '1.2.840.10001.4.1.2', 'Lateral View', 2, 2048, 2500, 0.1430, 0.1430, 2);

Step 10: Insert Annotations

Add sample annotations.

INSERT INTO image_annotations (id, image_id, annotation_type_id, annotation_uid, label, description, coordinates, area_cm2, mean_hu, std_hu, status, created_by, created_at) VALUES
    -- CT Chest nodule annotations
    (1, 1, 12, 'ANN-001', 'RUL Nodule', 'Solid pulmonary nodule right upper lobe', '{"cx": 180, "cy": 200, "rx": 15, "ry": 12}', 0.56, -25.5, 45.2, 'active', 'Dr. Miller', '2024-01-15 14:30:00'),
    (2, 2, 12, 'ANN-002', 'RLL Nodule', 'Part-solid nodule right lower lobe', '{"cx": 350, "cy": 280, "rx": 20, "ry": 18}', 1.13, -450.2, 180.5, 'active', 'Dr. Miller', '2024-01-15 14:45:00'),
    (3, 3, 5, 'ANN-003', 'Aorta ROI', 'Descending aorta measurement', '{"cx": 256, "cy": 256, "rx": 25, "ry": 25}', 1.96, 125.8, 15.3, 'active', 'Dr. Miller', '2024-01-15 15:00:00'),
    -- MRI Brain annotations
    (4, 4, 13, 'ANN-004', 'White Matter Lesion', 'Periventricular white matter hyperintensity', '{"cx": 150, "cy": 120, "rx": 8, "ry": 6}', 0.15, NULL, NULL, 'active', 'Dr. Garcia', '2024-01-16 10:30:00'),
    (5, 5, 13, 'ANN-005', 'White Matter Lesion 2', 'Juxtacortical white matter hyperintensity', '{"cx": 180, "cy": 100, "rx": 5, "ry": 4}', 0.06, NULL, NULL, 'active', 'Dr. Garcia', '2024-01-16 10:45:00'),
    -- CT Abdomen annotations
    (6, 6, 13, 'ANN-006', 'Liver Lesion', 'Hypodense liver lesion segment 6', '{"cx": 320, "cy": 200, "rx": 18, "ry": 15}', 0.85, 35.2, 12.8, 'active', 'Dr. Miller', '2024-01-17 11:00:00'),
    (7, 6, 5, 'ANN-007', 'Kidney Cyst', 'Simple renal cyst left kidney', '{"cx": 400, "cy": 280, "rx": 22, "ry": 20}', 1.38, 8.5, 5.2, 'active', 'Dr. Miller', '2024-01-17 11:15:00'),
    -- X-Ray spine annotations
    (8, 8, 4, 'ANN-008', 'Cobb Angle T5-T12', 'Thoracic scoliosis measurement', '{"points": [[150, 200], [200, 400], [180, 600], [220, 800]]}', NULL, NULL, NULL, 'active', 'Dr. Chen', '2024-01-18 09:00:00');

Step 11: Insert Measurements

Add quantitative measurements.

INSERT INTO annotation_measurements (id, annotation_id, measurement_type, measurement_name, value, unit, start_point, end_point, comparison_value, comparison_date, change_percent) VALUES
    -- Nodule measurements
    (1, 1, 'diameter', 'Long Axis', 8.5, 'mm', '165,200', '195,200', 7.2, '2023-07-15', 18.1),
    (2, 1, 'diameter', 'Short Axis', 6.8, 'mm', '180,188', '180,212', 5.8, '2023-07-15', 17.2),
    (3, 2, 'diameter', 'Long Axis', 12.0, 'mm', '330,280', '370,280', NULL, NULL, NULL),
    (4, 2, 'diameter', 'Short Axis', 10.5, 'mm', '350,262', '350,298', NULL, NULL, NULL),
    -- Aorta measurement
    (5, 3, 'diameter', 'Aortic Diameter', 28.5, 'mm', '231,256', '281,256', 27.8, '2023-01-15', 2.5),
    -- Liver lesion measurements
    (6, 6, 'diameter', 'Long Axis', 22.0, 'mm', '302,200', '338,200', 18.5, '2023-10-01', 18.9),
    (7, 6, 'diameter', 'Short Axis', 18.0, 'mm', '320,185', '320,215', 15.0, '2023-10-01', 20.0),
    -- Cobb angle
    (8, 8, 'angle', 'Cobb Angle', 25.5, 'degrees', NULL, NULL, 22.0, '2023-06-01', 15.9);

Step 12: Insert Annotation History

Track changes to annotations.

INSERT INTO annotation_history (id, annotation_id, action, previous_value, new_value, changed_by, changed_at, change_reason) VALUES
    (1, 1, 'create', NULL, 'RUL Nodule created', 'Dr. Miller', '2024-01-15 14:30:00', 'Initial annotation'),
    (2, 1, 'modify', 'Long Axis: 8.2mm', 'Long Axis: 8.5mm', 'Dr. Miller', '2024-01-15 14:35:00', 'Measurement refinement'),
    (3, 6, 'create', NULL, 'Liver Lesion created', 'Dr. Miller', '2024-01-17 11:00:00', 'Initial annotation'),
    (4, 6, 'modify', 'Hypodense lesion', 'Hypodense liver lesion segment 6', 'Dr. Chen', '2024-01-17 14:00:00', 'Added anatomic location'),
    (5, 8, 'create', NULL, 'Cobb Angle annotation', 'Dr. Chen', '2024-01-18 09:00:00', 'Initial measurement');

Step 13: Get Image with All Annotations

Retrieve annotations for an image.

SELECT
    ia.label,
    at.type_name,
    at.category,
    ia.description,
    ia.area_cm2,
    ia.mean_hu,
    ia.created_by,
    ia.created_at
FROM image_annotations ia
INNER JOIN annotation_types at ON ia.annotation_type_id = at.id
WHERE ia.image_id = 1
  AND ia.status = 'active'
ORDER BY ia.created_at;

Step 14: Get Annotation with Measurements

View annotation details and measurements.

SELECT
    ia.label,
    ia.description,
    am.measurement_name,
    am.value || ' ' || am.unit as current,
    am.comparison_value || ' ' || am.unit as previous,
    am.comparison_date,
    am.change_percent || '%' as change
FROM image_annotations ia
INNER JOIN annotation_measurements am ON ia.id = am.annotation_id
WHERE ia.id = 1
ORDER BY am.measurement_type;

Step 15: Lesion Tracking Report

Track lesions across studies.

SELECT
    ast.patient_id,
    ast.study_date,
    ia.label,
    am.measurement_name,
    am.value || ' ' || am.unit as measurement,
    am.change_percent,
    CASE
        WHEN am.change_percent > 20 THEN 'Significant Growth'
        WHEN am.change_percent < -20 THEN 'Significant Decrease'
        ELSE 'Stable'
    END as status
FROM image_annotations ia
INNER JOIN annotated_images ai ON ia.image_id = ai.id
INNER JOIN annotated_studies ast ON ai.study_id = ast.id
INNER JOIN annotation_measurements am ON ia.id = am.annotation_id
WHERE ia.annotation_type_id IN (12, 13)
  AND am.measurement_type = 'diameter'
  AND am.measurement_name = 'Long Axis'
ORDER BY ast.patient_id, ast.study_date;

Step 16: Annotation Statistics

Analyze annotation usage.

SELECT
    at.type_name,
    at.category,
    COUNT(ia.id) as annotation_count,
    COUNT(DISTINCT ia.created_by) as unique_annotators
FROM annotation_types at
LEFT JOIN image_annotations ia ON at.id = ia.annotation_type_id AND ia.status = 'active'
GROUP BY at.id, at.type_name, at.category
ORDER BY annotation_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS annotation_history;
DROP TABLE IF EXISTS annotation_measurements;
DROP TABLE IF EXISTS image_annotations;
DROP TABLE IF EXISTS annotation_types;
DROP TABLE IF EXISTS annotated_images;
DROP TABLE IF EXISTS annotated_studies;

Expected Outcomes

• Annotation types defined
• Annotations linked to images
• Measurements tracked
• History maintained
• Comparison data available

Annotation Categories

Key Concepts Learned

• Structured annotation types
• Coordinate storage
• Measurement tracking
• Comparison analysis
• Audit history