SQL 查询表中所有经纬度在一公里内的记录

最新推荐文章于 2024-01-15 16:06:52 发布

Pro_monkey

最新推荐文章于 2024-01-15 16:06:52 发布

阅读量1.9k

点赞数

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/Pro_monkey/article/details/80661618

版权

SQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

方法1：计算目标是否在范围内，平均执行7500ms

DECLARE @GGP_LOCATE_POINT GEOGRAPHY = GEOGRAPHY::Point(31.358772277832,120.690238952637, 4326);
DECLARE @buffer GEOGRAPHY;
set @buffer=@GGP_LOCATE_POINT.STBuffer(1000);
SELECT * FROM table
WHERE
@buffer.Filter(GEOGRAPHY::Point(31.358772277832,120.690238952637, 4326))<=1000

方法2：计算每个坐标的距离（圆），平均执行5500ms
DECLARE @GGP_LOCATE_POINT GEOGRAPHY = GEOGRAPHY::Point( 31.358772277832,120.690238952637, 4326);
SELECT * FROM table
WHERE
@GGP_LOCATE_POINT.STDistance(GEOGRAPHY::Point(31.358772277832,120.690238952637, 4326))<=1000

方法3：按矩形算
优点：快，缺点：不是圆型的范围，平均执行150ms(推荐这个，比较快)
DECLARE @GGP_LOCATE_POINT GEOGRAPHY = GEOGRAPHY::Point( 31.358772277832,120.690238952637, 4326);
DECLARE @FLT_LONGITUDE FLOAT, @FLT_LATITUDE FLOAT --纬度
SELECT @FLT_LONGITUDE =120.690238952637, @FLT_LATITUDE =31.358772277832
SELECT @GGP_LOCATE_POINT.STDistance(GEOGRAPHY::Point(31.358772277832,120.690238952637, 4326)) as [验证距离], * FROM taable
WHERE
flat BETWEEN @FLT_LATITUDE-0.010 AND @FLT_LATITUDE+0.010
AND flon BETWEEN @FLT_LONGITUDE-0.010 AND @FLT_LONGITUDE+0.010