需將 MapInfo 空間資料匯入 SQL Server 2008。空間型別分 Geometry (平面) 與 Geography (球面),距離計算結果不同。選用 Geography 但受限於:外環必須逆時針、內環順時針、不可超過半球。
使用 geography::STGeomFromText 匯入 WKT 時拋出錯誤:
Msg 6522: 輸入超過一個半球,非有效地理實例。常見原因:多邊形環方向錯誤。
改用 geometry::STGeomFromText 則成功,確認是環向問題。
public static bool IsRingClockwise(IList<Point> points)
{
double area = 0;
for (int i = 1; i < points.Count; i++)
area += points[i-1].X * points[i].Y - points[i].X * points[i-1].Y;
area += points[points.Count-1].X * points[0].Y - points[0].X * points[points.Count-1].Y;
return area < 0; // 負值 = 順時針
}
using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;
namespace SqlSpatialTools
{
public class Point
{
public double X { get; set; }
public double Y { get; set; }
public Point(double x, double y) { X = x; Y = y; }
public static Point Parse(string s)
{
var p = s.Split(new[] {' '}, StringSplitOptions.RemoveEmptyEntries);
return p.Length == 2 ? new Point(double.Parse(p[0]), double.Parse(p[1])) : null;
}
public override string ToString() => $"{X:F8} {Y:F8}";
}
public static class Wkt
{
public static bool IsRing(IList<Point> pts)
=> pts.Count > 2 && pts[0].X == pts[pts.Count-1].X && pts[0].Y == pts[pts.Count-1].Y;
public static string[] ReversePoints(List<Point> pts, bool reverse)
{
var sl = new List<string>();
foreach (var pt in pts) sl.Add(pt.ToString());
if (reverse) sl.Reverse();
return sl.ToArray();
}
public static string ReverseWKT(string wkt)
{
if (!Regex.IsMatch(wkt, "POLYGON", RegexOptions.IgnoreCase)) return wkt;
var ret = wkt;
var matches = Regex.Matches(ret, @"\(([^)]+)\)");
for (int i = matches.Count - 1; i >= 0; i--)
{
var m = matches[i];
var coords = m.Value.Trim('(', ')').Split(new[] {','}, StringSplitOptions.RemoveEmptyEntries);
var pts = new List<Point>();
foreach (var c in coords)
{
var pt = Point.Parse(c.Trim());
if (pt != null) pts.Add(pt);
}
if (IsRing(pts))
{
// Geography: 外環逆時針、內環順時針
// 這裡簡化:若為順時針則反轉 (實務需判斷外/內環)
bool clockwise = IsRingClockwise(pts);
var reversed = string.Join(",", ReversePoints(pts, clockwise));
ret = ret.Remove(m.Index, m.Length).Insert(m.Index, $"({reversed})");
}
}
return ret;
}
}
}
GEOMETRYCOLLECTION (線+面混合) 被 OGR 判讀為 POLYGON,導致修正失敗Geometry 模式匯入 Shapefile 穩定、中文無亂碼Geography 需嚴格環向控制,複雜幾何 (MultiPolygon, GeometryCollection) 需更完善解析器SqlServerSpatialTools 開源專案 (CodePlex/GitHub)ogr2ogr -f MSSQLSpatial 內建匯入工具MakeValid() 修正幾何空間資料匯入 SQL Server 的關鍵在於 Geography 的拓樸規則合規。自行撰寫 WKT 修正器可解決簡單 Polygon,但複雜幾何建議採用成熟工具鏈,避免重複造輪子。