Application: gvSIG desktop

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2020 gvSIG Association.

 *

 * This program 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, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.fmap.dal.store.jdbc2.spi.operations;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.apache.commons.lang3.StringUtils;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.ExpressionBuilder.Variable;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.dal.SQLBuilder;

import org.gvsig.fmap.dal.exception.DataException;

import org.gvsig.fmap.dal.feature.FeatureType;

import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException;

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference;

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

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

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryUtils;

import org.gvsig.fmap.geom.primitive.Envelope;

public class CalculateEnvelopeOfColumnOperation extends AbstractConnectionOperation {

    protected final TableReference table;

    protected final String columnName;

    protected final String baseFilter;

    protected final Envelope limit;

    protected final IProjection crs;

    protected final FeatureType featureType;

    public CalculateEnvelopeOfColumnOperation(

            JDBCHelper helper,

            FeatureType featureType,

            TableReference table,

            String columnName,

            String baseFilter,

            Envelope limit,

            IProjection crs

    ) {

        super(helper);

        this.featureType = featureType;

        this.table = table;

        this.columnName = columnName;

        this.baseFilter = baseFilter;

        this.limit = limit;

        this.crs = crs;

    }

    @Override

    public final Object perform(JDBCConnection conn) throws DataException {

        Envelope env = calculateEnvelopeOfColumn(conn);

        return env;

    }

    public String getSQL() {

        if (this.helper.hasSpatialFunctions()) {

            return getSQLWithAggregate();

        } else {

            return getSQLWihoutAggregate();

        }

    }

    public String getSQLWihoutAggregate() {

        //Crear un select que devuelve todas las lineas

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        sqlbuilder.select().from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        sqlbuilder.select().column().name(columnName).as_geometry();

        sqlbuilder.select().from().subquery(this.table.getSubquery());

        if (!StringUtils.isEmpty(baseFilter)) {

            sqlbuilder.select().where().set(expbuilder.custom(baseFilter));

        }

        // todo ?

        sqlbuilder.select().where().and(expbuilder.not_is_null(expbuilder.column(columnName)));

        String sql = sqlbuilder.select().toString();

        return sql;

    }

    public String getSQLWithAggregate() {

        //

        // Parece ser que en versiones anteriores a SQL Server 2012 no esta

        // disponible la funcion ST_ExtentAggregate.

        // Habria que determinar si es necesario implementar una alternativa

        // para estos casos.

        //

        // https://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/

        //

        JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();

        GeometryExpressionBuilder expbuilder = sqlbuilder.expression();

        SQLBuilder.SelectBuilder select = sqlbuilder.select();

        select.from().table()

                .database(this.table.getDatabase())

                .schema(this.table.getSchema())

                .name(this.table.getTable());

        select.column().value(

                expbuilder.as_geometry(

                        expbuilder.ST_ExtentAggregate(

                                sqlbuilder.column(select.from().table(),columnName)

                        )

                )

        );

        select.from().subquery(this.table.getSubquery());

        if (StringUtils.isEmpty(baseFilter)) {

            if (limit != null) {

                select.where().set(

                        expbuilder.ST_Intersects(

                                expbuilder.ST_Envelope(

                                        sqlbuilder.column(select.from().table(),columnName)

                                ),

                                expbuilder.ST_Envelope(

                                        expbuilder.geometry(limit.getGeometry(), crs)

                                )

                        )

                );

            }

        } else {

            select.where().set(expbuilder.custom(baseFilter));

            if (limit != null) {

                select.where().and(

                        expbuilder.ST_Intersects(

                                expbuilder.ST_Envelope(

                                        sqlbuilder.column(select.from().table(),columnName)

                                ),

                                expbuilder.ST_Envelope(

                                        expbuilder.geometry(limit.getGeometry(), crs)

                                )

                        )

                );

            }

        }

        select.where().and(

                expbuilder.not_is_null(sqlbuilder.column(select.from().table(),columnName))

        );

        this.helper.processSpecialFunctions(sqlbuilder, featureType, null, null);

        sqlbuilder.setProperties(

                Variable.class,

                PROP_TABLE, table

        );

        String sql = sqlbuilder.select().toString();

        return sql;

    }

    public Envelope calculateEnvelopeOfColumn(JDBCConnection conn) throws DataException {

        if (this.helper.hasSpatialFunctions()) {

            return calculateEnvelopeOfColumnWithAggregate(conn);

        } else {

            return calculateEnvelopeOfColumnWithoutAggregate(conn);

        }

    }

    protected Envelope calculateEnvelopeOfColumnWithAggregate(JDBCConnection conn) throws DataException {

        String sql = this.getSQL();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement(sql);

            try {

                LOGGER.debug("execute query SQL:" + sql);

                rs = st.executeQuery(sql);

                // No llamo al de JDBC utils para que no meta el error

                // en el log.

                // rs = JDBCUtils.executeQuery(st, sql);

            } catch (SQLException ex) {

//                Para calcular el envelope se esta lanzando una consulta como:

//                   SELECT ST_AsBinary(ST_Extent("geometry")) 

//                   FROM "gis_osm_roads_free_1" 

//                   WHERE ( ("geometry") IS NOT NULL )

//                Que falla cuando no hay registros al recibir un NULL en el 

//                ST_AsBinary. 

//                A falta de una forma mejor de detectar eso, si peta en este

//                punto asumiremos que no hay registros en la consulta y devolveremos

//                null como envelope.

//                A?ado un warn al LOGGER para tener constancia de lo que est? pasando.

                LOGGER.warn("Fail executing sql: "+sql, ex);

                return null;

            }

            if (!rs.next()) {

                return null;

            }

            Geometry geom = this.helper.getGeometryFromColumn(rs, 1);

            if (geom == null) {

                return null;

            }

            return geom.getEnvelope();

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex,sql);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

    protected Envelope calculateEnvelopeOfColumnWithoutAggregate(JDBCConnection conn) throws DataException {

        String sql = this.getSQL();

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement(sql);

            try {

                LOGGER.debug("execute query SQL:" + sql);

                rs = st.executeQuery(sql);

                // No llamo al de JDBC utils para que no meta el error

                // en el log.

                // rs = JDBCUtils.executeQuery(st, sql);

            } catch (SQLException ex) {

//                Para calcular el envelope se esta lanzando una colsulta como:

//                   SELECT ST_AsBinary(ST_Extent("geometry")) 

//                   FROM "gis_osm_roads_free_1" 

//                   WHERE ( ("geometry") IS NOT NULL )

//                Que falla cuando no hay registros al recibir un NULL en el 

//                ST_AsBinary. 

//                A falta de una forma mejor de detectar eso, si peta en este

//                punto asumiremos que no hay registros en la consulta y devolveremos

//                null como envelope.

                return null;

            }

            Envelope finalEnvelope = GeometryUtils.createEnvelope(Geometry.SUBTYPES.GEOM2D);

            while (rs.next()) {

                Geometry geometry = helper.getGeometryFromColumn(rs, 1);

                finalEnvelope.add(geometry);

            }

            return finalEnvelope;

        } catch (SQLException ex) {

            throw new JDBCSQLException(ex,sql);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}