Mysql 地区经纬度 查询

id, pt,city from locationpoint where 0.5 >= GLength(LineStringFromWKB(LineString(pt, point(113.4 ,34.46))))

 

附上表结构数据：

 

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,
  PRIMARY KEY (`id`)
)engine=Myisam;

INSERT INTO `locationPoint` VALUES (‘1147‘,point(117.17,31.52 ),‘安徽省‘, ‘合肥‘, ‘117.170‘, ‘31.520‘);          
INSERT INTO `locationPoint` VALUES (‘1148‘,point(117.02,30.31 ),‘安徽省‘, ‘安庆‘, ‘117.020‘, ‘30.310‘);          
INSERT INTO `locationPoint` VALUES (‘1149‘,point(117.21,32.56 ),‘安徽省‘, ‘蚌埠‘, ‘117.210‘, ‘32.560‘);          
INSERT INTO `locationPoint` VALUES (‘1150‘,point(115.47,33.52 ),‘安徽省‘, ‘亳州‘, ‘115.470‘, ‘33.520‘);          
INSERT INTO `locationPoint` VALUES (‘1151‘,point(117.52,31.36 ),‘安徽省‘, ‘巢湖‘, ‘117.520‘, ‘31.360‘);          
INSERT INTO `locationPoint` VALUES (‘1152‘,point(118.18,32.18 ),‘安徽省‘, ‘滁州‘, ‘118.180‘, ‘32.180‘);          
INSERT INTO `locationPoint` VALUES (‘1153‘,point(115.48,32.54 ),‘安徽省‘, ‘阜阳‘, ‘115.480‘, ‘32.540‘);          
INSERT INTO `locationPoint` VALUES (‘1154‘,point(117.28,30.39 ),‘安徽省‘, ‘贵池‘, ‘117.280‘, ‘30.390‘);          
INSERT INTO `locationPoint` VALUES (‘1155‘,point(116.47,33.57 ),‘安徽省‘, ‘淮北‘, ‘116.470‘, ‘33.570‘);          
INSERT INTO `locationPoint` VALUES (‘1156‘,point(116.58,32.37 ),‘安徽省‘, ‘淮南‘, ‘116.580‘, ‘32.370‘);          
INSERT INTO `locationPoint` VALUES (‘1157‘,point(118.18,29.43 ),‘安徽省‘, ‘黄山‘, ‘118.180‘, ‘29.430‘);          
INSERT INTO `locationPoint` VALUES (‘1158‘,point(115.21,33.15 ),‘安徽省‘, ‘界首‘, ‘115.210‘, ‘33.150‘);          
INSERT INTO `locationPoint` VALUES (‘1159‘,point(116.28,31.44 ),‘安徽省‘, ‘六安‘, ‘116.280‘, ‘31.440‘);          
INSERT INTO `locationPoint` VALUES (‘1160‘,point(118.28,31.43 ),‘安徽省‘, ‘马鞍山‘, ‘118.280‘, ‘31.430‘);        
INSERT INTO `locationPoint` VALUES (‘1161‘,point(117.58,32.47 ),‘安徽省‘, ‘明光‘, ‘117.580‘, ‘32.470‘);          
INSERT INTO `locationPoint` VALUES (‘1162‘,point(116.58,33.38 ),‘安徽省‘, ‘宿州‘, ‘116.580‘, ‘33.380‘);          
INSERT INTO `locationPoint` VALUES (‘1163‘,point(118.59,32.41 ),‘安徽省‘, ‘天长‘, ‘118.590‘, ‘32.410‘);          
INSERT INTO `locationPoint` VALUES (‘1164‘,point(117.48,30.56 ),‘安徽省‘, ‘铜陵‘, ‘117.480‘, ‘30.560‘);          
INSERT INTO `locationPoint` VALUES (‘1165‘,point(118.22,31.19 ),‘安徽省‘, ‘芜湖‘, ‘118.220‘, ‘31.190‘);          
INSERT INTO `locationPoint` VALUES (‘1166‘,point(118.44,30.57 ),‘安徽省‘, ‘宣州‘, ‘118.440‘, ‘30.570‘);          
INSERT INTO `locationPoint` VALUES (‘1167‘,point(119.18,26.05 ),‘福建省‘, ‘福州‘, ‘119.180‘, ‘26.050‘);          
INSERT INTO `locationPoint` VALUES (‘1168‘,point(119.31,25.58 ),‘福建省‘, ‘长乐‘, ‘119.310‘, ‘25.580‘);          
INSERT INTO `locationPoint` VALUES (‘1169‘,point(119.39,27.06 ),‘福建省‘, ‘福安‘, ‘119.390‘, ‘27.060‘);          
INSERT INTO `locationPoint` VALUES (‘1170‘,point(119.23,25.42 ),‘福建省‘, ‘福清‘, ‘119.230‘, ‘25.420‘);          
INSERT INTO `locationPoint` VALUES (‘1171‘,point(118.2 ,27.03 ),‘福建省‘, ‘建瓯‘, ‘118.200‘, ‘27.030‘);          
INSERT INTO `locationPoint` VALUES (‘1172‘,point(118.07,27.21 ),‘福建省‘, ‘建阳‘, ‘118.070‘, ‘27.210‘);          
INSERT INTO `locationPoint` VALUES (‘1173‘,point(118.35,24.49 ),‘福建省‘, ‘晋江‘, ‘118.350‘, ‘24.490‘);          
INSERT INTO `locationPoint` VALUES (‘1174‘,point(117.48,24.26 ),‘福建省‘, ‘龙海‘, ‘117.480‘, ‘24.260‘);          
INSERT INTO `locationPoint` VALUES (‘1175‘,point(117.01,25.06 ),‘福建省‘, ‘龙岩‘, ‘117.010‘, ‘25.060‘);          
INSERT INTO `locationPoint` VALUES (‘1176‘,point(118.23,24.57 ),‘福建省‘, ‘南安‘, ‘118.230‘, ‘24.570‘);          
INSERT INTO `locationPoint` VALUES (‘1177‘,point(118.1 ,26.38 ),‘福建省‘, ‘南平‘, ‘118.100‘, ‘26.380‘);          
INSERT INTO `locationPoint` VALUES (‘1178‘,point(119.31,26.39 ),‘福建省‘, ‘宁德‘, ‘119.310‘, ‘26.390‘);          
INSERT INTO `locationPoint` VALUES (‘1179‘,point(119.01,24.26 ),‘福建省‘, ‘莆田‘, ‘119.010‘, ‘24.260‘);          
INSERT INTO `locationPoint` VALUES (‘1180‘,point(118.36,24.56 ),‘福建省‘, ‘泉州‘, ‘118.360‘, ‘24.560‘);          
INSERT INTO `locationPoint` VALUES (‘1181‘,point(117.36,26.13 ),‘福建省‘, ‘三明‘, ‘117.360‘, ‘26.130‘);          
INSERT INTO `locationPoint` VALUES (‘1182‘,point(117.29,27.2  ),‘福建省‘, ‘邵武‘, ‘117.290‘, ‘27.200‘);          
INSERT INTO `locationPoint` VALUES (‘1183‘,point(118.38,24.44 ),‘福建省‘, ‘石狮‘, ‘118.380‘, ‘24.440‘);          
INSERT INTO `locationPoint` VALUES (‘1184‘,point(118.02,27.46 ),‘福建省‘, ‘武夷山‘, ‘118.020‘, ‘27.460‘);        
INSERT INTO `locationPoint` VALUES (‘1185‘,point(118.06,24.27 ),‘福建省‘, ‘厦门‘, ‘118.060‘, ‘24.270‘);          
INSERT INTO `locationPoint` VALUES (‘1186‘,point(117.23,25.58 ),‘福建省‘, ‘永安‘, ‘117.230‘, ‘25.580‘);          
INSERT INTO `locationPoint` VALUES (‘1187‘,point(117.24,25.17 ),‘福建省‘, ‘漳平‘, ‘117.240‘, ‘25.170‘);          
INSERT INTO `locationPoint` VALUES (‘1188‘,point(117.39,24.31 ),‘福建省‘, ‘漳州‘, ‘117.390‘, ‘24.310‘);          
INSERT INTO `locationPoint` VALUES (‘1189‘,point(103.51,36.04 ),‘甘肃省‘, ‘兰州‘, ‘103.510‘, ‘36.040‘);          
INSERT INTO `locationPoint` VALUES (‘1190‘,point(104.12,36.33 ),‘甘肃省‘, ‘白银‘, ‘104.120‘, ‘36.330‘);          
INSERT INTO `locationPoint` VALUES (‘1191‘,point(94.41 ,40.08 ),‘甘肃省‘, ‘敦煌‘, ‘94.410‘, ‘40.080‘);           
INSERT INTO `locationPoint` VALUES (‘1192‘,point(98.14 ,39.48 ),‘甘肃省‘, ‘嘉峪关‘, ‘98.140‘, ‘39.480‘);         
INSERT INTO `locationPoint` VALUES (‘1193‘,point(102.1 ,38.28 ),‘甘肃省‘, ‘金昌‘, ‘102.100‘, ‘38.280‘);          
INSERT INTO `locationPoint` VALUES (‘1194‘,point(98.31 ,39.44 ),‘甘肃省‘, ‘酒泉‘, ‘98.310‘, ‘39.440‘);           
INSERT INTO `locationPoint` VALUES (‘1195‘,point(103.12,35.37 ),‘甘肃省‘, ‘临夏‘, ‘103.120‘, ‘35.370‘);          
INSERT INTO `locationPoint` VALUES (‘1196‘,point(106.4 ,35.32 ),‘甘肃省‘, ‘平凉‘, ‘106.400‘, ‘35.320‘);          
INSERT INTO `locationPoint` VALUES (‘1197‘,point(105.42,34.37 ),‘甘肃省‘, ‘天水‘, ‘105.420‘, ‘34.370‘);          
INSERT INTO `locationPoint` VALUES (‘1198‘,point(102.39,37.56 ),‘甘肃省‘, ‘武威‘, ‘102.390‘, ‘37.560‘);          
INSERT INTO `locationPoint` VALUES (‘1199‘,point(107.4 ,35.45 ),‘甘肃省‘, ‘西峰‘, ‘107.400‘, ‘35.450‘);          
INSERT INTO `locationPoint` VALUES (‘1200‘,point(97.35 ,39.49 ),‘甘肃省‘, ‘玉门‘, ‘97.350‘, ‘39.490‘);           
INSERT INTO `locationPoint` VALUES (‘1201‘,point(100.26,38.56 ),‘甘肃省‘, ‘张掖‘, ‘100.260‘, ‘38.560‘);          
INSERT INTO `locationPoint` VALUES (‘1202‘,point(113.14,23.08 ),‘广东省‘, ‘广州‘, ‘113.140‘, ‘23.080‘);          
INSERT INTO `locationPoint` VALUES (‘1203‘,point(116.36,23.16 ),‘广东省‘, ‘潮阳‘, ‘116.360‘, ‘23.160‘);          
INSERT INTO `locationPoint` VALUES (‘1204‘,point(116.38,23.4  ),‘广东省‘, ‘潮州‘, ‘116.380‘, ‘23.400‘);          
INSERT INTO `locationPoint` VALUES (‘1205‘,point(116.46,23.28 ),‘广东省‘, ‘澄海‘, ‘116.460‘, ‘23.280‘);          
INSERT INTO `locationPoint` VALUES (‘1206‘,point(113.33,23.33 ),‘广东省‘, ‘从化‘, ‘113.330‘, ‘23.330‘);          
INSERT INTO `locationPoint` VALUES (‘1207‘,point(113.45,23.02 ),‘广东省‘, ‘东莞‘, ‘113.450‘, ‘23.020‘);          
INSERT INTO `locationPoint` VALUES (‘1208‘,point(112.19,22.12 ),‘广东省‘, ‘恩平‘, ‘112.190‘, ‘22.120‘);          
INSERT INTO `locationPoint` VALUES (‘1209‘,point(113.06,23.02 ),‘广东省‘, ‘佛山‘, ‘113.060‘, ‘23.020‘);          
INSERT INTO `locationPoint` VALUES (‘1210‘,point(112.5 ,22.53 ),‘广东省‘, ‘高明‘, ‘112.500‘, ‘22.530‘);          
INSERT INTO `locationPoint` VALUES (‘1211‘,point(112.26,23.02 ),‘广东省‘, ‘高要‘, ‘112.260‘, ‘23.020‘);          
INSERT INTO `locationPoint` VALUES (‘1212‘,point(110.5 ,21.54 ),‘广东省‘, ‘高州‘, ‘110.500‘, ‘21.540‘);          
INSERT INTO `locationPoint` VALUES (‘1213‘,point(112.57,22.46 ),‘广东省‘, ‘鹤山‘, ‘112.570‘, ‘22.460‘);          
INSERT INTO `locationPoint` VALUES (‘1214‘,point(114.41,23.43 ),‘广东省‘, ‘河源‘, ‘114.410‘, ‘23.430‘);          
INSERT INTO `locationPoint` VALUES (‘1215‘,point(113.12,23.23 ),‘广东省‘, ‘花都‘, ‘113.120‘, ‘23.230‘);          
INSERT INTO `locationPoint` VALUES (‘1216‘,point(110.37,21.39 ),‘广东省‘, ‘化州‘, ‘110.370‘, ‘21.390‘);          
INSERT INTO `locationPoint` VALUES (‘1217‘,point(114.28,22.48 ),‘广东省‘, ‘惠阳‘, ‘114.280‘, ‘22.480‘);          
INSERT INTO `locationPoint` VALUES (‘1218‘,point(114.22,23.05 ),‘广东省‘, ‘惠州‘, ‘114.220‘, ‘23.050‘);          
INSERT INTO `locationPoint` VALUES (‘1219‘,point(113.04,22.35 ),‘广东省‘, ‘江门‘, ‘113.040‘, ‘22.350‘);          
INSERT INTO `locationPoint` VALUES (‘1220‘,point(116.21,22.32 ),‘广东省‘, ‘揭阳‘, ‘116.210‘, ‘22.320‘);          
INSERT INTO `locationPoint` VALUES (‘1221‘,point(112.4 ,22.22 ),‘广东省‘, ‘开平‘, ‘112.400‘, ‘22.220‘);          
INSERT INTO `locationPoint` VALUES (‘1222‘,point(113.21,25.09 ),‘广东省‘, ‘乐昌‘, ‘113.210‘, ‘25.090‘);          
INSERT INTO `locationPoint` VALUES (‘1223‘,point(110.04,20.54 ),‘广东省‘, ‘雷州‘, ‘110.040‘, ‘20.540‘);          
INSERT INTO `locationPoint` VALUES (‘1224‘,point(110.17,21.37 ),‘广东省‘, ‘廉江‘, ‘110.170‘, ‘21.370‘);          
INSERT INTO `locationPoint` VALUES (‘1225‘,point(112.23,24.48 ),‘广东省‘, ‘连州‘, ‘112.230‘, ‘24.480‘);          
INSERT INTO `locationPoint` VALUES (‘1226‘,point(111.33,22.46 ),‘广东省‘, ‘罗定‘, ‘111.330

                  

	 	
                    

                        

                            

                                
< 上一篇
Spring JDBC主从数据库配置
                            
                            

                                 
下一篇 >
java中动态给sql追加？号