SQL Server中GEOMETRY转GEOGRAPHY失败求助（SRID27700转4326）

你的核心问题出在坐标系投影转换的正确性和坐标顺序匹配上：SRID 27700是英国平面坐标系（东ing/北northing），而GEOGRAPHY要求WGS84地理坐标系（纬度/经度，范围±90/±180），直接转换会把northing值误判为纬度，导致超限报错。以下是两种可靠的解决方法：

方法1：注册SRID 27700后用原生STTransform转换 #

SQL Server默认未内置SRID 27700的投影定义，需要先注册才能正确执行投影转换：

步骤1：检查是否已注册27700 #

SELECT * FROM sys.spatial_reference_systems WHERE spatial_reference_id = 27700;

如果无结果，执行以下注册语句：

步骤2：注册SRID 27700 #

EXEC sys.sp_register_spatial_reference_system
    @spatial_reference_id = 27700,
    @authority_name = 'EPSG',
    @authorized_spatial_reference_id = 27700,
    @well_known_text = 'PROJCS["OSGB 1936 / British National Grid",GEOGCS["OSGB 1936",DATUM["OSGB_1936",SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],TOWGS84[446.448,-125.157,542.06,0.15,0.247,0.842,-20.489],AUTHORITY["EPSG","6277"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4277"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",49],PARAMETER["central_meridian",-2],PARAMETER["scale_factor",0.9996012717],PARAMETER["false_easting",400000],PARAMETER["false_northing",-100000],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","27700"]]',
    @unit_of_measure = 'metre',
    @unit_conversion_factor = 1;

步骤3：执行转换 #

注册完成后，用STTransform转成WGS84（SRID 4326）的GEOMETRY，再转GEOGRAPHY：

-- 替换表名和字段名
SELECT
    GEOGRAPHY::STGeomFromWKB(
        YourGeomColumn.STTransform(4326).STAsBinary(),
        4326
    ) AS Geog_WGS84
FROM YourTable;

方法2：封装NetTopologySuite为CLR函数 #

既然你已经能用C#的NetTopologySuite成功转换，可将逻辑封装为SQL Server的CLR函数，实现批量转换：

步骤1：编写C#转换逻辑 #

创建类库项目，引用NetTopologySuite、ProjNet，编写以下代码：

using Microsoft.SqlServer.Server;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;
using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;

public class SpatialConverters
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static SqlBytes Convert27700To4326(SqlBytes geomBytes)
    {
        // 读取SQL Server GEOMETRY二进制
        var reader = new SqlServerBytesReader();
        var geom = reader.Read(geomBytes.Value);
        
        // 定义坐标系
        var csFactory = new CoordinateSystemFactory();
        var projFactory = new CoordinateTransformationFactory();
        
        var osgb36 = csFactory.CreateFromWkt("PROJCS[\"OSGB 1936 / British National Grid\",GEOGCS[\"OSGB 1936\",DATUM[\"OSGB_1936\",SPHEROID[\"Airy 1830\",6377563.396,299.3249646,AUTHORITY[\"EPSG\",\"7001\"]],TOWGS84[446.448,-125.157,542.06,0.15,0.247,0.842,-20.489],AUTHORITY[\"EPSG\",\"6277\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4277\"]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"latitude_of_origin\",49],PARAMETER[\"central_meridian\",-2],PARAMETER[\"scale_factor\",0.9996012717],PARAMETER[\"false_easting\",400000],PARAMETER[\"false_northing\",-100000],UNIT[\"metre\",1,AUTHORITY[\"EPSG\",\"9001\"]],AXIS[\"Easting\",EAST],AXIS[\"Northing\",NORTH],AUTHORITY[\"EPSG\",\"27700\"]]");
        var wgs84 = csFactory.CreateFromWkt("GEOGCS[\"WGS 84\",DATUM[\"WGS_1984\",SPHEROID[\"WGS 84\",6378137,298.257223563,AUTHORITY[\"EPSG\",\"7030\"]],AUTHORITY[\"EPSG\",\"6326\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4326\"]]");
        
        // 执行坐标转换
        var transform = projFactory.CreateFromCoordinateSystems(osgb36, wgs84);
        var transformedGeom = geom.Copy();
        transformedGeom.Apply(new CoordinateTransformFilter(transform));
        
        // 输出GEOGRAPHY二进制
        var writer = new SqlServerBytesWriter { IsGeography = true, Srid = 4326 };
        return new SqlBytes(writer.Write(transformedGeom));
    }
    
    private class CoordinateTransformFilter : ICoordinateFilter
    {
        private readonly ICoordinateTransformation _transform;
        public CoordinateTransformFilter(ICoordinateTransformation transform) => _transform = transform;
        
        public void Filter(Coordinate coord)
        {
            var transformed = _transform.MathTransform.Transform(new[] { coord.X, coord.Y });
            coord.X = transformed[0]; // 经度
            coord.Y = transformed[1]; // 纬度
        }
    }
}

步骤2：注册CLR函数到SQL Server #

-- 启用CLR集成
sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'clr enabled', 1;
RECONFIGURE;

-- 创建程序集（替换为你的DLL路径）
CREATE ASSEMBLY SpatialConversion
FROM 'C:\YourPath\SpatialConverters.dll'
WITH PERMISSION_SET = UNSAFE;

-- 创建转换函数
CREATE FUNCTION dbo.Convert27700To4326(@geom VARBINARY(MAX))
RETURNS VARBINARY(MAX)
AS EXTERNAL NAME SpatialConversion.SpatialConverters.Convert27700To4326;

步骤3：批量转换 #

SELECT
    GEOGRAPHY::STGeomFromWKB(dbo.Convert27700To4326(YourGeomColumn.STAsBinary()), 4326) AS Geog_WGS84
FROM YourTable;

前置检查 #

• 确认你的GEOMETRY字段SRID为27700：SELECT YourGeomColumn.STSrid FROM YourTable，若不是，执行UPDATE YourTable SET YourGeomColumn = YourGeomColumn.STSetSRID(27700)

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