multiple use of expression via jpql alias keyword

Question

I'm using spring data with a postgresql server and i want to perform some GPS-data range queries. This means, given a coordinate i compute on the fly the distance from the entry to the given point and check for a certain range.

Since i also want to order my data regarding the distance and additionally i want to retrieve the actual distance too, in sql i would use the AS keyword to compute the expression only once and then use this auxiliary expression in the where and the order by part. However, so far I haven't yet figured out how to do this in jqpl. So my query should do something like this:

SELECT NEW Result(p, <distance-expression>) FROM MyModel p where <distance-expression> <= :rangeParam order by <distance-expression>

however, i'm afraid that the will be evaluated more than once for each entry and so this will have a negative impact on the runtime/response time of the query.

Is there any way in jqpl to use the AS keyword to avoid the multiple evaluation of
<distance-expression>?

Best regards

Answer 1

A native query with an inner view should get the job done. Assuming class Location(id, latitude, longitude) and the Haversine formula for finding distances between points on great circles, the following repository method declaration with a custom native query should be sufficient:

@Query(nativeQuery = true
  , value = "SELECT "
          + "  r.id "
          + "  , r.latitude "
          + "  , r.longitude "
          + "FROM "
          + "  (SELECT "
          + "    l.id          AS id "
          + "    , l.latitude  AS latitude "
          + "    , l.longitude AS longitude "
          + "    , 2 * 6371 * ASIN(SQRT(POWER(SIN(RADIANS((l.latitude - ?1) / 2)), 2) + COS(RADIANS(l.latitude))*COS(RADIANS(?1))*POWER(SIN(RADIANS((l.longitude - ?2) / 2)), 2))) AS distance "
          + "  FROM "
          + "    location l) AS r "
          + "WHERE "
          + "  r.distance < ?3")
List<Location> findAllByProximity(BigDecimal latitude
                               , BigDecimal longitude
                               , BigDecimal distance);

Sample available on Github as an example (metric units assumed).

---

Note: The reason behind using a native query in the example as opposed to JPQL is the lack of support for trigonometric functions in JPQL. In cases where the expression is simpler and can be coded using native JPQL functions, the native query can be replaced with a JPA query.