建表
create table `map` (
`id` int(11) not null,
`address` varchar(255) not null default '',
`location` geometry not null,
primary key (`id`),
spatial key `idx_location` (`location`)
)
插入
insert into map (id, address, location) values (1, 'somewhere', st_geomfromtext('point(121.366961 31.190049)'));
注意必须使用 st_geomfromtext 函数,且 point() 里面是:经度+空格+纬度
查询
1. 查看经纬度
select address, st_astext(location) as location from map;
2. 计算两点之间的距离
select st_distance_sphere(point(121.590347, 31.388094),location) as distant from map;
算出来的结果,单位是米
注意现在point()里面经纬度之间是逗号分隔的
3. 查询距离小于1000m的地点,并由远及近排序
select id, address, st_distance_sphere(point(121.590347, 31.388094),location) as distant from map where st_distance_sphere(point(121.590347, 31.388094),location) < 1000 order by distant;
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持萬仟网。
如您对本文有疑问或者有任何想说的,请点击进行留言回复,万千网友为您解惑!