春季查询:具有可分页的Haversine公式 [英] Spring Query: Haversine formula with pageable

问题描述

我正在尝试使用 Haversine公式查找靠近Spring Data JPA查询 ="noreferrer">可分页，但我没有完成.

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

，但是它失败了，因为Spring/JPA似乎无法在where子句中使用别名. stacktrace中的SQL看起来像这样

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

因此，不会替换where子句中的别名.使用"col_1_0 _"(不带")代替dist也不起作用.

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

根据此 SO答案，至少MySQL由内而外解释，并且无法在Where子句中使用别名.建议的解决方案是使用HAVING而不是WHERE，但在HAVING子句中，别名也无法解析.

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.

我知道我可以将Haversine公式移到where子句中，但是我仍然需要在Order By子句中使用它，并且我认为使用Order By子句中相同的长Haversine公式(从中选择)会降低性能几十万个实体.

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.

然后我尝试手动创建查询，但是我不知道如何将Pageable应用于此:

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

因此，我要么需要使用@Query的第一种方法(我希望使用)，要么需要使用Pageable的第二种方法

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

推荐答案

在Neil Stockton的帮助下，我决定在WHERE和ORDER BY子句中都使用Haversine公式坚持使用非本地查询，因此我仍然可以使用Spring的分页功能.我的最终解决方案如下所示:

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

对于100.000个实体，这需要大约585毫秒才能找到给定位置的前10个最接近的实体，而在1.000.000个实体中找到前10个最接近的实体大约需要8 s，这对我来说现在是可以的.如果优化查询，我会在这里发布.

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.

这篇关于春季查询:具有可分页的Haversine公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！