I'm simply trying to get the calculated distance back out of the hql below. I've been told formulas (which I can't see working because this is parameterized -- I need to specify a second lat/lon for the distance) and filters. The filter seems like a better way to do the query but it still doesn't help me get the distance back for each result in the set.


Hibernate version:

3.1 beta 2

Mapping documents:

<hibernate-mapping>
<class name="value.Business" table="fc_business" lazy="false">
<id name="id" column="biz_id">
<generator class="native"/>
</id>
<property name="name" column="biz_name"/>
<property name="currentRating" column="current_rating"/>
<property name="numRatings" column="num_ratings"/>
<property name="numPositive" column="num_positive"/>
<property name="numNegative" column="num_negative"/>
<property name="statusCode" column="status_cd"/>
<property name="yearStarted">
<column name="started_yr" not-null="false"/>
</property>
<many-to-one name="address" class="value.Address" column="address_id" cascade="all"/>
</class>
</hibernate-mapping>


<hibernate-mapping>
<class name="value.Address" table="fc_address" lazy="false">
<id name="id" column="address_id">
<generator class="native"/>
</id>
<property name="latitude"/>
<property name="longitude"/>
<property name="line1"/>
<property name="line2"/>
<property name="city"/>
<property name="state"/>
<property name="zipcode"/>
<property name="zip4"/>
<property name="phone1"/>
<property name="phone2"/>
<property name="fax"/>
<property name="url"/>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

String hql = "from Business biz where biz.name like :criteria and "+
" acos( " +
" sin(:lat) * sin(biz.address.latitude) + " +
" cos(:lat) * cos(biz.address.latitude) * " +
" cos(biz.address.longitude - :lon) " +
" ) * 3956 < :distance order by biz.currentRating desc";

Query query = session.createQuery(hql)
.setInteger("distance", radius)
.setString("criteria", likeCriteria)
.setDouble("lat", targetZip.getLatitude())
.setDouble("lon", targetZip.getLongitude())
.setFirstResult(offset)
.setMaxResults(RESULTS_PER_PAGE);

Name and version of the database you are using:

mysql 5

After struggling most of the day I've come up with this. Of course now it seems that it probably should have been clear previously.

String hql = " select acos( " +
" sin(:lat) * sin(biz.address.latitude/57.29) + " +
" cos(:lat) * cos(biz.address.latitude/57.29) * " +
" cos(biz.address.longitude/57.29 - :lon) " +
" ) * 3956 as distance, biz " +
"from Business biz " +
"where biz.name like :criteria " +
// "having distance < :radius ";
" order by distance asc";

Query query = session.createQuery(hql)
.setInteger("radius", 5)
.setString("criteria", "%auto%")
.setDouble("lat", lat)
.setDouble("lon", lon).
setMaxResults(10);


List list = query.list();
Iterator results = list.iterator();
for (int i = 0; i < 5; i++)
{
Object[] arr = (Object[])results.next();
Double dbdist = (Double)arr[0];
Business biz = (Business)arr[1];
}

The only problem I have now is the "having distance < :radius" ... hibernate is throwing this exception. I got a very similar query to run fine in mysql. Trace below:

Nov 15, 2005 10:03:28 PM org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: line 1:241: unexpected token: having
E
Time: 2.648
There was 1 error:
1) testHqlSearch(HibernateUnitTest)org.hibernate.hql.ast.QuerySyntaxException: unexpected token: having near line 1, column 241 [ select acos( sin(:lat) * sin(biz.address.latitude/57.29) + cos(:lat) * cos(biz.address.latitude/57.29) * cos(biz.address.longitude/57.29 - :lon) ) * 3956 as distance, biz from com.chaos.value.Business biz where biz.name like :criteria having distance < :radius order by distance asc]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:240)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:151)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:101)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:468)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1025)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:975)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.chaos.unittest.HibernateUnitTest.testHqlSearch(HibernateUnitTest.java:464)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
Caused by: line 1:241: unexpected token: having
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:743)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:296)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:159)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:234)
... 22 more