SqlServerSpatial160.dll for Linux

Question

I'm trying to get our .NET Core 7.0 solution building and running on Linux. This solution makes heavy use of SQL Server spatial types (https://www.nuget.org/packages/Microsoft.SqlServer.Types/), which have a dependency on a native SQL Server type implementation in SqlServerSpatial160.dll.

For instance, see these lines from GLNativeMethods.cs in the nuget package:

[DllImport("SqlServerSpatial160.dll")]
public static extern double GeodeticPointDistance([In] Point p1, [In] Point p2, [In] EllipsoidParameters ep);

[DllImport("SqlServerSpatial160.dll")]
private static extern GL_HResult Outline(
      [In] GeoMarshalData g,
      [In, Out] GeoDataPinningAllocator resultAllocator);

The nuget package comes with appropriate implementations for Win64 and Win86:

But it doesn't seem to come with anything for Linux. That seems to be confirmed by the results we get from any code path that needs a geospatial type.

  Failed Swyfft.Common.UnitTests.Helpers.BestPointUnitTests.GetBestLatLon_H2H1_ShouldReturnHigh [1 ms]
  Error Message:
   System.DllNotFoundException : Unable to load shared library 'SqlServerSpatial160.dll' or one of its dependencies. In order to help diagnose loading problems, consider using a tool like strace. If you're using glibc, consider setting the LD_DEBUG environment variable:
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/runtimes/linux-x64/native/SqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/usr/share/dotnet/shared/Microsoft.NETCore.App/7.0.2/SqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/SqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/runtimes/linux-x64/native/libSqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/usr/share/dotnet/shared/Microsoft.NETCore.App/7.0.2/libSqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/libSqlServerSpatial160.dll.so: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/runtimes/linux-x64/native/SqlServerSpatial160.dll: cannot open shared object file: No such file or directory
/usr/share/dotnet/shared/Microsoft.NETCore.App/7.0.2/SqlServerSpatial160.dll: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/SqlServerSpatial160.dll: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/runtimes/linux-x64/native/libSqlServerSpatial160.dll: cannot open shared object file: No such file or directory
/usr/share/dotnet/shared/Microsoft.NETCore.App/7.0.2/libSqlServerSpatial160.dll: cannot open shared object file: No such file or directory
/home/ken/swyfft_web/Swyfft.Common.UnitTests/bin/Debug/net7.0/libSqlServerSpatial160.dll: cannot open shared object file: No such file or directory

  Stack Trace:
     at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticPointDistance(Point p1, Point p2, EllipsoidParameters ep)   at Microsoft.SqlServer.Types.SqlGeography.STDistance(SqlGeography other)
   at Swyfft.Common.Geo.SqlGeographyHelpers.GetDistance(IMappable p1, IMappable p2) in /home/ken/swyfft_web/Swyfft.Common/Geo/SqlGeographyHelpers.cs:line 104

So ... is there a Linux version of this anywhere? If not, is there a solution for folks like us who need to use geospatial types on SQL Server on Linux?

Ken Smith · Answer 1 · 2023-01-31T17:00:09.290

It turns out that for certain operations (like .GLNativeMethods.GeodeticPointDistance(), Microsoft.SqlServer.Types just calls SqlServerSpatial160.dll, which is a Windows-only library. So while you can generally use SQL Server types on Linux, there are a number of operations on those types that you can't do.

The solution I found was to find alternate implementations of the client-side operations I needed to do. For instance:

    /// <summary>
    /// This method uses a simplified method to calculate the Great Circle distance between two points.
    /// See https://stackoverflow.com/a/27883916/68231. It's supposedly not as accurate at small distances as the SQL Server
    /// method above - it doesn't take into account that the earth isn't a perfect sphere - but it's close enough for our purposes.
    /// </summary>
    public static double GetGreatCircleDistance(IMappable p1, IMappable p2)
    {
        const double radiusInMeters = 6_371_000;

        var radLat1 = Radians(p1.Latitude);
        var radLat2 = Radians(p2.Latitude);
        var dLatHalf = (radLat2 - radLat1) / 2;
        var dLonHalf = Math.PI * ((double)p2.Longitude - (double)p1.Longitude) / 360;

        // intermediate result
        var a = Math.Sin(dLatHalf);
        a *= a;

        // intermediate result
        var b = Math.Sin(dLonHalf);
        b *= b * Math.Cos(radLat1) * Math.Cos(radLat2);

        // central angle, aka arc segment angular distance
        var centralAngle = 2 * Math.Atan2(Math.Sqrt(a + b), Math.Sqrt(1 - a - b));

        return radiusInMeters * centralAngle;
    }

    private static double Radians(decimal x)
    {
        return (double)x * Math.PI / 180;
    }

See https://stackoverflow.com/a/27883916/68231 for more details on the implementation.

SqlServerSpatial160.dll for Linux

1 Answers1