目录创建数据表插入数据函数st_distance_sphere 和 st_distance查询距离成都最近的5个地方创建数据表create table data_point
(ID int auto_increment primary key,city varchar(64) not null,pt geometry not null
)
插入数据INSERT INTO data_point (city, pt) VALUES (‘成都‘,ST_GeomFromText(‘POINT(104.040 30.400)‘));
INSERT INTO data_point (city, pt) VALUES (‘巴中‘,ST_GeomFromText(‘POINT(106.43 31.51...
1、新建表DROPTABLEIFEXISTS `customer`;
CREATETABLE `customer` (`id` int(11) unsigned NOTNULL auto_increment COMMENT ‘自增主键‘,`name` varchar(50) NOTNULL COMMENT ‘名称‘,`lon` double(9,6) NOTNULL COMMENT ‘经度‘,`lat` double(8,6) NOTNULL COMMENT ‘纬度‘,PRIMARYKEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8DEFAULT CHARSET=utf8 COMMENT=‘商户表‘; 2、插入数据INSERTINTO `customer` VALUES (‘1‘, ‘...
DELIMITER $$DROP FUNCTION IF EXISTS `fun_distance`$$
CREATE FUNCTION `fun_distance`(lat1 float,lng1 float,lat2 float,lng2 float) RETURNS floatBEGINset @num=2 * 6378137*ASIN(SQRT(POW(SIN(PI()*(lat1-(lat2))/360),2)+ COS(PI()*lat1/180)*COS(lat2*PI()/180)*POW(SIN(PI()*(lng1-(lng2))/360),2)));RETURN @num;END$$
DELIMITER ;
返回的是2点之间的距离以米为单位mysql计算经纬度两点之间的距离（转载)标签：本文...
公式如下，单位米： 第一点经纬度：lng1 lat1 第二点经纬度：lng2 lat2 round(6378.138*2*asin(sqrt(pow(sin( (lat1*pi()/180-lat2*pi()/180)/2),2)+cos(lat1*pi()/180)*cos(lat2*pi()/180)*
pow(sin( (lng1*pi()/180-lng2*pi()/180)/2),2)))*1000)
例如：
SELECT store_id,lng,lat,
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-lat*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(lat*PI()/180)*POW(SIN((114.173...
转载▼标签：
杂谈分类： PHP 公式如下，单位米：
第一点经纬度：lng1 lat1
第二点经纬度：lng2 lat2
round(6378.138*2*asin(sqrt(pow(sin( (lat1*pi()/180-lat2*pi()/180)/2),2)+cos(lat1*pi()/180)*cos(lat2*pi()/180)* pow(sin( (lng1*pi()/180-lng2*pi()/180)/2),2)))*1000)
例如：
SELECT store_id,lng,lat, ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-lat*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(lat*...
首先新建一张表，里面包含经纬度SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for customer
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (`id` int(11) unsigned NOT NULL auto_increment COMMENT ‘自增主键‘,`name` varchar(50) NOT NULL COMMENT ‘名称‘,`lon` double(9,6) NOT NULL COMMENT ‘经度‘,`lat` double(8,6) NOT NULL COMMENT ...
id, pt,city from locationpoint where 0.5 >= GLength(LineStringFromWKB(LineString(pt, point(113.4 ,34.46))))
附上表结构数据：
CREATE TABLE `locationPoint` (`id` int(11) NOT NULL,pt point not null,`province` varchar(20) NOT NULL,`city` varchar(20) NOT NULL,`longitude` double(10,3) NOT NULL,`latitude` double(10,3) NOT NULL,PRIMARY KEY (`id`)
)engine=Myisam;INSERT INTO `locationPoint` VALUES (‘1147...
DROP FUNCTION IF EXISTS `getDistance`;2 DELIMITER ;;3 CREATE FUNCTION `getDistance`(4 lon1 float(10,7) 5 ,lat1 float(10,7)6 ,lon2 float(10,7) 7 ,lat2 float(10,7)8 ) RETURNS double9 begin
10 declare d double;
11 declare radius int;
12 set radius = 6378140;
13 set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2)
14 *SIN((lat1-lat2)*PI()/180/2)+
15 ...
(POWER(MOD(ABS(lon - x),360),2) + POWER(ABS(lat - y),2)) AS distance FROM `eyanghu_location` ORDER BY distance LIMIT 1000x,y替换成你当前坐标的经纬度就行。本文出自 “12571026” 博客，请务必保留此出处http://12581026.blog.51cto.com/12571026/1931654mysql实现地球地理位置经纬度坐标排序标签：mysql 地理位置排序本文系统来源：http://12581026.blog.51cto.com/12571026/1931654
公式例如以下，单位米：
第一点经纬度：lng1 lat1
第二点经纬度：lng2 lat2
round(6378.138*2*asin(sqrt(pow(sin(
(lat1*pi()/180-lat2*pi()/180)/2),2)+cos(lat1*pi()/180)*cos(lat2*pi()/180)*pow(sin( (lng1*pi()/180-lng2*pi()/180)/2),2)))*1000)比如：
SELECT store_id,lng,lat,
ROUND(6378.138*2*ASIN(SQRT(POW(SIN((22.299439*PI()/180-lat*PI()/180)/2),2)+COS(22.299439*PI()/180)*COS(lat*PI()/180)*POW(SIN((114.17388...
DELIMITER $$CREATE DEFINER = CURRENT_USER FUNCTION `getDistance`(`lon1` float,`lat1` float,`lon2` float,`lat2` float) RETURNS doublebegin declare d double; declare radius int; set radius = 6378140; #假设地球为正球形，直径为6378140米 set d = (2*ATAN2(SQRT(SIN((lat1-lat2)*PI()/180/2) *SIN((lat1-lat2)*PI()/180/2)+ COS(lat2*PI()/180)*COS(lat1*PI()/180) *SIN((lon1-lon2)...
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 school_base_infoorder by juli asc limit 0,20mysql实现经纬度计算两个坐标之间的距离sql语句标签：pow sql语句 info cos base select div ase mysq 本文系统来源：https://www.cnblog...
DEFINER=`root`@`%` FUNCTION `latlon`(lat1 DOUBLE,lon1 DOUBLE,lat2 DOUBLE,lon2 DOUBLE) RETURNS varchar(200) CHARSET utf8mb4
begin
return ROUND(6378.138 * 2 * ASIN(SQRT(POW(SIN((lat1 * PI() / 180 - lat2 * PI() / 180) / 2),2) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * POW(SIN((lon1 * PI() / 180 - lon2 * PI() / 180) / 2),2))) * 1000);
end mysql 计算经纬度函数（米）标签：经纬 pow roo col...
* 2 * ASIN(SQRT(POW(SIN((‘.$lat.‘ * PI() / 180 - lat * PI() / 180) / 2), 2) + COS(‘.$lat.‘ * PI() / 180) * COS(lat * PI() / 180) * POW(SIN((‘.$lng.‘ * PI() / 180 - lng * PI() / 180) / 2), 2))) *1000 AS distance
FROMdistance
ORDER BYdistance ASCphp计算距离
/*** 根据两点间的经纬度计算距离* @param $lng1* @param $lat1* @param $lng2* @param $lat2* @return int*/
public static function getDistance(...
创建位置表，并且插入测试数据
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 80011
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 80011
File Encoding : 65001
Date: 2018-11-07 16:58:27
*/
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------...
DEFINER=`root`@`%` FUNCTION `latlon`(lat1 DOUBLE,lon1 DOUBLE,lat2 DOUBLE,lon2 DOUBLE) RETURNS varchar(200) CHARSET utf8mb4
begin
return ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW(
SIN(
(
lat1 * PI() / 180 - lat2 * PI() / 180
) / 2
),
2
) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * POW(
SIN(
(
lon1 * PI() / 180 - lon2 * PI() / 180
) / 2
),
2
)
)
) * 1000
);
end