Hibernate Spatial › Hibernate Spatial - Users

Error executing spatial query

5 messages Options
Embed this post
Permalink
josegar74

Error executing spatial query

Reply Threaded More More options
Print post
Permalink
Hi!

I'm developing an application that uses HibernateSpatial to access Oracle Spatial 10g. The application has to get a list of POI's within a polygon. When I launch the spatial query I get an ORACLE error: "Invalid Column Index".

I have tried with the tutorial code and I get the same error when executing the find method.

I don't know if it's a bug or I'm doing something wrong. Can anybody help me?

Thanks in advance.

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Karel Maesen

Re: Error executing spatial query

Reply Threaded More More options
Print post
Permalink
Activate Hibernate SQL logging, look at the generated SQL and try to  
run it manually. This should help you understand the nature of the  
problem.

If the problem persists, try again on this list, but include the full  
error stacktrace.

Regards,

Karel

On 02 Mar 2008, at 22:32, jose garcia wrote:

> Hi!
>
> I'm developing an application that uses HibernateSpatial to access  
> Oracle Spatial 10g. The application has to get a list of POI's  
> within a polygon. When I launch the spatial query I get an ORACLE  
> error: "Invalid Column Index".
>
> I have tried with the tutorial code and I get the same error when  
> executing the find method.
>
> I don't know if it's a bug or I'm doing something wrong. Can  
> anybody help me?
>
> Thanks in advance.
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
josegar74

Re: Error executing spatial query

Reply Threaded More More options
Print post
Permalink
Hi Karel

The hibernate SQL logging is activated. In hibernate.cfg.xml:

        <property name="show_sql">true</property>

, but the log shows the parameters as a '?'.

The code of the tutorial that fails is:

   private List find(String wktFilter){
        WKTReader fromText = new WKTReader();
        Geometry filter = null;
        try{
                filter = fromText.read(wktFilter);
        } catch(ParseException e){
                throw new RuntimeException("Not a WKT String:" + wktFilter);
        }
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
        System.out.println("Filter is : " + filter);
        Criteria testCriteria = session.createCriteria(Event.class);
        testCriteria.add(SpatialRestrictions.within("location", filter, filter));
        List results = testCriteria.list();
        session.getTransaction().commit();
        return results;
    }

This is the error trace:

Filter is : POLYGON ((1 1, 20 1, 20 20, 1 20, 1 1))
Hibernate: select this_.EVENT_ID as EVENT1_0_0_, this_.EVENT_DATE as EVENT2_0_0_, this_.title as title0_0_, this_.LOC as LOC0_0_ from EVENTS this_ where MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(this_.LOC),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?)) = 1 and this_.LOC is not null
Exception in thread "main" org.hibernate.exception.GenericJDBCException: could not execute query
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2223)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
    at events.EventManager.find(EventManager.java:76)
    at events.EventManager.main(EventManager.java:26)
Caused by: java.sql.SQLException: Índice de columna no válido (Index column not valid)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OraclePreparedStatement.setSTRUCTInternal(OraclePreparedStatement.java:6266)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9147)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8739)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9471)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9454)
    at org.hibernatespatial.oracle.SDOGeometryType.nullSafeSet(SDOGeometryType.java:89)
    at org.hibernatespatial.GeometryUserType.nullSafeSet(GeometryUserType.java:184)
    at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:156)
    at org.hibernate.loader.Loader.bindPositionalParameters(Loader.java:1707)
    at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1678)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1563)
    at org.hibernate.loader.Loader.doQuery(Loader.java:673)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2220)
    ... 7 more


Thanks.

2008/3/4, Karel Maesen <[hidden email]>:
Activate Hibernate SQL logging, look at the generated SQL and try to
run it manually. This should help you understand the nature of the
problem.

If the problem persists, try again on this list, but include the full
error stacktrace.

Regards,

Karel


On 02 Mar 2008, at 22:32, jose garcia wrote:

> Hi!
>
> I'm developing an application that uses HibernateSpatial to access
> Oracle Spatial 10g. The application has to get a list of POI's
> within a polygon. When I launch the spatial query I get an ORACLE
> error: "Invalid Column Index".
>
> I have tried with the tutorial code and I get the same error when
> executing the find method.
>
> I don't know if it's a bug or I'm doing something wrong. Can
> anybody help me?
>
> Thanks in advance.

> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users


_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Karel Maesen

Re: Error executing spatial query

Reply Threaded More More options
Print post
Permalink
Hi,

I think you tripped over the same bug as some other users on the list.

As a work-around, change the line

                testCriteria.add(SpatialRestrictions.within("location",  
filter, filter));
to
                testCriteria.add(SpatialRestrictions.within("location",null, filter));

Then it should work.

The second parameter in the SpatialRestrictions methods is a filter  
geometry for a first-pass filtering based on the spatial index. This  
makes sense in Postgis (sort of), but not for Oracle. Unfortunately,  
if you don't pass in null for Oracle (or MySQL) for this parameter  
you  trip this bug.

In the next milestone release, the bug will be removed and these 3-
parameter methods will be deprecated in favor of 2-parameter methods.


Regards,

Karel

On 04 Mar 2008, at 20:28, jose garcia wrote:

> Hi Karel
>
> The hibernate SQL logging is activated. In hibernate.cfg.xml:
>
>         <property name="show_sql">true</property>
>
> , but the log shows the parameters as a '?'.
>
> The code of the tutorial that fails is:
>
>    private List find(String wktFilter){
>         WKTReader fromText = new WKTReader();
>         Geometry filter = null;
>         try{
>                 filter = fromText.read(wktFilter);
>         } catch(ParseException e){
>                 throw new RuntimeException("Not a WKT String:" +  
> wktFilter);
>         }
>         Session session = HibernateUtil.getSessionFactory
> ().getCurrentSession();
>         session.beginTransaction();
>         System.out.println("Filter is : " + filter);
>         Criteria testCriteria = session.createCriteria(Event.class);
>         testCriteria.add(SpatialRestrictions.within("location",  
> filter, filter));
>         List results = testCriteria.list();
>         session.getTransaction().commit();
>         return results;
>     }
>
> This is the error trace:
>
> Filter is : POLYGON ((1 1, 20 1, 20 20, 1 20, 1 1))
> Hibernate: select this_.EVENT_ID as EVENT1_0_0_, this_.EVENT_DATE  
> as EVENT2_0_0_, this_.title as title0_0_, this_.LOC as LOC0_0_ from  
> EVENTS this_ where MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
> (this_.LOC),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?)) = 1 and this_.LOC  
> is not null
> Exception in thread "main"  
> org.hibernate.exception.GenericJDBCException: could not execute query
>     at  
> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(
> SQLStateConverter.java:103)
>     at org.hibernate.exception.SQLStateConverter.convert
> (SQLStateConverter.java:91)
>     at org.hibernate.exception.JDBCExceptionHelper.convert
> (JDBCExceptionHelper.java:43)
>     at org.hibernate.loader.Loader.doList(Loader.java:2223)
>     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:
> 2104)
>     at org.hibernate.loader.Loader.list(Loader.java:2099)
>     at org.hibernate.loader.criteria.CriteriaLoader.list
> (CriteriaLoader.java:94)
>     at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
>     at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
>     at events.EventManager.find(EventManager.java:76)
>     at events.EventManager.main(EventManager.java:26)
> Caused by: java.sql.SQLException: Índice de columna no válido  
> (Index column not valid)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:112)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:146)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:208)
>     at oracle.jdbc.driver.OraclePreparedStatement.setSTRUCTInternal
> (OraclePreparedStatement.java:6266)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical
> (OraclePreparedStatement.java:9147)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
> (OraclePreparedStatement.java:8739)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
> (OraclePreparedStatement.java:9471)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObject
> (OraclePreparedStatement.java:9454)
>     at org.hibernatespatial.oracle.SDOGeometryType.nullSafeSet
> (SDOGeometryType.java:89)
>     at org.hibernatespatial.GeometryUserType.nullSafeSet
> (GeometryUserType.java:184)
>     at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:156)
>     at org.hibernate.loader.Loader.bindPositionalParameters
> (Loader.java:1707)
>     at org.hibernate.loader.Loader.bindParameterValues(Loader.java:
> 1678)
>     at org.hibernate.loader.Loader.prepareQueryStatement
> (Loader.java:1563)
>     at org.hibernate.loader.Loader.doQuery(Loader.java:673)
>     at  
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
> (Loader.java:236)
>     at org.hibernate.loader.Loader.doList(Loader.java:2220)
>     ... 7 more
>
>
> Thanks.
>
> 2008/3/4, Karel Maesen <[hidden email]>: Activate Hibernate SQL  
> logging, look at the generated SQL and try to
> run it manually. This should help you understand the nature of the
> problem.
>
> If the problem persists, try again on this list, but include the full
> error stacktrace.
>
> Regards,
>
> Karel
>
>
> On 02 Mar 2008, at 22:32, jose garcia wrote:
>
> > Hi!
> >
> > I'm developing an application that uses HibernateSpatial to access
> > Oracle Spatial 10g. The application has to get a list of POI's
> > within a polygon. When I launch the spatial query I get an ORACLE
> > error: "Invalid Column Index".
> >
> > I have tried with the tutorial code and I get the same error when
> > executing the find method.
> >
> > I don't know if it's a bug or I'm doing something wrong. Can
> > anybody help me?
> >
> > Thanks in advance.
>
> > _______________________________________________
> > hibernatespatial-users mailing list
> > [hidden email]
> > http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> > hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/ 
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
josegar74

