I am trying to do some reprojection in SQL Server, but unfortunately I can't find any solution for it. I know this is not implemented in SQL Server, so wondering if there is any shortcut for it.
Some people suggested using of external libraries such as ogr2ogr while this is not a good option for me as I need to do it on the fly. I have found a post here Transform/ Project a geometry from one SRID to another, but this one converts to lat and long and has some limited capability.
I am looking for a solution to allow me to do reprojection in any srid, very similar to ST_Transform
in postgresql (postgis)
Well, if you are looking for an easy option, I am afraid it is not possible. But there is a workaround for this problem. I had a similar issue a while ago, and I had to implement a CLR in .NET and then import this as an assembly in MSSQL, a bit slow, but works fine. The inputs are: the Source projection, destination projection, and geometry as text, and the output is re-projected geometry as text. Please see the result of this assembly from .Net and PostgreSQL next to each other (almost the same).
and the input was:
So, the solution is as follows:
1- Open .NET and create a c# library and paste the following code there (this code get each line of data, goes through each element, breaks it, extracts each long and lat, reprojects it, and replaces it)
2- Before running this, you need to install some libraries and the easiest one is using Nugget Manager as shown below.
3- Next you need to compile the code and get a dll, and then copy these dll (s) to a new folder, if you like you leave them in the debug folder (some of these dll are added to the projects due to dependencies) as shown below.
4- Enable CLR in MSSQL and then add the assembly to MSSQL server (by creating a function as shown below)
5- Getting the formula for the source and destination projection. You need to get these two formulas from somewhere. For me, for getting the source projection and destination projection, I use the following website.(This solution uses Proj4j, so you need to get the equivalent of the srid from somewhere).
https://epsg.io/ as shown below:
and the same for destination
6- Running the sql function as shown below:
and the input and output next to each other:
If you like, you can save these projection formulas in the code and use a switch case to obtain the corresponded formula for each projection, then you can pass a number (e.g. 2193) and then code finds the equivalent formula.