merge_regions

#!/bin/bash -x
# Combine vector data in mbtiles format

# Combines all tiles from current directory into target/, replacing duplicates
#     via a UNIQUE constraint in table index for map on [zoom,x,y].
# A paramater must be given, to use as base (inherit the metadata from base).

#SCRIPTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" >/dev/null && pwd )"

CWD=$(pwd)
SOURCEDIR=$CWD
case $1 in
   "-s" | "--structure")
      structure_flag="true"
      shift 1
      ;;
esac
case $1 in
   "-t" | "--test")
      response=$(sqlite3 $2|grep map | grep -i index)
      echo $response
      exit 1
      ;;
esac

# The function of this script that actually does what's wanted
append_data(){
   echo "adding $append_this to $DEST"
   echo "
   PRAGMA journal_mode=PERSIST;
   PRAGMA page_size=80000;
   PRAGMA synchronous=OFF;
   ATTACH DATABASE '$append_this' AS source;
   REPLACE INTO map (zoom_level,tile_column,tile_row,tile_id) \
      SELECT zoom_level,tile_column,tile_row,tile_id FROM source.map;
   REPLACE INTO images (tile_id,tile_data) SELECT tile_id,tile_data FROM source.images;"\
   | sqlite3 $DEST
}

# Create structure that includes the UNIQUE specifier that prevents bloat
# Not used in this script, but preserved in case it is needed
create_structure(){
# create the empty database
   echo ".q" | sqlite3 $DEST
   # provide the structure
   echo "
   CREATE TABLE images (tile_id TEXT, tile_data BLOB);
   CREATE TABLE map (zoom_level INTEGER,tile_column INTEGER,tile_row INTEGER,tile_id TEXT,grid_id TEXT);
   CREATE TABLE metadata (name TEXT, value TEXT);
   CREATE TABLE 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);
   CREATE TABLE 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', CURRENT_TIMESTAMP)),
       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));
   CREATE TABLE gpkg_geometry_columns (
       table_name TEXT NOT NULL, column_name TEXT NOT NULL, geometry_type_name TEXT NOT NULL,
       srs_id INTEGER NOT NULL, z TINYINT NOT NULL, m TINYINT NOT NULL,
       CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
       CONSTRAINT uk_gc_table_name UNIQUE (table_name),
       CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
       CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
   CREATE TABLE gpkg_tile_matrix_set (
       table_name TEXT NOT NULL PRIMARY KEY, srs_id INTEGER NOT NULL,
       min_x DOUBLE NOT NULL, min_y DOUBLE NOT NULL, max_x DOUBLE NOT NULL, max_y DOUBLE NOT NULL,
       CONSTRAINT fk_gtms_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),
       CONSTRAINT fk_gtms_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
   CREATE TABLE gpkg_tile_matrix (
       table_name TEXT NOT NULL, zoom_level INTEGER NOT NULL,
       matrix_width INTEGER NOT NULL, matrix_height INTEGER NOT NULL,
       tile_width INTEGER NOT NULL, tile_height INTEGER NOT NULL,
       pixel_x_size DOUBLE NOT NULL, pixel_y_size DOUBLE NOT NULL,
       CONSTRAINT pk_ttm PRIMARY KEY (table_name, zoom_level),
       CONSTRAINT fk_tmm_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name));
   CREATE TABLE gpkg_metadata (
       id INTEGER CONSTRAINT m_pk PRIMARY KEY ASC NOT NULL UNIQUE,
       md_scope TEXT NOT NULL DEFAULT 'dataset', md_standard_uri TEXT NOT NULL,
       mime_type TEXT NOT NULL DEFAULT 'text/xml',metadata TEXT NOT NULL);
   CREATE TABLE gpkg_metadata_reference (
       reference_scope TEXT NOT NULL,table_name TEXT,column_name TEXT,row_id_value INTEGER,
       timestamp DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ',CURRENT_TIMESTAMP)),
       md_file_id INTEGER NOT NULL,md_parent_id INTEGER,
       CONSTRAINT crmr_mfi_fk FOREIGN KEY (md_file_id) REFERENCES gpkg_metadata(id),
       CONSTRAINT crmr_mpi_fk FOREIGN KEY (md_parent_id) REFERENCES gpkg_metadata(id));
   CREATE UNIQUE INDEX map_index ON map (zoom_level,tile_column,tile_row);
   CREATE UNIQUE INDEX images_index ON images (tile_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 map.tile_id = images.tile_id;
   CREATE VIEW package_tiles AS
     SELECT map.rowid as id,
       map.zoom_level as zoom_level,
       map.tile_column as tile_column,
       ((1 << map.zoom_level) - map.tile_row - 1) as tile_row,
       images.tile_data as tile_data
     FROM map JOIN images ON map.tile_id = images.tile_id;
   CREATE TABLE omtm (name TEXT, value TEXT);"| \
   sqlite3 $DEST
}

DEST=$1

# if region not specified as base, it's an error`
if [ $# -ne 1 ] ;then
   SOURCE=$SOURCEDIR/$1 
else 
   SOURCE=`ls $SOURCEDIR/*.mbtiles 2>/dev/null | head -n1`
fi
if [ "$structure_flag" == "true" ];then
   create_structure
   exit 1
fi

for f in `ls *.mbtiles`;do
   if [ "$1" == $f ] && [ "$structure_flag" != "true" ];then
      echo skipping $f
      continue
   fi
   append_this=$f
   append_data $append_this
done

# the vacuum command will collapse out any garbage space
echo "vacuuming $DEST"
sqlite3 $DEST "vacuum;"