Annex E: Draft GeoPackage Extension Template (Informative)

3D Tiles Spec :  https://github.com/AnalyticalGraphicsInc/3d-tiles

table_name

data_type

identifier

description

last_change

min_x

min_y

max_x

max_y

srs_id

Name of table defined in gpkg_extensions

cslt_3dtiles

*

*

*

*

*

*

*

4326

min_x,max_x,min_y,max_y can be derived from the bounding_volume  of the root tile

table_name

column_name

extension_name

definition

scope

{table_name}

Null

cslt_3dtiles

TBD

read-write

{table_name}_tiles

Null

cslt_3dtiles

TBD

read-write

{table_name}_properties

Null

cslt_3dtiles

TBD

read-write

{table_name}_tile_resources

Null

cslt_3dtiles

TBD

read-write

Column Name

Column Type

Column Description

Null

Default

Key

id

INTEGER

Auto incrementing primary key.

no

PK

version

TEXT

The 3D Tiles version. The version defines the base set of tile formats supported.

no

tileset_version

TEXT

Application-specific version of this tileset.

yes

gltf_up_axis

TEXT

Specifies the up-axis of glTF models contained in the tiles.

no

Y

geometric_error

REAL

The error, in meters, introduced if this tileset is not rendered. At runtime, the geometric error is used to compute Screen Space Error (SSE), i.e., the error measured in pixels.

no

0

is_root

TEXT

Indication of the root tileset defined by 'Y' or 'N'.

no

root_tile_id

INTEGER

 id from <table_name>_tiles.

no

FK

Tileset Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tileset.schema.json

Column Name

Column Type

Column Description

Null

Default

Key

Id

INTEGER

Auto incrementing primary key.

no

PK

parent_tile_id

INTEGER

Value from the id column of this table representing the parent tile of the current tile.

no

FK

bounding_volume

TEXT

The bounding volume that encloses the tile.

no

viewer_request_volume

TEXT

Optional bounding volume that the viewer must be contained within before the tile's content will be requested, and before the tile will be refined based on geometricError.

yes

geometric_error

REAL

The geometric error, in meters, introduced if this tile is rendered and its children are not. At runtime, the geometric error is used to compute Screen Space Error (SSE), i.e., the error measured in pixels.

no

refine

TEXT

Specifies if the additive or replacement refinement is used when traversing the tileset for rendering. This property is required for the root tile of a tileset. It is optional for all other tiles. The default is to inherit from the parent tile.

yes

transform

TEXT

Represented in JSON array notation.

A floating-point 4x4 affine transformation matrix, stored in column-major order, that transforms the tile's content (i.e., its features as well as content_bounding_volume, bounding_volume, and viewer_request_volume) from the tile's local coordinate system to the parent tile's coordinate system, or, in the case of a root tile, from the tile's local coordinate system to the tileset's coordinate system. This transform does not apply to geometric_error, nor does it apply any volume property when the volume is a region, defined in WGS84 / EPSG:4326 coordinates.

yes

content_bounding_volume

TEXT

An optional bounding volume that tightly encloses just the tile's contents. Used for replacement refinement,   content_bounding_volume enables tight view frustum culling. When omitted, the bounding_volume is used.

yes

content_tileset_id

INTEGER

The id referencing a record in this table with content_data.

yes

FK

content_data

BLOB

The tile content supported by the core 3D Tiles spec. This can be any of the following formats;

Batched 3D Model (*.b3dm), Instanced 3D Model (*.i3dm), Composite (*.cmpt)

yes

Tile Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tile.schema.json

Tile Content Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tile.content.schema.json

Bounding Volume Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/boundingVolume.schema.json

Column Name

Column Type

Column Description

Null

Default

Key

tileset_id

INTEGER

The id found in associated tilesets table.

no

FK

name

TEXT

A unique name per tileset_id of the property being specified.

no

minimum

REAL

The minimum value of this property of all the features in the tileset.

no

maximum

REAL

The maximum value of this property of all the features in the tileset.

no

Tileset Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/tileset.schema.json

Properties Schema : https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/properties.schema.json

Column Name

Column Type

Column Description

Null

Default

Key

id

INTEGER

Auto increment primary key.

no

PK

data

BLOB

Binary data representing a URI referenced resource.

no

glTF URIs: https://github.com/KhronosGroup/glTF/tree/master/specification/2.0#uris

Test Id:

Requirement:

Requirement 1

Test purpose:

Verify that the gpkg_contents table has at least one cslt_3dtiles entry.

Test method:

SELECT COUNT(*) FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

- Extension not in use if count is empty.

Test Id:

Requirement:

Requirement 2

Test purpose:

Verify whether the Related Tables Extension is in use within a GeoPackage.

Test method:

SELECT COUNT(*) FROM gpkg_extensions WHERE extension_name = 'cslt_3dtiles'

- Extension not in use if count is empty.

Test Id:

Requirement:

Requirement 2

Test purpose:

Verify whether the GeoPackage contains at least one cslt_3dtiles content layer set.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

- Fail if number of rows returned is 0.

2. For each table_name row returned in Step 1:

- SELECT column_name, scope, table_name FROM gpkg_extensions WHERE table_name IN ('{table_name}', '{table_name}_tiles', '{table_name}_properties', '{table_name}_tile_resources');

- Fail if number of rows returned is not equal to 4.

