point介绍
point是MySQL中用来表示GIS中的地理坐标,在GIS场景中广泛使用。
一.创建带有point类型的表格
CREATE TABLE `test-point` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
`point` point NOT NULL COMMENT '经纬度',
`text` varchar(50) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
二.添加数据
INSERT INTO `test-point` ( point,text ) VALUES ( st_GeomFromText ( 'POINT(1 1)' ),'第1个点');
INSERT INTO `test-point` ( point,text ) VALUES ( st_PointFromText ( 'POINT(2 2)' ),'第2个点');
ST_GeomFromText、st_PointFromTex目的是将字符串转换成point类型的数据,详解见附录。
三.查询数据
SELECT id,st_x(point) x,st_y(point) y,point,text FROM `test-point`
SELECT id,st_AsText(point),text FROM `test-point`
st_x(point)是获取POINT(1 2)中的1,st_y(point)是获取POINT(1 2)中的2。
st_AsText(point)是将point类型转换成字符串,详解见附录。
四.更新数据
update `test-point` set point=st_PointFromText('POINT(5 5)') where id =10;
update `test-point` set point=st_GeomFromText('POINT(6 6)') where id =10;
五.查询一个点与数据库其他点之间的距离
SELECT
id,
st_x ( point ) latitude,
st_y ( point ) longitude,
round(( ST_DISTANCE_SPHERE ( st_GeomFromText ( 'POINT (1 1)' ), point )), 1 ) AS distance
FROM
`test-point`
st_distance_sphere函数是将坐标距离转换成米也可以使用st_distance。
st_distance_sphere函数的计算结果要比st_distance转换为米的结果更精确。
注意:st_distance函数随着点与点之间的距离增加误差越来越大
详解见附录。
六.查询一个点为圆心,方圆2000米内的数据
SELECT
id,
st_x ( point ) latitude,
st_y ( point ) longitude,
round(( ST_DISTANCE_SPHERE ( st_GeomFromText ( 'POINT (1 1)' ), point )), 1 ) AS distance
FROM
`test-point`
HAVING
distance <=2000
七.附录
注意事项
- Mysql5.7版本的语法不需要加"st_"这个前缀。
- POINT(经度,纬度) 经度和纬度不要写反,5.7查询不到结果,8.0版本查询为零,因为经度范围是 -180°-180°,纬度是-90°-90°,如果写反,纬度会超出范围,导致报错。
- POINT类型里面单位是小数,不是度.POINT(12.1 14.6)
本文章中构造函数参考
Geometry参考的构造函数ST_PointFromText_云数据库 RDS(RDS)-阿里云帮助中心 (aliyun.com)