mysql_5.7_坐标的支持
建表时坐标字段
CREATE TABLE `NewTable` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`code` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '服务中心代码' ,
`name` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称' ,
`description` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述' ,
`location` geometry NOT NULL COMMENT '服务中心坐标' ,
`area` geometry NOT NULL COMMENT '服务中心覆盖区域' ,
`disabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否禁用' ,
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
PRIMARY KEY (`id`),
INDEX `center_location_key` (`location`(32)) ,
INDEX `center_area_key` (`area`(32))
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='服务中心表'
AUTO_INCREMENT=2
ROW_FORMAT=DYNAMIC
;
插入数据
INSERT INTO `shp`.`t_service_center` (`id`, `code`, `name`, `description`, `location`, `area`, `disabled`, `created_time`, `updated_time`)
VALUES ('1', '00000001', '江阴路门店', '上海市黄浦区江阴街27号',
GeomFromText('POINT(121.495104 31.214316)'),
GeomFromText('POINT(121.495104 31.214316)'), '0', '2018-09-30 10:43:25', '2018-09-30 10:43:25');
坐标函数
st_geomfromtext('POINT(121.495104 31.214316)') #将文本转为几何坐标
st_distance_sphere(列的坐标, 参考坐标) #该列的坐示离 参考坐标 多少米远
st_distance_sphere(c.location, st_geomfromtext('POINT(121.495104 31.214316)')) <= 1000
列的坐示c表的location离参考坐标 'POINT(121.495104 31.214316)' 小于1000米的数据
#{coordinates}='POINT(121.495104 31.214316)'
select
p.id,
p.category_id,
p.code,
p.title,
p.provider_id
from t_product p
inner join t_product_center pc on p.id = pc.product_id
inner join t_service_center c on pc.center_id = c.id
where
st_distance_sphere(c.location, st_geomfromtext(#{coordinates})) <= 1000
and p.disabled = 0;