The gpkg_tile_matrix and gpkg_tiles tables — how tile pyramids are stored

In the previous section, we explored how gpkg_tile_matrix_set and gpkg_tile_matrix define the structure of a tile pyramid. Now we'll examine how the actual tile images are stored in user-defined tile tables, understand the relationship between all the tile-related tables, and learn how to efficiently populate, query, and manage tile data in GeoPackage for Danish municipal applications.

Understanding the Tile Storage Architecture

GeoPackage's tile storage follows a hierarchical structure with multiple interconnected tables:

gpkg_contents - Registers the tile pyramid as a dataset

gpkg_tile_matrix_set - Defines the overall extent and coordinate system

gpkg_tile_matrix - Defines parameters for each zoom level

User tile table - Stores the actual tile images (e.g., aerial_photos)

Example relationship:

gpkg_contents (table_name='aerial_photos', data_type='tiles')
    ↓
gpkg_tile_matrix_set (table_name='aerial_photos', extent, srs_id)
    ↓
gpkg_tile_matrix (table_name='aerial_photos', zoom_level=10, matrix params)
    ↓
aerial_photos (zoom_level=10, tile_column=145, tile_row=67, tile_data=BLOB)

The User Tile Table Structure

Each tile pyramid requires its own table to store the actual tile images. The table name is user-defined but must be registered in gpkg_contents.

Required Table Structure

CREATE TABLE aerial_photos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  zoom_level INTEGER NOT NULL,
  tile_column INTEGER NOT NULL,
  tile_row INTEGER NOT NULL,
  tile_data BLOB NOT NULL,
  UNIQUE (zoom_level, tile_column, tile_row)
);

Column Definitions

id - Primary key, auto-incrementing integer (optional but recommended)

zoom_level - The zoom level of this tile (references gpkg_tile_matrix.zoom_level)

tile_column - Column number in the tile matrix (0 = leftmost)

tile_row - Row number in the tile matrix (0 = topmost)

tile_data - The actual tile image stored as binary data (BLOB)

UNIQUE constraint - Ensures only one tile exists for each (zoom_level, column, row) combination

Tile Data Format

The tile_data BLOB column contains the complete tile image in a standard format:

JPEG - For photographic imagery (aerial photos, satellite images)

• Good compression for natural imagery
• Lossy compression
• No transparency support
• Typical file size: 10-50KB per 256×256 tile

PNG - For graphics, maps with transparency, or when lossless quality is required

• Lossless compression
• Supports transparency (alpha channel)
• Larger file sizes than JPEG
• Typical file size: 20-100KB per 256×256 tile

WebP - Modern format with better compression

• Both lossy and lossless modes
• Supports transparency
• Smaller than PNG, similar quality to JPEG
• Not universally supported by older software

Example: Checking tile format

-- Detect image format from BLOB header
SELECT 
    zoom_level,
    tile_column,
    tile_row,
    CASE 
        WHEN substr(tile_data, 1, 3) = X'FFD8FF' THEN 'JPEG'
        WHEN substr(tile_data, 1, 8) = X'89504E470D0A1A0A' THEN 'PNG'
        WHEN substr(tile_data, 1, 4) = X'52494646' THEN 'WebP'
        ELSE 'Unknown'
    END AS format,
    length(tile_data) AS size_bytes
FROM aerial_photos
LIMIT 10;

Creating a Complete Tile Pyramid

Let's walk through creating a complete tile pyramid for a Danish municipality.

Step 1: Register in gpkg_contents

INSERT INTO gpkg_contents (
    table_name,
    data_type,
    identifier,
    description,
    last_change,
    min_x, min_y, max_x, max_y,
    srs_id
) VALUES (
    'vejle_ortho_2024',
    'tiles',
    'Vejle Orthophotos 2024',
    'Aerial photography of Vejle Kommune, 10cm resolution, captured April 2024',
    datetime('now'),
    520000, 6180000, 540000, 6200000,  -- Extent in EPSG:25832
    25832
);

Step 2: Define tile matrix set

INSERT INTO gpkg_tile_matrix_set (
    table_name,
    srs_id,
    min_x, min_y, max_x, max_y
) VALUES (
    'vejle_ortho_2024',
    25832,
    520000, 6180000, 540000, 6200000
);

Step 3: Define zoom levels

