gistfile1.cs

using SpatialImporter.Models;
using System;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace SpatialImporter
{
    public static class DataQuality
    {
        // Used by EF to return details of an invalid boundary.
        private class ReturnWrapper
        {
            public long Id { get; set; }
            public string BoundaryName { get; set; }
        }

        // A collection of SQL statements used to clean up dodgy data.
        private static class SqlStatements
        {
            public static string ExistsAnInvalidBoundary =
                @"
SELECT  TOP(1) Id, BoundaryName
FROM    dbo.Boundaries
WHERE   Data.STIsValid() = 0;
";

            public static string FixSingleBoundarySql =
                @"
UPDATE TOP(1)	dbo.Boundaries
SET		Data = Data.MakeValid()
WHERE   Id = @Id
   AND  BoundaryName = @BoundaryName; 
";

            public static string FixRingOrders =
                @"
declare @h geography = 'FULLGLOBE';

update dbo.Boundaries
set Data = Data.ReorientObject()
where Data.STArea() > @h.STArea() / 2;
";
        }

        // Set to something useful when you use it, e.g DataQuality.Log = Console.WriteLine;
        public static Action<string> Log = s => { };

        // Run this method to sort the boundaries out.
        public static void FixAllBoundaries()
        {
            using (var ctx = new SpatialContext())
            {
                ctx.Database.CommandTimeout = 60*60; // 60 minutes per command.
                long id;
                string name;
                // Do one boundary at time as they can take bloody ages and it's nice
                // to be able to have feedback as it progresses.
                while (ThereIsAnInvalidBoundary(ctx, out id, out name))
                {
                    Log("Fixing boundary: " + id + "...");
                    ctx.Database.ExecuteSqlCommand(SqlStatements.FixSingleBoundarySql, 
                            new SqlParameter("@Id", id),
                            new SqlParameter("@BoundaryName", name));
                    Log("Boundary: " + id + " fixed.");
                }
            }
            Log("All boundaries are valid.");
        }

        private static bool ThereIsAnInvalidBoundary(DbContext ctx, out long id, out string boundaryName)
        {
            id = 0;
            boundaryName = "";
            var query = ctx.Database.SqlQuery<ReturnWrapper>(SqlStatements.ExistsAnInvalidBoundary).ToList();
            var resultFound = query.Any();
            if (resultFound)
            {
                id = query.First().Id;
                boundaryName = query.First().BoundaryName;
            }
            return resultFound;
        }

        public static void FixRingOrders()
        {
            Log("Fixing ring orders...");
            using (var ctx = new SpatialContext())
            {
                ctx.Database.CommandTimeout = 10*60;
                ctx.Database.ExecuteSqlCommand(SqlStatements.FixRingOrders);
            }
            Log("Ring orders fixed.");
        }
    }
}