Criteria API. Real need for theta-style join / Spatial Data

Rafael Machado · **Posted:** Wed Jul 05, 2006 5:57 pm

Hibernate version: 3.1

Name and version of the database you are using: 9.2

Hello everyone.

We are starting a new project here which we are going to manipulate spatial data (Oracle SDO) and we intend to use Hibernate. We are building a service that knows how to perform spatial operations over our domain. And many applications will connect to this server.

The most common operation this server must implement is a method like this:

Code:

  Set<Code> findElements(ElementType sourceType, Code sourceElement, ElementType targetType,  Xxxx spatialRelationship, Yyyy restrictions)

It finds elements ids that have some kind of spatial relationship (for example, intersection) with another type of element, and the possibility to restrict even more the search with alphanumeric attributes (mapped into relational data).

An sql example that could perform this operation:

Code:

SELECT a.code
FROM    target_table a, source_table b
WHERE b.code = ?
AND      mdsys.sdo_relate(a.shape, b.shape, mask=ANYINTERACT querytype=window') = 'TRUE'

I created a custom type to represent Geometries and I am already able to manipulate spatial data using HQL. However, we need strong flexibility to dynamically build our queries and the possibility to inject sql restrictions is very desirable. That's why Criteria API is our preferred solution! In fact, where you read Yyyy, I would like to use Hibernate Criterion structures. But I am still studying this topic. I have read Reference Documentation, Wiki and HiA book. But I still don't have too much experience with Hibernate.

But as you could see on the above sql example, I need a Cartesian product and a spatial operation (spatial join). I think it's just like a theta-style join. I cannot associate these elements in the XML mapping. Thus, I couldn't get a way to use Criteria API to perform this operation. I thought about creating a new Criterion (SpatialCriterion?). That would allow me to express a spatial operation, but still would have the Cartesian product problem to solve.

I have looked through Hibernate source code and as far as I understood, the only way to use the Criteria API with a spatial join between elements that are not associated is to change Hibernate. Am I right? Does anybody knows if there is any way to accomplish this specific operation just extending the framework, like creating a custom user type, or new criterion? I think changing Hibernate Criteria API to support Cartesian product should not be so easy, as it does not have this feature until now. And I have seen some posts on the forum where people tried to use it this way. Any orientations about changing Criteria API to support this operation? Or is it better forget Criteria API in this case?

I know I can dynamically generate HQL, but Criteria API matches much better our needs.

Thanks in advance for any reply and I apologize for my poor English.

Best Regards.

Rafael

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Theta-style joins are cartesian products.

Criteria cannot do them. That functionality can be reproduced using DetachedCriteria (subqueries), but that can be significantly slower than theta style joins.

To work with your example, the equivalent subselect query would be

Code:

SELECT a.code
FROM    target_table a
WHERE 'TRUE' = (select mdsys.sdo_relate(a.shape, b.shape, mask=ANYINTERACT querytype=window')
      from source_table b
      where b.code = ?)

DetachedCriteria can do this in conjunction with Restrictions.sqlRestriction().

Rafael Machado · **Posted:** Wed Jul 05, 2006 6:58 pm

tenwit wrote:

Theta-style joins are cartesian products.

Criteria cannot do them. That functionality can be reproduced using DetachedCriteria (subqueries), but that can be significantly slower than theta style joins.

To work with your example, the equivalent subselect query would be

Code:

SELECT a.code
FROM    target_table a
WHERE 'TRUE' = (select mdsys.sdo_relate(a.shape, b.shape, mask=ANYINTERACT querytype=window')
      from source_table b
      where b.code = ?)

DetachedCriteria can do this in conjunction with Restrictions.sqlRestriction().

Thank you for your quick reply, tenwit.

I knew I could use DetachedCriteria, but as you said, performance might be affected. In this case, I think it would be dramatically affected, as spatial operations are very heavy and not so easy to tune.

I think I cannot run from theta-style joins. :(