SQL Geography

SQL Geography and Geometry.md

• WKT: Well-Known Text
• SRID: a unique identifier for the co-ordinate system to be used defined by EPSG. All Geography types in SQl Server have an associated SRID. Geography instances with different SRIDS cannot be compared without additional transformation.

Note: SRIDS must be the same when performing opertations on multiple geography instances*

SqlGeometry is 2D while SqlGeography is 3D+. It is worth considering deafaulting to SqlGeometry simply unless 3D measuements or localised geography systems are used. Many 3rd parties provide data in 2D and it is easier to transform geometries to difference co-ordinate systems.

Converting KML to WKT

Try SharpKml (https://github.com/samcragg/sharpkml) great library for manipulating KML

1. Load your KML:

var kml = KmlFile.Load(filename);

2. Find your elements

foreach(var placemark in file.Root.Flatten().OfType<Placemark>())
{
  var polygon = placemark.Geometry as Polygon;
  if (polygon != null)
  {
    var coordinates = polygon.OuterBoundary.LinearRing.Coordinates;
    ...

3. Generate your WKT

public string GetWkt(CoordinateCollection coordinates)
{
  return $"POLYGON(({string.Join(", ", coordinates.Select(c => $"{c.Longitude} {c.Latitude}"))}))"; // x y, ... (x != Lat x == Lng)
}

4. Win!

var sql = $"INSERT INTO [dbo].[Areas]([Name], [Definition]) VALUES ('{name}', geography::STGeomFromText('{GetWkt(coordinates)}', 4326))";

Using SqlGeometry and Geography in C#

Use the SqlServer.Types nuget package corresponding to the SQL Server version you are using: https://www.nuget.org/packages/Microsoft.SqlServer.Types/

SQL Server versions: https://support.microsoft.com/en-au/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an

eg.

• SQL Server 2008R2: 10.50.6000.34 => 10.50.1600.1
• SQL Server 2012 => 11.0.x
• SQL Server 2014 (SP1) => 12.0.4100.1
• SQL Server 2014 (SP2) => 12.0.5000
• SQL Server 2016 (RTM) => 13.0.1601.5
• SQL Server 2016 (SP1) => 13.0.4001.0
• SQL Server 2017 => 14.0.x

SQL Server 2008R2

Will need some manual work to get going

1. Create a folder "SqlServerTypes"
2. Create a subfolder x64
3. Copy SqlServerSpatial.dll from an SQL Server 2008R2 install (%systemroot%/system32/SqlSevrerSpatial.dll) into the x64 folder as 'Content' as 'Copy Always'
4. Rinse repeat for x86 if necessary (keep in mind IIS Express runs in 32bit mode by default and may need to be changed to x64 if x86 is not included)
5. Add the following class

using System;
using System.IO;
using System.Runtime.InteropServices;

namespace SqlServerTypes
{
    /// <summary>
    /// Utility methods related to CLR Types for SQL Server 
    /// </summary>
    public class Utilities
    {
        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        private static extern IntPtr LoadLibrary(string libname);

        /// <summary>
        /// Loads the required native assemblies for the current architecture (x86 or x64)
        /// </summary>
        /// <param name="rootApplicationPath">
        /// Root path of the current application. Use Server.MapPath(".") for ASP.NET applications
        /// and AppDomain.CurrentDomain.BaseDirectory for desktop applications.
        /// </param>
        public static void LoadNativeAssemblies(string rootApplicationPath)
        {
            var nativeBinaryPath = IntPtr.Size > 4
                ? Path.Combine(rootApplicationPath, @"SqlServerTypes\x64\")
                : Path.Combine(rootApplicationPath, @"SqlServerTypes\x86\");

            //LoadNativeAssembly(nativeBinaryPath, "msvcr80.dll");
            LoadNativeAssembly(nativeBinaryPath, "SqlServerSpatial.dll");
        }

        private static void LoadNativeAssembly(string nativeBinaryPath, string assemblyName)
        {
            var path = Path.Combine(nativeBinaryPath, assemblyName);
            var ptr = LoadLibrary(path);
            if (ptr == IntPtr.Zero)
            {
                throw new Exception(string.Format(
                    "Error loading {0} (ErrorCode: {1})",
                    assemblyName,
                    Marshal.GetLastWin32Error()));
            }
        }
    }
}