-- Zoom level 10: Overview (6.1m/pixel)
INSERT INTO gpkg_tile_matrix (
    table_name, zoom_level,
    matrix_width, matrix_height,
    tile_width, tile_height,
    pixel_x_size, pixel_y_size
) VALUES (
    'vejle_ortho_2024', 10,
    32, 32,
    256, 256,
    6.104,  -- (540000-520000)/(32*256)
    6.104   -- (6200000-6180000)/(32*256)
);

-- Zoom level 11: Planning (3.05m/pixel)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 11,
    64, 64,
    256, 256,
    3.052,
    3.052
);

-- Zoom level 12: Detail (1.5m/pixel)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 12,
    128, 128,
    256, 256,
    1.526,
    1.526
);

-- Zoom level 13: Fine detail (0.76m/pixel)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 13,
    256, 256,
    256, 256,
    0.763,
    0.763
);

-- Zoom level 14: High detail (0.38m/pixel)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 14,
    512, 512,
    256, 256,
    0.381,
    0.381
);

-- Zoom level 15: Very high detail (0.19m/pixel)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 15,
    1024, 1024,
    256, 256,
    0.191,
    0.191
);

-- Zoom level 16: Full resolution (0.095m/pixel ≈ 10cm)
INSERT INTO gpkg_tile_matrix VALUES (
    'vejle_ortho_2024', 16,
    2048, 2048,
    256, 256,
    0.095,
    0.095
);

Step 4: Create the tile table

CREATE TABLE vejle_ortho_2024 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    tile_data BLOB NOT NULL,
    UNIQUE (zoom_level, tile_column, tile_row)
);

Step 5: Create indexes for performance

-- Index on zoom level for efficient querying by zoom
CREATE INDEX idx_vejle_ortho_zoom 
ON vejle_ortho_2024(zoom_level);

-- Composite index for tile lookup
CREATE INDEX idx_vejle_ortho_tile_coords 
ON vejle_ortho_2024(zoom_level, tile_column, tile_row);

Populating Tile Data

There are several approaches to populating tile tables with actual image data.

Approach 1: Using GDAL/OGR Tools

GDAL is the most common tool for generating tiles from source imagery:

# Convert GeoTIFF to GeoPackage tiles
gdal_translate \
    -of GPKG \
    -co TILE_FORMAT=JPEG \
    -co QUALITY=85 \
    -co TILING_SCHEME=CUSTOM \
    vejle_orthophoto_2024.tif \
    vejle_municipality.gpkg \
    -co RASTER_TABLE=vejle_ortho_2024

# Add multiple zoom levels using gdaladdo
gdaladdo \
    -r average \
    vejle_municipality.gpkg \
    2 4 8 16 32 64

Approach 2: Manual SQL Insertion

For custom workflows or testing, you can insert tiles directly:

-- Insert a single tile (example with placeholder data)
INSERT INTO vejle_ortho_2024 (
    zoom_level,
    tile_column,
    tile_row,
    tile_data
) VALUES (
    10,              -- Zoom level
    15,              -- Column
    20,              -- Row
    readfile('tile_10_15_20.jpg')  -- Read tile from file
);

-- Batch insert from a staging table
INSERT INTO vejle_ortho_2024 (zoom_level, tile_column, tile_row, tile_data)
SELECT zoom_level, tile_column, tile_row, tile_data
FROM temp_tiles;

Approach 3: Using Python with PIL/Pillow

import sqlite3
from PIL import Image
import io

def insert_tile(conn, table_name, zoom, col, row, image_path, format='JPEG'):
    """Insert a tile into GeoPackage tile table"""
    
    # Open and process image
    img = Image.open(image_path)
    
    # Ensure correct size (e.g., 256x256)
    if img.size != (256, 256):
        img = img.resize((256, 256), Image.LANCZOS)
    
    # Convert to bytes
    buffer = io.BytesIO()
    img.save(buffer, format=format, quality=85 if format == 'JPEG' else None)
    tile_data = buffer.getvalue()
    
    # Insert into database
    cursor = conn.cursor()
    cursor.execute(f'''
        INSERT OR REPLACE INTO {table_name} 
        (zoom_level, tile_column, tile_row, tile_data)
        VALUES (?, ?, ?, ?)
    ''', (zoom, col, row, tile_data))
    
    conn.commit()

