Spring查询：带有可分页的Haversine公式

Question

我正在尝试使用Haversine公式在一个Spring Data JPA Query中找到接近于某个位置的实体，并使用Pageable进行操作，但我还没有完成它。

我的第一个尝试看起来像这样:

    @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子句中使用别名。堆栈跟踪中的SQL看起来像这样

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

因此，在where子句中别名不会被替换。使用“col_1_0_”（不带引号）代替dist也无效。

根据这个SO答案，至少MySQL是内向外解释的，在Where子句中使用别名是不可能的。一个建议的解决方案是使用HAVING代替WHERE，但即使在HAVING子句中，别名也无法被解析。

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

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

    @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 的第二种方法。

- krinklesaurus

Answer 1

在 Neil Stockton 的帮助下，我决定继续使用非本地查询，在 WHERE 和 ORDER BY 子句中同时使用 Haversine 公式，以便仍然可以使用 Spring 的分页功能。我的最终解决方案如下：

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个实体，并且需要大约8秒钟来查找在1,000,000个实体中最接近的前10个实体，对我目前来说还可以。如果我优化查询，我会在这里发布。

- krinklesaurus

---

你的例子中，m.latitude 不应该改为 e.latitude 吗？非常感谢！ - Cir0X

Answer 2

JPQL不允许在WHERE和HAVING子句中使用“结果别名”，它们只能用于ORDER子句。同样，RADIANS、COS、ACOS、SIN、ASIN等的使用是非便携式JPQL；一些实现可能支持它们（我知道DataNucleus JPA支持它们，看起来你也有Hibernate），但不能保证。

只需使用NativeQuery并推入所需的SQL即可。您会失去(RDBMS)可移植性，但是对于您上面尝试的内容（对于JPA提供程序或RDBMS），您本来就没有这个。

- Neil Stockton

---

谢谢Neil！我不知道RADIANS，COS，ACOS等已经与JPQL规范不兼容。正如你建议的那样，我使用Hibernate作为JPA提供程序，所以语句到目前为止都可以工作。现在我决定坚持使用非本地查询，并在WHERE和ORDER子句中使用Haversine公式，因为使用本地查询会失去Spring的分页功能。我测试了10万个实体的性能，到目前为止还可以。 - krinklesaurus