JavaWeb日记——SQL和hibernate实现经纬度求距离并排序

来源：互联网发布：淘宝的虚拟试衣间入口编辑：程序博客网时间：2024/06/05 08:57

我们在做定位的时候，App端获取到定位，经度和维度都是Double，获取附近的人，就要根据用户经纬度求出附近的人的距离，并按降序排序。

以一个user表为例
表的结构如下

CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `latitude` double DEFAULT NULL,  `longitude` double DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='latin1_swedish_ci'

最原始的sql语句

select *,(2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*(111.86141967773438-latitude)/360),2)+COS(PI()*33.07078170776367/180)* COS(latitude * PI()/180)*POW(SIN(PI()*(33.07078170776367-longitude)/360),2)))) as juli from user  order by juli asc

其中111.86141967773438代表参考点维度，33.07078170776367代表参考点经度

若是hibernate语句

select new com.ima.dto.NearByUser(u.id,u.latitude,u.longitude,(2 * 6378.137* ASIN(SQRT(POW(SIN(PI()*(:latitude-latitude)/360),2)+COS(PI()*:longitude/180)* COS(latitude * PI()/180)*POW(SIN(PI()*(:longitude-longitude)/360),2)))) as juli) from User u order by juli asc

:latitude是传入维度，:longtitude是传入经度

NearByUser的代码

public class NearByUser {    private Long id;    private Double latitude;    private Double longitude;    private Double distance;    public NearByUser(Long id, Double latitude, Double longitude, Double distance) {        this.id = id;        this.latitude = latitude;        this.longitude = longitude;        this.distance = distance;    }    public Long getId() {        return id;    }    public void setId(Long id) {        this.id = id;    }    public Double getLatitude() {        return latitude;    }    public void setLatitude(Double latitude) {        this.latitude = latitude;    }    public Double getLongitude() {        return longitude;    }    public void setLongitude(Double longitude) {        this.longitude = longitude;    }    public Double getDistance() {        return distance;    }    public void setDistance(Double distance) {        this.distance = distance;    }}

User代码

public class User {    private Long id;    private Double latitude;    private Double longitude;    public User(Long id, Double latitude, Double longitude) {        this.id = id;        this.latitude = latitude;        this.longitude = longitude;    }    public Long getId() {        return id;    }    public void setId(Long id) {        this.id = id;    }    public Double getLatitude() {        return latitude;    }    public void setLatitude(Double latitude) {        this.latitude = latitude;    }    public Double getLongitude() {        return longitude;    }    public void setLongitude(Double longitude) {        this.longitude = longitude;    }}

0 0