[MySQL] mysql地理位置服务geometry字段类型
这个字段类型是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;
相关文章
- 认证
- SQLite向MySQL的数据库迁移
- ImportError: cannot import name UnrewindableBodyError
- Flask 启动报错 error: [Errno 10053]
- Nginx 优化静态文件访问
- MySQL 的性能(下篇)—— 性能优化方法
- MySQL 的性能(上篇)—— SQL 执行分析
- [SQL]SQL 执行顺序
- [工具]PyCharm激活、注册码无效解决办法
- Redis 详解
- Redis事务
- Redis持久化
- Redis集群搭建
- Tampermonkey for Mac(油猴Safari浏览器插件)中文版v 4.17.6162
- Redis分布式锁的10个坑
- 如何解决 Redis 数据倾斜、热点等问题
- 免费给自己的网站加 HTTPS
- iterm2 快捷键(转载)
- Too many authentication failures for xxxx_username
- peewee insert 数据时报错:'buffer' object has no attribute 'translate'