# Usage
conn = sqlite3.connect('vejle_municipality.gpkg')
insert_tile(conn, 'vejle_ortho_2024', 10, 15, 20, 'source_image.jpg')
conn.close()

Approach 4: Generating Overview Tiles

Creating lower zoom level (overview) tiles from higher zoom tiles:

def create_overview_tile(conn, table_name, from_zoom, to_zoom, to_col, to_row):
    """Create overview tile by combining 4 higher-zoom tiles"""
    from PIL import Image
    import io
    
    # Overview tile combines 4 tiles from higher zoom
    from_col = to_col * 2
    from_row = to_row * 2
    
    # Fetch the 4 source tiles
    cursor = conn.cursor()
    cursor.execute(f'''
        SELECT tile_column, tile_row, tile_data 
        FROM {table_name}
        WHERE zoom_level = ? 
          AND tile_column IN (?, ?)
          AND tile_row IN (?, ?)
    ''', (from_zoom, from_col, from_col + 1, from_row, from_row + 1))
    
    tiles = cursor.fetchall()
    
    if len(tiles) == 0:
        return  # No source tiles available
    
    # Create composite image
    overview = Image.new('RGB', (512, 512))
    
    for col, row, data in tiles:
        img = Image.open(io.BytesIO(data))
        x = (col - from_col) * 256
        y = (row - from_row) * 256
        overview.paste(img, (x, y))
    
    # Resize to 256x256
    overview = overview.resize((256, 256), Image.LANCZOS)
    
    # Save as tile
    buffer = io.BytesIO()
    overview.save(buffer, format='JPEG', quality=85)
    tile_data = buffer.getvalue()
    
    # Insert overview tile
    cursor.execute(f'''
        INSERT OR REPLACE INTO {table_name}
        (zoom_level, tile_column, tile_row, tile_data)
        VALUES (?, ?, ?, ?)
    ''', (to_zoom, to_col, to_row, tile_data))
    
    conn.commit()

Querying Tile Data

Basic Tile Queries

-- Get a specific tile
SELECT tile_data 
FROM vejle_ortho_2024
WHERE zoom_level = 12 
  AND tile_column = 64 
  AND tile_row = 72;

-- Count tiles at each zoom level
SELECT 
    zoom_level,
    COUNT(*) AS tile_count,
    ROUND(SUM(length(tile_data)) / 1024.0 / 1024.0, 2) AS size_mb
FROM vejle_ortho_2024
GROUP BY zoom_level
ORDER BY zoom_level;

-- Find missing tiles in a zoom level
SELECT 
    z.zoom_level,
    z.expected_tiles,
    COUNT(t.id) AS actual_tiles,
    z.expected_tiles - COUNT(t.id) AS missing_tiles
FROM (
    SELECT 
        zoom_level,
        matrix_width * matrix_height AS expected_tiles
    FROM gpkg_tile_matrix
    WHERE table_name = 'vejle_ortho_2024'
) z
LEFT JOIN vejle_ortho_2024 t ON z.zoom_level = t.zoom_level
GROUP BY z.zoom_level;

-- Get tiles within a geographic area
-- (Requires calculating which tiles intersect the area)
SELECT 
    t.zoom_level,
    t.tile_column,
    t.tile_row,
    t.tile_data
FROM vejle_ortho_2024 t
WHERE t.zoom_level = 12
  AND t.tile_column BETWEEN 50 AND 70
  AND t.tile_row BETWEEN 60 AND 80;

Calculating Tile Coverage

-- Function to determine which tiles cover a geographic extent
-- Given: extent in EPSG:25832 (560000, 6190000, 565000, 6195000)
-- Want: tiles at zoom level 12 that intersect this extent

