Application: gvSIG desktop

/*

 * To change this license header, choose License Headers in Project Properties.

 * To change this template file, choose Tools | Templates

 * and open the template in the editor.

 */

package org.gvsig.sqlite.dal.geopackage;

import java.nio.charset.StandardCharsets;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.Level;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.commons.io.IOUtils;

import org.apache.commons.lang3.math.NumberUtils;

import org.cresques.cts.IProjection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection;

import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils;

import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase;

import org.gvsig.sqlite.dal.SQLiteHelper;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

/**

 *

 * @author fdiaz

 */

public class GeopackageSpatialRefSys {

    final static protected Logger LOGGER = LoggerFactory.getLogger(GeopackageSpatialRefSys.class);

//    private static JsonArray spatial_ref_sys = null;

    private String srs_name;

    private int srs_id;

    private String organization;

    private int organization_coordsys_id;

    private String definition;

    private String description;

    public GeopackageSpatialRefSys(IProjection projection) {

        if (!search(projection)) {

            if (projection.isProjected()) {

                definition = "undefined";

                description = "undefined cartesian coordinate reference system";

                organization = "NONE";

                organization_coordsys_id = -1;

                srs_id = -1;

                srs_name = "Undefined cartesian SRS";

            } else {

                definition = "undefined";

                description = "undefined geographic coordinate reference system";

                organization = "NONE";

                organization_coordsys_id = 0;

                srs_id = 0;

                srs_name = "Undefined geographic SRS";

            }

        }

    }

//    private boolean search_json(IProjection projection) {

//        if(spatial_ref_sys == null) {

//            InputStream res = GeopackageSpatialRefSys.class.getResourceAsStream("....gpkg.....");

//            spatial_ref_sys = Json.createArray(res);

//        }

//        String projFullCode = projection.getFullCode();

//        for (JsonValue item : spatial_ref_sys) {

//            String org = ((JsonObject)item).getString("organization", null);

//            int srsId = ((JsonObject)item).getInt("organization_coordsys_id", Integer.MIN_VALUE);

//            if(org != null && srsId != Integer.MIN_VALUE && StringUtils.startsWith(projFullCode, org+":"+srsId)){

//                setOrganization(org);

//                setOrganizationCoordsysId(srsId);

//                setSrsName(((JsonObject)item).getString("srs_name", null));

//                setDefinition(((JsonObject)item).getString("definition", null));

//                setDescription(((JsonObject)item).getString("description", null));

//            }

//            return true;

//        }

//        return false;

//    }

    private boolean search(IProjection projection) {

        String abrev = "UNKNOWN";

        try {

            abrev = projection.getAbrev();

            String[] ss = abrev.split(":");

            String authority = ss[0];

            int authorityCode = NumberUtils.toInt(ss[1], Integer.MIN_VALUE);

            if (authorityCode == Integer.MIN_VALUE) {

                return false;

            }

            String resourceName = "/org/gvsig/sqlite/dal/epsg_crs_wkt/" + authority + "-CRS-" + authorityCode + ".wkt";

            String wkt = IOUtils.resourceToString(resourceName, StandardCharsets.UTF_8);

            Pattern pattern = Pattern.compile("[A-Z]+\\[\\s*\"(?<srsname>[^\"]*)\".*");

            Matcher matcher = pattern.matcher(wkt);

            if (matcher != null && matcher.matches()) {

                setSrsName(matcher.group("srsname"));

            } else {

                setSrsName(abrev);

            }

            setDefinition(wkt);

            setOrganization(authority);

            setOrganizationCoordsysId(authorityCode);

            setSrsId(authorityCode);

            setDescription("");

            return true;

        } catch (Exception ex) {

            LOGGER.debug("Can't search projection '" + abrev + "'", ex);

            return false;

        }

    }

    public void setSrsName(String srs_name) {

        this.srs_name = srs_name;

    }

    public String getSrsName() {

        return srs_name;

    }

    public void setSrsId(int srs_id) {

        this.srs_id = srs_id;

    }

    public int getSrsId() {

        return srs_id;

    }

    public void setOrganization(String organization) {

        this.organization = organization;

    }

    public String getOrganization() {

        return organization;

    }

    public void setOrganizationCoordsysId(int organization_coordsys_id) {

        this.organization_coordsys_id = organization_coordsys_id;

    }

    public int getOrganizationCoordsysId() {

        return organization_coordsys_id;

    }

    public void setDefinition(String definition) {

        this.definition = definition;

    }

    public String getDefinition() {

        return definition;

    }

    public void setDescription(String description) {

        this.description = description;

    }

    public String getDescription() {

        return description;

    }

    public boolean exists(JDBCConnection conn) {

        Statement sta = null;

        ResultSet rs = null;

        try {

            sta = conn.createStatement();

            String sql = String.format(

                    "Select * from gpkg_spatial_ref_sys where organization = '%s' and organization_coordsys_id = %d",

                    getOrganization(),

                    getOrganizationCoordsysId()

            );

            rs = JDBCUtils.executeQuery(sta, sql);

            return rs.next();

        } catch (Exception ex) {

            LOGGER.debug("Can't verify if exists the spatial_ref_sys '" + getOrganization() + ":" + getOrganizationCoordsysId() + "' in BD.", ex);

        } finally {

            JDBCUtils.closeQuietly(rs);

            JDBCUtils.closeQuietly(sta);

        }

        return false;

    }

