org.jgrasstools.gears.modules.r.tmsgenerator.MBTilesHelper Maven / Gradle / Ivy

package org.jgrasstools.gears.modules.r.tmsgenerator;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.imageio.ImageIO;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;

import org.geotools.coverage.grid.io.AbstractGridCoverage2DReader;
import org.geotools.coverage.grid.io.AbstractGridFormat;
import org.geotools.coverage.grid.io.GridFormatFinder;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.jgrasstools.gears.libs.exceptions.ModelsRuntimeException;
import org.jgrasstools.gears.utils.images.ImageUtilities;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;
import org.opengis.referencing.operation.TransformException;

public class MBTilesHelper implements AutoCloseable {

    static {
        try {
            // make sure sqlite drivers are there
            Class.forName("org.sqlite.JDBC");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * We have a fixed tile size.
     */
    public final static int TILESIZE = 256;

    // TABLE tiles (zoom_level INTEGER, tile_column INTEGER, tile_row INTEGER, tile_data BLOB);
    public final static String TABLE_TILES = "tiles";
    public final static String COL_TILES_ZOOM_LEVEL = "zoom_level";
    public final static String COL_TILES_TILE_COLUMN = "tile_column";
    public final static String COL_TILES_TILE_ROW = "tile_row";
    public final static String COL_TILES_TILE_DATA = "tile_data";

    public final static String SELECTQUERY = "SELECT " + COL_TILES_TILE_DATA + " from " + TABLE_TILES + " where "
        + COL_TILES_ZOOM_LEVEL + "=? AND " + COL_TILES_TILE_COLUMN + "=? AND " + COL_TILES_TILE_ROW + "=?";

    private final static String CREATE_TILES = //
        "CREATE TABLE " + TABLE_TILES + "( " + //
            COL_TILES_ZOOM_LEVEL + " INTEGER, " + //
            COL_TILES_TILE_COLUMN + " INTEGER, " + //
            COL_TILES_TILE_ROW + " INTEGER, " + //
            COL_TILES_TILE_DATA + " BLOB" + //
            ")";

    // TABLE METADATA (name TEXT, value TEXT);
    public final static String TABLE_METADATA = "metadata";
    public final static String COL_METADATA_NAME = "name";
    public final static String COL_METADATA_VALUE = "value";

    private final static String CREATE_METADATA = //
        "CREATE TABLE " + TABLE_METADATA + "( " + //
            COL_METADATA_NAME + " TEXT, " + //
            COL_METADATA_VALUE + " TEXT " + //
            ")";

    private final static String SELECT_BOUNDS = //
        "select " + COL_METADATA_VALUE + " from " + TABLE_METADATA + " where " + COL_METADATA_NAME + "='bounds'";

    private final static String SELECT_IMAGEFORMAT = //
        "select " + COL_METADATA_VALUE + " from " + TABLE_METADATA + " where " + COL_METADATA_NAME + "='format'";

    // INDEXES on Metadata and Tiles tables
    private final static String INDEX_TILES = "CREATE UNIQUE INDEX tile_index ON " + TABLE_TILES + " ("
        + COL_TILES_ZOOM_LEVEL + ", " + COL_TILES_TILE_COLUMN + ", " + COL_TILES_TILE_ROW + ")";
    private final static String INDEX_METADATA =
        "CREATE UNIQUE INDEX name ON " + TABLE_METADATA + "( " + COL_METADATA_NAME + ")";

    private Connection connection;

    private volatile int addedTiles = 0;

    private String imageFormat;

    public void open(File dbFile) throws SQLException {
        // create a database connection
        connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile.getAbsolutePath());
    }

    public void close() {
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            // connection close failed.
            throw new ModelsRuntimeException("An error occurred while closing the database connection.", this);
        }
    }

