Calculating distance between two points gives very different answers compared to SQL Server

[richardthombs]

I can't believe that something like this is really a bug, more likely it is my inadequate understanding of Postgres / PostGIS and NetTopologySuite.

TDLR: I am puzzled why this example code gives the correct distances when using .UseSqlServer but very different values when the DbContext is created using .UseNpgSql instead.

Repo with a repro: richardthombs/spatial-test

---

For the SQL Server tests, I used SQL Server Express 14.0.1000.

For the Postgres tests, I used the postgis/postgis docker container launched as follows:

docker run --name postgres -e POSTGRES_PASSWORD=secret -d -p 5432:5432 --rm postgis/postgis

The test

Using this Place class:

public class Place
{
    public int PlaceID { get; set; }
    public string Name { get; set; }
    public Point Point { get; set; }
}

I create 3 instances

ctx.Places.Add(new Place { Name = "Oxford", Point = new Point(-1.2577263, 51.7520209) { SRID = 4326 } });
ctx.Places.Add(new Place { Name = "Swindon", Point = new Point(-1.7797176, 51.5557739) { SRID = 4326 } });
ctx.Places.Add(new Place { Name = "Reading", Point = new Point(-0.9781303, 51.4542645) { SRID = 4326 } });

Then query the distance from a point:

var distances = ctx.Places
    .Select(x => new { x.Name, Distance = x.Point.Distance(reading) })
    .ToList();

I do this for SQL Server and for Postgres and get very different distances:

SQL Server
Oxford 38376.197446871825
Swindon 56790.50143570342
Reading 0

Postgres with PostGIS
Oxford 0.4084517070070321
Swindon 0.8079890827292466
Reading 0

SQL Server seems to be in meters, but I have no clue what is going on with Postgres!

[roji]

@richardthombs I haven't looked at your repro yet, but do you maybe need to use geography on PostGIS? Take a look at the PostGIS docs to understand the difference between geometry and geography.

[richardthombs]

@roji Thanks for the fast reply. Do you happen to know how I can coerce EF Core into using the Geography type with Npgsql?

[richardthombs]

Ah, think I see it: https://www.npgsql.org/efcore/mapping/nts.html#geography-geodetic-support

So basically I have to add this in OnModelCreating:

if (Database.IsNpgsql()) builder.Entity<Place>().Property(x => x.Point).HasColumnType("geography (point)");

It would be very helpful if there was a global configuration option to switch the type translation to do this by default.

I guess I will close this and open a feature request instead :)

Thanks for your help @roji