将几何形状从一个空间参考系(SRID)转换/投影到另一个空间参考系。

Question

我有一个数据库表，目前它保存在SRID 27700（英国国家网格）中的几何数据。然而，在检索数据时，我需要将其转换为SRID 4326（WGS84）。是否有一种方法可以应用诸如PostGIS中的ST_Transform函数来处理我的数据，以便获得所需的结果？

注意：解决方案需要能够使用T-SQL实现，而不是存储过程等。我必须能够构造一条语句，并将其保存在表中作为字符串字段以供以后检索。这是因为我的解决方案与数据库无关。

我目前在Oracle中执行此操作的方式如下：

select CLUSTER_ID, 
       NUM_POINTS, 
       FEATURE_PK, 
       A.CELL_CENTROID.SDO_POINT.X, 
       A.CELL_CENTROID.SDO_POINT.Y, 
       A.CLUSTER_CENTROID.SDO_POINT.X, 
       A.CLUSTER_CENTROID.SDO_POINT.Y, 
       TO_CHAR (A.CLUSTER_EXTENT.GET_WKT ()),  
       TO_CHAR (A.CELL_GEOM.GET_WKT ()), 
       A.CLUSTER_EXTENT.SDO_SRID 
from (SELECT CLUSTER_ID, 
             NUM_POINTS, 
             FEATURE_PK, 
             SDO_CS.transform (CLUSTER_CENTROID, 4326) cluster_centroid,
             CLUSTER_EXTENT, 
             SDO_CS.transform (CELL_CENTROID, 4326) cell_centroid, 
             CELL_GEOM FROM :0) a  
where sdo_filter( A.CELL_GEOM, 
                  SDO_CS.transform(mdsys.sdo_geometry(2003, :1, NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(:2, :3, :4, :5)),81989)) = 'TRUE'

在 PostgreSQL 中使用 PostGIS，我是这样做的：

select CLUSTER_ID, 
       NUM_POINTS, 
       FEATURE_PK, ST_X(a.CELL_CENTROID), 
       ST_Y(a.CELL_CENTROID), 
       ST_X(ST_TRANSFORM(a.CLUSTER_CENTROID, 4326)),  
       ST_Y(ST_TRANSFORM(a.CLUSTER_CENTROID, 4326)), 
       ST_AsText(a.CLUSTER_EXTENT),  
       ST_AsText(a.CELL_GEOM), 
       ST_SRID(a.CLUSTER_EXTENT)  
FROM (SELECT CLUSTER_ID, 
      NUM_POINTS, 
      FEATURE_PK, 
      ST_TRANSFORM(ST_SetSRID(CLUSTER_CENTROID, 27700), 4326) cluster_centroid, 
      CLUSTER_EXTENT, 
      ST_TRANSFORM(ST_SetSRID(CELL_CENTROID, 27700), 4326) cell_centroid, 
      CELL_GEOM 
from :0) AS a 
where ST_Intersects(ST_Transform(ST_SetSRID(a.CELL_GEOM, 27700), :1), ST_Transform(ST_GeomFromText('POLYGON(('||:2||' '||:3||', '||:4||' '||:3||', '||:4||' '||:5||', '||:2||' '||:5||', '||:2||' '||:3||'))', 4326), :1))

- CSharpened

Answer 1

您可以将类似于DotNetCoords的内容封装在SQL CLR函数中来完成此操作。

请参见：http://www.doogal.co.uk/dotnetcoords.php 我已经将其封装在CLR函数中，以将坐标从东/北转换为纬度/经度，我认为这就是您所要求的。一旦实现了CLR函数，它就是一个纯SQL解决方案（即您可以在存储过程或视图中全部运行它）。

编辑：明天上班时我会在这里发布一些示例代码，希望能有所帮助。

编辑：您需要从http://www.doogal.co.uk/dotnetcoords.php下载源代码，并且您需要Visual Studio来打开和修改它。该库的文档在此处：http://www.doogal.co.uk/Help/Index.html 然后，您可以向源文件添加一个新类，类似于以下内容：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using DotNetCoords;
using Microsoft.SqlServer.Server;

/// <summary>
/// Sql Server CLR functions for the DotNetCoords library.
/// </summary>
public class CLRFunctions
{