WITH extent AS (
    SELECT 
        560000 AS query_min_x,
        6190000 AS query_min_y,
        565000 AS query_max_x,
        6195000 AS query_max_y
),
tile_params AS (
    SELECT 
        tm.zoom_level,
        tms.min_x AS origin_x,
        tms.max_y AS origin_y,  -- TMS: row 0 at top
        tm.pixel_x_size,
        tm.pixel_y_size,
        tm.tile_width,
        tm.tile_height,
        tm.matrix_width,
        tm.matrix_height
    FROM gpkg_tile_matrix tm
    JOIN gpkg_tile_matrix_set tms ON tm.table_name = tms.table_name
    WHERE tm.table_name = 'vejle_ortho_2024'
      AND tm.zoom_level = 12
)
SELECT 
    -- Calculate tile column range
    CAST((e.query_min_x - tp.origin_x) / (tp.pixel_x_size * tp.tile_width) AS INTEGER) AS min_col,
    CAST((e.query_max_x - tp.origin_x) / (tp.pixel_x_size * tp.tile_width) AS INTEGER) AS max_col,
    
    -- Calculate tile row range (TMS: row 0 at top)
    CAST((tp.origin_y - e.query_max_y) / (tp.pixel_y_size * tp.tile_height) AS INTEGER) AS min_row,
    CAST((tp.origin_y - e.query_min_y) / (tp.pixel_y_size * tp.tile_height) AS INTEGER) AS max_row
FROM extent e, tile_params tp;

-- Use the result to query tiles
-- Assuming results: min_col=58, max_col=61, min_row=65, max_row=68
SELECT 
    zoom_level,
    tile_column,
    tile_row,
    length(tile_data) AS size_bytes
FROM vejle_ortho_2024
WHERE zoom_level = 12
  AND tile_column BETWEEN 58 AND 61
  AND tile_row BETWEEN 65 AND 68
ORDER BY tile_column, tile_row;

Finding Empty (Blank) Tiles

-- Tiles outside data coverage may still be generated as blank tiles
-- These waste storage and can be identified and removed

-- Find unusually small tiles (likely blank)
SELECT 
    zoom_level,
    tile_column,
    tile_row,
    length(tile_data) AS size_bytes
FROM vejle_ortho_2024
WHERE length(tile_data) < 2000  -- JPEG tiles < 2KB likely blank
ORDER BY length(tile_data);

-- Calculate statistics on tile sizes
SELECT 
    zoom_level,
    COUNT(*) AS tile_count,
    MIN(length(tile_data)) AS min_size,
    AVG(length(tile_data)) AS avg_size,
    MAX(length(tile_data)) AS max_size,
    ROUND(STDEV(length(tile_data)), 0) AS stddev_size
FROM vejle_ortho_2024
GROUP BY zoom_level;

Optimizing Tile Storage

Compression and Quality

JPEG quality settings:

-- High quality (larger files, better visual quality)
-- Quality 95: ~50-80KB per tile
-- Use for: Final publication, detailed inspection

-- Medium quality (balanced)
-- Quality 85: ~20-40KB per tile
-- Use for: Most aerial photography applications

-- Lower quality (smaller files)
-- Quality 75: ~15-25KB per tile
-- Use for: Base maps, overview levels

Format selection by use case:

-- Aerial photography → JPEG (quality 85)
CREATE TABLE aerial_photos (...);

-- Scanned historical maps → PNG (preserve detail and transparency)
CREATE TABLE historical_maps (...);

-- Rendered base maps → PNG or WebP
CREATE TABLE base_map (...);

-- Elevation hillshade → PNG (grayscale, smaller than color)
CREATE TABLE elevation_hillshade (...);

Storage Efficiency

-- Calculate storage used by tile pyramid
SELECT 
    table_name,
    SUM(length(tile_data)) / 1024.0 / 1024.0 / 1024.0 AS size_gb,
    COUNT(*) AS tile_count,
    SUM(length(tile_data)) / COUNT(*) AS avg_tile_size_bytes
FROM vejle_ortho_2024
GROUP BY table_name;

-- Identify zoom levels with most storage
SELECT 
    zoom_level,
    COUNT(*) AS tile_count,
    ROUND(SUM(length(tile_data)) / 1024.0 / 1024.0, 2) AS size_mb,
    ROUND(100.0 * SUM(length(tile_data)) / 
        (SELECT SUM(length(tile_data)) FROM vejle_ortho_2024), 1) AS pct_total
FROM vejle_ortho_2024
GROUP BY zoom_level
ORDER BY zoom_level;

Selective Tile Storage

Not all tiles need to be stored—consider selective population:

-- Only store tiles that intersect actual data coverage
-- Example: Municipality boundary polygon

-- Create temporary table with tiles to keep
CREATE TEMP TABLE tiles_to_keep AS
SELECT DISTINCT
    t.zoom_level,
    t.tile_column,
    t.tile_row
