将GeoJson导入SQL Server表后Geometry字段始终为空？

核心问题分析 #

• 坐标提取错误：原代码仅提取了多边形的第一个点（$.geometry.coordinates[0][0]），而非整个外环的坐标集合，导致生成的WKT格式完全无效，STPolyFromText无法解析返回NULL。
• 坐标系参数不匹配：你的GeoJSON坐标是WGS84经纬度（EPSG:4326），但代码中错误使用了EPSG:3035（欧洲投影坐标系），坐标系不匹配会导致地理对象创建失败。
• WKT格式拼接错误：未将GeoJSON的坐标数组正确转换为WKT要求的格式（每个点需以经度 纬度形式呈现，环内点用逗号分隔，整体用括号包裹）。
• 未兼容MultiPolygon类型：原代码仅处理Polygon，未考虑MultiPolygon的嵌套坐标结构。

修正后的代码实现 #

DECLARE @GeoJSON nvarchar(max) = N'{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.564987562532224,
              40.292745296473385
            ],
            [
              -3.564987562532224,
              40.16285276280664
            ],
            [
              -3.4121841156292874,
              40.16285276280664
            ],
            [
              -3.4121841156292874,
              40.292745296473385
            ],
            [
              -3.564987562532224,
              40.292745296473385
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.3271182792090315,
              40.4031979514057
            ],
            [
              -3.3271182792090315,
              40.36480015317514
            ],
            [
              -3.1790407121077635,
              40.36480015317514
            ],
            [
              -3.1790407121077635,
              40.4031979514057
            ],
            [
              -3.3271182792090315,
              40.4031979514057
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.2262995101188494,
              40.623560335315744
            ],
            [
              -3.297187707135521,
              40.623560335315744
            ],
            [
              -3.297187707135521,
              40.554176446403545
            ],
            [
              -3.2262995101188494,
              40.554176446403545
            ],
            [
              -3.2262995101188494,
              40.623560335315744
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.4610182069080224,
              40.50628355201741
            ],
            [
              -3.5303311106570447,
              40.50628355201741
            ],
            [
              -3.5303311106570447,
              40.46794459511571
            ],
            [
              -3.4610182069080224,
              40.46794459511571
            ],
            [
              -3.4610182069080224,
              40.50628355201741
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -3.0,
              40.0
            ],
            [
              -3.0,
              40.1
            ],
            [
              -2.9,
              40.1
            ],
            [
              -2.9,
              40.0
            ],
            [
              -3.0,
              40.0
            ]
          ],
          [
            [
              -2.95,
              40.05
            ],
            [
              -2.95,
              40.08
            ],
            [
              -2.92,
              40.08
            ],
            [
              -2.92,
              40.05
            ],
            [
              -2.95,
              40.05
            ]
          ]
        ],
        "type": "Polygon"
      }
    },
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            [
              -2.8,
              40.2
            ],
            [
              -2.8,
              40.3
            ],
            [
              -2.7,
              40.3
            ],
            [
              -2.7,
              40.2
            ],
            [
              -2.8,
              40.2
            ]
          ],
          [
            [
              -2.75,
              40.25
            ],
            [
              -2.75,
              40.28
            ],
            [
              -2.72,
              40.28
            ],
            [
              -2.72,
              40.25
            ],
            [
              -2.75,
              40.25
            ]
          ]
        ],
        "type": "MultiPolygon"
      }
    }
  ]
}'

CREATE TABLE IF NOT EXISTS ImportDataGeo (
    Id INT IDENTITY(1, 1) PRIMARY KEY,
    Geometry GEOGRAPHY,
    GeoType NVARCHAR(50)
);

-- 清空表（可选，用于测试）
TRUNCATE TABLE ImportDataGeo;

-- 导入GeoJSON，兼容Polygon和MultiPolygon
INSERT INTO ImportDataGeo (Geometry, GeoType)
SELECT
    CASE 
        WHEN geo.type = 'Polygon' THEN geography::STPolyFromText('POLYGON(' + wkt_rings + ')', 4326).ReorientObject()
        WHEN geo.type = 'MultiPolygon' THEN geography::STMPolyFromText('MULTIPOLYGON(' + wkt_polygons + ')', 4326).ReorientObject()
    END AS Geometry,
    geo.type AS GeoType
FROM OPENJSON(@GeoJSON, '$.features')
CROSS APPLY (
    SELECT 
        JSON_VALUE(feature.value, '$.geometry.type') AS type,
        JSON_QUERY(feature.value, '$.geometry.coordinates') AS coordinates
    FROM OPENJSON(feature.value, '$.geometry') AS feature
) AS geo
CROSS APPLY (
    -- 处理Polygon：将外环和内环转换为WKT格式的环
    SELECT STRING_AGG('(' + STRING_AGG(CONCAT(c.value[0], ' ', c.value[1]), ', ') + ')', ', ') AS wkt_rings
    FROM OPENJSON(geo.coordinates) AS rings
    CROSS APPLY OPENJSON(rings.value) AS c
    WHERE geo.type = 'Polygon'
) AS polygon_rings
CROSS APPLY (
    -- 处理MultiPolygon：将每个多边形转换为WKT格式
    SELECT STRING_AGG('(' + STRING_AGG('(' + STRING_AGG(CONCAT(c.value[0], ' ', c.value[1]), ', ') + ')', ', ') + ')', ', ') AS wkt_polygons
    FROM OPENJSON(geo.coordinates) AS polygons
    CROSS APPLY OPENJSON(polygons.value) AS rings
    CROSS APPLY OPENJSON(rings.value) AS c
    WHERE geo.type = 'MultiPolygon'
) AS multipolygon_polygons;

-- 查看导入结果
SELECT Id, GeoType, Geometry.ToString() AS WKT, Geometry.STIsValid() AS IsValid FROM ImportDataGeo;

关键修正点说明 #

• 正确提取坐标：通过嵌套OPENJSON遍历GeoJSON的坐标结构，分别处理Polygon的环和MultiPolygon的多边形集合。
• 匹配正确坐标系：使用EPSG:4326（WGS84）作为地理对象的空间参考ID，与GeoJSON的经纬度坐标匹配。
• 生成合法WKT：通过STRING_AGG将坐标点拼接为符合WKT规范的格式，确保STPolyFromText和STMPolyFromText能正确解析。
• 兼容MultiPolygon：通过CASE分支根据几何类型调用对应的地理对象构造函数，同时处理MultiPolygon的嵌套坐标结构。
• 验证有效性：最后查询中加入STIsValid()验证生成的地理对象是否合法，确保数据正确性。

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