找到距所选点特定距离的所有地址的最佳方法是什么？ [英] What is the best approach to find all addresses that are in a specific distance to the selected point

问题描述

我正在开发一个应用程序，用于显示位于特定位置的地址。我知道如何找到两点之间的距离，但问题是我不确定在性能方面什么是最好的方法。

一种方法是检索所有地址并逐一检查它们到后端的选定地址，但是有什么方法可以最小化我检索的项目数从数据库，而不是使用内存？最好的方法是做什么和怎么做？

想象一下，我有300,000条记录是否必须全部检索并计算它们到选定点的距离？正如詹姆斯建议我可以在不同地区记录并计算距离，那么哪种方法可以很好地遵循，通过查询或Java进行距离计算？

  public class Address {
 long ID; 
双纬度; 
双经度; 
 .. 
} 
  

Calculation

pre $ public static double distFrom（double lat1，double lng1，double lat2，double lng2）{
double earthRadius = 3958.75;
double dLat = Math.toRadians（lat2-lat1）;
double dLng = Math.toRadians（lng2-lng1）;
double sindLat = Math.sin（dLat / 2）;
double sindLng = Math.sin（dLng / 2）;
double a = Math.pow（sindLat，2）+ Math.pow（sindLng，2）
* Math.cos（Math.toRadians（lat1））* Math.cos（Math.toRadians（lat2 ））;
double c = 2 * Math.atan2（Math.sqrt（a），Math.sqrt（1-a））;
double dist = earthRadius * c;

return dist;
}


这个问题和这一个提供方法来计算通过MySQL的距离，但哪种方式更好Java或MySQL我很困惑。

纬度和经度的行，我还会在插入时计算以下字段：

• radiansLongitude （ Math.toRadians（longitude） $ li $ sinRadiansLatitude （ Math.sin（Math.toRadians（latitude） $ li code cosRadiansLatitude （ Math.cos（Math.toRadians（latitude）
  
  然后，当我搜索纬度 code> / 经度有问题，我准备好的声明如下：
  
  

    b 
   acos（
   sin（：纬度）* sinRadiansLatitude + 
   cos（：纬度）* cosRadiansLatitude * 
   cos（弧度经度 - ：经度）
  ）* YYYY<：距离
  和l.latitude>：minimumSearchLatitude 
  和l.latitude<：maximumSearchLatitude 
  和l.longitude>：min imumSearchLongitude 
  和l.longitude<：maximumSearchLongitude 
   order by acos（
   sin（：latitude）* sinRadiansLatitude + 
   cos（：latitude）* cosRadiansLatitude * 
   cos（ radiansLongitude  - ：longitude）
  ）* YYYY asc 
    

  其中 YYYY = 3965给你以英里为单位的距离或 yyyy = 6367可用于以km为单位的距离。
  
  

  最后，我使用了 maximumSearchLatitude / maximumSearchLongitude / minimumSearchLongitude / maximumSearchLongitude 参数在数据库执行任何计算之前从结果集中排除大多数点。你可能会也可能不需要这个。如果你确实使用了这些，那么这些参数将取决于你选择的值，因为它取决于你正在搜索的内容。

  数据库中的索引是必要的。
  
  
  

  使用这种方法的好处是，每次只需计算一次就不需要改变的信息，而计算每行每行 radiansLongitude ， sinRadiansLatitude ， cosRadiansLatitude 你执行搜索的时间会非常快。
  
  

  另一种选择是使用地理空间索引，这意味着所有这些都是由数据库为您处理的。我不知道Hibernate和它的整合程度如何。

  
  
  

  免责声明：我看了很长一段时间，而且我不是GIS专家！ / p>
  

  I am developing an application that is supposed to show addresses that are in a specific distance of a location. I know how to find the distance between two points, but the problem is I am not sure what would be the best approach in terms of performance.

  One way is to retrieve all addresses and check them one by one toward the selected address in back-end but is there any way to minimize the number of items that I retrieve from database, rather than using the memory? whats the best approach to do it and how?

  Imagine I have 300,000 records do I have to retrieve them all and calculate their distance to the selected point? As James suggested I can have the records in different regions and calculate the distance, then which method would be good to follow,distance calculation through query or Java?

    public class Address{
      long Id;
      Double latitude;
      Double longitude;
      ..
    }
  

  Calculation

  public static double distFrom(double lat1, double lng1, double lat2, double lng2) {
    double earthRadius = 3958.75;
    double dLat = Math.toRadians(lat2-lat1);
    double dLng = Math.toRadians(lng2-lng1);
    double sindLat = Math.sin(dLat / 2);
    double sindLng = Math.sin(dLng / 2);
    double a = Math.pow(sindLat, 2) + Math.pow(sindLng, 2)
          * Math.cos(Math.toRadians(lat1)) *     Math.cos(Math.toRadians(lat2));
    double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
    double dist = earthRadius * c;
  
    return dist;
  }
  

  This question and this one offer methods to calculate distance through mysql but which way is better Java or mysql I am quite confused.

  解决方案

  When I have implemented this in MySQL (for storing places on an oblate sphere, which is basically what earth is (I assume you're talking about earth!)), I have stored as much pre-calculated information as possible in the database. So, for a row that stores latitude and longitude, I also calculate at insertion time the following fields:

  • radiansLongitude (Math.toRadians(longitude))
  • sinRadiansLatitude (Math.sin(Math.toRadians(latitude))
  • cosRadiansLatitude (Math.cos(Math.toRadians(latitude))

  Then when I search for the places that are within X units of the latitude/longitude in question, my prepared statement is as follows:

  from Location l where
      acos(
          sin(:latitude) * sinRadiansLatitude + 
          cos(:latitude) * cosRadiansLatitude * 
          cos(radiansLongitude - :longitude) 
          ) * YYYY < :distance
      and l.latitude>:minimumSearchLatitude
      and l.latitude<:maximumSearchLatitude 
      and l.longitude>:minimumSearchLongitude 
      and l.longitude<:maximumSearchLongitude 
      order by acos(
                  sin(:latitude) * sinRadiansLatitude + 
                  cos(:latitude) * cosRadiansLatitude * 
                  cos(radiansLongitude - :longitude)  
          ) * YYYY asc
  

  Where YYYY = 3965 gives you distances in miles or YYYY = 6367 can be used for distances in km.

  Finally, I have used the maximumSearchLatitude / maximumSearchLongitude / minimumSearchLongitude / maximumSearchLongitude parameters to exclude the majority of points from the resultset before the database has to perform any calculations. You may or may not need this. If you do use this, it'll be up to you what values you choose for these parameters, as it will depend on what you're searching.

  Obviously judicious applications of indexes in the database will be necessary.

  The benefit of using this approach is that the information which never changes but is needed every time is only calculated once, whereas calculating the values of radiansLongitude, sinRadiansLatitude, cosRadiansLatitude for every row every time you perform a search is going to get very expensive very fast.

  The other option is to use a geospatial index, which means that all of this is taken handled for you by the database. I don't know how well Hibernate integrates with that though.

  Disclaimer: it's a long time since I looked at this, and I'm not a GIS expert!

  这篇关于找到距所选点特定距离的所有地址的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！