这个字段类型是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,content,tel, (st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service ORDER BY distance;判断距离SELECT name,content,tel,astext(location),FLOOR(st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service having distance < 1000 ORDER BY distance;
转载于:https://www.cnblogs.com/taoshihan/p/11454633.html