    public void createTables(boolean makeIndexes) throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.addBatch("DROP TABLE IF EXISTS " + TABLE_TILES);
            statement.addBatch("DROP TABLE IF EXISTS " + TABLE_METADATA);
            statement.addBatch(CREATE_TILES);
            statement.addBatch(CREATE_METADATA);
            if (makeIndexes) {
                statement.addBatch(INDEX_TILES);
                statement.addBatch(INDEX_METADATA);
            }
            statement.executeBatch();
        }
        connection.setAutoCommit(false);
    }

    public void createIndexes() throws SQLException {
        try (Statement statement = connection.createStatement()) {
            statement.addBatch(INDEX_TILES);
            statement.addBatch(INDEX_METADATA);
            statement.executeBatch();
        }
        connection.commit();
    }

    public void fillMetadata(float n, float s, float w, float e, String name, String format, int minZoom, int maxZoom)
        throws SQLException {
        // type = baselayer
        // version = 1.1
        // descritpion = name
        try (Statement statement = connection.createStatement()) {
            String query = toMetadataQuery("name", name);
            statement.addBatch(query);
            query = toMetadataQuery("description", name);
            statement.addBatch(query);
            query = toMetadataQuery("format", format);
            statement.addBatch(query);
            query = toMetadataQuery("minZoom", minZoom + "");
            statement.addBatch(query);
            query = toMetadataQuery("maxZoom", maxZoom + "");
            statement.addBatch(query);
            query = toMetadataQuery("type", "baselayer");
            statement.addBatch(query);
            query = toMetadataQuery("version", "1.1");
            statement.addBatch(query);
            // left, bottom, right, top
            query = toMetadataQuery("bounds", w + "," + s + "," + e + "," + n);
            statement.addBatch(query);

            statement.executeBatch();
        }

        connection.commit();

    }

    private String toMetadataQuery(String key, String value) {
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO " + TABLE_METADATA + " ");
        sb.append("(");
        sb.append(COL_METADATA_NAME);
        sb.append(",");
        sb.append(COL_METADATA_VALUE);
        sb.append(") values ('");
        sb.append(key);
        sb.append("','");
        sb.append(value);
        sb.append("')");
        String query = sb.toString();
        return query;
    }

    public synchronized void addTile(int x, int y, int z, BufferedImage image, String format) throws Exception {
        addedTiles++;

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ImageIO.write(image, format, baos);
        byte[] res = baos.toByteArray();

        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO " + TABLE_TILES + " ");
        sb.append("(");
        sb.append(COL_TILES_ZOOM_LEVEL);
        sb.append(",");
        sb.append(COL_TILES_TILE_COLUMN);
        sb.append(",");
        sb.append(COL_TILES_TILE_ROW);
        sb.append(",");
        sb.append(COL_TILES_TILE_DATA);
        sb.append(") values (");
        sb.append(z);
        sb.append(",");
        sb.append(x);
        sb.append(",");
        sb.append(y);
        sb.append(",");
        sb.append("?");
        sb.append(")");
        String query = sb.toString();

        try (PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setBytes(1, res);
            statement.execute();
        }

        if (addedTiles % 20 == 0) {
            connection.commit();
        }
    }

    /**
     * Get a Tile image from the database.
     * 
     * @param x
     * @param y
     * @param z
     * @return
     * @throws Exception
     */
    public BufferedImage getTile(int x, int y, int z) throws Exception {
        try (PreparedStatement statement = connection.prepareStatement(SELECTQUERY)) {
            statement.setInt(1, z);
            statement.setInt(2, x);
            statement.setInt(3, y);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                byte[] imageBytes = resultSet.getBytes(1);
                boolean orig = ImageIO.getUseCache();
                ImageIO.setUseCache(false);
                InputStream in = new ByteArrayInputStream(imageBytes);
                BufferedImage bufferedImage = ImageIO.read(in);
                ImageIO.setUseCache(orig);
                return bufferedImage;
            }
        }
        return null;
    }

    /**
     * Get the bounds as [w,s,e,n]
     * 
     * @return
     * @throws Exception
     */
    public double[] getBounds() throws Exception {
        try (Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery(SELECT_BOUNDS);
            if (resultSet.next()) {
                String boundsWSEN = resultSet.getString(1);
                String[] split = boundsWSEN.split(",");
                double[] bounds = { Double.parseDouble(split[0]), Double.parseDouble(split[1]),
                    Double.parseDouble(split[2]), Double.parseDouble(split[3]) };

                return bounds;
            }
        }
        return null;
    }

    /**
     * @return the image format (jpg, png).
     * @throws Exception
     */
    public String getImageFormat() throws Exception {
        if (imageFormat == null) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(SELECT_IMAGEFORMAT);
                if (resultSet.next()) {
                    imageFormat = resultSet.getString(1);
                }
            }
        }
        return imageFormat;
    }

    /**
     * Read the image of a tile from a generic geotools coverage reader.
     * 
     * @param reader the reader, expected to be in CRS 3857.
     * @param x the tile x.
     * @param y the tile y.
     * @param zoom the zoomlevel.
     * @return the image.
     * @throws IOException 
     */
    public static BufferedImage readGridcoverageImageForTile(AbstractGridCoverage2DReader reader, int x, int y,
        int zoom, CoordinateReferenceSystem resampleCrs) throws IOException {
        double north = tile2lat(y, zoom);
        double south = tile2lat(y + 1, zoom);
        double west = tile2lon(x, zoom);
        double east = tile2lon(x + 1, zoom);

        Coordinate ll = new Coordinate(west, south);
        Coordinate ur = new Coordinate(east, north);

        try {
            CoordinateReferenceSystem sourceCRS = DefaultGeographicCRS.WGS84;

            MathTransform transform = CRS.findMathTransform(sourceCRS, resampleCrs);
            ll = JTS.transform(ll, null, transform);
            ur = JTS.transform(ur, null, transform);
        } catch (Exception e) {
            e.printStackTrace();
        }

        BufferedImage image =
            ImageUtilities.imageFromReader(reader, TILESIZE, TILESIZE, ll.x, ur.x, ll.y, ur.y, resampleCrs);
        return image;
    }

    public static double tile2lon(int x, int z) {
        return x / Math.pow(2.0, z) * 360.0 - 180.0;
    }

    public static double tile2lat(int y, int z) {
        double n = Math.PI - (2.0 * Math.PI * y) / Math.pow(2.0, z);
        return Math.toDegrees(Math.atan(Math.sinh(n)));
    }

    public static void main(String[] args) throws IOException {
        String ctp = "/home/hydrologis/data/CTP/trentino_ctp/ctp.shp";
        File file = new File(ctp);
        AbstractGridFormat format = GridFormatFinder.findFormat(file);
        AbstractGridCoverage2DReader reader = format.getReader(file);
        BufferedImage image = MBTilesHelper.readGridcoverageImageForTile(reader, 1, 1, 19, null);

    }

}