我有一个数据库表,当前在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'
在使用PostGIS的PostgreSQL中,我这样做是这样的:
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))
最佳答案
您可以在SQL CLR函数中包装类似DotNetCoords之类的文件。
看到这里:-http://www.doogal.co.uk/dotnetcoords.php
我将其包装在CLR函数中,以将坐标从东/北转换为纬度/经度,我认为这是您所要的。一旦实现了CLR函数,它就是一个纯SQL解决方案(即,您可以在存储过程或 View 中全部运行它)。
编辑:明天上类时,我将在此处发布一些示例代码,希望对您有所帮助。
编辑:您需要从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)
关于sql-server-2008 - 将几何图形从一个SRID转换/投影到另一个,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/10502063/