这个字段类型是MysqL5.7新增的功能,主要就是解决坐标存储和距离计算的常见问题
创建表:
CREATE TABLE `service` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`content` varchar(128) NOT NULL DEFAULT '',
`tel` varchar(20) NOT NULL DEFAULT '',
`location` geometry NOT NULL,
PRIMARY KEY (`id`),
KEY `location` (`location`(32))
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
插入坐标
insert into service (name,content,tel,location)values("陶士涵",'牛逼','18898989898',ST_GeomFromText('POINT(116.28828 40.053257)'));
读取坐标
select *,astext(location) from service;
查询距离
SELECT name,(st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service ORDER BY distance;
判断距离
SELECT name,astext(location),FLOOR(st_distance (location,40.047955) ) *111195) AS distance FROM service having distance < 1000 ORDER BY distance;
总结
如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您喜欢交流学习经验,点击链接加入交流1群:1065694478(已满)交流2群:163560250