org.jgrasstools.gears.io.geopaparazzi.geopap4.DaoGpsLog Maven / Gradle / Ivy

/*
 * This file is part of JGrasstools (http://www.jgrasstools.org)
 * (C) HydroloGIS - www.hydrologis.com
 *
 * JGrasstools is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see .
 */
package org.jgrasstools.gears.io.geopaparazzi.geopap4;

import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_GPSLOGS;
import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_GPSLOG_DATA;
import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_GPSLOG_PROPERTIES;
import static org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.TABLE_IMAGES;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.geotools.geometry.jts.ReferencedEnvelope;
import org.geotools.referencing.crs.DefaultGeographicCRS;
import org.jgrasstools.dbs.compat.IJGTConnection;
import org.jgrasstools.dbs.compat.IJGTResultSet;
import org.jgrasstools.dbs.compat.IJGTStatement;
import org.jgrasstools.gears.io.geopaparazzi.OmsGeopaparazziProject3To4Converter;
import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.GpsLogsDataTableFields;
import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.GpsLogsPropertiesTableFields;
import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.GpsLogsTableFields;
import org.jgrasstools.gears.io.geopaparazzi.geopap4.TableDescriptions.ImageTableFields;

/**
 * @author Andrea Antonello (www.hydrologis.com)
 */
@SuppressWarnings("nls")
public class DaoGpsLog {

    // private static SimpleDateFormat dateFormatter =
    // TimeUtilities.INSTANCE.TIME_FORMATTER_SQLITE_UTC;
    // private static SimpleDateFormat dateFormatterForLabelInLocalTime =
    // TimeUtilities.INSTANCE.TIMESTAMPFORMATTER_LOCAL;

    /**
     * Create log tables.
     *
     * @throws IOException if something goes wrong.
     */
    public static void createTables( Connection connection ) throws IOException, SQLException {

        /*
         * gps log table
         */
        StringBuilder sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOGS);
        sB.append(" (");
        sB.append(GpsLogsTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_STARTTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ENDTS.getFieldName()).append(" LONG NOT NULL,");
        sB.append(GpsLogsTableFields.COLUMN_LOG_LENGTHM.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_ISDIRTY.getFieldName()).append(" INTEGER NOT NULL, ");
        sB.append(GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName()).append(" TEXT NOT NULL ");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS = sB.toString();

