2.2 Preview 3 SQL Server Computed Location Column - Distance Function

[aherrick]

I have a SQL Server DB with a Latitude, Longitude, and Location column (computed)

In EF, I should be able to use the Distance function to query. When I use it, I receive no results. I should receive my Venue back as it's less than 100 miles away (per the query)

See the stripped down test case below.

using GeoAPI.Geometries;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite;
using System.Linq;

namespace Location
{
    public class Venue
    {
        public int Id { get; set; }
        public IGeometry Location { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }

    public class Ctx : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0",
                    b => b.UseNetTopologySuite());

        public DbSet<Venue> Venue { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Venue>(entity =>
            {
                // this is how db scaffold generates the column
                entity.Property(e => e.Location).HasComputedColumnSql("([geography]::Point([Latitude],[Longitude],(4326)))");
            });
        }
    }

    public class Program
    {
        public const double MILES_TO_METERS = 1609.344;

        public static void Main()
        {
            var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

            using (var context = new Ctx())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var lat = 39.9568;
                var lng = -86.1432;
                var point = geometryFactory.CreatePoint(new Coordinate(lat, lng));

                // not exactly sure why I have to explicity add Location as it should be computed if
                // not, it throws null pointer
                context.Add(new Venue { Latitude = lat, Longitude = lng, Location = point });

                context.SaveChanges();
            }

            using (var context = new Ctx())
            {
                var radiusMiles = 100;
                var distanceMax = MILES_TO_METERS * radiusMiles;
                var currentLoc = geometryFactory.CreatePoint(new Coordinate(39.869730, -86.142460));

                var venues = (from v in context.Venue
                              where v.Location.Distance(currentLoc) <= distanceMax
                              select v).ToList();

                //venues.Count == 0, should be 1
            }
        }
    }
}

[ajcvickers]

@aherrick This is because in an NTS Coordinate type, X is longitude, and Y is latitude. The code above has them switched in the two places that you create a Coordinate instance.

[ajcvickers]

