mysql gps字段类型,在MySQL中快速检索带有GPS坐标的SELECT语句

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.

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;

}

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 ?

解决方案

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