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;