Hibernate Spatial › Hibernate Spatial - Users

Spatial queries using JPA

7 messages

Spatial queries using JPA

Reply Threaded

Thanks to Karel's help I am now able to persist spatial entities using
JPA and now trying to query spatial data using JPA Query class.

The tutorial example for spatial query uses org.hibernate.Criteria.
Is there a more JPA friendly way to execute spatial queries and do so
efficiently?

Thanks for any clues.

--
Regards,
Farrukh Najmi

Web: http://www.wellfleetsoftware.com

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

Farrukh Najmi

Re: Spatial queries using JPA

Reply Threaded

I spoke too soon. Here is some example of how to do this which I will
try shortly:

<http://www.hibernatespatial.org/pipermail/hibernatespatial-users/2007-December/000060.html>

Farrukh Najmi wrote:

> Thanks to Karel's help I am now able to persist spatial entities using
> JPA and now trying to query spatial data using JPA Query class.
>
> The tutorial example for spatial query uses org.hibernate.Criteria.
> Is there a more JPA friendly way to execute spatial queries and do so
> efficiently?
>
> Thanks for any clues.
>
>

--
Regards,
Farrukh Najmi

Web: http://www.wellfleetsoftware.com

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

Farrukh Najmi

Re: Spatial queries using JPA

Reply Threaded

Hi Karel,

Based on a quick survey it seems that hibernate-spatial defines wrapper
functions mapping to OGC Simples features for SQL spec around db
specific spatial functions. Very cool! So it should be possible to use
these functions in a db-independent manner within JPA Querys.

I tried the following code:

public void testInsertGeometryValue() throws Exception {
GeometryValueType geoValue = new GeometryValueType();
String wktPoint = "POINT(10 15)";
String wktFilter = "POLYGON((1 1,20 1,20 20, 1 20, 1 1))";

WKTReader wktReader = new WKTReader();
Geometry geom = null;
Geometry filter = null;
try {
geom = wktReader.read(wktPoint);
filter = wktReader.read(wktFilter);
} catch (ParseException e) {
throw new RuntimeException("Not a WKT string:" + wktPoint);
}

geoValue.setValue(geom);

try {
EntityTransaction txn = ...
EntityManager em = ...

//insert spatial object into db
em.persist(geoValue);
txn.commit();

//verify insert by reading back using spatial query
involving spatial function
String queryStr = "SELECT Object(o) FROM
mypkg.GeometryValueType AS o WHERE within(value_, :filter) = true";

Query query = em.createQuery(queryStr);
query.setParameter("filter", filter);
List objs = query.getResultList();

} finally {
....
}

}

This gave me the following exception:

Caused by: 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.hql.QueryLoader.list(QueryLoader.java:378)
at
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
... 25 more
Caused by: org.postgresql.util.PSQLException: ERROR: parse error -
invalid geometry
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
at
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 33 more

I see in the hibernate-spatial tutorial the following:

...
Query q = session
.createQuery("from Event where within(location, ?) = true");
Type geometryType = new CustomType(GeometryUserType.class, null);
q.setParameter(0, filter, geometryType);
List result = q.list();
...

So there is something I am missing related to Custom types. What would I
need to change to make this work?

Thank again for your kind help.

Farrukh Najmi wrote:

> I spoke too soon. Here is some example of how to do this which I will
> try shortly:
>
> <http://www.hibernatespatial.org/pipermail/hibernatespatial-users/2007-December/000060.html>
>
> Farrukh Najmi wrote:
>
>> Thanks to Karel's help I am now able to persist spatial entities using
>> JPA and now trying to query spatial data using JPA Query class.
>>
>> The tutorial example for spatial query uses org.hibernate.Criteria.
>> Is there a more JPA friendly way to execute spatial queries and do so
>> efficiently?
>>
>> Thanks for any clues.
>>
>>
>>
>
>
>

Farrukh Najmi

Re: Spatial queries using JPA

Reply Threaded

Looks like my problem is the same as this one here where Karel had
responded that he had committed a fix to main trunk:

