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);