Converting / Designing geometry from one SRID to another

Question

I have a database table that currently stores geometric data in the SRID 27700 (British National Grid). However, when I receive the data, I need to convert it to SRID 4326 (WGS84). Is there a way to apply a function like ST_Transform found in PostGIS to my data to get the result I need?

NOTE. The solution must be implemented using T-SQL and non-stored procedures, etc. I should be able to build a statement and save it in the table as a row field for later search. This is because my solution is an agnostic database.

The way I'm doing it now 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' 

In PostgreSQL using PostGIS, I do 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)) 

Answer 1

You can wrap something like DotNetCoords in the SQL CLR function for this.

See here: - http://www.doogal.co.uk/dotnetcoords.php

I wrapped it in a CLR function to convert the coordinates from Easting / Northing to Lat / Long, which I think is what you are asking for. After implementing the CLR function, this is a pure SQL solution (i.e. you can run all of this in a stored procedure or view).

EDIT . I will post an example code here when I work tomorrow, I hope this helps.

EDIT . You 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. The documentation for the library is here http://www.doogal.co.uk/Help/Index.html

What you can do then, you can add a new class to source files like 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); } } 

Then you will need to assemble and import the assembly into SQL Server (replace the paths with your own locations) (for some reason I cannot install the assembly when PERMISSION_SET is "SAFE", so I would sort it first before setting up the production environment) .

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

Then you need to create a SQL Server function to interact with 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] 

This is an installed CLR function.

Then you can call the function directly from SQL Server to convert (I mixed the numbers in this post to preserve anonymity so that they might not make sense here, but the function really works).

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

To use it in a result set, 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) 

Answer 2

Unfortunately, this is simply not possible. Spatial Tools SQL Server provides several reprogramming features, but they are only for a very small number of predictions (not the ones you need).

There is an example from SQL Server tools - https://bitbucket.org/geographika/sql-server-spatial-tools/src/5ca44b55d3f3/SQL%20Scripts/projection_example.sql - but this will not help you because they do not support projection, which you are talking about.

So, you need to make another decision - either preprocess the data to add a new column with the projected values, or reprogram it in your code.