        /*
         * gps log data table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" (");
        sB.append(GpsLogsDataTableFields.COLUMN_ID.getFieldName() + " INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_ALTIM.getFieldName()).append(" REAL NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName()).append(" DATE NOT NULL,");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName()).append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(" + GpsLogsTableFields.COLUMN_ID.getFieldName() + ") ON DELETE CASCADE");
        sB.append(");");
        String CREATE_TABLE_GPSLOG_DATA = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_id_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_ID = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_ts_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_TS = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_x_by_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_X_BY_Y = sB.toString();

        sB = new StringBuilder();
        sB.append("CREATE INDEX gpslog_logid_x_y_idx ON ");
        sB.append(TABLE_GPSLOG_DATA);
        sB.append(" ( ");
        sB.append(GpsLogsDataTableFields.COLUMN_LOGID.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName());
        sB.append(", ");
        sB.append(GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName());
        sB.append(" );");
        String CREATE_INDEX_GPSLOG_LOGID_X_Y = sB.toString();

        /*
         * properties table
         */
        sB = new StringBuilder();
        sB.append("CREATE TABLE ");
        sB.append(TABLE_GPSLOG_PROPERTIES);
        sB.append(" (");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_ID.getFieldName());
        sB.append(" INTEGER PRIMARY KEY, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName());
        sB.append(" INTEGER NOT NULL ");
        sB.append("CONSTRAINT " + GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName() + " REFERENCES ");
        sB.append(TABLE_GPSLOGS);
        sB.append("(");
        sB.append(GpsLogsTableFields.COLUMN_ID);
        sB.append(") ON DELETE CASCADE,");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_COLOR.getFieldName()).append(" TEXT NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_WIDTH.getFieldName()).append(" REAL NOT NULL, ");
        sB.append(GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_VISIBLE.getFieldName()).append(" INTEGER NOT NULL");
        sB.append(");");
        String CREATE_TABLE_GPSLOGS_PROPERTIES = sB.toString();

        try (Statement statement = connection.createStatement()) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.

            statement.executeUpdate(CREATE_TABLE_GPSLOGS);

            statement.executeUpdate(CREATE_TABLE_GPSLOG_DATA);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_ID);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_TS);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_X_BY_Y);
            statement.executeUpdate(CREATE_INDEX_GPSLOG_LOGID_X_Y);

            statement.executeUpdate(CREATE_TABLE_GPSLOGS_PROPERTIES);
        } catch (Exception e) {
            throw new IOException(e.getLocalizedMessage());
        }

    }

    public static void addGpsLog( Connection connection, OmsGeopaparazziProject3To4Converter.GpsLog log, float width,
            String color, boolean visible ) throws Exception {
        Date startTS = TimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.parse(log.startTime);
        Date endTS = TimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.parse(log.endTime);

        String insertSQL1 = "INSERT INTO " + TableDescriptions.TABLE_GPSLOGS + "(" + //
                TableDescriptions.GpsLogsTableFields.COLUMN_ID.getFieldName() + ", " + //
                TableDescriptions.GpsLogsTableFields.COLUMN_LOG_STARTTS.getFieldName() + ", " + //
                TableDescriptions.GpsLogsTableFields.COLUMN_LOG_ENDTS.getFieldName() + ", " + //
                TableDescriptions.GpsLogsTableFields.COLUMN_LOG_LENGTHM.getFieldName() + ", " + //
                TableDescriptions.GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName() + ", " + //
                TableDescriptions.GpsLogsTableFields.COLUMN_LOG_ISDIRTY.getFieldName() + //
                ") VALUES" + "(?,?,?,?,?,?)";
        try (PreparedStatement writeStatement = connection.prepareStatement(insertSQL1)) {
            writeStatement.setLong(1, log.id);
            writeStatement.setLong(2, startTS.getTime());
            writeStatement.setLong(3, endTS.getTime());
            writeStatement.setDouble(4, 0.0);
            writeStatement.setString(5, log.text);
            writeStatement.setInt(6, 1);

            writeStatement.executeUpdate();
        }

        String insertSQL2 = "INSERT INTO " + TableDescriptions.TABLE_GPSLOG_PROPERTIES + "(" + //
                GpsLogsPropertiesTableFields.COLUMN_ID.getFieldName() + ", " + //
                GpsLogsPropertiesTableFields.COLUMN_LOGID.getFieldName() + ", " + //
                GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_COLOR.getFieldName() + ", " + //
                GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_WIDTH.getFieldName() + ", " + //
                GpsLogsPropertiesTableFields.COLUMN_PROPERTIES_VISIBLE.getFieldName() + //
                ") VALUES" + "(?,?,?,?,?)";
        try (PreparedStatement writeStatement = connection.prepareStatement(insertSQL2)) {
            writeStatement.setLong(1, log.id);
            writeStatement.setLong(2, log.id);
            writeStatement.setString(3, color);
            writeStatement.setFloat(4, width);
            writeStatement.setInt(5, visible ? 1 : 0);

            writeStatement.executeUpdate();
        }

        for( OmsGeopaparazziProject3To4Converter.GpsPoint point : log.points ) {
            addGpsLogDataPoint(connection, point, log.id);
        }

    }

    /**
     * Adds a new XY entry to the gps table.
     *
     * @param connection the db connection.
     * @param point      the point to add.
     * @param gpslogId   the id of the log the point is part of.
     *
     * @throws IOException if something goes wrong
     */
    public static void addGpsLogDataPoint( Connection connection, OmsGeopaparazziProject3To4Converter.GpsPoint point,
            long gpslogId ) throws Exception {
        Date timestamp = TimeUtilities.INSTANCE.TIME_FORMATTER_LOCAL.parse(point.utctime);

        String insertSQL = "INSERT INTO " + TableDescriptions.TABLE_GPSLOG_DATA + "(" + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_ID.getFieldName() + ", " + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_LOGID.getFieldName() + ", " + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName() + ", " + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName() + ", " + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_DATA_ALTIM.getFieldName() + ", " + //
                TableDescriptions.GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName() + //
                ") VALUES" + "(?,?,?,?,?,?)";
        try (PreparedStatement writeStatement = connection.prepareStatement(insertSQL)) {
            writeStatement.setLong(1, point.id);
            writeStatement.setLong(2, gpslogId);
            writeStatement.setDouble(3, point.lon);
            writeStatement.setDouble(4, point.lat);
            writeStatement.setDouble(5, point.altim);
            writeStatement.setLong(6, timestamp.getTime());

            writeStatement.executeUpdate();
        }
    }

    /**
     * Get the list of available logs.
     * 
     * @param connection the connection to use.
     * @return the list of logs.
     * @throws SQLException
     */
    public static List getLogsList( IJGTConnection connection ) throws Exception {
        List logsList = new ArrayList<>();
        String sql = "select " + //
                GpsLogsTableFields.COLUMN_ID.getFieldName() + "," + //
                GpsLogsTableFields.COLUMN_LOG_STARTTS.getFieldName() + "," + //
                GpsLogsTableFields.COLUMN_LOG_ENDTS.getFieldName() + "," + //
                GpsLogsTableFields.COLUMN_LOG_TEXT.getFieldName() + //
                " from " + TABLE_GPSLOGS; //
        try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(sql);) {
            statement.setQueryTimeout(30); // set timeout to 30 sec.


            // first get the logs
            while( rs.next() ) {
                long id = rs.getLong(1);

                long startDateTimeString = rs.getLong(2);
                long endDateTimeString = rs.getLong(3);
                String text = rs.getString(4);

                GpsLog log = new GpsLog();
                log.id = id;
                log.startTime = startDateTimeString;
                log.endTime = endDateTimeString;
                log.text = text;
                logsList.add(log);
            }
        }
        return logsList;
    }

    /**
     * Gather gps points data for a supplied log.
     * 
     * @param connection the connection to use.
     * @param log the log.
     * @throws Exception 
     */
    public static void collectDataForLog( IJGTConnection connection, GpsLog log ) throws Exception {
        long logId = log.id;

        String query = "select "
                + //
                GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName() + ","
                + //
                GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName() + ","
                + //
                GpsLogsDataTableFields.COLUMN_DATA_ALTIM.getFieldName() + ","
                + //
                GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName()
                + //
                " from " + TABLE_GPSLOG_DATA + " where "
                + //
                GpsLogsDataTableFields.COLUMN_LOGID.getFieldName() + " = " + logId + " order by "
                + GpsLogsDataTableFields.COLUMN_DATA_TS.getFieldName();

        try (IJGTStatement newStatement = connection.createStatement(); IJGTResultSet result = newStatement.executeQuery(query);) {
            newStatement.setQueryTimeout(30);

            while( result.next() ) {
                double lat = result.getDouble(1);
                double lon = result.getDouble(2);
                double altim = result.getDouble(3);
                long ts = result.getLong(4);

                GpsPoint gPoint = new GpsPoint();
                gPoint.lon = lon;
                gPoint.lat = lat;
                gPoint.altim = altim;
                gPoint.utctime = ts;
                log.points.add(gPoint);
            }
        }
    }

    /**
     * A class representing a gps point.
     */
    public static class GpsPoint {
        public double lat;
        public double lon;
        public double altim;
        public long utctime;
    }

    /**
     * A gps log with an empty holder for gps data.
     */
    public static class GpsLog {
        public long id;
        public long startTime;
        public long endTime;
        public String text;
        public List points = new ArrayList<>();
        
        @Override
        public String toString() {
            return text;
        }
    }
    
    
    /**
     * Get the current data envelope.
     * 
     * @param connection the db connection.
     * @return the envelope.
     * @throws Exception
     */
    public static ReferencedEnvelope getEnvelope( IJGTConnection connection ) throws Exception {
        String query = "SELECT min(" + //
                GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName() + "), max(" + //
                GpsLogsDataTableFields.COLUMN_DATA_LON.getFieldName() + "), min(" + //
                GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName() + "), max(" + //
                GpsLogsDataTableFields.COLUMN_DATA_LAT.getFieldName() + ") " + //
                " FROM " + TABLE_GPSLOG_DATA;
        try (IJGTStatement statement = connection.createStatement(); IJGTResultSet rs = statement.executeQuery(query);) {
            if (rs.next()) {
                double minX = rs.getDouble(1);
                double maxX = rs.getDouble(2);
                double minY = rs.getDouble(3);
                double maxY = rs.getDouble(4);

                ReferencedEnvelope env = new ReferencedEnvelope(minX, maxX, minY, maxY, DefaultGeographicCRS.WGS84);
                return env;
            }
        }

        return null;
    }
}