0

I have an SQL statement:

SELECT x.SPEED, (6371 * acos(cos(radians(16.65555))) 
                 * cos(radians(LATITUDE)) * cos(radians(LONGITUDE) - radians(54.55555)) 
                 + sin(radians(16.65555)) * sin(radians(LATITUDE))) AS dist 
FROM MY_TABLE x 
HAVING dist <= 50 
ORDER BY dist

How can I put this into a NamedQuery within a Java entity class in a way that the calculated value is set into this entity as a transient attribute called distance?

For the time being I have tried this:

SELECT vsle, 
       (:distance_unit * FUNC('acos', FUNC('cos', FUNC('radians', :latitude)) * 
       FUNC('cos', FUNC('radians', vsle.geoPosition.latitude)) * 
       FUNC('cos', FUNC('radians', vsle.geoPosition.longitude) - FUNC('radians', :longitude)) + 
       FUNC('sin', FUNC('radians', :latitude)) * FUNC('sin', FUNC('radians', vsle.geoPosition.latitude)) ) ) 
AS distance 
FROM VehicleStateLogEntity vsle 
WHERE (distance <= :radius)

but this fails with a java.lang.NullPointerException. It seems as if the calculated value cannot be accessed via its assigned name distance.

Cœur
  • 37,241
  • 25
  • 195
  • 267
du-it
  • 2,561
  • 8
  • 42
  • 80

1 Answers1

3

I found a way to build the JPQL valid:

SELECT vsle, 
   (:distance_unit * FUNC('acos', FUNC('cos', FUNC('radians', :latitude)) * 
   FUNC('cos', FUNC('radians', vsle.geoPosition.latitude)) * 
   FUNC('cos', FUNC('radians', vsle.geoPosition.longitude) - FUNC('radians', :longitude)) + 
   FUNC('sin', FUNC('radians', :latitude)) * FUNC('sin', FUNC('radians', vsle.geoPosition.latitude)) ) ) AS distance 
FROM VehicleStateLogEntity vsle 
WHERE ((:distance_unit * FUNC('acos', FUNC('cos', FUNC('radians', :latitude)) * 
   FUNC('cos', FUNC('radians', vsle.geoPosition.latitude)) * 
   FUNC('cos', FUNC('radians', vsle.geoPosition.longitude) - FUNC('radians', :longitude)) + 
   FUNC('sin', FUNC('radians', :latitude)) * FUNC('sin', FUNC('radians', vsle.geoPosition.latitude)) ) )<= :radius) 
ORDER BY distance

It's weird that I can't access the alias distance in the SELECT-clause but I can in the ORDER BY-clause.

du-it
  • 2,561
  • 8
  • 42
  • 80
  • Worked like a charm. awesome. Note. distance_unit should be 111.045 if you want to give your distances in kilometers and 69.0 if you want them in statute miles – Sacky San Aug 26 '17 at 15:26
  • Correction. distance_unit should be 6371 if you want to give your distances in kilometers and 3959 if you want them in statute miles. The detailed documentation by google maps on this is at https://developers.google.com/maps/solutions/store-locator/clothing-store-locator#outputting-data-as-xml-using-php – Sacky San Aug 27 '17 at 22:28