geopackage tests

create-gpkg.py

import sqlite3
import os
import json
from pathlib import Path

def create_3dtiles_geopackage(tileset_path, output_gpkg="3dtiles.gpkg"):
    """
    Create a GeoPackage containing 3D Tiles content.
    
    Args:
        tileset_path (str): Path to the tileset.json file
        output_gpkg (str): Output GeoPackage filename
    """
    # Use Path for better path handling
    tileset_path = Path(tileset_path)
    tileset_dir = tileset_path.parent
    
    print(f"Creating GeoPackage from 3D Tiles at: {tileset_path}")
    
    # Create or open GeoPackage
    conn = sqlite3.connect(output_gpkg)
    c = conn.cursor()
    
    # Create required GeoPackage tables
    c.execute('''
    CREATE TABLE IF NOT EXISTS gpkg_spatial_ref_sys (
        srs_name TEXT NOT NULL,
        srs_id INTEGER NOT NULL PRIMARY KEY,
        organization TEXT NOT NULL,
        organization_coordsys_id INTEGER NOT NULL,
        definition TEXT NOT NULL,
        description TEXT
    )
    ''')
    
    c.execute('''
    CREATE TABLE IF NOT EXISTS gpkg_contents (
        table_name TEXT NOT NULL PRIMARY KEY,
        data_type TEXT NOT NULL,
        identifier TEXT UNIQUE,
        description TEXT DEFAULT '',
        last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
        min_x DOUBLE,
        min_y DOUBLE,
        max_x DOUBLE,
        max_y DOUBLE,
        srs_id INTEGER,
        CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id)
    )
    ''')
    
    # Add WGS84 as default spatial reference system
    c.execute('''
    INSERT OR IGNORE INTO gpkg_spatial_ref_sys 
    VALUES ('WGS84', 4326, 'EPSG', 4326, 
    'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]',
    'World Geodetic System 1984')
    ''')
    
    # Create custom table for 3D Tiles content
    c.execute('''
    CREATE TABLE IF NOT EXISTS threed_tiles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        path TEXT UNIQUE NOT NULL,
        content_type TEXT NOT NULL,
        content BLOB NOT NULL,
        last_modified DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
    )
    ''')
    
    # Create index for faster lookups
    c.execute('CREATE INDEX IF NOT EXISTS idx_threed_tiles_path ON threed_tiles(path)')
    
    # Register the table in gpkg_contents
    # Get the bounding box from tileset.json if available
    min_x, min_y, max_x, max_y = None, None, None, None
    
    try:
        with open(tileset_path, 'r') as f:
            tileset_data = json.load(f)
            if 'root' in tileset_data and 'boundingVolume' in tileset_data['root']:
                if 'region' in tileset_data['root']['boundingVolume']:
                    # Convert region [west, south, east, north, minimum height, maximum height] to min_x, min_y, max_x, max_y
                    region = tileset_data['root']['boundingVolume']['region']
                    min_x, min_y, max_x, max_y = region[0], region[1], region[2], region[3]
                elif 'box' in tileset_data['root']['boundingVolume']:
                    # Extract approximate bounding box from center and half-extents
                    box = tileset_data['root']['boundingVolume']['box']
                    center_x, center_y = box[0], box[1]
                    half_x, half_y = box[3], box[7]  # Assuming X is index 3 and Y is index 7
                    min_x, min_y = center_x - half_x, center_y - half_y
                    max_x, max_y = center_x + half_x, center_y + half_y
    except (json.JSONDecodeError, KeyError, IndexError) as e:
        print(f"Warning: Could not extract bounding box from tileset: {e}")
    
    c.execute('''
    INSERT OR REPLACE INTO gpkg_contents 
    (table_name, data_type, identifier, description, min_x, min_y, max_x, max_y, srs_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', ('threed_tiles', '3d-tiles', '3D Tiles Dataset', 
          f'3D Tiles from {tileset_path.name}', 
          min_x, min_y, max_x, max_y, 4326))
    
    # Insert tileset.json
    with open(tileset_path, 'rb') as f:
        tileset_content = f.read()
        c.execute('INSERT OR REPLACE INTO threed_tiles (path, content_type, content) VALUES (?, ?, ?)',
                  ('tileset.json', 'application/json', tileset_content))
        print(f"Added tileset.json ({len(tileset_content)} bytes)")
    
    # Count tiles processed
    tile_count = 1  # Starting with tileset.json
    
    # Walk through the directory and store all tile files
    for file_path in tileset_dir.glob('**/*'):
        if file_path.is_file() and file_path.suffix.lower() in ['.b3dm', '.pnts', '.i3dm', '.cmpt', '.vctr', '.json', '.glb']:
            # Skip tileset.json as we've already processed it
            if file_path.name == 'tileset.json' and file_path.parent == tileset_dir:
                continue
                
            # Get relative path from tileset directory
            rel_path = str(file_path.relative_to(tileset_dir))
            
            # Use forward slashes for paths
            rel_path = rel_path.replace('\\', '/')
            
            with open(file_path, 'rb') as f:
                file_content = f.read()
                
                # Determine content type
                content_type = 'application/octet-stream'
                if file_path.suffix == '.json':
                    content_type = 'application/json'
                elif file_path.suffix == '.b3dm':
                    content_type = 'application/vnd.geo+b3dm'
                elif file_path.suffix == '.pnts':
                    content_type = 'application/vnd.geo+pnts'
                elif file_path.suffix == '.i3dm':
                    content_type = 'application/vnd.geo+i3dm'
                elif file_path.suffix == '.cmpt':
                    content_type = 'application/vnd.geo+cmpt'
                elif file_path.suffix == '.vctr':
                    content_type = 'application/vnd.geo+vctr'
                elif file_path.suffix == '.glb':
                    content_type = 'model/gltf-binary'
                
                c.execute('INSERT OR REPLACE INTO threed_tiles (path, content_type, content) VALUES (?, ?, ?)',
                          (rel_path, content_type, file_content))
                
                tile_count += 1
                if tile_count % 10 == 0:
                    print(f"Processed {tile_count} files...")
    
    # Create a metadata table
    c.execute('''
    CREATE TABLE IF NOT EXISTS gpkg_extensions (
        table_name TEXT,
        column_name TEXT,
        extension_name TEXT NOT NULL,
        definition TEXT NOT NULL,
        scope TEXT NOT NULL,
        CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name)
    )
    ''')
    
    # Register the 3D Tiles extension
    c.execute('''
    INSERT OR REPLACE INTO gpkg_extensions
    (table_name, column_name, extension_name, definition, scope)
    VALUES (?, ?, ?, ?, ?)
    ''', ('threed_tiles', None, '3d-tiles', 
          'https://github.com/OGC/ogc-3d-tiles-specification', 'read-write'))
    
    conn.commit()
    conn.close()
    
    print(f"Successfully created GeoPackage with {tile_count} 3D Tiles files at: {output_gpkg}")
    return output_gpkg

if __name__ == "__main__":
    # Example usage:
    tileset_path = "./small-havarim/tileset.json"
    create_3dtiles_geopackage(tileset_path, "improved_3dtiles.gpkg")

index.html

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>3D Tiles from GeoPackage - Cesium POC</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/cesium/1.105.0/Cesium.js"></script>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/cesium/1.105.0/Widgets/widgets.min.css" rel="stylesheet">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.min.js"></script>
    <style>
        html,
        body,
        #cesiumContainer {
            width: 100%;
            height: 100%;
            margin: 0;
            padding: 0;
            overflow: hidden;
        }

        .top-panel {
            position: absolute;
            top: 10px;
            left: 10px;
            right: 10px;
            z-index: 999;
            background: rgba(42, 42, 42, 0.8);
            border-radius: 4px;
            padding: 10px;
            color: white;
            display: flex;
            justify-content: space-between;
        }

        .toolbar {
            display: flex;
            flex-direction: column;
        }

        .toolbar h3 {
            margin-top: 0;
        }

        .toolbar button {
            margin: 5px 0;
            padding: 8px 12px;
            cursor: pointer;
        }

        .log-panel {
            width: 60%;
            height: 150px;
            overflow-y: auto;
            background: rgba(0, 0, 0, 0.5);
            padding: 10px;
            border-radius: 4px;
            font-family: monospace;
            font-size: 12px;
        }

        .log-panel p {
            margin: 3px 0;
        }

        .message {
            position: absolute;
            top: 50%;
            left: 50%;
            transform: translate(-50%, -50%);
            background: rgba(0, 0, 0, 0.7);
            color: white;
            padding: 20px;
            border-radius: 5px;
            display: none;
            text-align: center;
            max-width: 80%;
        }
    </style>
</head>

<body>
    <div id="cesiumContainer"></div>
    <div class="top-panel">
        <div class="toolbar">
            <h3>3D Tiles from GeoPackage POC</h3>
            <button id="uploadGpkgBtn">Upload GeoPackage</button>
            <button id="loadSampleBtn">Load Sample Data</button>
            <button id="clearTilesBtn">Clear All Tilesets</button>
        </div>
        <div class="log-panel" id="logPanel">
            <p>Log messages will appear here</p>
        </div>
    </div>
    <div id="message" class="message"></div>

    <script type="module">
        // Import the GeoPackage3DTilesLoader module
        const GeoPackage3DTilesLoader = {
            // In-memory implementation since we don't have actual module loading here
            constructor(viewer) {
                this.viewer = viewer;
                this.tilesets = [];
                this.logger = function () { };
            },

            async loadGeoPackage(file) {
                return new Promise((resolve, reject) => {
                    const reader = new FileReader();

                    reader.onload = async (e) => {
                        try {
                            const arrayBuffer = e.target.result;
                            this.log(`Loaded file: ${file.name} (${(arrayBuffer.byteLength / 1024 / 1024).toFixed(2)} MB)`);

                            await this._processGeoPackage(arrayBuffer);
                            resolve();
                        } catch (error) {
                            this.log(`Error: ${error.message}`, 'error');
                            reject(error);
                        }
                    };

                    reader.onerror = (event) => {
                        this.log(`Failed to read file: ${event.target.error}`, 'error');
                        reject(new Error('Failed to read file: ' + event.target.error));
                    };

                    reader.readAsArrayBuffer(file);
                });
            },

            setLogger(logFunction) {
                this.logger = logFunction;
            },

            log(message, type = 'info') {
                console.log(`[GeoPackage3DTilesLoader] ${message}`);
                if (this.logger) {
                    this.logger(message, type);
                }
            },

            async _processGeoPackage(arrayBuffer) {
                try {
                    this.log('Processing GeoPackage file...');

                    // Attempt to parse as SQLite database
                    this.log('Loading SQLite engine...');

                    // Check if SQL.js is available
                    if (!window.initSqlJs) {
                        this.log('SQL.js not loaded. Loading from CDN...', 'warning');
                        await new Promise((resolve) => {
                            const script = document.createElement('script');
                            script.src = 'https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/sql-wasm.min.js';
                            script.onload = resolve;
                            document.head.appendChild(script);
                        });
                    }

                    // Initialize SQL.js
                    const SQL = await initSqlJs({
                        locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.8.0/${file}`
                    });

                    this.log('Opening SQLite database...');
                    const db = new SQL.Database(new Uint8Array(arrayBuffer));

                    // Query GeoPackage metadata
                    this.log('Querying GeoPackage metadata...');
                    let tables = [];

                    try {
                        const result = db.exec("SELECT name FROM sqlite_master WHERE type='table'");
                        if (result && result[0]) {
                            tables = result[0].values.map(v => v[0]);
                            this.log(`Found ${tables.length} tables in GeoPackage: ${tables.join(', ')}`);
                        } else {
                            this.log('No tables found in SQLite database', 'warning');
                        }
                    } catch (e) {
                        this.log(`Error querying tables: ${e.message}`, 'error');
                        throw new Error('Failed to query GeoPackage tables. This may not be a valid GeoPackage file.');
                    }

                    // Look for 3D Tiles related tables or extensions
                    this.log('Looking for 3D Tiles data...');

                    // In a real implementation, we would look for specific tables or
                    // custom extensions that store 3D Tiles content

                    // For now, we'll simulate finding 3D Tiles
                    this.log('No built-in 3D Tiles capability found in GeoPackage', 'warning');
                    this.log('This POC will load a sample 3D Tileset instead');

                    // Close the database
                    db.close();

                    // Load a sample tileset instead
                    await this._createSampleTileset();

                } catch (error) {
                    this.log(`Failed to process GeoPackage: ${error.message}`, 'error');
                    throw error;
                }
            },

            async _createSampleTileset() {
                this.log('Creating sample 3D Tileset...');
                // cesium ion loading of kela terrain ( havarim ) 
                const tileset = this.viewer.scene.primitives.add(
                    new Cesium.Cesium3DTileset({
                        url: Cesium.IonResource.fromAssetId(3135461) // Sample building tileset
                    })
                );

                this.tilesets.push(tileset);

                // Wait for the tileset to load
                this.log('Loading 3D Tileset...');

                try {
                    await tileset.readyPromise;
                    this.log('3D Tileset loaded successfully');

                } catch (error) {
                    this.log(`Failed to load 3D Tileset: ${error.message}`, 'error');
                    throw error;
                }

                return tileset;
            },

            removeAllTilesets() {
                this.log(`Removing ${this.tilesets.length} tilesets`);

                for (const tileset of this.tilesets) {
                    this.viewer.scene.primitives.remove(tileset);
                }

                this.tilesets = [];
                this.log('All tilesets removed');
            }
        };

        // Initialize Cesium
        Cesium.Ion.defaultAccessToken = 'ACCESS_TOKEN';

        const viewer = new Cesium.Viewer('cesiumContainer', {
            terrainProvider: Cesium.createWorldTerrain(),
            infoBox: false,
            selectionIndicator: false,
            shadows: true,
            shouldAnimate: true
        });

        // Create log function
        function log(message, type = 'info') {
            const logPanel = document.getElementById('logPanel');
            const p = document.createElement('p');
            p.innerText = message;
            p.style.color = type === 'error' ? '#ff6b6b' :
                type === 'warning' ? '#feca57' : '#1dd1a1';
            logPanel.appendChild(p);
            logPanel.scrollTop = logPanel.scrollHeight;
        }

        // Create the GeoPackage 3D Tiles loader
        const gpkgLoader = Object.create(GeoPackage3DTilesLoader);
        GeoPackage3DTilesLoader.constructor(viewer);
        gpkgLoader.setLogger(log);

        // Initialize viewer with default view

        log('Initializing Cesium viewer');
        // neot hakikar havarim location
        viewer.camera.flyTo({
            destination: Cesium.Cartesian3.fromDegrees(35.373285, 30.917193, 5000.0),
            orientation: {
                heading: Cesium.Math.toRadians(0),
                pitch: Cesium.Math.toRadians(-70),
                roll: 0.0
            }
        });

        // Function to show messages to user
        function showMessage(text, duration = 3000) {
            const messageDiv = document.getElementById('message');
            messageDiv.innerText = text;
            messageDiv.style.display = 'block';

            if (duration > 0) {
                setTimeout(() => {
                    messageDiv.style.display = 'none';
                }, duration);
            }
            return messageDiv;
        }

        // Set up button event handlers
        document.getElementById('uploadGpkgBtn').addEventListener('click', function () {
            const input = document.createElement('input');
            input.type = 'file';
            input.accept = '.gpkg';

            input.onchange = async function (event) {
                const file = event.target.files[0];
                if (!file) return;

                log(`Processing GeoPackage: ${file.name}`);
                showMessage(`Processing ${file.name}...`, 0);

                try {
                    await gpkgLoader.loadGeoPackage(file);
                    showMessage('GeoPackage processed successfully!', 3000);
                } catch (error) {
                    log(`Error: ${error.message}`, 'error');
                    showMessage(`Error: ${error.message}`, 5000);
                }
            };

            input.click();
        });

        document.getElementById('loadSampleBtn').addEventListener('click', async function () {
            log('Loading sample 3D Tileset data');
            showMessage('Loading sample 3D Tiles...', 0);

            try {
                await gpkgLoader._createSampleTileset();
                showMessage('Sample 3D Tiles loaded successfully!', 3000);
            } catch (error) {
                log(`Error: ${error.message}`, 'error');
                showMessage(`Error: ${error.message}`, 5000);
            }
        });

        document.getElementById('clearTilesBtn').addEventListener('click', function () {
            gpkgLoader.removeAllTilesets();
            showMessage('All tilesets removed', 2000);
        });

        // Initial log message
        log('3D Tiles from GeoPackage POC initialized');
        log('Please upload a GeoPackage file or load sample data');
    </script>
</body>

</html>