- For each row returned in Step 2, fail if column_name is not null or scope is not "read-write".

3. For each table_name row returned in Step 3:

- SELECT tbl_name FROM sqlite_master WHERE tbl_name = '{table_name}';

- Fail if no rows returned.

- Pass if no fails.

Test Id:

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info(table_name)

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tilesets Table Definition in Requirement 3. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

-Fail otherwise.

Test Id:

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have valid values.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- SELECT geometric_error, root_tile_id FROM table_name;

3. For each row in Step 2:

- Fail if geometric_error is null or < 0.

- Fail if root_tile_id is null.

- Fail if version is null.

- Fail if glTFUpAxis is null or not in ["X", "Y", "Z"].

Test Id:

Requirement:

Requirement 3

Test purpose:

Verify that the tilesets tables have valid tile relationships.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

2. For each table_name row returned in Step 1:

- SELECT root_tile_id FROM table_name;

3. For each root_tile_id in Step 2:

- SELECT COUNT(*) FROM {table_name}_tiles WHERE id = root_tile_id;

- Pass if rows returned equal to 1.

- Fail otherwise.

Test Id:

Requirement:

Requirement 4

Test purpose:

Verify that the tilesets tables have exactly one root tileset.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles'

2. For each table_name row returned:

- SELECT COUNT(*) FROM table_name WHERE is_root = 'Y';

- Pass if rows returned equal to 1.

- Fail otherwise.

Test Id:

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_tiles')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Tiles Table Definition in Requirement 5. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.

Test Id:

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have valid table values.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- SELECT * FROM {table_name}_tiles;

3. For each row in Step 2:

- Fail if bounding_volume is null.

- Fail if bounding_volume, viewer_request_volume, content_bounding_volume are not null and do not match the schema.

https://github.com/AnalyticalGraphicsInc/3d-tiles/blob/master/schema/boundingVolume.schema.json

- Fail if refine is not null and does not match ["ADD", "REPLACE"].

- Fail if geometric_error < 0.

- Fail if transform is not a JSON array with 16 items.

- Fail if content_data is not null and does not represent a valid 3dtile format.

https://github.com/AnalyticalGraphicsInc/3d-tiles/tree/master/TileFormats

Test Id:

Requirement:

Requirement 5

Test purpose:

Verify that the tileset_tiles tables have valid parent and tileset relationships.

Test method:

1.  SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- SELECT parent_tile_id, content_tileset_id FROM {table_name}_tiles;

3. For each row in Step 2 with parent_tile_id != null:

- SELECT COUNT(*) FROM {table_name}_tiles WHERE id =  parent_tile_id;

- Fail if rows returned not equal to 1.

4. For each row in Step 2 with content_tileset_id != null:

- SELECT COUNT(*) FROM {table_name} WHERE id =   content_tileset_id;

- Fail if rows returned not equal to 1.

Test Id:

Requirement:

Requirement 6

Test purpose:

Verify that the tileset_properties tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_properties')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Properties Table Definition in Requirement 6. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.

Test Id:

Requirement:

Requirement 7

Test purpose:

Verify that the tileset_tile_resources tables have the required table definition.

Test method:

1. SELECT table_name FROM gpkg_contents WHERE data_type = 'cslt_3dtiles';

2. For each table_name row returned in Step 1:

- PRAGMA table_info('<table_name>_tile_resources')

- Pass if the column names, types, nullability, default values, primary key, and unique key constraints match all of those in the contents of Tileset Tile Resources Table Definition in Requirement 7. Column order, check constraint and trigger definitions, and other column definitions in the returned SQL are irrelevant.

- Fail otherwise.

CREATE TABLE tilesets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
version TEXT NOT NULL,
tileset_version TEXT,
glTF_up_axis TEXT DEFAULT 'Y',
geometric_error REAL DEFAULT 0 NOT NULL,
is_root TEXT NOT NULL,
root_tile_id INTEGER NOT NULL,
CONSTRAINT fk_tilesets_root_tile_id FOREIGN KEY (root_tile_id)
REFERENCES tilesets_tiles (id)
);

CREATE TABLE tilesets_tiles (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 parent_tile_id INTEGER,
 bounding_volume TEXT NOT NULL,
 viewer_request_volume TEXT,
 geometric_error REAL DEFAULT 0 NOT NULL,
 refine TEXT,
 transform DEFAULT '[ 1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 1.0, 0.0, 0.0, 0.0, 0.0, 1.0 ]' TEXT,
 content_bounding_volume TEXT,
 content_tileset_id INTEGER,
 content_data BLOB,
 CONSTRAINT fk_tilesets_tiles_parent_id FOREIGN KEY (parent_tile_id)
 REFERENCES tilesets_tiles (id),
 CONSTRAINT fk_tilesets_tiles_ts_id FOREIGN KEY (content_tileset_id)
 REFERENCES tilesets (id)
 );

CREATE TABLE tilesets_properties (
tileset_id INTEGER,
name TEXT NOT NULL,
minimum REAL NOT NULL,
maximum REAL NOT NULL,
CONSTRAINT fk_tilesets_properties_ts_id FOREIGN KEY (tileset_id)
REFERENCES tilesets (id), UNIQUE (tileset_id, name)
);

CREATE TABLE tilesets_tile_resources (
id INTEGER PRIMARY KEY AUTOINCREMENT,
data BLOB NOT NULL
);