方法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
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
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
优点:快,缺点:不是圆型的范围,平均执行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