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

o0麦嘎

于 2025-04-02 13:45:44 发布

阅读量290

点赞数 1

CC 4.0 BY-SA版权

文章标签： mysql 经纬度

本文链接：https://blog.youkuaiyun.com/Gpointy/article/details/146933853

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 < 130
	and tl.longitude > 103 
	and tl.latitude < 18
	and tl.latitude > 10
	and ST_CONTAINS(ST_POLYGONFROMTEXT('POLYGON((103 10,130 10,130 18,103 18,103 10))'),tl.location);

在这里插入图片描述

3.2 语句

SELECT
    tl.id,
    tl.name,
    tl.longitude,
    tl.latitude,
    ST_AsGeoJSON(tl.location) as location
FROM tf_location tl
	WHERE tl.longitude &lt; #{maxLong}
      and tl.longitude &gt; #{minLong}
      and tl.latitude &lt; #{maxLat}
      and tl.latitude &gt; #{minLat}
      and ST_CONTAINS(ST_POLYGONFROMTEXT(#{polygon}), tl.location)