示例如何在sqlserver数据表中查找 10km 内的商家

以下是在 SQL Server 中查找 10公里内商家 的完整示例，包含 表结构设计、空间索引创建 和 查询优化方法：

1. 数据表结构设计

-- 创建商家表，包含地理坐标列（Geography类型）
CREATE TABLE Merchants (
    MerchantID INT PRIMARY KEY IDENTITY(1,1),
    MerchantName NVARCHAR(100),
    Location GEOGRAPHY,  -- 使用Geography类型存储坐标
    Address NVARCHAR(200)
);

-- 插入示例数据（WGS84经纬度，SRID=4326）
INSERT INTO Merchants (MerchantName, Location, Address)
VALUES 
    ('商家A', GEOGRAPHY::Point(31.2304, 121.4737, 4326),   -- 上海坐标
    ('商家B', GEOGRAPHY::Point(31.2243, 121.4758, 4326),   -- 上海附近
    ('商家C', GEOGRAPHY::Point(39.9042, 116.4074, 4326);  -- 北京坐标（超出10公里）

2. 创建空间索引（关键性能优化）

CREATE SPATIAL INDEX SIndx_Merchants_Location 
ON Merchants(Location)  
USING GEOGRAPHY_GRID  
WITH (
  GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),  
  CELLS_PER_OBJECT = 16
);

3. 查询10公里内的商家

方法1：直接使用 STDistance

-- 定义用户当前位置（例如：上海中心坐标）
DECLARE @UserLocation GEOGRAPHY = GEOGRAPHY::Point(31.2304, 121.4737, 4326);

-- 查询10公里内的商家
SELECT 
    MerchantID,
    MerchantName,
    Address,
    @UserLocation.STDistance(Location) AS DistanceInMeters
FROM Merchants
WHERE 
    Location.STDistance(@UserLocation) <= 10000  -- 10公里 = 10,000米
ORDER BY DistanceInMeters;

方法2：利用空间索引优化范围过滤

-- 使用空间函数快速过滤大致范围（提高性能）
SELECT 
    MerchantID,
    MerchantName,
    Address,
    @UserLocation.STDistance(Location) AS DistanceInMeters
FROM Merchants
WHERE 
    Location.STIntersects(@UserLocation.STBuffer(10000)) = 1  -- 先筛选地理范围
    AND Location.STDistance(@UserLocation) <= 10000          -- 再精确计算
ORDER BY DistanceInMeters;

4. 查询结果示例

关键说明

1. 单位处理

   • STDistance 返回单位为米，直接比较 <= 10000 表示10公里。
   • 若需输入公里，需在代码中转换（如 10000 = 10 * 1000）。

2. 空间索引的作用

   • 空间索引会将地理数据分块（GRIDS），显著加速范围过滤（尤其是数据量大的场景）。
   • 方法2通过 STBuffer(10000) 生成一个10公里的圆形区域，先用索引快速筛选可能的目标，再精确计算距离。

3. 性能对比

   • 无索引：全表扫描，计算每条记录的 STDistance，性能随数据量线性下降。
   • 有索引：通过空间索引快速排除无关区域，性能提升可达数十倍。

5. 常见问题处理

• 坐标格式验证
  确保插入的经纬度值合法（纬度范围：-90~90，经度范围：-180~180）。
• 跨半球计算
  SQL Server 的 Geography 类型自动处理地球曲率和国际日期变更线，无需额外代码。

• 动态参数传递
  可将用户坐标参数化（如通过应用程序传入），避免SQL注入：

  -- C# 示例参数化代码
  SqlCommand cmd = new SqlCommand(
    "SELECT ... WHERE Location.STDistance(@UserLocation) <= 10000",
    connection);
  cmd.Parameters.Add("@UserLocation", SqlDbType.Udt).Value = userGeoPoint;

通过上述方法，您可以高效地在 SQL Server 中实现地理围栏查询，适用于O2O、LBS等场景。