Stack trace of null-ref issue is the same as #13457. Tested with current 2.2 bits and it works.

Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object.
   at lambda_method(Closure , InternalEntityEntry )
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.OriginalValues..ctor(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.EnsureOriginalValues()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntrySubscriber.SnapshotAndSubscribe(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.StartTracking(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.PaintAction(EntityEntryGraphNode node, Boolean force)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityEntryGraphIterator.TraverseGraph[TState](EntityEntryGraphNode node, TState state, Func`3 handleNode)
   at Microsoft.EntityFrameworkCore.DbContext.SetEntityState[TEntity](TEntity entity, EntityState entityState)
   at Program.Main() in C:\Stuff\TwoTwoPreview\TwoTwoPreview\Program.cs:line 59
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
      'Ctx' disposed.

[aherrick]

Thanks! Seems strange I've always seen Lat,Lng

Anyway, can you help me understand what value Location.Distance(point) returns? I can't seem to understand. Doesn't seem to be Meters or anything I can easily convert to Miles. I've resorted to a custom DistanceTo method. In EF Distance always returned Meters.

using GeoAPI.Geometries;
using Microsoft.EntityFrameworkCore;
using NetTopologySuite;
using System;
using System.Linq;

namespace Location
{
    public class Venue
    {
        public int Id { get; set; }
        public IGeometry Location { get; set; }
        public double Latitude { get; set; }
        public double Longitude { get; set; }
    }

    public class Ctx : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0",
                    b => b.UseNetTopologySuite());

        public DbSet<Venue> Venue { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Venue>(entity =>
            {
                // this is how db scaffold generates the column
                entity.Property(e => e.Location).HasComputedColumnSql("([geography]::Point([Latitude],[Longitude],(4326)))");
            });
        }
    }

    public class Program
    {
        public const double MILES_TO_METERS = 1609.344;

        public static void Main()
        {
            var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);

            using (var context = new Ctx())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var lat = 39.9568;
                var lng = -86.1432;
                var point = geometryFactory.CreatePoint(new Coordinate(lng, lat));

                // not exactly sure why I have to explicity add Location as it should be computed if
                // not, it throws null pointer
                context.Add(new Venue { Latitude = lat, Longitude = lng, Location = point });

                context.SaveChanges();
            }

            using (var context = new Ctx())
            {
                var radiusMiles = 100;
                var distanceMax = MILES_TO_METERS * radiusMiles;
                var currentLng = -86.142460;
                var currnetLat = 39.869730;
                var currentLoc = geometryFactory.CreatePoint(new Coordinate(currentLng, currnetLat));

                var venues = (from v in context.Venue
                              where v.Location.Distance(currentLoc) <= distanceMax
                              select v).ToList();

                // 1 is correct
                Console.WriteLine(venues.Count);

                // what is this value??
                Console.WriteLine(venues.First().Location.Distance(currentLoc));

                Console.ReadLine();
            }
        }
    }
}

[corstian]

@aherrick, I believe it returns arc degrees. According to NASA 1 arc degree is 111'139 meters. I'm not sure how this translates to WGS84 though.

It would be great though if there would be methods that return reasonable values for distance calculations and other common tasks, because I would see myself copying the code multiple times to different projects. (Calculating bearing between two points is another common use-case for me, as example)

[aherrick]

@corstian Thanks for the comment. In EF (not Core) Distance would just return meters which was great plus it was baked in.

I'm currently using the following method. It works, but again not ideal.

Any thoughts from the team? @ajvickers @bricelam Thank you!

   public static double DistanceTo(double lat1, double lon1, double lat2, double lon2, char unit = 'K')
        {
            double rlat1 = Math.PI * lat1 / 180;
            double rlat2 = Math.PI * lat2 / 180;
            double theta = lon1 - lon2;
            double rtheta = Math.PI * theta / 180;
            double dist =
                Math.Sin(rlat1) * Math.Sin(rlat2) + Math.Cos(rlat1) *
                Math.Cos(rlat2) * Math.Cos(rtheta);
            dist = Math.Acos(dist);
            dist = dist * 180 / Math.PI;
            dist = dist * 60 * 1.1515;

            switch (unit)
            {
                case 'K': //Kilometers -> default
                    return dist * 1.609344;

                case 'N': //Nautical Miles
                    return dist * 0.8684;

                case 'M': //Miles
                    return dist;
            }

            return dist;
        }

[aherrick]

Additionally, It appears to return different values when called in memory vs against the DB.

// pull all venues into memory (imagine a cache), doesn't work
  var venues = (from v in await DB.Venue.ToListAsync()
                          where v.Location.Distance(point) <= distanceMax
                        select v).ToList();

// filter from DB, works
            var venues = await (from v in DB.Venue
                                where v.Location.Distance(point) <= distanceMax
                                select v).ToListAsync();

[ajcvickers]

@aherrick For SQL Server, the default mapping is to "geography" types. This means that when SQL Server performs the Distance calculation it will return the distance in meters. For example, this:

Console.WriteLine(context.Venue.Select(v => v.Location.Distance(currentLoc)).First());

will output ~9668 meters, which is ~6 miles.

However, if you perform the calculation on the client, rather than in SQL Server, such as your code is doing, then NTS performs raw geometry values since it doesn't support geography calculations.

This is certainly something we are concerned about from the usability perspective. We will likely follow up with the NTS people on this, and also highlight it in the docs.

[aherrick]

Exactly what I'm seeing... Thank you!

[bricelam]

We've discussed it with the NTS team, but I couldn't find an issue for it. You may want to submit a new issue on them to rally support from the community.

[corstian]

@aherrick if you're filing an issue be sure to link to here. You've got my vote!

[aherrick]

@bricelam are we certain this is the case? When I switch back to Preview 2, the Coordinate constuctor works with Latitude, Longitude.

            var coord = new Coordinate(lat, lng);

            var geomFactory = new GeometryFactory(new PrecisionModel(), 4326);
            return geomFactory.CreatePoint(coord);

[bricelam]

Preview 2 maps to SQL Server geometry columns which calculates without units on a Cartesian plane so flipping x and y will give the same result.

Preview 3 maps to geography where switching lat and long will make a difference.

[tmutton]

What's the current state with this? I'm seeing units in what looks like degrees on SQLite. Has this been fixed in the latest Preview?

[ajcvickers]

@tmutton This issue has been closed, and the final release of 2.2 was released some time ago. If you're having an issue with the latest release, then please file a new issue containing a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.