分类 IT 下的文章

以下是在 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. 查询结果示例

MerchantIDMerchantNameAddressDistanceInMeters
1商家A上海某地址0.0 (同一坐标)
2商家B上海附近地址约 800 米

关键说明

  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等场景。

  • 先备份

    cp /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

  • 下载阿里云yum源

    curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

  • 清空缓存并重新生成缓存

    sudo yum clean all
sudo yum makecache

  • 在出口ZT上允许客户端NAT并开启伪装。

    net.ipv4.ip_forward  = 1  （在文件 /etc/sysctl.conf 中）
sysctl -p (生效配置)

firewall-cmd --permanent --zone=public --add-masquerade 
firewall-cmd --reload

  • ZT后台添加路由

    0.0.0.0/0 => 出口ZT的IP

  • 客户端

    Allow Default Route

安装：

curl -s https://install.zerotier.com | sudo bash

启动：

systemctl start zerotier-one  #启动
systemctl enable zerotier-one  #自启动
systemctl restart zerotier-one  #重启

加入网络：

zerotier-cli join <id>

其它命令：

zerotier-cli status #查看服务状态
zerotier-cli leave <id> #离开网络
zerotier-cli listnetworks #查看所有网络
  1. 1
  2. 2
  3. 3
  4. 4
  5. ...
  6. 8