Re: Error executing spatial query

Reply Threaded More More options
Print post
Permalink
Hi,

It works.

There's another bug when hibernate is configurated to recreate schema:

<property name="hbm2ddl.auto">create</property>

The geometry column in Oracle is created as:

CREATE TABLE TELEATLAS.EVENTS (
EVENT_ID NUMBER(19,0) NOT NULL,
EVENT_DATE TIMESTAMP(6) NULL,
TITLE VARCHAR2(255) NULL,
LOC GEOMETRY NULL,
PRIMARY KEY(EVENT_ID)
)

and the programs fail when executing the querys. The correct table definition has to be:

CREATE TABLE TELEATLAS.EVENTS (
EVENT_ID NUMBER(19,0) NOT NULL,
EVENT_DATE TIMESTAMP(6) NULL,
TITLE VARCHAR2(255) NULL,
LOC SDO_GEOMETRY NULL,
PRIMARY KEY(EVENT_ID)
)


Thanks a lot.
 

2008/3/4, Karel Maesen <[hidden email]>:
Hi,

I think you tripped over the same bug as some other users on the list.

As a work-around, change the line


                testCriteria.add(SpatialRestrictions.within("location",
filter, filter));

to
                testCriteria.add(SpatialRestrictions.within("location",null, filter));

Then it should work.

The second parameter in the SpatialRestrictions methods is a filter
geometry for a first-pass filtering based on the spatial index. This
makes sense in Postgis (sort of), but not for Oracle. Unfortunately,
if you don't pass in null for Oracle (or MySQL) for this parameter
you  trip this bug.

In the next milestone release, the bug will be removed and these 3-
parameter methods will be deprecated in favor of 2-parameter methods.


Regards,


Karel


On 04 Mar 2008, at 20:28, jose garcia wrote:

