[postgis-users] problem with subselect in hql

[Mikkel Troest]

Hi,
I have a newbie question, which I hope someone out there can help me with:

I have a spatial table, positions:

_____________________________________________________
| position_id (bigint) | position_lonlat (geometry) |- remaining columns 
omitted...
-----------------------------------------------------

I can run the following sql query directly on the PostGreSQL DB:
  select length2d_spheroid(theline, 'SPHEROID[\"WGS_1984\", 6378137, 
298.257223563]') from (  select makeline(p1.position_lonlat, 
p2.position_lonlat) as theline  from positions p1, positions as p2 where 
p1.position_id = 18 and p2.position_id = 19 ) theresult;

- It may seem mad, but my application needs to be able to get a 
linestring between two arbitrary points.

-- The query does this and returns the length according to wgs84.

Right.
I've added the following to Norman Barker's PostGISDialect.java:
  registerFunction( PostGISDialect.NAMESPACE + "length2d_spheroid", new 
StandardSQLFunction("length2d_spheroid", Hibernate.DOUBLE) );
  registerFunction( PostGISDialect.NAMESPACE + "makeline", new 
StandardSQLFunction("makeline", Hibernate.custom(GeometryType.class) ) );
to be able to support the makeline and length2d_spheroid functions.

I try to translate this sql into hql:
  Query lslen2dsph = session.createQuery("select 
spatial.Length2d_spheroid(theline, 'SPHEROID[\"WGS_1984\", 6378137, 
298.257223563]') from (select spatial.Makeline(pstart.geometry, 
pend.geometry) as theline  from RPosition pstart, RPosition as pend 
where pstart = :pstart and pend = :pend)  theresult");
  lslen2dsph.setParameter("pstart", pstart);
  lslen2dsph.setParameter("pend", pend);
  //Note that pstart and pend are Geometries retrieved elsewhere...
  //also note that RPosition is my annotated class - giving the same 
table as "positions" in the sql above.
  //another note: pstart.geometry is the same as p1.position_lonlat above.

When I run the query, I get:
2007-05-01 14:07:06 org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:96: unexpected token: (
Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: 
unexpected token: ( near line 1, column 96 [select 
spatial.Length2d_spheroid(theline, 'SPHEROID["WGS_1984", 6378137, 
298.257223563]') from (select spatial.Makeline(pstart.geometry, 
pend.geometry) as theline  from 
dk.bestikket.gps.server.persistence.RPosition pstart, 
dk.bestikket.gps.server.persistence.RPosition as pend where pstart = 
:pstart and pend = :pend)  theresult]


Can anyone tell me what's wrong with my hql query (lslen2dsph)?

If I do a session.createSQLQuery() I have no problems getting thing run. 
I would very much like, though, to
keep it in hql for consistency's sake, and to avoid confusion of 
table/column names, etc.

Admittedly, both HQL and postgis are very new stuff to me, so I may 
easily be missing some obvoius points.
Please bear with me if that is the case!

Thanks!

:O) Mikkel Troest





----------
This mail was scanned for viruses at mail.mikkle.dk by ClamAV (tm) and found to be virus free.