Application: gvSIG desktop

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

import java.sql.Connection;

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.fmap.dal.exception.DataException;

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

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

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

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

import org.gvsig.fmap.geom.Geometry;

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

public class CalculateEnvelopeOfColumnOperation extends AbstractConnectionOperation {

    private final String subquery;

    private final String schemaName;

    private final String tableName;

    private final String columnName;

    private final String baseFilter;

    private final Envelope limit;

    private final IProjection crs;

    private final String dbName;

    public CalculateEnvelopeOfColumnOperation(

            JDBCHelper helper,

            String subquery,

            String dbName,

            String schemaName,

            String tableName,

            String columnName,

            String baseFilter,

            Envelope limit,

            IProjection crs

    ) {

        super(helper);

        this.subquery = subquery;

        this.dbName = dbName;

        this.schemaName = schemaName;

        this.tableName = tableName;

        this.columnName = columnName;

        this.baseFilter = baseFilter;

        this.limit = limit;

        this.crs = crs;

    }

    @Override

    public final Object perform(Connection conn) throws DataException {

        Envelope env = calculateEnvelopeOfColumn(

            conn,

            subquery,

            dbName,

            schemaName,

            tableName,

            columnName,

            baseFilter,

            limit,

            crs

        );

        return env;

    }

    public Envelope calculateEnvelopeOfColumn(

            Connection conn,

            String subquery,

            String dbName,

            String schemaName,

            String tableName,

            String columnName,

            String baseFilter,

            Envelope limit,

            IProjection crs

    ) throws DataException {

        //

        // 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();

        sqlbuilder.select().column().value(

            sqlbuilder.getAsGeometry(

                sqlbuilder.ST_ExtentAggregate(

                        sqlbuilder.column(columnName)

                )

            )

        );

        if (StringUtils.isEmpty(subquery)) {

            sqlbuilder.select().from().table().database(dbName).schema(schemaName).name(tableName);

        } else {

            sqlbuilder.select().from().subquery(subquery);

        }

        if (StringUtils.isEmpty(baseFilter)) {

            if (limit != null) {

                sqlbuilder.select().where().set(

                        sqlbuilder.ST_Intersects(

                                sqlbuilder.ST_Envelope(

                                        sqlbuilder.column(columnName)

                                ),

                                sqlbuilder.ST_Envelope(

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

                                )

                        )

                );

            }

        } else {

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

            if (limit != null) {

                sqlbuilder.select().where().and(

                        sqlbuilder.ST_Intersects(

                            sqlbuilder.ST_Envelope(

                                    sqlbuilder.column(columnName)

                            ),

                            sqlbuilder.ST_Envelope(

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

                            )

                        )

                );

            }

        }

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

        Statement st = null;

        ResultSet rs = null;

        try {

            st = conn.createStatement();

            rs = JDBCUtils.executeQuery(st, sql);

            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);

        } finally {

            JDBCUtils.closeQuietly(st);

            JDBCUtils.closeQuietly(rs);

        }

    }

}