FROM vejle_ortho_2024 t
WHERE EXISTS (
    SELECT 1 FROM municipal_boundary m
    WHERE ST_Intersects(
        m.geom,
        -- Calculate tile extent
        MakeBox2D(
            MakePoint(
                520000 + t.tile_column * 256 * (
                    SELECT pixel_x_size FROM gpkg_tile_matrix 
                    WHERE table_name = 'vejle_ortho_2024' 
                      AND zoom_level = t.zoom_level
                ),
                6200000 - t.tile_row * 256 * (
                    SELECT pixel_y_size FROM gpkg_tile_matrix 
                    WHERE table_name = 'vejle_ortho_2024' 
                      AND zoom_level = t.zoom_level
                )
            ),
            MakePoint(
                520000 + (t.tile_column + 1) * 256 * (
                    SELECT pixel_x_size FROM gpkg_tile_matrix 
                    WHERE table_name = 'vejle_ortho_2024' 
                      AND zoom_level = t.zoom_level
                ),
                6200000 - (t.tile_row + 1) * 256 * (
                    SELECT pixel_y_size FROM gpkg_tile_matrix 
                    WHERE table_name = 'vejle_ortho_2024' 
                      AND zoom_level = t.zoom_level
                )
            )
        )
    )
);

-- Remove tiles outside coverage
DELETE FROM vejle_ortho_2024
WHERE NOT EXISTS (
    SELECT 1 FROM tiles_to_keep tk
    WHERE tk.zoom_level = vejle_ortho_2024.zoom_level
      AND tk.tile_column = vejle_ortho_2024.tile_column
      AND tk.tile_row = vejle_ortho_2024.tile_row
);

-- Vacuum to reclaim space
VACUUM;

Working with Multiple Tile Pyramids

A single GeoPackage can contain multiple tile pyramids.

Example: Aerial Photos + Base Map

-- Tile pyramid 1: Aerial photography
CREATE TABLE aerial_photos_2024 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    tile_data BLOB NOT NULL,
    UNIQUE (zoom_level, tile_column, tile_row)
);

-- Tile pyramid 2: OpenStreetMap base map
CREATE TABLE osm_basemap (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    tile_data BLOB NOT NULL,
    UNIQUE (zoom_level, tile_column, tile_row)
);

-- Tile pyramid 3: Elevation hillshade
CREATE TABLE elevation_hillshade (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    tile_data BLOB NOT NULL,
    UNIQUE (zoom_level, tile_column, tile_row)
);

-- Each requires its own entries in:
-- - gpkg_contents
-- - gpkg_tile_matrix_set  
-- - gpkg_tile_matrix

-- List all tile pyramids
SELECT 
    table_name,
    identifier,
    description,
    COUNT(DISTINCT zoom_level) AS zoom_levels
FROM gpkg_contents c
LEFT JOIN gpkg_tile_matrix tm ON c.table_name = tm.table_name
WHERE c.data_type = 'tiles'
GROUP BY c.table_name;

Tile Table Maintenance

Updating Tiles

-- Replace a specific tile
UPDATE vejle_ortho_2024
SET tile_data = readfile('new_tile_12_64_72.jpg')
WHERE zoom_level = 12 
  AND tile_column = 64 
  AND tile_row = 72;

-- Update timestamp in gpkg_contents
UPDATE gpkg_contents
SET last_change = datetime('now')
WHERE table_name = 'vejle_ortho_2024';

Deleting Tiles

-- Delete tiles at a specific zoom level
DELETE FROM vejle_ortho_2024
WHERE zoom_level = 8;

-- Delete tiles outside a geographic area
DELETE FROM vejle_ortho_2024
WHERE zoom_level = 12
  AND (tile_column < 50 OR tile_column > 70
       OR tile_row < 60 OR tile_row > 80);

-- Remove entire tile pyramid
DROP TABLE vejle_ortho_2024;
DELETE FROM gpkg_tile_matrix WHERE table_name = 'vejle_ortho_2024';
DELETE FROM gpkg_tile_matrix_set WHERE table_name = 'vejle_ortho_2024';
DELETE FROM gpkg_contents WHERE table_name = 'vejle_ortho_2024';

Vacuum and Optimization

-- After deleting tiles, reclaim space
VACUUM;

-- Analyze for query optimization
ANALYZE;

-- Rebuild indexes
REINDEX vejle_ortho_2024;