<http://www.nabble.com/Finding-features-that-intersect-a-bounding-box-with-Hibernate%09Entity-Manager-td14269364.html>

I will try out building against the trunk and see if problem disappears.
Will report back soon.

Farrukh Najmi wrote:

> Hi Karel,
>
> Based on a quick survey it seems that hibernate-spatial defines wrapper
> functions mapping to OGC Simples features for SQL spec around db
> specific spatial functions. Very cool! So it should be possible to use
> these functions in a db-independent manner within JPA Querys.
>
> I tried the following code:
>
> public void testInsertGeometryValue() throws Exception {
> GeometryValueType geoValue = new GeometryValueType();
> String wktPoint = "POINT(10 15)";
> String wktFilter = "POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>
> WKTReader wktReader = new WKTReader();
> Geometry geom = null;
> Geometry filter = null;
> try {
> geom = wktReader.read(wktPoint);
> filter = wktReader.read(wktFilter);
> } catch (ParseException e) {
> throw new RuntimeException("Not a WKT string:" + wktPoint);
> }
>
> geoValue.setValue(geom);
>
> try {
> EntityTransaction txn = ...
> EntityManager em = ...
>
> //insert spatial object into db
> em.persist(geoValue);
> txn.commit();
>
> //verify insert by reading back using spatial query
> involving spatial function
> String queryStr = "SELECT Object(o) FROM
> mypkg.GeometryValueType AS o WHERE within(value_, :filter) = true";
>
> Query query = em.createQuery(queryStr);
> query.setParameter("filter", filter);
> List objs = query.getResultList();
>
> } finally {
> ....
> }
>
> }
>
> This gave me the following exception:
>
> Caused by: 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.hql.QueryLoader.list(QueryLoader.java:378)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
> at
> org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
> ... 25 more
> Caused by: org.postgresql.util.PSQLException: ERROR: parse error -
> invalid geometry
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
> at
> org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
> at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
> at org.hibernate.loader.Loader.doQuery(Loader.java:674)
> at
> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
> at org.hibernate.loader.Loader.doList(Loader.java:2220)
> ... 33 more
>
> I see in the hibernate-spatial tutorial the following:
>
> ...
> Query q = session
> .createQuery("from Event where within(location, ?) = true");
> Type geometryType = new CustomType(GeometryUserType.class, null);
> q.setParameter(0, filter, geometryType);
> List result = q.list();
> ...
>
> So there is something I am missing related to Custom types. What would I
> need to change to make this work?
>
> Thank again for your kind help.
>
> Farrukh Najmi wrote:
>
>> I spoke too soon. Here is some example of how to do this which I will
>> try shortly:
>>
>> <http://www.hibernatespatial.org/pipermail/hibernatespatial-users/2007-December/000060.html>
>>
>> Farrukh Najmi wrote:
>>
>>
>>> Thanks to Karel's help I am now able to persist spatial entities using
>>> JPA and now trying to query spatial data using JPA Query class.
>>>
>>> The tutorial example for spatial query uses org.hibernate.Criteria.
>>> Is there a more JPA friendly way to execute spatial queries and do so
>>> efficiently?
>>>
>>> Thanks for any clues.
>>>
>>>
>>>
>>>
>>
>>
>
>
>

Farrukh Najmi

Re: Spatial queries using JPA

Reply Threaded

Hmm. I carefully did a clean build of latest trunk bits of
hibernate-spatial and submodules and installed it locally in my private
repo. I then did a clean build of my code and ran my test program
against the latest hibernate-spatial trunk bits. I still get the same error.

Karel, since you had said the issue was fixed in main trunk in following
thread:

<http://www.nabble.com/Finding-features-that-intersect-a-bounding-box-with-Hibernate%09Entity-Manager-td14269364.html>

Can you please tell me what else to look for that I may be doing wrong.
Thanks very much and sorry to ask so many questions in one day.

Farrukh Najmi wrote:

