mysql 经纬度范围_根据经纬度查询最近距离，mysql查询经纬度附近范围

public class Test{

private static List ilist = new ArrayList();

public static void main(String[] args) {

Test test3 = new Test();

Double localDouble = 0.0;

//定义一个二维数组存放经纬度

Double[][] doubles = {

{ 22.6036906766, 113.8793209706 }, //雍华源

{ 22.5205569549, 113.9394272419 }, //西海湾花园

{ 22.6494305358, 114.0411629507 }, //世纪春城4期

{ 22.5255080247,114.0384880750 }, //金海湾花园

{ 22.5246432654,114.0720634923 }, //金港豪庭

{ 22.5963291708,113.9689558477 }, //得意名居

{ 22.5509638661,113.9358124450 }, //麒麟花园A区西门

{ 22.5509638661,113.9358124450 }, //麒麟花园A区北门

{ 22.5254496086,114.0555439122 }, //裕康时尚名居

{ 22.7789489191,114.3063672776 }, //桑泰丹华园

{ 22.5240537775,114.0641924822 }, //皇庭彩园

{ 22.5141408858,114.0624887496 } }; //城市3米6

//门店的经纬度

Double[] initlocal = {22.539899298946577,113.95296375395431 };

for (int i = 0; i < doubles.length; i++) {

System.out.println("doubles.length==============="+doubles.length);

System.out.println("(" + doubles[i][0] + "," + doubles[i][1] + ")");

Double z = test3.getDistance(doubles[i][0], doubles[i][1],initlocal[0], initlocal[1]);

System.out.println(z);

//获取最短距离后把经纬度和距离存放到对象中

LocalAddress localaddress = new LocalAddress(doubles[i][0], doubles[i][1], z);

//将对象用list保存

ilist.add(localaddress);

}

List shotlocal = getLocalList();

Double lat=shotlocal.get(0).getLat();

Double lon= shotlocal.get(0).getLon();

localDouble = shotlocal.get(0).getDistance();

System.err.println("最近的距离：" + localDouble + "。对应的经纬是：" +"(" + lat + "," + lon + ")");

}

/*第一种求最近距离 ===================================================*/

/**

* 获取最短的距离

* @param lat

* @param lon

* @param initlat

* @param initlon

* @return

*/

public Double GetShotLocal(Double lat, Double lon, Double initlat,Double initlon) {

Double x = (initlat - lat) * (initlat - lat);

Double y = (initlon - lon) * (initlon - lon);

Double z = Math.sqrt(x + y);

return z;

}

/**

* 对List 进行排序

* @return

*/

public static List getLocalList() {

Collections.sort(ilist, new Comparator() {

@Override

public int compare(LocalAddress arg0, LocalAddress arg1) {

Double double1 = arg0.getDistance();

Double double2 = arg1.getDistance();

if(double1>double2){

return 1;

}else if (double1 == double2) {

return 0;

}else {

return -1;

}

}

});

return ilist;

}

/* 第二种求最近距离 ==================================================================*/

private static final double EARTH_RADIUS = 6378.137;//地球半径,单位千米

private static double rad(double d)

{

return d * Math.PI / 180.0;

}

/**

*

* @param lat1 第一个纬度

* @param lng1 第一个经度

* @param lat2 第二个纬度

* @param lng2 第二个经度

* @return 两个经纬度的距离

*/

public static double getDistance(double lat1,double lng1,double lat2,double lng2)

{

double radLat1 = rad(lat1);

double radLat2 = rad(lat2);

double a = radLat1 - radLat2;

double b = rad(lng1) - rad(lng2);

double s = 2 * Math.asin(Math.sqrt(Math.pow(Math.sin(a/2),2) +

Math.cos(radLat1)*Math.cos(radLat2)*Math.pow(Math.sin(b/2),2)));

s = s * EARTH_RADIUS;

s = Math.round(s * 10000) / 10000;

return s;

}

}

entity

public class LocalAddress {

private Double lat; //纬度

private Double lon; //经度

private Double distance; //最近距离

public LocalAddress() {

// TODO Auto-generated constructor stub

}

public LocalAddress(Double lat, Double lon, Double distance) {

super();

this.lat = lat;

this.lon = lon;

this.distance = distance;

}

}

SELECT

*,(

6371 * acos (

cos ( radians(#{lat}) )

* cos( radians( lat ) )

* cos( radians( lng ) - radians(#{lng}) )

+ sin ( radians(#{lat}) )

* sin( radians( lat ) )

)

) AS distance

FROM machine

HAVING 3 > distance

ORDER BY distance

LIMIT 0 , 20;