Spring Query: Haversine formula with pageable

Question

I'm trying to use the Haversine formula to find entities near to a location in a Spring Data JPA Query with Pageable but I dont get it done.

My first approach looks like this

    @Query("SELECT m, (6371 * acos(cos(radians(:latitude)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(m.latitude)))) as dist FROM Entity m WHERE dist < :distance ORDER BY dist DESC")
    public List<Entity> findEntitiesByLocation(@Param("latitude") final double latitude, @Param("longitude") final double longitude, @Param("distance") final double distance, Pageable pageable);

but it fails as Spring/JPA seems to be not capable of using aliases in the where clause. The SQL in the stacktrace looks like this

select (...),(haversine) as col_1_0_ where dist<? order by col_1_0_ DESC

so the alias in the where clause is not replaced. Using "col_1_0_" (without the ") instead of dist also does not work.

According to this SO Answer, at least MySQL is interpreted inside out and using aliases in the Where clause is not possible. A suggested solution would be using HAVING instead of WHERE, but also in a HAVING clause, the alias is not resolved.

I know that I can move the Haversine Formula into the where clause, but I still need it in the Order By clause and I think it might slow down performance using the same long Haversine formula in the Order By clause as I select from a few hundred thousands entities.

I then tried to create the Query manually but I don't know how to apply the Pageable to this:

    @Override
    public List<Entity> findEntitiesByLocation(final double latitude, final double longitude, final double distance, Pageable pageable) {
    final javax.persistence.Query query = this.entityManager.createQuery(SELECT_ENTITES_BY_DISTANCE);

    query.setParameter("latitude", latitude);
    query.setParameter("longitude", longitude);
    query.setParameter("distance", distance);

    final List<Entity> entities = query.getResultList();
    // order by distance
    entities .sort(new EntityDistanceComparator(latitude, longitude));

    return entities ;
}

So, I either need the first approach with @Query to work (which I'd prefer) OR the second approach with Pageable

Answer 1

With Neil Stockton's help I decided to stick with the non native query using the Haversine formula in both the WHERE and ORDER BY clause so I can still use Spring's pagination feature. My final solution looks like this:

static final String HAVERSINE_PART = "(6371 * acos(cos(radians(:latitude)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:longitude)) + sin(radians(:latitude)) * sin(radians(m.latitude))))";

@Query("SELECT m FROM Entity m WHERE "+HAVERSINE_PART+" < :distance ORDER BY "+HAVERSINE_PART+" DESC")
public List<Entity> findEntitiesByLocation(@Param("latitude") final double latitude, @Param("longitude") final double longitude, @Param("distance") final double distance, Pageable pageable);

With 100.000 entities, this takes approximately about 585 ms to find the top 10 nearest entities to a given location and about 8s to find the top 10 nearest entities within 1.000.000 entities, which is ok for me right now. If I optimize the query, I'll post it here.

Answer 2

JPQL doesn't allow "result aliases" in the WHERE, HAVING clauses; they can only be used in the ORDER clause. Similarly the use of RADIANS, COS, ACOS, SIN, ASIN etc is non-portable JPQL; some implementations may support them (I know DataNucleus JPA does, and looks like you have Hibernate also) but not guaranteed.

Just use a NativeQuery and push in whatever SQL you require. You lose (RDBMS) portability, but then you didn't have that with what you were attempting above (for JPA provider or RDBMS).