> Looks like my problem is the same as this one here where Karel had
> responded that he had committed a fix to main trunk:
>
> <http://www.nabble.com/Finding-features-that-intersect-a-bounding-box-with-Hibernate%09Entity-Manager-td14269364.html>
>
> I will try out building against the trunk and see if problem disappears.
> Will report back soon.
>
> Farrukh Najmi wrote:
>
>> Hi Karel,
>>
>> Based on a quick survey it seems that hibernate-spatial defines wrapper
>> functions mapping to OGC Simples features for SQL spec around db
>> specific spatial functions. Very cool! So it should be possible to use
>> these functions in a db-independent manner within JPA Querys.
>>
>> I tried the following code:
>>
>> public void testInsertGeometryValue() throws Exception {
>> GeometryValueType geoValue = new GeometryValueType();
>> String wktPoint = "POINT(10 15)";
>> String wktFilter = "POLYGON((1 1,20 1,20 20, 1 20, 1 1))";
>>
>> WKTReader wktReader = new WKTReader();
>> Geometry geom = null;
>> Geometry filter = null;
>> try {
>> geom = wktReader.read(wktPoint);
>> filter = wktReader.read(wktFilter);
>> } catch (ParseException e) {
>> throw new RuntimeException("Not a WKT string:" + wktPoint);
>> }
>>
>> geoValue.setValue(geom);
>>
>> try {
>> EntityTransaction txn = ...
>> EntityManager em = ...
>>
>> //insert spatial object into db
>> em.persist(geoValue);
>> txn.commit();
>>
>> //verify insert by reading back using spatial query
>> involving spatial function
>> String queryStr = "SELECT Object(o) FROM
>> mypkg.GeometryValueType AS o WHERE within(value_, :filter) = true";
>>
>> Query query = em.createQuery(queryStr);
>> query.setParameter("filter", filter);
>> List objs = query.getResultList();
>>
>> } finally {
>> ....
>> }
>>
>> }
>>
>> This gave me the following exception:
>>
>> Caused by: 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.hql.QueryLoader.list(QueryLoader.java:378)
>> at
>> org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
>> at
>> org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
>> at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
>> at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
>> at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:66)
>> ... 25 more
>> Caused by: org.postgresql.util.PSQLException: ERROR: parse error -
>> invalid geometry
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
>> at
>> org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
>> at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
>> at org.hibernate.loader.Loader.doQuery(Loader.java:674)
>> at
>> org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
>> at org.hibernate.loader.Loader.doList(Loader.java:2220)
>> ... 33 more
>>
>> I see in the hibernate-spatial tutorial the following:
>>
>> ...
>> Query q = session
>> .createQuery("from Event where within(location, ?) = true");
>> Type geometryType = new CustomType(GeometryUserType.class, null);
>> q.setParameter(0, filter, geometryType);
>> List result = q.list();
>> ...
>>
>> So there is something I am missing related to Custom types. What would I
>> need to change to make this work?
>>
>> Thank again for your kind help.
>>
>> Farrukh Najmi wrote:
>>
>>
>>> I spoke too soon. Here is some example of how to do this which I will
>>> try shortly:
>>>
>>> <http://www.hibernatespatial.org/pipermail/hibernatespatial-users/2007-December/000060.html>
>>>
>>> Farrukh Najmi wrote:
>>>
>>>
>>>
>>>> Thanks to Karel's help I am now able to persist spatial entities using
>>>> JPA and now trying to query spatial data using JPA Query class.
>>>>
>>>> The tutorial example for spatial query uses org.hibernate.Criteria.
>>>> Is there a more JPA friendly way to execute spatial queries and do so
>>>> efficiently?
>>>>
>>>> Thanks for any clues.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

Karel Maesen

Re: Spatial queries using JPA

Reply Threaded

Hi,

On 14 May 2008, at 22:45, Farrukh Najmi wrote:

