Hi,
I am trying to do something that I think should be fairly simple but so far haven't had any luck.
what I would like to do is to add some sql to the select part of a Criteria object, I want to add SELECT myFunction AS distance so that I can later order by distance.
The function I want to insert is :-
Code:
(
SQRT( POW( 69.1 * ( addr.latitude - 51.3814282 ) , 2 ) + POW( 69.1 * ( - 2.3574537 - addr.longitude ) * COS( addr.latitude / 57.3 ) , 2 ) )
) AS distance
My current criteria looks like (I already use the equation to limit the results by distance - I want to be able to sort by distance as well though...):-
Code:
Criteria addr = criteria.createCriteria("location.address");
addr.add((Restrictions.and(Restrictions.between("latitude", latmin,
latmax), Restrictions.between("longitude", truelongmin, truelongmax))));
String sql = "SQRT( POW( 69.1 * ( {alias}.latitude - " + point[1]
+" ) , 2 ) + POW( 69.1 * ( "+point[0] +" - {alias}.longitude ) * COS( {alias}.latitude /"
+" 57.3 ) , 2 ) ) < "+distance;
addr.add(Restrictions.sqlRestriction(sql));
Any suggestions would be appreciated.
Cheers,
Rob
Login
FAQ
Search