> Hi Karel
>
> The hibernate SQL logging is activated. In hibernate.cfg.xml:
>
>         <property name="show_sql">true</property>
>
> , but the log shows the parameters as a '?'.
>
> The code of the tutorial that fails is:
>
>    private List find(String wktFilter){
>         WKTReader fromText = new WKTReader();
>         Geometry filter = null;
>         try{
>                 filter = fromText.read(wktFilter);
>         } catch(ParseException e){
>                 throw new RuntimeException("Not a WKT String:" +
> wktFilter);
>         }
>         Session session = HibernateUtil.getSessionFactory
> ().getCurrentSession();
>         session.beginTransaction();
>         System.out.println("Filter is : " + filter);
>         Criteria testCriteria = session.createCriteria(Event.class);
>         testCriteria.add(SpatialRestrictions.within("location",
> filter, filter));
>         List results = testCriteria.list();
>         session.getTransaction().commit();
>         return results;
>     }
>
> This is the error trace:
>
> Filter is : POLYGON ((1 1, 20 1, 20 20, 1 20, 1 1))
> Hibernate: select this_.EVENT_ID as EVENT1_0_0_, this_.EVENT_DATE
> as EVENT2_0_0_, this_.title as title0_0_, this_.LOC as LOC0_0_ from
> EVENTS this_ where MDSYS.OGC_WITHIN(MDSYS.ST_GEOMETRY.FROM_SDO_GEOM
> (this_.LOC),MDSYS.ST_GEOMETRY.FROM_SDO_GEOM(?)) = 1 and this_.LOC
> is not null
> Exception in thread "main"
> org.hibernate.exception.GenericJDBCException: could not execute query
>     at
> org.hibernate.exception.SQLStateConverter.handledNonSpecificException(
> SQLStateConverter.java:103)
>     at org.hibernate.exception.SQLStateConverter.convert
> (SQLStateConverter.java:91)
>     at org.hibernate.exception.JDBCExceptionHelper.convert
> (JDBCExceptionHelper.java:43)
>     at org.hibernate.loader.Loader.doList(Loader.java:2223)
>     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:
> 2104)
>     at org.hibernate.loader.Loader.list(Loader.java:2099)
>     at org.hibernate.loader.criteria.CriteriaLoader.list
> (CriteriaLoader.java:94)
>     at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
>     at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
>     at events.EventManager.find(EventManager.java:76)
>     at events.EventManager.main(EventManager.java:26)
> Caused by: java.sql.SQLException: Índice de columna no válido
> (Index column not valid)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:112)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:146)
>     at oracle.jdbc.driver.DatabaseError.throwSqlException
> (DatabaseError.java:208)
>     at oracle.jdbc.driver.OraclePreparedStatement.setSTRUCTInternal
> (OraclePreparedStatement.java:6266)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical
> (OraclePreparedStatement.java:9147)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
> (OraclePreparedStatement.java:8739)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal
> (OraclePreparedStatement.java:9471)
>     at oracle.jdbc.driver.OraclePreparedStatement.setObject
> (OraclePreparedStatement.java:9454)
>     at org.hibernatespatial.oracle.SDOGeometryType.nullSafeSet
> (SDOGeometryType.java:89)
>     at org.hibernatespatial.GeometryUserType.nullSafeSet
> (GeometryUserType.java:184)
>     at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:156)
>     at org.hibernate.loader.Loader.bindPositionalParameters
> (Loader.java:1707)
>     at org.hibernate.loader.Loader.bindParameterValues(Loader.java:
> 1678)
>     at org.hibernate.loader.Loader.prepareQueryStatement
> (Loader.java:1563)
>     at org.hibernate.loader.Loader.doQuery(Loader.java:673)
>     at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
> (Loader.java:236)
>     at org.hibernate.loader.Loader.doList(Loader.java:2220)
>     ... 7 more
>
>
> Thanks.
>
> 2008/3/4, Karel Maesen <[hidden email]>: Activate Hibernate SQL
> logging, look at the generated SQL and try to
> run it manually. This should help you understand the nature of the
> problem.
>
> If the problem persists, try again on this list, but include the full
> error stacktrace.
>
> Regards,
>
> Karel
>
>
> On 02 Mar 2008, at 22:32, jose garcia wrote:
>
> > Hi!
> >
> > I'm developing an application that uses HibernateSpatial to access
> > Oracle Spatial 10g. The application has to get a list of POI's
> > within a polygon. When I launch the spatial query I get an ORACLE
> > error: "Invalid Column Index".
> >
> > I have tried with the tutorial code and I get the same error when
> > executing the find method.
> >
> > I don't know if it's a bug or I'm doing something wrong. Can
> > anybody help me?
> >
> > Thanks in advance.
>
> > _______________________________________________
> > hibernatespatial-users mailing list
> > [hidden email]
> > http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> > hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users
>
> _______________________________________________
> hibernatespatial-users mailing list
> [hidden email]
> http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/
> hibernatespatial-users

_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users


_______________________________________________
hibernatespatial-users mailing list
[hidden email]
http://www.hibernatespatial.org/cgi-bin/mailman/listinfo/hibernatespatial-users
Free Embeddable Forum Powered by Nabble Help