将几何图形从一个 SRID 转换/投影到另一个 [英] Transform/ Project a geometry from one SRID to another
问题描述
我有一个数据库表，它当前在 SRID 27700(英国国家网格)中保存几何数据.在检索数据时，我需要将其转换为 SRID 4326 (WGS84).有什么方法可以将 PostGIS 中的 ST_Transform 等函数应用于我的数据以获得我需要的结果?
I have a database table which currently holds geometric data in SRID 27700 (British National Grid). While retrieving the data however I need to transform it to SRID 4326 (WGS84). Is there any way to apply a function such as ST_Transform found in PostGIS to my data in order to get the result I need?
注意:该解决方案需要能够使用 T-SQL 而不是存储过程等来实现.我必须能够构造一个语句并将其作为字符串字段保存在表中以供以后检索.这是因为我的解决方案与数据库无关.
NOTE: The solution needs to be able to be implemented using T-SQL and not stored procedures etc. I have to be able to construct a statement and have it saved in a table as a string field for retrieval later. This is because my solution is database agnostic.
我目前在 Oracle 中这样做的方式如下:
The way I am currently doing this in Oracle is as follows:
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 中，我是这样做的:
In PostgreSQL using PostGIS I am doing it like this:
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))
推荐答案
您可以将 DotNetCoords 之类的东西包装在 SQL CLR 函数中来执行此操作.
You could wrap something like DotNetCoords in a SQL CLR function to do this.
请参阅此处:- http://www.doogal.co.uk/dotnetcoords.php
我将它包装在一个 CLR 函数中，以将坐标从东/北转换为纬度/经度，我认为这正是您所要求的.一旦实现了 CLR 函数，它就是一个纯 SQL 解决方案(即您可以在存储过程或视图中运行它).
I've wrapped it in a CLR function to convert coordinates from Easting/Northing to Lat/Long which I think is what you are asking for. Once the CLR function is implemented it is a pure SQL solution (i.e. you can run it all in a Stored Procedure or View).
编辑:我明天上班时会在此处发布一些示例代码，希望对您有所帮助.
EDIT: I will post some sample code up here when I get to work tomorrow, hopefully it will help.
编辑:您需要从 http 下载源代码://www.doogal.co.uk/dotnetcoords.php 并且您需要 Visual Studio 来打开和修改它.图书馆的文档在这里 http://www.doogal.co.uk/Help/Index.html
EDIT: You'll need to download the source code from http://www.doogal.co.uk/dotnetcoords.php and you will need Visual Studio to open and modify it. Documentation for the library is here http://www.doogal.co.uk/Help/Index.html
然后你可以做的是你可以在源文件中添加一个新的类，类似于:-
What you can do then is you can add a new class to the source files similar to this:-
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"时我无法安装程序集，因此我会在安装之前先对其进行排序生产环境).
You will then need to build and import the assembly into SQL Server (replace paths with your own locations) (for some reason I cannot get the assembly to install when PERMISSION_SET is 'SAFE' so I would sort this first before installing in a production environment).
CREATE ASSEMBLY DotNetCoords
FROM N'C:\Projects\DotNetCoords\bin\Debug\DotNetCoords.dll'
WITH PERMISSION_SET = UNSAFE
GO
然后您需要创建一个 SQL Server 函数来连接到 CLR 函数:-
You'll then need to create a SQL Server function to interface to the CLR function:-
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函数.
This is the CLR function installed then.
然后，您应该可以直接从 SQL Server 调用该函数来进行转换(我也混淆了这篇文章中的数字并保持匿名，因此它们在这里可能没有意义，但该函数确实可以正常工作).
You should then be able to call the function direct from SQL Server to do your conversion (I have mixed up the numbers in this post too keep anonymity so they might not make sense here but the function does work fine).
/*------------------------
SELECT Latitude, Longitude FROM dbo.ToLatLong(327262, 357394)
------------------------*/
Latitude Longitude
52.13413530182533 -9.34267170569508
(1 row(s) affected)
要在结果集中使用它，您需要使用 CROSS APPLY 子句:-
To use it in a resultset you need to use the CROSS APPLY clause:-
/*------------------------
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)
这篇关于将几何图形从一个 SRID 转换/投影到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
