SQL Server / EF Core / NetTopologySuite / ProjNet different Distance results

[wstaelens]

Not sure if I need to ask on https://github.com/dotnet/efcore or here.

Database with table Record and Location column.

Location is of type geography (srid 4326):

double maxDistance = 25000d; // 25km
  
          var results = await _Context.Records
                .Where(r => r.Location != null && r.Location.Distance(currentLocation) < maxDistance)
                .OrderBy(r => r.Location.Distance(currentLocation))
		.Take(10)
                .AsNoTracking()
                .ToListAsync();

Gives me for distance (server-evaluated):

1. 48981,05505461556
2. 52814,82467814827
3. 57150,41554145624

when transforming these locations (client-side) to EPSG:3812 and recalculating distance again I get:

  var wgs84_epsg4326 = GeographicCoordinateSystem.WGS84;

            string wktLambert2008 = @"PROJCS[""ETRS89 / Belgian Lambert 2008"",
    GEOGCS[""ETRS89"",
        DATUM[""European_Terrestrial_Reference_System_1989"",
            SPHEROID[""GRS 1980"",6378137,298.257222101,
                AUTHORITY[""EPSG"",""7019""]],
            TOWGS84[0,0,0,0,0,0,0],
            AUTHORITY[""EPSG"",""6258""]],
        PRIMEM[""Greenwich"",0,
            AUTHORITY[""EPSG"",""8901""]],
        UNIT[""degree"",0.0174532925199433,
            AUTHORITY[""EPSG"",""9122""]],
        AUTHORITY[""EPSG"",""4258""]],
    PROJECTION[""Lambert_Conformal_Conic_2SP""],
    PARAMETER[""latitude_of_origin"",50.797815],
    PARAMETER[""central_meridian"",4.35921583333333],
    PARAMETER[""standard_parallel_1"",49.8333333333333],
    PARAMETER[""standard_parallel_2"",51.1666666666667],
    PARAMETER[""false_easting"",649328],
    PARAMETER[""false_northing"",665262],
    UNIT[""metre"",1,
        AUTHORITY[""EPSG"",""9001""]],
    AXIS[""Easting"",EAST],
    AXIS[""Northing"",NORTH],
    AUTHORITY[""EPSG"",""3812""]]";

            var lambert2008 = CoordinateSystemWktReader.Parse(wktLambert2008) as ProjectedCoordinateSystem;
            var transformationFactory = new CoordinateTransformationFactory();
            var transformation4326ToLambert2k8 = transformationFactory.CreateFromCoordinateSystems(wgs84_epsg4326, lambert2008);

foreach (var r in results) {
                 var pointPNLambert2008 = transformation4326ToLambert2k8.MathTransform.Transform(r.Location.Y, r.Location.X);
                var pointNTSLambert2008 = geometryFactoryLambert2008.CreatePoint(new Coordinate(pointPNLambert2008.x, pointPNLambert2008.y));
                double distanceInKilometers = currentLocationNTSLambert2008.Distance(pointNTSLambert2008) / 1000;
		Console.WriteLine($"{distanceInKilometers} km");
}

1. 31,335098193166367 km
2. 49,76827662490822 km
3. 37,187532306642424 km

As you see, my original query has indeed a correct order by, but when doing a measurement in EPSG:3812 I get different order.
The EPSG:3812 seems to be more accurate for my region.

I guess that the original query measures differently (degrees? srid more inaccurate for region?) and thus might give me incorrect results (?).

When I add my points to the database I add them use srid 4326.

var geometryFactory = NetTopologySuite.NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
newRecord.Location = geometryFactory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(vm.Longitude.Value, vm.Latitude.Value));

1. Is there a way to execute an EF Core query and do a transform/reprojection to a different srid server-side?
2. In case I would add a second column called "LocationEPSG3812" and add my coordinates to the database using ".CreateGeometryFactory(srid: 3812);" and then execute my original query again on that new column. Would that Distance give me the correct results similar as to my second query or would SQL Server still ignore this srid and give the same results as the first original query?

Thanks for helping me understand

[FObermaier]

If you want the ordering done on the server side you will have to add a geometry column with transformed locations. Otherwise you'll have to do reprojection and ordering on the client side.

[wstaelens]

@FObermaier do you mean like in case I add a second geometry column "LocationEPSG3812" ?:
and add points to the database using srid 3812 like this:

var geometryFactory = NetTopologySuite.NtsGeometryServices.Instance.CreateGeometryFactory(srid: 3812); newRecord.LocationEPSG3812 = geometryFactory.CreatePoint(new NetTopologySuite.Geometries.Coordinate(vm.Longitude.Value, vm.Latitude.Value));

That the .Distance Distance will result in a query on sql server which will take srid 3812 into account?

[FObermaier]

It won't suffice to claim that the geometry is in spatial reference system defined by SRID 3812. You will actually have to transform the wgs84 points (like you did in the initial post) and insert these.