Postgre SQLIE An Imagehandling Extension for Postgre SQL

Postgre. SQL-IE: An Image-handling Extension for Postgre. SQL Denise Guliato, Ernani V. de Melo, Robson C. Soares Universidade Federal de Uberlândia, Minas Gerais, BRASIL Rangaraj M. Rangayyan University of Calgary, Alberta, CANADA Universidade Federal de Uberlândia Faculdade de Computação

Outline • Content-based Image Retrieval (CBIR) • Postgre. SQL with Image-handling Extension (Postgre. SQL-IE) • SISPRIM – A Research System that Supports CBIR Applied to the Analysis of Mammograms • Remarks

Content-based Image Retrieval Retrieving semantically relevant images from a large database or repository. Challenges for a Database System: – Storage and management of images in a proper manner for easy accessibility. – Flexibility to include and use new feature descriptors, new feature vectors, or new access methods.

Content-based Image Retrieval Commercial Database Management Systems: • Private architecture • High cost • Cannot be updated by the user. Examples: • DB 2 with Image Extender (IBM): – four feature descriptors (3 color, 1 texture). • Oracle with inter. Media: – four feature descriptors (2 color, 1 texture, 1 shape). • Informix with Excalibur: – five feature descriptors (2 color, 1 texture, 1 shape, 1 brightness)

Content-based Image Retrieval In all the cases: • only one image can be stored with a given image attribute; • only one feature vector can be associated with a given image attribute; • The systems do not allow the inclusion of new functionalities, new feature extractors, or new access methods.

Outline • Content-based Image Retrieval (CBIR) • Postgre. SQL with Image-handling Extension (Postgre. SQL-IE) • SISPRIM – A System Design for CBIR Applied to the Analysis of Mammograms • Remarks

Postgre. SQL with Image-handling Extension Postgre. SQL-IE • Characteristics: – Able to handle images, independent of the application; – Makes use of the power available with Postgre. SQL; – Open source; • Makes available a new image data type • Extends the Database Catalog of Postgre. SQL • Extends the SQL to support similarity queries • Easy to install and use.

New Image Data Type: PGImage • Makes it possible to store more than one image in the same attribute; • Makes it possible to combine feature descriptors of different images (stored in the same attribute) in the same feature vector; • Is useful for the development of medical applications involving CT, MR, or mammographic images.

New Image Data Type: PGImage An example Consider an application that involves: 1. a short clinical history of a patient 2. a set of exams obtained at different instants of time 3. each exam is composed of CT and MRI images (the number of slices in CT and MRI may vary between patients)

New Image Data Type: PGImage Two possible database schemes c_image: PGImage Postgre. SQL-IE DB 2

New Image Data Type: PGImage data type is an UDT that encapsulates: • An identifier of the a given image set (imgid) • Classes associated with each image of this image set (imgclass)

New Image Data Type: PGImage A simple example • • • CREATE TABLE patient( p_id INTEGER NOT NULL, p_bday DATE, p_first_pregnancy INTEGER, PRIMARY KEY (p_id) ); • • • CREATE TABLE patient_case( c_id INTEGER NOT NULL, c_patient INTEGER NOT NULL, c_examdate DATE NOT NULL, c_tumor CHAR NOT NULL, c_mammo PGImage, PRIMARY KEY (c_id), FOREIGN KEY (c_patient) REFERENCES patient (p_id), CHECK ((c_mammo). classes in ('mammography', 'lesion_contour')) • • ); f

Extended Database Catalog of Postgre. SQL 2 0 1 1 1 3

Extended Database Catalog of Postgre. SQL The pge_regimage maintains information about all the images stored in the database back

Extended Database Catalog of Postgre. SQL The tables pge_extractor, pge_vector and pge_accessmethod keep track of all pge_extractor feature vectors and feature descriptors, access methods added to the system by an authorized user. pge_accessmethod pge_vector back

Extended Database Catalog of Postgre. SQL The table pge_vector_extractor relates each pge_extractor feature vector to a respective set of feature descriptors that compose the vector; Because of this, Postgre. SQL-IE make it possible to combine or more feature descriptors in the same feature vector back pge_vector_extractor

Extended Database Catalog of Postgre. SQL The table pge_tabvector associates each pge_tabvector feature vector with an access method, in pge_tabvector order to speed up the answer to a query. pge_access_method pge_vector

pge_tabvector pge_extractor pge_patient_case_c_mamo

