从PostgreSQL迁移至SQL Server：十进制经纬度空间索引等价实现

我完全懂你现在的痛点——从PostgreSQL转Windows版SQL Server，空间索引这块的语法差异确实坑人，尤其是没法把现有decimal类型的经纬度改成geography字段的情况，官方文档有时候也没讲透这种“非原生空间字段”的场景。

下面给你一步步拆解等价的实现方案，还适配Rails迁移的执行方式：

1. 先创建持久化计算列，把Decimal转成Geography类型 #

SQL Server的空间索引只能建在geography类型的列上，所以我们得先基于现有的longitude（decimal）和latitude（decimal）字段，生成一个持久化的计算列，把经纬度转成空间点对象：

ALTER TABLE geocodes
ADD location AS geography::STPointFromText('POINT(' + CAST(longitude AS VARCHAR(20)) + ' ' + CAST(latitude AS VARCHAR(20)) + ')', 4326) PERSISTED;

• 这里的4326是WGS84坐标系，和你之前PostGIS用的标准一致，确保空间计算的兼容性
• PERSISTED关键字必须加，它会让SQL Server把计算结果物理存储在表中，不然没法在这个列上创建索引

2. 在计算列上创建空间索引 #

有了geography类型的计算列后，就可以创建空间索引了，SQL Server的语法和PostGIS的GIST索引对应起来是这样的：

CREATE SPATIAL INDEX index_on_geocodes_location 
ON geocodes (location)
USING GEOGRAPHY_GRID
WITH (
  GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH),
  CELLS_PER_OBJECT = 16
);

• GEOGRAPHY_GRID是SQL Server针对地理空间数据的索引类型，和PostGIS的GIST索引作用类似
• GRIDS参数设置各级网格的密度，HIGH适合点数据较多的场景，如果你数据量不大，用默认值也可以
• CELLS_PER_OBJECT指定每个空间对象分配的网格数，16是比较通用的默认值

3. 适配Rails迁移的代码实现 #

因为Rails对SQL Server的空间索引支持不算完善，所以直接用execute执行原生SQL就好，迁移文件示例如下：

class AddSpatialIndexToGeocodes < ActiveRecord::Migration[6.1]
  def up
    # 添加持久化计算列
    execute <<-SQL
      ALTER TABLE geocodes
      ADD location AS geography::STPointFromText('POINT(' + CAST(longitude AS VARCHAR(20)) + ' ' + CAST(latitude AS VARCHAR(20)) + ')', 4326) PERSISTED;
    SQL

    # 创建空间索引
    execute <<-SQL
      CREATE SPATIAL INDEX index_on_geocodes_location 
      ON geocodes (location)
      USING GEOGRAPHY_GRID
      WITH (
        GRIDS = (LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH),
        CELLS_PER_OBJECT = 16
      );
    SQL
  end

  def down
    # 先删索引再删计算列，顺序不能反
    execute "DROP INDEX index_on_geocodes_location ON geocodes;"
    execute "ALTER TABLE geocodes DROP COLUMN location;"
  end
end

额外注意事项 #

• 确保你的SQL Server版本是2008及以上（这个版本开始支持空间索引）
• 转VARCHAR的时候，VARCHAR(20)足够覆盖绝大多数decimal格式的经纬度精度，如果你用的是更高精度的decimal，适当调整长度即可
• 经纬度的顺序和你PostGIS里的一致：POINT(longitude latitude)，SQL Server的STPointFromText和PostGIS的ST_GeographyFromText格式要求相同，不用调整顺序

内容的提问来源于stack exchange，提问作者Abhinav Khare