>
> Hmm. I carefully did a clean build of latest trunk bits of
> hibernate-spatial and submodules and installed it locally in my
> private
> repo. I then did a clean build of my code and ran my test program
> against the latest hibernate-spatial trunk bits. I still get the
> same error.
>
> Karel, since you had said the issue was fixed in main trunk in
> following
> thread:
>
> <http://www.nabble.com/Finding-features-that-intersect-a-bounding-
> box-with-Hibernate%09Entity-Manager-td14269364.html>

I don't think the discussion in that thread is relevant. That "fix"
was the addition of an "extent" function to HQL. But you don't seem
to be using that.

> Can you please tell me what else to look for that I may be doing
> wrong.

I haven't used JPA, so I'm not entirely sure, but it looks like the
object you pass as parameter into the query cannot be treated
properly. Remember that the JPA has no notion of a UserType, so it
probably doesn't first properly insert the required type of object
into the prepared JDBC query statement.

You could try this:
...
Query query = em.createQuery(queryStr);
//cast to HibernateQuery so as get access to the underlying Hibernate
Query object.
org.hibernate.Query q = ((HibernateQuery)query).getHibernateQuery();
// do as with non-JPA Hibernate.
Type geometryType = new CustomType(GeometryUserType.class, null);
q.setParameter(0, filter, geometryType)
...

Let me know if this works.

> Thanks very much and sorry to ask so many questions in one day.
>

Really no problem. This discussion is very informative for me, since
I get to see how to use JPA with Hibernate Spatial.

Regards,

Karel Maesen

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

Farrukh Najmi

Re: Spatial queries using JPA

Reply Threaded

Hi Karel,

Your suggestion worked flawlessly! Here is the code I used to
successfully query the spatial table using JPA and EJBQL:

//verify insert by reading back
String queryStr = "SELECT Object(o) FROM
mypkg.GeometryValueType AS o WHERE within(value_, :filter) = true";

EntityManager em = pm.getEntityManager();
Query query = em.createQuery(queryStr);

//cast to HibernateQuery so as get access to the underlying
Hibernate Query object.
org.hibernate.Query hibernateQuery =
((org.hibernate.ejb.QueryImpl)query).getHibernateQuery();
// do as with non-JPA Hibernate.
Type geometryType = new CustomType(GeometryUserType.class,
null);
hibernateQuery.setParameter("filter", filter, geometryType);

List objs = query.getResultList();

Seems like JPA should support a UserType concept. I will pass this
suggestion on to the spec team at Sun. Thanks again for your terrific help.

Karel Maesen wrote:

> Hi,
>
> On 14 May 2008, at 22:45, Farrukh Najmi wrote:
>
>
>> Hmm. I carefully did a clean build of latest trunk bits of
>> hibernate-spatial and submodules and installed it locally in my
>> private
>> repo. I then did a clean build of my code and ran my test program
>> against the latest hibernate-spatial trunk bits. I still get the
>> same error.
>>
>> Karel, since you had said the issue was fixed in main trunk in
>> following
>> thread:
>>
>> <http://www.nabble.com/Finding-features-that-intersect-a-bounding-
>> box-with-Hibernate%09Entity-Manager-td14269364.html>
>>
>
>
> I don't think the discussion in that thread is relevant. That "fix"
> was the addition of an "extent" function to HQL. But you don't seem
> to be using that.
>
>
>
>> Can you please tell me what else to look for that I may be doing
>> wrong.
>>
>
> I haven't used JPA, so I'm not entirely sure, but it looks like the
> object you pass as parameter into the query cannot be treated
> properly. Remember that the JPA has no notion of a UserType, so it
> probably doesn't first properly insert the required type of object
> into the prepared JDBC query statement.
>
> You could try this:
> ...
> Query query = em.createQuery(queryStr);
> //cast to HibernateQuery so as get access to the underlying Hibernate
> Query object.
> org.hibernate.Query q = ((HibernateQuery)query).getHibernateQuery();
> // do as with non-JPA Hibernate.
> Type geometryType = new CustomType(GeometryUserType.class, null);
> q.setParameter(0, filter, geometryType)
> ...
>
> Let me know if this works.
>