Transform/ Project a geometry from one SRID to another

Question

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?

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.

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'

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))

Valuation

Answer 1

You could wrap something like DotNetCoords in a SQL CLR function to do this.

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

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.

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);
    }

}

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

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]

This is the CLR function installed then.

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)

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)

Shallow

While this is currently untested in my situation it does seem to be pretty much the best solution to my problem that I have come across so far and I cannot see there being a problem with implementing it. Thanks for taking the time to post it. I have awarded the bounty to you. – Valuation

Thanks, I havent had time to look further into it, but it would be good to implement all the functions of the library as CLR functions and sort it so it installs with 'SAFE' pesmissions. – Shallow

Answer 2

Unfortunately, this simply isn't possible. SQL Server Spatial Tools provides a few reprojection functions, but they are only for a very few number of projections (and not the one you require).

There is an example from SQL server tools -- https://bitbucket.org/geographika/sql-server-spatial-tools/src/5ca44b55d3f3/SQL%20Scripts/projection_example.sql -- but it won't help you because they don't support the projection you are talking about.

So, you'll need to adopt a different solution -- either pre-process the data to add a new column with projected values, or reproject in your code.

Darendaresay