Sqlite/sqlserver server side vs client side evaluation

[andrejohansson]

I'm having a bit trouble understanding what and when server side evaluation of geocodes are being used.

I have an sqlite database, with a Point column called Location on my Address entity. I'm using the Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite package as described on the Efcore Spatial Data Page

The Address entity configuration defines WSG84 per the instructions on the Sqlite Provider Page

modelBuilder
    .Entity<Address>()
    .Property(c => c.Location)
    .HasSrid(4326);

Points are seeded with the same SRID

address.Location = new Point(
    Geo.HelsingborgLongitude,
    Geo.HelsingborgLatitude)
{
    SRID = 4326
};

Now I have defined Epsg 25832 and a ProjectTo extension method, taken from the documentation.

If I do a simple unit test with two points and a distance calculation everything is fine and I get sane values.

But if I use the distance calculation in a query I get into trouble. Querying like this throws errors:

            return queryable
                .Where(s =>
                    s.Address
                        .Location
                        .ProjectTo(Geo.SRID.Epsg25832)
                        .Distance(query
                            .GeoQuery
                            .Location
                            .ProjectTo(Geo.SRID.Epsg25832))
                    <
                    // https://epsg.io/25832 unit is metres for 25832
                    query.GeoQuery.DistanceInMeters
               );

Running this throws a NotSupportedException saying No support for transforming between the two specified coordinate systems

Even if I configure the column with srid 4326 and seed points with srid 4326, the Geometry returned and sent to the ProjectTo method is getting a 0 value and thus the exception is thrown.

I guess this has something to do with this phrase from the documentation:

NTS ignores SRID values during operations. It assumes a planar coordinate system.

Question 1: Why then should I configure column and points with an srid?

If I then hard code the geometry srid to 4326, in the ProjectTo method, then I get a linq translation error:

Translation of method 'Repositories.GeometryExtensions.ProjectTo' failed.

So, in the end, it seems like I'm missing something crucial.

This is what I'm trying to accomplish:

1. Querying entities within a radius of x metres of a given point
2. For performance, I want the query to run on the server side
   • I'm assuming that the db provider will have the knowledge to turn my queries into server side executed sql
3. I wan´t to filter or return the calculated distance in metres (which is the unit of https://epsg.io/25832)

Question 2: Is it not possible to achieve the above goals when using sqlite and sqlserver providers? Question 3: Can I use the method IsWithinDistance and pass it a distance in metres somehow to get server side evaluation? Question 4: Can I convert my distance in metres to degrees with the information I have (epsg 25832 -> WGS84 and the distance in metres) before I query in order to avoid the usage of ProjectTo?