[postgis-users] problem with subselect in hql

[Mikkel Troest]

Right, I figured it out. Can't do subselcts in from claus with hql anyway.

Bruce Rindahl's answer to Flavio Perri (subject: Degrees of a linestring 
segment) pointed me in the right direction:
my working result, based on that, is:

select spatial.Length2d_spheroid( spatial.Makeline( pstart.geometry, 
pend.geometry  )  , 'SPHEROID[\"WGS_1984\", 6378137, 298.257223563]' ) 
from RPosition pstart, RPosition as pend where pstart = :pstart and pend 
= :pend

in other words:

select functionA(functionB(alias1, alias2), additionalArguments) from 
myTable alias1, myTable as alias2 where alias1 = ? AND alias2 = ?

Sorry for the inconveinience!

:O) Mikkle

Mikkel Troest wrote:
> 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.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
> ----------
> This mail was scanned for viruses at mail.mikkle.dk by ClamAV (tm) and 
> found to be virus free.
>





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