Designing the Database

To serve as the database server, three systems were considered: Oracle¹, PostgreSQL², and MySQL³. I evaluated each with regard to portability, speed, available language bindings, and maturity.

Oracle is known for its ability to handle databases of enormous size, certainly much more gracefully than the other possibilities and is a very mature product. However, it is only available on a select few platforms and the supported language bindings number few, ruling it out for the current time. PostgreSQL was considered next, and was also a very mature product (with its beginning in Berkeley in the mid-eighties). PostgreSQL, primarily because of it being open source, is very portable, and has a wide array of language bindings. It is also one of the very few Relation Database Management Systems (RDBMSs) offering an Object-Relational interface, enabling many new possibilities in the area of tables and relations. However, PostgreSQL still has a way to come in speed and was thus not an ideal choice. Next was considered MySQL, a derivative of mSQL. Also open source, this RDBMS is very portable, has a wide array of language bindings, is a fairly mature product, and has shown itself to be extremely fast. Thus, MySQL was selected to serve as the database backend for this project.

In order for this database to meet the needs of the Image and Signal Processing Directive I consulted those who worked on the gt2 format and created filters from ground-truthed data on various aspects of the information to be stored, as well as personally analyzing code which handles this data. After this work the planning of the layout for the database was began. For this project there would be many differing classes of targets (and types of targets, and variants of types, and finally specifics of variants), so a table (Table 1; Appendix C.1, Page ) holding generic information for each specific was designed. A table for each target id (which is the reference string unique to each target, in the format ``class_type_variant_specific'') was constructed to facilitate the large number of references to be stored in this database. (Table 2; Appendix C.3, Page ) Because of space limitations the database stores paths and filenames (indexed by a unique id) in a separate table (Table 3; Appendix C.2, Page ), letting the filter generator retrieve the image sequence from the indicated location rather than querying the database for the actual image (though the infrastructure for this possibility has been designed (Table 4; Appendix C.4, Page )). Relationships among these tables are shown in Table 5.

  

Table 1: Target table properties
Table 1: Target table properties (continued)

FIELD	TYPE	USE
FIELD	TYPE	USE
id	unsigned integer, not null, auto incremented, primary key	used as a reference and unique identifier
name	variable length character	stores name of target
class	unsigned integer	class (tank, apc, et)
type	unsigned integer	type of class (T72, BMP)
variant	unsigned integer	variant of type
specific	unsigned integer	specific of variant
priority	unsigned integer	priority of target (for allocating resources)

  

Table 2: Frame table properties
Table 2: Frame table properties (continued)

FIELD	TYPE	USE
FIELD	TYPE	USE
id	unsigned integer, not null, auto incremented, primary key	used as a reference and unique identifier
time_record	HHMMSS	time at which the sequence was recorded
time_gt	YYYYMMDDHHMMSS	date and time reference was first ground-truthed
time_modified	YYYYMMDDHHMMSS	date and time of last modification
aspect	floating double precision	aspect of target with regard to camera
depression	floating double precision	angle of depression
sequence_id	unsigned integer	reference to sequence_location.id
findex	unsigned integer	index number found in gt2 format
frame	unsigned integer	frame number
aim_x	unsigned integer	X coordinate aimpoint
aim_y	unsigned integer	Y coordinate aimpoint
ul_x	unsigned integer	upper left X coordinate for patch
ul_y	unsigned integer	upper left Y coordinate for patch
range	unsigned integer	range to target (in meters)
patch_min	floating double precision	minimum pixel value of all patch pixels
patch_max	floating double precision	maximum pixel value of all patch pixels
patch_mean	floating double precision	mean pixel value of all patch pixels
b_mean	floating double precision	mean background pixel value
patch_edge_offset_top	integer	for use in cad overlay (for creating now-rectangular patches)
patch_edge_offset_bottom	integer	for use in cad overlay
patch_edge_offset_left	integer	for use in cad overlay
patch_edge_offset_right	integer	for use in cad overlay
scm	floating double precision	signal to cluster ratio

  

Table 3: Sequence location table properties
Table: Sequence location table properties (continued)

FIELD	TYPE	USE
FIELD	TYPE	USE
id	unsigned integer, not null, auto incremented, primary key	used as a reference and unique identifier
file	text	filename and path of file location
bytes_pixel	unsigned integer	bytes per pixel for sequence

  

Table 4: Image table properties
Table 4: Image table properties (continued)

FIELD	TYPE	USE
FIELD	TYPE	USE
id	unsigned integer, not null, auto incremented, primary key	used as a reference and unique identifier
frame_table	variable length character	to which table the frame_id belongs
frame_id	unsigned integer	to which frame we belong

  

Table 5: Table relationships
Table: Table relationships (continued)

target.id (one)	$\rightarrow$	frame_t_<id> (many)
sequence_location.id (one)	$\rightarrow$	frame_t_<id> (many)
image.id (one)	$\rightarrow$	frame_t_<id> (many)
image.frame_table and image.frame_id (one)	$\rightarrow$	frame_t_<id>.id (one)