Extending the SQL to support similarity queries: SQL-IE • Includes flexibility to create, and facilities to use: – new feature descriptors: • At present, 11 shape descriptors and 1 histogrambased descriptor available. – new feature vectors – new access methods: • At present, a scan access method is available with the similarity operators – KNN (K Nearest Neighbor) – RANGE.

Extending the SQL to support similarity queries: SQL-IE • Composed of a set of 16 new functions, written in plpgsql, to extend SQL and a set of 12 internal functions written in C • The main functions available in SQL-IE are: – Insert_Image. – Create_Extractor – Define_Feature_Vector – Create_Access. Method – Set_Feature_Vector

Extending the SQL to support similarity queries: SQL-IE • • • Delete_Extractor Delete_Feature_Vector Delete_Access_Methods Un. Set_Feature_Vector Update_Image_Attr Replace_Image_Attr Show_Extractor Value_Extractor Show_Image Width_IE Height_IE

SQL-IE: An Example

patient_case

SQL-IE: An Example

SQL-IE: An Query Example SQL-IE makes it possible to pose complex queries combining conventional and visual data: Return all the information of the patients associated with the 6 images most similar to the image given as reference (related to the patient id = 1) and age > 62, according to Index_SI ( ‘Shape_SI’, ‘ c_mammo, patient_case’, ‘scan’).

SQL-IE: The Query Answer

SQL-IE: Another Query Example

Easy to Install and Use To install Postgre. SQL-IE is necessary: 1. Download the script; 2. Download the libraries (feature extractors); 3. Copy the libraries to the lib folder of Postgre. SQL; 4. Create a database called template_extended 1; 5. Create a new database application using template_extended 1 as the template.

Outline • Content-based Image Retrieval (CBIR) • Postgre. SQL with Image-handling Extension (Postgre. SQL-IE) • SISPRIM – A Research System that Supports CBIR Applied to the Analysis of Mammograms • Remarks

SISPRIM – A Research System that Supports CBIR Applied to Mammograms The system is composed of: – A Mammographic Database. – A Research Engine. – A Web Graphical Interface (GUI). • Available via the Web. • Part of AMDI – Indexed Atlas of Digital Mammograms.

SISPRIM

SISPRIM Mammographic Database • Modeled using Postgre. SQL-IE Database Management System. • Includes: – All available mammographic views (CC and MLO views). – Contours of the breast, pectoral muscle, lesions, and clusters of calcifications. – Radiological findings (local and global). – Diagnosis proven by biopsy. – Clinical history of the patient. – Lifestyle of the patient.

SISPRIM Research Engine • Manages the actions of the user; • Manages the Web GUI which incorporates facilities to pose complex queries as: return the age of the first pregnancy and the patient_id associated with the 5 images most similar to the image given as reference, and with the first pregnancy is after 40; • Accesses the mammographic database, as required, to answer queries.

SISPRIM A Web Graphical User Interface • Supports two kinds of users: – Administrator interface: guides the user in the creation of new feature descriptors, new feature vectors, or new access methods. – Researcher interface: guides the user to the resources previously configured.

SISPRIM Administrator Interface

SISPRIM A Web Graphical Interface • Supports two kinds of users: – Administrator interface: guides the user in the creation of new feature descriptors, new feature vectors, or new access methods. – Researcher interface: guides the user to the resources previously configured.

SISPRIM A Web Graphical Interface return the age of the first pregnancy and the patient_id associated with the 5 images most similar to the image given as reference, and with the first pregnancy is after 40;

Remarks • Postgre. SQL-IE is open source, extendable, and easy to install; • At present, Postgre. SQL-IE possesses 12 feature descriptors organized in four libraries: – further developments are in progress; • The available libraries are developed for Windows and Linux operating systems;

Remarks • To date, Postgre. SQL-IE makes available two similarity operators (KNN, range): – new studies are being conducted to extend Postgre. SQL-IE with two new similarity operators based on fuzzy sets • New access methods have to be developed;

Remarks • In order to validate Postgre. SQL-IE we developed SISPRIM, a research system that supports CBIR applied to the analysis of mammograms; • The time spent on SISPRIM implementation was reduced by using Postgre. SQL-IE • The script to install Postgre. SQL-IE is available at www. lcc. ufu. br/pdi/downloads

Thank you! Acknowledgment • CNPq – Conselho Nacional de Desenvolvimento Científico e Tecnológico do Brasil. • Catalyst Program of Research Services, University of Calgary, Canada. guliato@ufu. br www. lcc. ufu. br/pdi