mysql 地理位置查询

mysql空间扩展

mysql 官方指南为 GLength(LineStringFromWKB(LineString(point1, point2)))

首先创建位置表结构

CREATE TABLE `locationpoint` (
  `id` int(11) NOT NULL,
  `pt` point NOT NULL,
  `province` varchar(20) NOT NULL,
  `city` varchar(20) NOT NULL,
  `longitude` double(10,3) NOT NULL,
  `latitude` double(10,3) NOT NULL,
  `geo_code` varchar(12) NOT NULL COMMENT 'geohash编码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入字段 示例一部分数据

INSERT INTO `locationpoint` VALUES ('1147', GeomFromText('POINT(117.17 31.52)'), '安徽省', '合肥', '117.170', '31.520', 'uxyrvrbpvpzr');
INSERT INTO `locationpoint` VALUES ('1148', GeomFromText('POINT(117.02 30.31)'), '安徽省', '安庆', '117.020', '30.310', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1149', GeomFromText('POINT(117.21 32.56)'), '安徽省', '蚌埠', '117.210', '32.560', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1150', GeomFromText('POINT(115.47 33.52)'), '安徽省', '亳州', '115.470', '33.520', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1151', GeomFromText('POINT(117.52 31.36)'), '安徽省', '巢湖', '117.520', '31.360', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1152', GeomFromText('POINT(118.18 32.18)'), '安徽省', '滁州', '118.180', '32.180', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1153', GeomFromText('POINT(115.48 32.54)'), '安徽省', '阜阳', '115.480', '32.540', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1154', GeomFromText('POINT(117.28 30.39)'), '安徽省', '贵池', '117.280', '30.390', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1155', GeomFromText('POINT(116.47 33.57)'), '安徽省', '淮北', '116.470', '33.570', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1156', GeomFromText('POINT(116.58 32.37)'), '安徽省', '淮南', '116.580', '32.370', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1157', GeomFromText('POINT(118.18 29.43)'), '安徽省', '黄山', '118.180', '29.430', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1158', GeomFromText('POINT(115.21 33.15)'), '安徽省', '界首', '115.210', '33.150', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1159', GeomFromText('POINT(116.28 31.44)'), '安徽省', '六安', '116.280', '31.440', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1160', GeomFromText('POINT(118.28 31.43)'), '安徽省', '马鞍山', '118.280', '31.430', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1161', GeomFromText('POINT(117.58 32.47)'), '安徽省', '明光', '117.580', '32.470', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1162', GeomFromText('POINT(116.58 33.38)'), '安徽省', '宿州', '116.580', '33.380', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1163', GeomFromText('POINT(118.59 32.41)'), '安徽省', '天长', '118.590', '32.410', 'zzzzzzzzzzzz');
INSERT INTO `locationpoint` VALUES ('1164', GeomFromText('POINT(117.48 30.56)'), '安徽省', '铜陵', '117.480', '30.560', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1165', GeomFromText('POINT(118.22 31.19)'), '安徽省', '芜湖', '118.220', '31.190', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1166', GeomFromText('POINT(118.44 30.57)'), '安徽省', '宣州', '118.440', '30.570', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1167', GeomFromText('POINT(119.18 26.05)'), '福建省', '福州', '119.180', '26.050', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1168', GeomFromText('POINT(119.31 25.58)'), '福建省', '长乐', '119.310', '25.580', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1169', GeomFromText('POINT(119.39 27.06)'), '福建省', '福安', '119.390', '27.060', 'bpbpbpbpbpbp');
INSERT INTO `locationpoint` VALUES ('1170', GeomFromText('POINT(119.23 25.42)'), '福建省', '福清', '119.230', '25.420', 'bpbpbpbpb

sql 根据空间查询

  select id, point(longitude,latitude) as pt from locationpoint where 0.5 >= GLength(LineStringFromWKB(LineString(pt, point(113.4 ,34.46))))

根据地址获取经纬度: https://blog.csdn.net/qq_21891743/article/details/79105899.