如何在SQL Server中进行空间重投影

Question

我正试图在SQL Server中进行一些重投影的操作，但很不幸我找不到任何解决方案。我知道SQL Server没有实现这个功能，所以不知道是否有任何捷径可用。

有些人建议使用外部库，比如ogr2ogr，但对我来说并不是一个好选择，因为我需要即时处理。我在这里找到了一个帖子Transform/ Project a geometry from one SRID to another，但它只能转换成纬度和经度，并且功能有限。

我正在寻找一种解决方案，允许我在任何SRID下进行重投影，类似于postgresql（postgis）中的ST_Transform。

- user3867743

---

参见：https://gis.stackexchange.com/q/2723/9 - Rowland Shaw

Answer 1

如果你正在寻找一个简单的选项，我很遗憾告诉你这是不可能的。但是有一个解决该问题的方法。我之前也遇到过类似的问题，我必须在.NET中实现一个CLR，然后将其作为程序集导入MSSQL，速度有点慢，但效果很好。输入是：源投影，目标投影和几何文本，输出是重新投影的几何图形文本。请看下面来自.Net和PostgreSQL的程序集结果（几乎相同）。 输入如下：

     POLYGON ((1755828.2027002387 5944302.7072003055, 1755826.3549002428 5944302.8313003061, 1755825.1724002406 5944285.1574003045, 1755809.5710002393 5944286.4007003047, ......))";// geomobj.line; //"POINT (1736946.0983 5923253.9175)";

因此，解决方案如下：

1- 打开.NET并创建一个C#库，在其中粘贴以下代码（此代码获取每行数据，遍历每个元素，对其进行分解，提取每个纬度和经度，重新投影，并替换）。

 using Microsoft.SqlServer.Server;
 using System;
 using System.Collections;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;

public class classval
{
    public string line;
    public string src;
    public string dst;

    public classval(string line, string src, string dst)
    {
        this.line = line;
        this.src = src;
        this.dst = dst;
    }



}
public class RPG_Transform
{
    private static IEnumerable<classval> ConvertedEnumerable(string line, string src, string dst)
    {
        return new List<classval> { new classval(line, src, dst) };
    }

    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable STRPG_Transform(string Geometry, string src, string dst)
    {
        return ConvertedEnumerable(Geometry, src, dst);
    }

    private static void FillRow(Object classvalobj, out string Geometry, out string srcprj, out string dstprj)
    {

        classval geomobj = (classval)classvalobj;
        string _geometry = geomobj.line;  
        string proj4_src = geomobj.src;  
        string proj4_dst = geomobj.dst; 
        string _originalgeom = _geometry;
        _geometry = _geometry.Remove(0, _geometry.IndexOf('('));
        // _geometry = _geometry.Replace("(", "[ ");
        // _geometry = _geometry.Replace(")", " ]");
        string[] splitbycomma = _geometry.Split(',');

        foreach (var itembycomma in splitbycomma)
        {

            string tmpitem = itembycomma;
            tmpitem = tmpitem.Replace('(', ' ');
            tmpitem = tmpitem.Replace(')', ' ');
            tmpitem = tmpitem.Trim();
            string[] splitbyspace = tmpitem.Split(' ');
            for (int ibs = 0; ibs < splitbyspace.Length - 1; ibs++)
            {
                string originallonglat = splitbyspace[ibs] + " " + splitbyspace[ibs + 1];

                double[] yxval = new double[] { double.Parse(splitbyspace[ibs]), double.Parse(splitbyspace[ibs + 1]) };

                double[] z = new double[1] { 0 };




                DotSpatial.Projections.ProjectionInfo src =
                    DotSpatial.Projections.ProjectionInfo.FromProj4String(proj4_src);
                DotSpatial.Projections.ProjectionInfo trg =
                    DotSpatial.Projections.ProjectionInfo.FromProj4String(proj4_dst);

                DotSpatial.Projections.Reproject.ReprojectPoints(yxval, z, src, trg, 0, 1);

                string longlat = yxval[0] + " " + yxval[1];

                _originalgeom = _originalgeom.Replace(originallonglat, longlat);
            }
        }
        srcprj = proj4_src;
        dstprj = proj4_dst;
        Geometry = _originalgeom;
    }

}

2- 在运行之前，您需要安装一些库，最简单的方法是使用Nugget Manager，如下所示。

3- 接下来，您需要编译代码并获取一个dll文件，然后将这些dll文件复制到一个新文件夹中。如果您愿意，可以将它们留在debug文件夹中（由于依赖关系，某些dll文件被添加到项目中）如下所示。

4- 在MSSQL中启用CLR，然后将程序集添加到MSSQL服务器中（通过创建函数如下所示）

  sp_configure 'show advanced options', 1;  
 GO  
 RECONFIGURE;  
 GO  
 sp_configure 'clr enabled', 1;  
 GO  
 RECONFIGURE;  
 GO  

 ALTER DATABASE Prod SET trustworthy ON
  CREATE ASSEMBLY CLRFunctionAssem
  FROM N'E:\CLR\RPG_Transform\RPG_Transform\bin\Debug\RPG_Transform.dll'
  WITH PERMISSION_SET = UNSAFE
  GO

--DROP ASSEMBLY CLRFunctionAssem ---if you need to drop it in the future

   CREATE FUNCTION dbo.RPG_STTransform(@Geometry nvarchar(max), @src 
  nvarchar(max),@dst nvarchar(max))
    RETURNS TABLE
   ( _geom  nvarchar(max) ,srcprj  nvarchar(max) ,dstprj  nvarchar(max) 
  ) with execute as caller
  AS
   EXTERNAL NAME CLRFunctionAssem.[RPG_Transform].STRPG_Transform

5- 获取源投影和目标投影的公式。您需要从某个地方获取这两个公式。我通常使用以下网站获取源投影和目标投影的公式（此解决方法使用Proj4j，因此您需要从某个地方获取与srid相当的等效项）。
https://epsg.io/ 如下图所示：

目标投影同理。

6- 运行以下SQL函数：

         SELECT   top 1000 pk   , geom.STAsText() as input,conv._geom as ouput, 
    geometry::STGeomFromText(conv._geom,4326)
  FROM [Prod].[dbo].DPO_Geographic_Units as a  CROSS APPLY dbo.RPG_STTransform (
  a.Geom.STAsText()--geometry as text
 ,'+proj=tmerc +lat_0=0 +lon_0=173 +k=0.9996 +x_0=1600000 +y_0=10000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs ',--source =2193
 '+proj=longlat +datum=WGS84 +no_defs'--destination =4326
 ) as conv
 where [Geom] is not null

并将输入和输出放在一起：

如果您愿意，可以将这些投影公式保存在代码中，并使用switch case获取每个投影对应的公式，然后可以传递一个编号（例如2193），代码会找到相应的公式。

- Mohsen Sichani