在MySQL中快速检索带有GPS坐标的SELECT语句 [英] fast retrieval of SELECT statement with GPS coordinates in MySQL

问题描述

我在MySQL db中有一个表格，其中的两列是给定地理点的纬度和经度。它被定义为Float（2,6）。
我想从给定点中只选择特定半径内的记录。

I have a table in MySQL db which two of it's columns are the latitude and the longitude of a given geo-point. it's defined as Float(2,6). I want to select only the records within a specific radius from a given point.

我在Java中找到以下代码，它检查到geo

I found the following code in Java, that checks the distance between to geo-points:

public class Location {

private int latitudeE6;
private int longitudeE6;
    ...
}

public static double CalculateDistance(Location StartP, Location EndP) {

      double lat1 = StartP.getLatitudeE6()/1E6;
      double lat2 = EndP.getLatitudeE6()/1E6; 
      double lon1 = StartP.getLongitudeE6()/1E6;     
      double lon2 = EndP.getLongitudeE6()/1E6;
      double dLat = Math.toRadians(lat2-lat1); 
      double dLon = Math.toRadians(lon2-lon1);   
      double a = Math.sin(dLat/2) * Math.sin(dLat/2) + 
      Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2)) *
      Math.sin(dLon/2) * Math.sin(dLon/2); 
      double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
      return earthradius * c;
   }

目前，我在没有位置的情况下执行SELECT操作，然后重新执行结果集并检查它是否在给定的半径内。 （用Java）。
这当然不是最优雅，最有效的方式。
你能想出更好的方法吗？

Currently, I'm doing SELECT without the location, and then I go over the result set and checks if it's in the radius given. (in Java). This is surely not the most elegant, efficient way of doing it. Can you think of a better way ?

推荐答案

下面是描述（我认为）如何完成MySQL试图完成的一个演示：使用MySQL进行地理/空间搜索 a>

Here's a presentation that describes (I think) how to do what you're attempting to accomplish in MySQL: Geo/Spatial Search with MySQL

这篇关于在MySQL中快速检索带有GPS坐标的SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！