SASGIS

CREATE TABLE grid_key (

    grid_id TEXT,

    key_name TEXT

);

CREATE UNIQUE INDEX grid_key_lookup ON grid_key (grid_id, key_name);



CREATE TABLE grid_utfgrid (

    grid_id TEXT,

    grid_utfgrid BLOB

);

CREATE UNIQUE INDEX grid_utfgrid_lookup ON grid_utfgrid (grid_id);



CREATE TABLE images (

    tile_data blob,

    tile_id text

);

CREATE UNIQUE INDEX images_id ON images (tile_id);



CREATE TABLE keymap (

    key_name TEXT,

    key_json TEXT

);

CREATE UNIQUE INDEX keymap_lookup ON keymap (key_name);



CREATE TABLE map (

   zoom_level INTEGER,

   tile_column INTEGER,

   tile_row INTEGER,

   tile_id TEXT,

   grid_id TEXT

);

CREATE UNIQUE INDEX map_index ON map (zoom_level, tile_column, tile_row);



CREATE TABLE metadata (

    name text,

    value text

);

CREATE UNIQUE INDEX name ON metadata (name);

CREATE VIEW grid_data AS

    SELECT

        map.zoom_level AS zoom_level,

        map.tile_column AS tile_column,

        map.tile_row AS tile_row,

        keymap.key_name AS key_name,

        keymap.key_json AS key_json

    FROM map

    JOIN grid_key ON map.grid_id = grid_key.grid_id

    JOIN keymap ON grid_key.key_name = keymap.key_name;



CREATE VIEW grids AS

    SELECT

        map.zoom_level AS zoom_level,

        map.tile_column AS tile_column,

        map.tile_row AS tile_row,

        grid_utfgrid.grid_utfgrid AS grid

    FROM map

    JOIN grid_utfgrid ON grid_utfgrid.grid_id = map.grid_id;



CREATE VIEW tiles AS

    SELECT

        map.zoom_level AS zoom_level,

        map.tile_column AS tile_column,

        map.tile_row AS tile_row,

        images.tile_data AS tile_data

    FROM map

    JOIN images ON images.tile_id = map.tile_id;