    /// <summary>
    /// Coordinateses the enumerable.
    /// </summary>
    /// <param name="Easting">The easting.</param>
    /// <param name="Northing">The northing.</param>
    /// <returns></returns>
    private static IEnumerable<OSRef> CoordinatesEnumerable(double Easting, double Northing)
    {
        return new List<OSRef> { new OSRef(Easting,Northing) };
    }

    /// <summary>
    /// Toes the lat long.
    /// </summary>
    /// <param name="Easting">The easting.</param>
    /// <param name="Northing">The northing.</param>
    /// <returns></returns>
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable ToLatLong(double Easting, double Northing)
    {
        return CoordinatesEnumerable(Easting, Northing);
    }

    /// <summary>
    /// Fills the row.
    /// </summary>
    /// <param name="obj">The obj.</param>
    /// <param name="Lat">The lat.</param>
    /// <param name="Long">The long.</param>
    private static void FillRow(Object obj, out SqlDouble Lat, out SqlDouble Long)
    {
        OSRef Coordinates = (OSRef)obj;
        LatLng latlong = Coordinates.ToLatLng();
        latlong.ToWGS84();
        Lat = new SqlDouble(latlong.Latitude);
        Long = new SqlDouble(latlong.Longitude);
    }

}

您需要构建并导入程序集到SQL Server（将路径替换为您自己的位置）（由于某些原因，当PERMISSION_SET为'SAFE'时，我无法安装程序集，因此在生产环境中安装之前，请先解决此问题）。

CREATE ASSEMBLY DotNetCoords
FROM N'C:\Projects\DotNetCoords\bin\Debug\DotNetCoords.dll'
WITH PERMISSION_SET = UNSAFE
GO

接下来，您需要创建一个SQL Server函数来与CLR函数进行交互：

CREATE FUNCTION dbo.ToLatLong(@Easting float, @Northing float)
RETURNS TABLE
(Latitude float null, Longitude float null) with execute as caller
AS
EXTERNAL NAME [DotNetCoords].[CLRFunctions].[ToLatLong]

这是安装的CLR函数。

然后，您应该能够直接从SQL Server调用该函数进行转换（我在此帖子中混淆了数字以保持匿名性，因此它们可能在这里没有意义，但该函数确实可以正常工作）。

/*------------------------
SELECT Latitude, Longitude FROM dbo.ToLatLong(327262, 357394)
------------------------*/
Latitude            Longitude
52.13413530182533       -9.34267170569508

(1 row(s) affected)

要在结果集中使用它，您需要使用CROSS APPLY子句：-

/*------------------------
SELECT TOP 2    a.[Column 0] AS osaddessp,
                            a.[Column 9] AS east,
                            a.[Column 10] AS north,
                            c.[Latitude] AS lat,
                            c.[Longitude] AS long
FROM    MyTable AS a CROSS APPLY ToLatLong (a.[Column 9], a.[Column 10]) AS c;
------------------------*/
osaddessp       east    north   lat         long
100134385607    327862  334794  52.3434530182533    -2.19342342569508
100123433149    780268  353406  52.3453417606796    -3.19252323679263

(10 row(s) affected)

- general exception

---

虽然在我的情况下目前还没有经过测试，但它似乎是我迄今遇到的解决问题的最佳方案，我认为实施它不会有任何问题。感谢您抽出时间发布它。我已将赏金授予您。 - CSharpened

谢谢，我还没有时间进一步研究它，但是实现库的所有功能作为CLR函数并对其进行排序以便使用“SAFE”权限安装将是很好的。 - general exception

Answer 2

很遗憾，这是不可能的。 SQL Server Spatial Tools 提供了一些重投影功能，但它们仅适用于极少数的投影（而不是您需要的那个）。 有一个 来自 SQL server 工具的示例 -- https://bitbucket.org/geographika/sql-server-spatial-tools/src/5ca44b55d3f3/SQL%20Scripts/projection_example.sql -- 但它对您没有帮助，因为它们不支持您所说的投影。 因此，您需要采用另一种解决方案--要么预处理数据以添加具有投影值的新列，要么在代码中进行重投影。

- Josh Marcus