[postgis-users] problems postgis, hibernate and java

[David Potts]

I have been writing an application that uses the  org.postgis.Geometry
data type.  I have been impressed so far, but I have a slight problem with
use of the Geometry when supplied to a query.

If I attempt to access a Geometry defined with in the database and one of
the postgis defined functions such as spatial.srid, every thing works as
expected.  Data is provided and everything works as expected.

But if I try supplying a Geometry via an external reference  eg

Query theQuery= ManagerUtil.getManager().createQuery("select
spatial.distance(p.osNgr,:geom) from PlaceDTO p  ");

where :geom is set via  theQuery.setParameter("geom",thePlace.getOsNgr());

I get an exception thrown,
===========================================
javax.persistence.PersistenceException:
org.hibernate.exception.GenericJDBCException: could not execute query
        at
org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
        at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
        at dist.dao.Place.findAllByDistance(Place.java:112)
        at dist.ana.JpaTest.main(JpaTest.java:97)
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:64)
        ... 2 more
Caused by: org.postgresql.util.PSQLException: ERROR: parse error - invalid
geometry
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1525)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1309)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:354)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:258)
        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)
        ... 10 more
Exception in thread "main" java.lang.NullPointerException
        at dist.ana.JpaTest.main(JpaTest.java:99)
==========================================================

Turning on the statment loging in the database results in the following
output being generated in the postgres log
===========================================================
LOG:  00000: execute <unnamed>: select placedto0_.place_id as place1_3_,
placedto0_.place_name as place2_3_, placedto0_.os_grid as os3_3_,
placedto0_.os_easting as os4_3_, placedto0_.os_northing as os5_3_,
placedto0_.os_ngr as os6_3_, placedto0_.parish_id as parish7_3_,
placedto0_.number_manors as number8_3_, placedto0_.manor as manor3_,
placedto0_.port as port3_, placedto0_.castle as castle3_, placedto0_.road
as road3_, placedto0_.town as town3_, placedto0_.river as river3_,
placedto0_.one as one3_, placedto0_.zero as zero3_, placedto0_.display as
display3_, placedto0_.buffercircle as bufferc18_3_, placedto0_.hundred_id
as hundred19_3_, placedto0_.lathe_id as lathe20_3_ from public.place_table
placedto0_ where x(placedto0_.os_ngr)>0 and distance(placedto0_.os_ngr,
$1)<$2
DETAIL:  parameters: $1 =
'\254\355\000\005sr\000\021org.postgis.Point\000\000\000\000\000\000\001\000\002\000\004D\000\001mD\000\001xD\000\001yD\000\001zxr\000\024org.postgis.Geometry\000\000\000\000\000\000\001\000\002\000\004I\000\011dimensionZ\000\013haveMeasureI\000\004sridI\000\004typexp\000\000\000\002\000\000\000l4\000\000\000\001\000\000\000\000\000\000\000\000A#I\200\000\000\000\000A\0016@\000\000\000\000\000\000\000\000\000\000\000\000',
$2 = '10000'
LOCATION:  exec_execute_message, postgres.c:1820
ERROR:  XX000: parse error - invalid geometry
===================================================

The XX000 define an internal error.  I suspect that copying the Geomtry
type in to hibernate is the route of the problem.  To use a Geomtry data
structure with hibernate in a query requires the use.

@Type(type = "org.postgis.hibernate.GeometryType")
@Column(name="os_ngr", columnDefinition="geometry")

My question is, is something simalir required for the use of
theQuery.setParameter("geom",thePlace.getOsNgr()) ? statment?

Is suspect that hibernate is doing sometime of object serilsation on a
datatype that it does not correctly understand and getting it wrong.

Any suggestions?


Dave.






-- 
Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of the
Pinan Software