JPQL按纬度和经度获取最近的记录
我有具有纬度和经度的实体，我想选择最接近给定点的实体。
我发现这个示例看起来可行，
SELECT *,
SQRT(POW((69.1 * (locations.latitude - 27.950898)) , 2 ) +
POW((53 * (locations.longitude - -82.461517)), 2)) AS distance
FROM locations
ORDER BY distance ASC
LIMIT 5
但我更喜欢使用 JPQL 来完成，因为我有很多其他联接等，感觉在 JPQL 而不是本机查询中更容易完成。
当我尝试这样的事情时，JPA 正在抱怨 SQRT 令牌。
SELECT DISTINCT p, SQRT(....) AS distance, FROM Place p .... ORDER BY distance ASC
任何人都知道如何编写这样的查询，或者也许是另一种更好的方法？
谢谢！ /奥斯卡
I'm have entities with latitude and longitude and I would like to select those nearest to a given point.
I found this example that looks like it would work
SELECT *,
SQRT(POW((69.1 * (locations.latitude - 27.950898)) , 2 ) +
POW((53 * (locations.longitude - -82.461517)), 2)) AS distance
FROM locations
ORDER BY distance ASC
LIMIT 5
But I would prefer to do it with JPQL since I have a bunch of other joins etc that feels easier to do in JPQL instead of native query.
When I try something like this the JPA is complaining about the SQRT token.
SELECT DISTINCT p, SQRT(....) AS distance, FROM Place p .... ORDER BY distance ASC
Anyone has knows how to write a query like this, or maybe another better approach?
Thanks!
/Oskar
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
据我通过研究和可能的反复试验可知，JPQL 根本不具备处理这种情况的能力。我必须将我的查询重写为本机查询。
As far as I can tell from researching this and may trial and errors JPQL is simply not equipped to handle this scenario. I had to rewrite my queries as native.
JPQL 语言参考 指出：
因此，您没有
POW。您可以安全地使用本机查询。
不过，我更喜欢的方法是在不进行计算的情况下进行查询，然后在代码中对结果进行计算。这不应该是一个很大的性能风险。
The JPQL language reference says that:
So, you don't have
POW.You can safely use a native query.
The approach I'd prefer, though, is to make the query without the calculations and then make the calculation on the result in your code. It shouldn't be a big performance hazard.