如何从按距离排序的JPA实体获得结果？ [英] How can I get results from a JPA entity ordered by distance?

问题描述

我目前正在编写一个移动应用程序，用户必须从列表中选择一个位置。所有位置都使用JPA从Play应用程序存储在Postgres数据库中。

I am currently writing a mobile application where the user has to pick a location from a list. All the locations are stored in a Postgres database using JPA from a Play app.

我想要做的是获取应用中的用户位置，然后请求获取离该用户最近的前20或50个位置。

What I would like to do is get the users location in the app, and then make a request to get the first 20 or 50 locations nearest to that user.

如果我使用自己的数据结构，我会使用KD-Tree。但是，我对JPA / Play / PostgreSQL很新，所以我不确定如何手动处理数据持久性。

If I was using my own data structure for this, I would use a KD-Tree. However, I am very new to JPA/Play/PostgreSQL so I am unsure how to handle data persistance manually.

我唯一能想到的就是我目前的知识要查看每个位置并确定它的距离，但是在如此巨大的数据库上这将是非常慢的。

The only thing I can think of with my current knowledge would be to look at each location and determine it's distance but that would be incredibly slow on such a huge database.

我可以运行一个查询说给我一个X第一个结果按照这个纬度和经度的距离排序？

Is there a query I can run to say "give me the X first results ordered by distance from this latitude and longitude?

编辑：我正在使用Heroku，因为应用程序处于开发的早期阶段，我宁愿不如果你想在你的应用程序中使用PostGIS，必须支付Heroku所需的200美元/月。

I am using Heroku and since the application is in the early stages of development, I would prefer to not have to pay the $200/month Heroku requires if you want to use PostGIS with your app.

推荐答案

你真的不想为此推出自己的数据结构，但幸运的是你正在使用PostgreSQL，所以你很幸运。使用 PostGIS 。它比你在合理的amo中构建的任何东西快几个数量级没时间了。

You really don't want to be rolling your own data structure for this, but fortunately you're using PostgreSQL, so you're in luck. Use PostGIS. It's going to be orders of magnitude faster than anything you can build in a reasonable amount of time.

这篇关于如何从按距离排序的JPA实体获得结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！