Sleep Mode Limitations / Criteria

Question

Hi, I tried to develop a better way to do this today to no avail.

What I ideally would like to do is create an alias distance calculated using the SQL formula below (although I am open to other ways of calculating the distance, it was exactly as it seemed to be easier)

As soon as I have this alias, I want to be able to use it in restriction mode to find everything that is at a certain distance.

I would also like to be able to sort by distance.

This is part of the more stringent search criteria that are being created, so I would ideally like to continue using the criteria. (I am already limiting the range of Lat and Long values ​​to make distance calculation required for smaller fields.

Criteria criteria = session.createCriteria(Activity.class)
       .createAlias("activityLocations", "actloc")
       .createAlias("actloc.location", "location")
       .createAlias("location.address", "addr");
       criteria.add((Restrictions.and(Restrictions.between("addr.latitude", latmin,     
       latmax),Restrictions.between("addr.longitude", truelongmin, truelongmax))));


       String sql =  "SQRT( POW( 69.1 * ( addr3_.latitude - " + point[1]     
       +" ) , 2 ) + POW( 69.1 * ( "+point[0] +" - addr3_.longitude ) * COS( addr3_.latitude /"
       +" 57.3 ) , 2 ) )  < "+distance;    
       criteria.add(Restrictions.sqlRestriction(sql));

addr3_ sql-, , Hibernate ( , , , !)

Answer 1

SQL {alias}. , " ", location.address:

Criteria criteria = session.createCriteria(Activity.class) 
       .createAlias("activityLocations", "actloc") 
       .createAlias("actloc.location", "location");

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));