-- Check database integrity
PRAGMA integrity_check;

Quality Assurance

Validation Queries

-- 1. Check for missing zoom levels
SELECT DISTINCT tm.zoom_level
FROM gpkg_tile_matrix tm
WHERE tm.table_name = 'vejle_ortho_2024'
  AND NOT EXISTS (
      SELECT 1 FROM vejle_ortho_2024 t
      WHERE t.zoom_level = tm.zoom_level
  );

-- 2. Check for tiles outside valid range
SELECT 
    t.zoom_level,
    t.tile_column,
    t.tile_row
FROM vejle_ortho_2024 t
JOIN gpkg_tile_matrix tm ON t.zoom_level = tm.zoom_level
WHERE tm.table_name = 'vejle_ortho_2024'
  AND (t.tile_column < 0 
       OR t.tile_column >= tm.matrix_width
       OR t.tile_row < 0 
       OR t.tile_row >= tm.matrix_height);

-- 3. Check for duplicate tiles (shouldn't exist with UNIQUE constraint)
SELECT 
    zoom_level,
    tile_column,
    tile_row,
    COUNT(*) AS count
FROM vejle_ortho_2024
GROUP BY zoom_level, tile_column, tile_row
HAVING COUNT(*) > 1;

-- 4. Verify tile format consistency
SELECT 
    zoom_level,
    CASE 
        WHEN substr(tile_data, 1, 3) = X'FFD8FF' THEN 'JPEG'
        WHEN substr(tile_data, 1, 8) = X'89504E470D0A1A0A' THEN 'PNG'
        ELSE 'Unknown'
    END AS format,
    COUNT(*) AS count
FROM vejle_ortho_2024
GROUP BY zoom_level, format
ORDER BY zoom_level, format;

-- 5. Check for suspiciously small tiles
SELECT 
    zoom_level,
    tile_column,
    tile_row,
    length(tile_data) AS size_bytes
FROM vejle_ortho_2024
WHERE length(tile_data) < 1000  -- Less than 1KB
ORDER BY length(tile_data);

Coverage Reports

-- Generate coverage report by zoom level
SELECT 
    tm.zoom_level,
    tm.matrix_width * tm.matrix_height AS expected_tiles,
    COUNT(t.id) AS actual_tiles,
    ROUND(100.0 * COUNT(t.id) / (tm.matrix_width * tm.matrix_height), 1) AS coverage_pct,
    ROUND(SUM(length(t.tile_data)) / 1024.0 / 1024.0, 2) AS size_mb
FROM gpkg_tile_matrix tm
LEFT JOIN vejle_ortho_2024 t ON tm.zoom_level = t.zoom_level
WHERE tm.table_name = 'vejle_ortho_2024'
GROUP BY tm.zoom_level
ORDER BY tm.zoom_level;

Advanced Patterns

Pattern 1: Tile Metadata

Sometimes you need to store metadata about tiles:

-- Extended tile table with metadata
CREATE TABLE aerial_photos_extended (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    tile_data BLOB NOT NULL,
    
    -- Additional metadata
    capture_date DATE,
    cloud_coverage_pct REAL,
    sun_elevation_deg REAL,
    processing_quality TEXT,
    source_image_id TEXT,
    
    UNIQUE (zoom_level, tile_column, tile_row)
);

-- Query tiles by metadata
SELECT zoom_level, tile_column, tile_row
FROM aerial_photos_extended
WHERE capture_date BETWEEN '2024-04-01' AND '2024-04-30'
  AND cloud_coverage_pct < 5.0;

Pattern 2: Versioned Tiles

Track tile versions over time:

CREATE TABLE aerial_photos_versioned (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    zoom_level INTEGER NOT NULL,
    tile_column INTEGER NOT NULL,
    tile_row INTEGER NOT NULL,
    version_date DATE NOT NULL,
    tile_data BLOB NOT NULL,
    is_current INTEGER DEFAULT 1,
    
    UNIQUE (zoom_level, tile_column, tile_row, version_date)
);

-- Get current version of tiles
SELECT zoom_level, tile_column, tile_row, tile_data
FROM aerial_photos_versioned
WHERE is_current = 1;

-- Get historical tile
SELECT tile_data
FROM aerial_photos_versioned
WHERE zoom_level = 12
  AND tile_column = 64
  AND tile_row = 72
  AND version_date = '2023-05-15';

Pattern 3: Tile Attribution

Track data sources for each tile:

CREATE TABLE tile_attribution (
    tile_id INTEGER PRIMARY KEY,
    data_source TEXT,
    license TEXT,
    attribution_text TEXT,
    capture_date DATE,
    
    FOREIGN KEY (tile_id) REFERENCES vejle_ortho_2024(id)
);

-- Query with attribution
SELECT 
    t.zoom_level,
    t.tile_column,
    t.tile_row,
    a.data_source,
    a.attribution_text
FROM vejle_ortho_2024 t
LEFT JOIN tile_attribution a ON t.id = a.tile_id
WHERE t.zoom_level = 12
  AND t.tile_column BETWEEN 60 AND 70;

Performance Considerations

Indexing Strategy

-- Essential: Unique constraint (already creates index)
CREATE UNIQUE INDEX idx_tiles_coords 
ON vejle_ortho_2024(zoom_level, tile_column, tile_row);

-- Helpful: Zoom level index for range queries
CREATE INDEX idx_tiles_zoom 
ON vejle_ortho_2024(zoom_level);

-- Consider: Partial indexes for specific zoom levels
CREATE INDEX idx_tiles_high_zoom 
ON vejle_ortho_2024(tile_column, tile_row)
WHERE zoom_level >= 14;

Query Optimization

-- Fast: Uses unique index
SELECT tile_data 
FROM vejle_ortho_2024
WHERE zoom_level = 12 
  AND tile_column = 64 
  AND tile_row = 72;

-- Fast: Range query with index
SELECT tile_column, tile_row
FROM vejle_ortho_2024
WHERE zoom_level = 12
  AND tile_column BETWEEN 60 AND 70
  AND tile_row BETWEEN 65 AND 75;
  
-- Slow: Full table scan without zoom_level
SELECT tile_data
FROM vejle_ortho_2024
WHERE tile_column = 64 AND tile_row = 72;  -- Missing zoom_level!

Batch Operations

-- Use transactions for bulk inserts
BEGIN TRANSACTION;

-- Insert many tiles
INSERT INTO vejle_ortho_2024 VALUES (NULL, 12, 60, 65, readfile('tile1.jpg'));
INSERT INTO vejle_ortho_2024 VALUES (NULL, 12, 61, 65, readfile('tile2.jpg'));
-- ... thousands more

COMMIT;

-- Much faster than individual transactions

Best Practices Summary

1. Use standard tile table structure - Follow the GeoPackage specification exactly for maximum compatibility

2. Create appropriate indexes - At minimum, the UNIQUE constraint on (zoom_level, tile_column, tile_row)

3. Choose appropriate image format - JPEG for photos, PNG for graphics/transparency

4. Optimize compression - Balance quality and file size (JPEG quality 85 is often ideal)

5. Validate tile coverage - Check for missing or invalid tiles

6. Use transactions - Batch insertions in transactions for better performance

7. Clean up regularly - Remove unused tiles and VACUUM to reclaim space

8. Document tile pyramids - Use descriptive names and detailed descriptions in gpkg_contents

9. Test tile access - Verify tiles can be retrieved efficiently for expected use cases

10. Monitor storage - Track tile pyramid sizes and growth over time

Summary

The user-defined tile table is where the actual raster data lives in a GeoPackage, storing individual tiles as BLOBs indexed by zoom level, column, and row coordinates. Understanding how to create, populate, query, and maintain tile tables is essential for working with aerial imagery, base maps, and other raster datasets in Danish municipal applications. Proper indexing, format selection, and quality assurance ensure tile pyramids perform efficiently while maintaining data quality and manageable file sizes.

Knowledge Check Quiz

Test your understanding of tile tables and tile pyramid storage:

Question 1: What are the required columns in a GeoPackage tile table?

a) id, tile_data
b) zoom_level, tile_column, tile_row, tile_data
c) x, y, z, image
d) level, x_coord, y_coord, blob_data