    public void save(SQLiteHelper helper) {

        JDBCConnection conn = null;

        ResultSet tables = null;

        Statement sta = null;

        try {

            conn = helper.getConnection();

            conn.begin();

            sta = conn.createStatement();

            JDBCSQLBuilderBase sqlBuilder = helper.createSQLBuilder();

            StringBuilder builder = new StringBuilder();

            if (!exists(conn)) {

                tables = conn.getMetaData().getTables(null, null, "gpkg_spatial_ref_sys", null);

                if (!tables.next()) {

                    createGpkgSpatialRefSysTable(conn);

                }

                builder.append("INSERT INTO gpkg_spatial_ref_sys (srs_name, srs_id, organization, organization_coordsys_id, definition, description) VALUES (");

                builder.append(sqlBuilder.as_string(getSrsName()));

                builder.append(",");

                builder.append(getSrsId());

                builder.append(",");

                builder.append(sqlBuilder.as_string(getOrganization()));

                builder.append(",'");

                builder.append(getOrganizationCoordsysId());

                builder.append("',");

                builder.append(sqlBuilder.as_string(getDefinition()));

                builder.append(",");

                builder.append(sqlBuilder.as_string(getDescription()));

                builder.append(")");

                JDBCUtils.execute(sta, builder.toString());

//            } else {

//                builder.append("UPDATE gpkg_spatial_ref_sys SET ");

//                builder.append("srs_name = ");

//                builder.append(sqlBuilder.as_string(getSrsName()));

//                builder.append(", srs_id = ");

//                builder.append(getSrsId());

//                builder.append(", definition = '");

//                builder.append(sqlBuilder.as_string(getDefinition()));

//                builder.append(", description = ");

//                builder.append(sqlBuilder.as_string(getDescription()));

//                builder.append(" WHERE organization = ");

//                builder.append(sqlBuilder.as_string(getOrganization()));

//                builder.append(" and organization_coordsys_id = ");

//                builder.append(getOrganizationCoordsysId());

//                builder.append(";");

//                JDBCUtils.executeUpdate(sta, builder.toString());

            }

        } catch (Exception ex2) {

            throw new GeopackageException(

                    "Can't save the spatial_ref_sys '" + getOrganization() + ":" + getOrganizationCoordsysId() + "' in BD.", ex2);

        } finally {

            if(conn != null) {

                try {

                    conn.commit();

                } catch (SQLException ex) {

                    throw new GeopackageException(

                    "Can't save the spatial_ref_sys '" + getOrganization() + ":" + getOrganizationCoordsysId() + "' in BD.", ex);

                }

            }

            JDBCUtils.closeQuietly(sta);

            JDBCUtils.closeQuietly(tables);

            JDBCUtils.closeQuietly(sta);

        }

    }

    public boolean load(JDBCConnection conn) throws SQLException {

        Statement sta = null;

        ResultSet rs = null;

        try {

            sta = conn.createStatement();

            String sql = String.format(

                    "Select * from gpkg_spatial_ref_sys where organization = '%s' and organization_coordsys_id = %d",

                    getOrganization(),

                    getOrganizationCoordsysId()

            );

            rs = sta.executeQuery(sql);

            if (rs.next()) {

                this.setSrsName(rs.getString("srs_name"));

                this.setSrsId(rs.getInt("srs_id"));

                this.setDefinition(rs.getString("definition"));

                this.setDescription(rs.getString("description"));

                return true;

            } else {

                return false;

            }

        } catch (SQLException ex) {

            LOGGER.warn("Can't load exists the spatial_ref_sys '" + getOrganization() + ":" + getOrganizationCoordsysId() + "' in BD.", ex);

        } finally {

            if (rs != null) {

                rs.close();

            }

            if (sta != null) {

                sta.close();

            }

        }

        return false;

    }

    public static boolean createGpkgSpatialRefSysTable(JDBCConnection conn) {

        try (Statement sta = conn.createStatement()) {

            String sql = "CREATE TABLE \"gpkg_spatial_ref_sys\" (\n"

                    + "        \"srs_name\"        TEXT NOT NULL,\n"

                    + "        \"srs_id\"        INTEGER NOT NULL,\n"

                    + "        \"organization\"        TEXT NOT NULL,\n"

                    + "        \"organization_coordsys_id\"        INTEGER NOT NULL,\n"

                    + "        \"definition\"        TEXT NOT NULL,\n"

                    + "        \"description\"        TEXT,\n"

                    + "        PRIMARY KEY(\"srs_id\")\n"

                    + ");";

            JDBCUtils.execute(sta, sql);

            return true;

        } catch (SQLException ex) {

            LOGGER.warn("Can't create spatial_ref_sys table'", ex);

        }

        return false;

    }

}