Important:

The only fields you have to fill are Name,SpatialTable and TileTable. These three fields together are the primary key. All other fields will be calculated by the plugin. It is not necessary to do calculations. The plugin implements the following mechanism during the first request:

1. Check if ResX or ResY is null, if true, calculate the values from a random chosen tile from the corresponding tile table.
2. Check if one of MinX,MinY,MaxX,MaxY is null, if true, calculate the values from the corresponding spatial table.
3. If something has been calculated, store these values into to db to avoid recalculation at the next restart

This approach assures that the probably costly calculation is only executed once, but updating,adding or removing tiles requires you to nullify one of the MinX,MinY,MaxX,MaxY Attributes to force a new extent calculation.

First example:

Assume we have a Map called Europe with 2 Pyramids. Since we have 3 levels, we need 3 tile tables and 3 spatial tables. Lets start filling the META table.

Remember: You do not have to fill the other attributes.

A tile table needs the following attributes:

• Location (String) : A unique identifier for the tile
• Data (Blob) : The image data (Encoded in a format readable by JAI, e. g. png,jpeg,...)

The table EUTILE0 for example ( 0x.... indicates binary data)

A spatial table contains the georeferencing information. Depending on your database installation, there are 2 possibilites.

1. You have no spatial db extension implies storing the extent in 4 Double fields
2. Having a spatial extension offers the possiblity to use a geometry column

A spatial Table without using a spatial extension needs the following attributes:

• Location (String) : A unique identifier used for joining into the tiles table
• MinX (Double): min x of the tile extent
• MinY (Double): min y of the tile extent
• MaxX (Double): max x of the tile extent
• MaxY (Double): max y of the tile extent

The table EUSPAT0 for example

A spatial Table using a spatial extension needs the following attributes:

• Location (String) : A unique identifier used for joining into the tiles table
• Geometry (Geometry): A Multipoligon type provided by your db

The table EUTILE0 for example

Reducing the number of tables

Since the number of rows in a spatial table has to be equal to the number of rows in a tile table, there is a one to one relationship. It is no problem to join these tables. Applying this redesign to our samples results in the following table structure:

The EU0 Table

or using a geometry column

Further DB Design Rules

• It is possible to add custom columns to your tables
• It is possible to create one meta table for all your maps and pyramid levels , or create one meta table for each map containing records for the map and its pyramids, or do any mixture of these two approaches, the only rule is to store a map and its pyramids level together in the same meta table.
• All primary key attributes will be handled as JDBC String, the exact DB Type and length depends on your needs.
• All numerical fields will be handled as JDBC Double.
• Tile image data has to be a BLOB

Mapping of table and attribute names

Since there a lot of naming conventions in different enterprises, I is not idea to force the use of predefined table and attribute names. In our example, the names of the spatial and tile tables is selectable, the name of the meta table and all the attribute names were assumed. The mapping of these names is part of the XML configuration. The corresponding XML fragment shows the mapping of the assumed names.

A sample XML fragment file

mapping.xml.inc

<!-- possible values: universal,postgis,db2,mysql,oracle -->
<spatialExtension name="universal"/>
<mapping>
        <masterTable name="META" >
            <coverageNameAttribute name="Name"/>
            <maxXAttribute name="MaxX"/>
            <maxYAttribute name="MaxY"/>
            <minXAttribute name="MinX"/>
            <minYAttribute name="MinY"/>
            <resXAttribute name="ResX"/>
            <resYAttribute name="RresY"/>
            <tileTableNameAtribute    name="TileTable" />
            <spatialTableNameAtribute name="SpatialTable" />
        </masterTable>
        <tileTable>
            <blobAttributeName name="Data" />
            <keyAttributeName name="Location" />
        </tileTable>
        <spatialTable>
            <keyAttributeName name="Location" />
            <geomAttributeName name="Geom" />
            <tileMaxXAttribute name="MaxX"/>
            <tileMaxYAttribute name="MaxY"/>
            <tileMinXAttribute name="MinX"/>
            <tileMinYAttribute name="MinY"/>
        </spatialTable>
</mapping>

The structure of this XML Fragment is kept very simple, use it as a pattern.

The name attribute of the <spatialExtension> has to be one of the following values

• universal (a vendor neutral JDBC approach which should work with any jdbc database with BLOB support)
• db2 (use spatial extender)
• mysql (use the spatial features of mysql)
• postgis (use the spatial features of postgis)
• oracle (use location based services included in every oracle edition, no oracle spatial needed !!!)

If your spatial extension is universal you need to specify <tileMinXAttribute>,<tileMinYAttribute>,<tileMaxXAttribute>,<tileMaxYAttribute>, but you can omit <geomAttributeName>.

In all other cases, you need the <geomAttributeName> Element and can omit <tileMinXAttribute>,<tileMinYAttribute>,<tileMaxXAttribute>,<tileMaxYAttribute>.

This approach assures that there are no predefined table and attribute names, allowing seamless integration into existing naming conventions.

<connect>
        <dstype value="DBCP"/>
        <username value="user" />
        <password value="password" />
        <jdbcUrl value="jdbc:h2:target/h2/testdata" />
        <driverClassName value="org.h2.Driver"/>
        <maxActive value="10"/>
        <maxIdle value="0"/>
</connect>

<connect>
        <dstype value="JNDI"/>
         <jndiReferenceName value="jdbc/myDataSourceName"/>
</connect>

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE ImageMosaicJDBCConfig [
	<!ENTITY mapping PUBLIC "mapping" "mapping.xml.inc">
	<!ENTITY connect PUBLIC "connect" "connect.xml.inc">
]>


<config version="1.0">
	<coverageName name="oek"/>
	<coordsys name="EPSG:31287"/>
	<!-- interpolation 1 = nearest neighbour, 2 = bipolar, 3 = bicubic -->
	<scaleop interpolation="1"/>
	<verify cardinality="false"/>
	&mapping;
	&connect;
</config>

// First, get a reader
// the configUrl references the config xml  and is object of one of the following types
// 1) java.net.URL
// 2) java.io.File
// 3) java.lang.String (A filename string or an url string)


AbstractGridFormat format =(AbstractGridFormat)GridFormatFinder.findFormat(configUrl);
ImageMosaicJDBCReader reader= (ImageMosaicJDBCReader)format.getReader(configUrl,null);

// get a parameter object for a grid geometry
ParameterValue<GridGeometry2D>gg=AbstractGridFormat.READ_GRIDGEOMETRY2D.createValue();

// create an envelope, 2 Points, lower left and upper right, x,y order
GeneralEnvelope envelope = new GeneralEnvelope(new double[] {10,20},new double[] {30,40});

// set a CRS for the envelope
envelope.setCoordinateReferenceSystem(CRS.decode"EPSG:4326");

// Set the envelope into the parameter object
gg.setValue(new GridGeometry2D(new GeneralGridRange(new Rectangle(0, 0, width, heigth)),envelope));

// create a parameter Object for the background color (NODATA), this param is optional
final ParameterValue outTransp=ImageMosaicJDBCFormat.OUTPUT_TRANSPARENT_COLOR.createValue();
outTransp.setValue(Color.WHITE);

// call the plugin passing an array with the two parameter objects
try {
    GridCoverage2D coverage = (GridCoverage2D) reader.read(new GeneralParameterValue[] {
       gg, outTransp});
    } catch (IOException e) {
       e.printStackTrace();
    }