MYSQL实现获取某个经纬度区域内的数据

1.创建表

2.插入表数据

INSERT INTO `tf_sys`.`tf_location`(`name`, `longitude`, `latitude`, `location`) VALUES ('资料名称1', 114.437625, 16.016914, ST_GeomFromText('POINT(114.437625 16.016914)'));
INSERT INTO `tf_sys`.`tf_location`(`name`, `longitude`, `latitude`, `location`) VALUES ('资料名称2', 120.437625, 16.016914, ST_GeomFromText('POINT(120.437625 16.016914)'));
INSERT INTO `tf_sys`.`tf_location`(`name`, `longitude`, `latitude`, `location`) VALUES ('资料名称3', 114.437625, 20.016914, ST_GeomFromText('POINT(114.437625 20.016914)'));
INSERT INTO `tf_sys`.`tf_location`(`name`, `longitude`, `latitude`, `location`) VALUES ('资料名称4', 120.437625, 20.016914, ST_GeomFromText('POINT(120.437625 20.016914)'));

3.查询

3.1 sql

SELECT tl.name,tl.longitude,tl.latitude,ST_AsGeoJSON(tl.location) as location
FROM tf_location tl
WHERE tl.longitude < 130and tl.longitude > 103 and tl.latitude < 18and tl.latitude > 10and ST_CONTAINS(ST_POLYGONFROMTEXT('POLYGON((103 10,130 10,130 18,103 18,103 10))'),tl.location);

3.2 语句

SELECTtl.id,tl.name,tl.longitude,tl.latitude,ST_AsGeoJSON(tl.location) as location
FROM tf_location tlWHERE tl.longitude < #{maxLong}and tl.longitude > #{minLong}and tl.latitude < #{maxLat}and tl.latitude > #{minLat}and ST_CONTAINS(ST_POLYGONFROMTEXT(#{polygon}), tl.location)

3.3 解释

① 几何图形需要形成一个闭合区域
② 获取集合图形经纬度的最大值和最小值
③ POLYGON((103 10,130 10,130 18,103 18,103 10))，按瞬时间填写经纬度，并且第一个和最后一个的值保持一样形成一个闭合的区域