Application: gvSIG desktop

package org.gvsig.sqlite.dal.operations.sql;

import java.util.List;

import junit.framework.TestCase;

import org.cresques.cts.IProjection;

import org.gvsig.expressionevaluator.Expression;

import org.gvsig.expressionevaluator.ExpressionUtils;

import org.gvsig.expressionevaluator.GeometryExpressionBuilder;

import org.gvsig.fmap.crs.CRSFactory;

import org.gvsig.fmap.dal.DALLocator;

import org.gvsig.fmap.dal.DataManager;

import org.gvsig.fmap.dal.expressionevaluator.DALExpressionBuilder;

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

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

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

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

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

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

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

import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation;

import org.gvsig.fmap.geom.Geometry;

import org.gvsig.fmap.geom.GeometryUtils;

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

import org.gvsig.sqlite.dal.TestUtils;

import org.gvsig.sqlite.dal.geopackage.index.GeopackageIndexRTree;

import org.gvsig.tools.library.impl.DefaultLibrariesInitializer;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

public class TestSTDistance extends TestCase {

  private static final Logger LOGGER = LoggerFactory.getLogger(TestSTDistance.class);

  public TestSTDistance(String testName) {

    super(testName);

  }

  @Override

  protected void setUp() throws Exception {

    super.setUp();

    new DefaultLibrariesInitializer().fullInitialize();

  }

  @Override

  protected void tearDown() throws Exception {

    super.tearDown();

  }

  public void testSTDistanceWithoutSpatialIndex() throws Exception {

    JDBCHelper helper = TestUtils.createJDBCHelper();

    JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

    OperationsFactory operations = helper.getOperations();

    List<String> expectedSQLs = TestUtils.getSQLs("testSTDistance.sql");

    FeatureStore sourceStore = TestUtils.openSourceStore1();

    TableReference table = operations.createTableReference(

            "dbtest", 

            sqlbuilder.default_schema(), 

            "test", 

            null

    );

    FeatureType featureType = sourceStore.getDefaultFeatureType();

    FeatureQuery query = sourceStore.createFeatureQuery();

    IProjection proj = CRSFactory.getCRS("EPSG:4326");

    Point point = GeometryUtils.createPoint(0, 40);

    Geometry buffer = point.buffer(1);

    DataManager dataManager = DALLocator.getDataManager();

    DALExpressionBuilder dalBuilder = dataManager.createDALExpressionBuilder();

    GeometryExpressionBuilder builder = dalBuilder.expression();

    String filter = builder.ST_Intersects(

            builder.geometry(buffer, proj), 

            builder.column(featureType.getDefaultGeometryAttributeName())

    ).toString();

    String sortBy = builder.ST_Distance(

            builder.geometry(buffer), 

            builder.column(featureType.getDefaultGeometryAttributeName())

    ).toString();

    Expression sortByExpression = ExpressionUtils.createExpression(sortBy);

    query.setFilter(filter);

    query.getOrder().add(sortByExpression, true);

    query.setLimit(1);

    ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

            table,

            null,

            null,

            query,

            featureType,

            featureType,

            0,

            0, 

            0

    );

    String sql = resultSetForSetProvider.getSQL();

    System.out.println("###### SQL:"+sql);

    System.out.println("###### EXP:"+expectedSQLs.get(0));

    assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(0), sql);

  }

  public void testSTDistanceWithSpatialIndex() throws Exception {

    JDBCHelper helper = TestUtils.createJDBCHelper();

    JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder();

    OperationsFactory operations = helper.getOperations();

    List<String> expectedSQLs = TestUtils.getSQLs("testSTDistance.sql");

    FeatureStore sourceStore = TestUtils.openSourceStore1();

    TableReference table = operations.createTableReference(

            "dbtest", 

            sqlbuilder.default_schema(), 

            "test", 

            null

    );

    FeatureType featureType = sourceStore.getDefaultFeatureType();

    GeopackageIndexRTree index = new GeopackageIndexRTree();

    index.setHasIndex(featureType.getDefaultGeometryAttribute(), true);

    FeatureQuery query = sourceStore.createFeatureQuery();

    IProjection proj = CRSFactory.getCRS("EPSG:4326");

    Point point = GeometryUtils.createPoint(0, 40);

    Geometry buffer = point.buffer(1);

    DataManager dataManager = DALLocator.getDataManager();

    DALExpressionBuilder dalBuilder = dataManager.createDALExpressionBuilder();

    GeometryExpressionBuilder builder = dalBuilder.expression();

    String filter = builder.ST_Intersects(

            builder.geometry(buffer, proj), 

            builder.column(featureType.getDefaultGeometryAttributeName())

    ).toString();

    String sortBy = builder.ST_Distance(

            builder.geometry(buffer), 

            builder.column(featureType.getDefaultGeometryAttributeName())

    ).toString();

    Expression sortByExpression = ExpressionUtils.createExpression(sortBy);

    query.setFilter(filter);

    query.getOrder().add(sortByExpression, true);

    query.setLimit(1);

    ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider(

            table,

            null,

            null,

            query,

            featureType,

            featureType,

            0,

            0, 

            0

    );

    String sql = resultSetForSetProvider.getSQL();

    System.out.println("###### SQL:"+sql);

    System.out.println("###### EXP:"+expectedSQLs.get(1));

    assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(1), sql);

  }

}