Question 2: What data type is used to store tile images in GeoPackage?

a) TEXT
b) BLOB
c) IMAGE
d) BYTEA

Question 3: Which constraint should always be present on a tile table?

a) PRIMARY KEY on id
b) FOREIGN KEY to gpkg_tile_matrix
c) UNIQUE on (zoom_level, tile_column, tile_row)
d) CHECK on tile_data size

Question 4: What image format is typically best for aerial photography tiles?

a) PNG for best quality
b) JPEG for good compression of photographic content
c) GIF for smaller file sizes
d) TIFF for best compatibility

Question 5: In the tile table, what does tile_column = 0 represent?

a) The center column
b) The rightmost column
c) The leftmost column
d) An invalid value

Question 6: Why is it important to create indexes on tile tables?

a) Indexes are required by the GeoPackage specification
b) To improve query performance when retrieving specific tiles
c) To reduce storage space
d) To validate tile data

Question 7: What happens if you insert a tile with the same (zoom_level, tile_column, tile_row) as an existing tile?

a) Both tiles are stored
b) The new tile replaces the old one
c) An error occurs due to the UNIQUE constraint
d) The tiles are automatically merged

Question 8: What is a reasonable JPEG quality setting for most aerial photography applications?

a) 100 (maximum quality)
b) 85 (good balance of quality and size)
c) 50 (smallest file size)
d) Quality doesn't matter for GeoPackage

