h2/src/test/org/h2/test/db/TestSpatial.java
/**
     * Test the in the in-memory spatial index
     */
Test the in the in-memory spatial index
Test the in the in - memory spatial index
private void testMemorySpatialIndex() throws SQLException {
        deleteDb("spatial");
        Connection conn = getConnection(url);
        Statement stat = conn.createStatement();
        stat.execute("create memory table test(id int primary key, polygon geometry)");
        stat.execute("create spatial index idx_test_polygon on test(polygon)");
        stat.execute("insert into test values(1, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')");
        ResultSet rs;
        rs = stat.executeQuery("select * from test " +
                "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry " +
                "and polygon && 'POLYGON ((10 10, 10 20, 20 20, 10 10))'::Geometry");
        assertFalse(rs.next());
        rs = stat.executeQuery(
                "explain select * from test " +
                "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
        rs.next();
        assertContains(rs.getString(1), "/* PUBLIC.IDX_TEST_POLYGON: POLYGON &&");
        stat.executeQuery("select * from test where polygon > 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
        stat.executeQuery("select * from test where polygon < 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
        rs = stat.executeQuery(
                "select * from test " +
                "where intersects(polygon, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')");
        assertTrue(rs.next());
        rs = stat.executeQuery(
                "select * from test " +
                "where intersects(polygon, 'POINT (1 1)')");
        assertTrue(rs.next());
        rs = stat.executeQuery(
                "select * from test " +
                "where intersects(polygon, 'POINT (0 0)')");
        assertFalse(rs.next());
        stat.execute("drop table test");
        conn.close();
        deleteDb("spatial");
    }
private void testMemorySpatialIndex ( ) throws SQLException { deleteDb ( "spatial" ) ; Connection conn = getConnection ( url ) ; Statement stat = conn . createStatement ( ) ; stat . execute ( "create memory table test(id int primary key, polygon geometry)" ) ; stat . execute ( "create spatial index idx_test_polygon on test(polygon)" ) ; stat . execute ( "insert into test values(1, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')" ) ; ResultSet rs ; rs = stat . executeQuery ( "select * from test " + "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry " + "and polygon && 'POLYGON ((10 10, 10 20, 20 20, 10 10))'::Geometry" ) ; assertFalse ( rs . next ( ) ) ; rs = stat . executeQuery ( "explain select * from test " + "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry" ) ; rs . next ( ) ; assertContains ( rs . getString ( 1 ) , "/* PUBLIC.IDX_TEST_POLYGON: POLYGON &&" ) ; stat . executeQuery ( "select * from test where polygon > 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry" ) ; stat . executeQuery ( "select * from test where polygon < 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry" ) ; rs = stat . executeQuery ( "select * from test " + "where intersects(polygon, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')" ) ; assertTrue ( rs . next ( ) ) ; rs = stat . executeQuery ( "select * from test " + "where intersects(polygon, 'POINT (1 1)')" ) ; assertTrue ( rs . next ( ) ) ; rs = stat . executeQuery ( "select * from test " + "where intersects(polygon, 'POINT (0 0)')" ) ; assertFalse ( rs . next ( ) ) ; stat . execute ( "drop table test" ) ; conn . close ( ) ; deleteDb ( "spatial" ) ; }
Test the in the in-memory spatial index
Test the in the in - memory spatial index
// an query that can not possibly return a result // TODO equality should probably also use the spatial index // rs = stat.executeQuery("explain select * from test " + //         "where polygon = 'POLYGON ((1 1, 1 2, 2 2, 1 1))'"); // rs.next(); // assertContains(rs.getString(1), "/* PUBLIC.IDX_TEST_POLYGON: POLYGON ="); // these queries actually have no meaning in the context of a spatial index, but // check them anyhow