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

方法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