主要是sql查询符合在圆形，多边形区域经纬度的数据

查询

圆形

中心点 + 距离

drop table if exists demo;
CREATE TABLE `demo` (`id` int(5) NOT NULL AUTO_INCREMENT COMMENT '主键',`shop_name` varchar(50) DEFAULT NULL COMMENT '商品名称',`lng` DECIMAL( 11, 8 ) DEFAULT NULL COMMENT '经度',`lat` DECIMAL( 10, 8 ) DEFAULT NULL COMMENT '纬度',`address` text COMMENT '位置名称',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='demo';INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('4', 'xxx公司', '113.94080600', '22.56090600', '技术大厦');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('5', '科陆电子烟', '113.94154900', '22.56113500', '科陆电子科技大厦');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('6', '酷派信息港', '113.94554300', '22.56051200', '酷派信息港');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('7', '松坪山公园', '113.94728100', '22.55851100', '松坪山公园');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('8', '航天微电机大厦', '113.93847800', '22.56067100', '航天微电机大厦');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('9', '蓝马智造园', '113.93478700', '22.56027400', '蓝马智造园');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('10', '万科智城', '113.94206100', '22.57062800', '万科智城');
INSERT INTO `demo` (`id`, `shop_name`, `lng`, `lat`, `address`) VALUES ('11', '洪浪北', '113.91020200', '22.57442700', '洪浪北');

-- 地球半径：6371
-- distance:距离（公里）
SELECT  *,(  6378.138 * acos (  cos ( radians(21.785367) )  * cos( radians( lat ) )  * cos( radians( lng ) - radians(118.356734) )  + sin ( radians(21.785367) )  * sin( radians( lat ) )  )  ) AS distance  -- 距离      
FROM demo
HAVING distance < 463 -- 筛选距离小于463km的(可不写)，如果没查到数据就是没有小于463km的
ORDER BY distance -- 根据距离远近来排序 默认升序 （可不写）
LIMIT 0 , 20;  -- 显示前20条数据（可不写）eg:
SELECT  *,(  6378 * acos (  cos ( radians(21.785367) )  * cos( radians( lat ) )  * cos( radians( lng ) - radians(118.356734) )  + sin ( radians(21.785367) )  * sin( radians( lat ) )  )  ) AS distance  
FROM demo
HAVING distance < 463 
ORDER BY distance 
LIMIT 0 , 20;  

结果：

多边形

DROP TABLE IF EXISTS `geom`;
CREATE TABLE `geom`  (`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,`lnglat` varchar(255) NOT NULL COMMENT '经纬度',PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;INSERT INTO `geom` VALUES (1, "1,1");
INSERT INTO `geom` VALUES (2, "2,2");
INSERT INTO `geom` VALUES (3, "3,3");
INSERT INTO `geom` VALUES (4, "4,4");
INSERT INTO `geom` VALUES (5, "5,5");
INSERT INTO `geom` VALUES (6, "6,6");select`geom`.id, `geom`.lnglat,SUBSTRING_INDEX(`geom`.lnglat,",",1) lng,SUBSTRING_INDEX(`geom`.lnglat,",",-1) lat
from geom 
where MBRWithin(ST_GeomFromText(CONCAT( 'POINT(',SUBSTRING_INDEX(`geom`.lnglat,",",1),' ',SUBSTRING_INDEX(`geom`.lnglat,",",-1), ')')),GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));-----------------------------------------------------------------------------------------下面是简写
pt 代表 POINT(1 1)
select AsText(pt) from geom where MBRWithin(pt,GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'));
select AsText(pt) from geom where MBRContains(GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'),pt);

结果：