Question 9: How can you determine the image format of tiles in a tile table?

a) Check the file extension
b) Examine the first few bytes of the tile_data BLOB
c) Query the gpkg_tile_matrix table
d) Image format is stored in a separate column

Question 10: What SQL command should you run after deleting many tiles to reclaim disk space?

a) REINDEX
b) ANALYZE
c) VACUUM
d) OPTIMIZE

Question 11: Can a single GeoPackage contain multiple tile pyramids?

a) No, only one tile pyramid per GeoPackage
b) Yes, each with its own tile table
c) Yes, but they must share the same tile table
d) Only if they use the same coordinate system

Question 12: What is the purpose of the id column in a tile table?

a) It stores the zoom level
b) It's a unique identifier for each tile (primary key)
c) It references the gpkg_contents table
d) It stores the tile format

Question 13: When creating overview tiles from higher-zoom tiles, how many higher-zoom tiles typically combine into one overview tile?

a) 2 tiles
b) 4 tiles
c) 8 tiles
d) 16 tiles

Question 14: What does the AUTOINCREMENT keyword ensure for the id column?

a) IDs are assigned sequentially
b) IDs are never reused after deletion
c) IDs start at 1
d) IDs are random

Question 15: Why might you want to store tiles selectively rather than generating all possible tiles for a zoom level?

a) To save storage space by only storing tiles that contain actual data
b) To improve query performance
c) Because GeoPackage limits the number of tiles
d) To make the database more compatible

Answer Key

1. b) zoom_level, tile_column, tile_row, tile_data - These four columns are required by the GeoPackage specification. The id column is recommended but not strictly required.
2. b) BLOB - Tile images are stored as binary large objects (BLOBs) in SQLite.
3. c) UNIQUE on (zoom_level, tile_column, tile_row) - This constraint ensures only one tile exists for each position in the pyramid.
4. b) JPEG for good compression of photographic content - JPEG provides excellent compression for aerial photos with acceptable quality loss.
5. c) The leftmost column - Column numbering starts at 0 on the left (min_x) and increases eastward.
6. b) To improve query performance when retrieving specific tiles - Indexes allow fast lookup of tiles by their coordinates.
7. c) An error occurs due to the UNIQUE constraint - The UNIQUE constraint prevents duplicate tiles. Use INSERT OR REPLACE to overwrite.
8. b) 85 (good balance of quality and size) - Quality 85 provides good visual quality with reasonable file sizes for most applications.
9. b) Examine the first few bytes of the tile_data BLOB - Image formats have distinctive "magic bytes" at the start (JPEG: FFD8FF, PNG: 89504E47...).
10. c) VACUUM - VACUUM reclaims space from deleted records and rebuilds the database file.
11. b) Yes, each with its own tile table - A GeoPackage can contain multiple tile pyramids, each registered separately.
12. b) It's a unique identifier for each tile (primary key) - The id serves as the primary key for efficient database operations.
13. b) 4 tiles - One overview tile typically combines 2×2 = 4 tiles from the next higher zoom level.
14. b) IDs are never reused after deletion - AUTOINCREMENT ensures deleted IDs are never reassigned, maintaining referential integrity.
15. a) To save storage space by only storing tiles that contain actual data - Selective storage avoids creating blank tiles outside the data coverage area.