0

I am trying to sort a SQLite table that contains coordinates by their distance to a reference position.
I would like to execute this on the SQLite level, to avoid retrieving 100.000 items from the database.
The problem is, that the Sqlite-net-pcl OrderBy() method does not accept any "complex" lambdas.

GeoCoordinate refPos = new GeoCoordinate(50, 15);  
var distanceQuery = _connection.Table<Locations>().OrderBy(L => Distance(L.Position, refPos);
var distanceResult = await distanceQuery.ToListAsync();

Where Distance() calculates the Rhumb-Line-Distance between 2 coordinates, which is just a bit of math.
Of course sqlite-net-pcl doesn't accept the Lambda Function and throws a "NotSupportedException".
Does anyone know of a workaround for this?

For completeness, here is the Distance function:

private double Distance(GeoCoordinate origin, GeoCoordinate target)
{
    const double radius = 6371d;

    //Convert to radians:
    origin.Latitude *= Math.PI / 180d;
    origin.Longitude *= Math.PI / 180d;

    target.Latitude *= Math.PI / 180d;
    target.Longitude *= Math.PI / 180d;

    double dLat = target.Latitude - origin.Latitude;
    double dLon = target.Longitude - origin.Longitude;

    double dPhi = Math.Log(Math.Tan(target.Latitude / 2 + Math.PI / 4) / Math.Tan(origin.Latitude / 2 + Math.PI / 4));
    double q = Math.Cos(origin.Latitude);

    // East-West line gives dPhi=0
    if (dPhi != 0) q = dLat / dPhi;

    // if dLon over 180° take shorter rhumb across 180° meridian:
    if (dLon > Math.PI) dLon = 2 * Math.PI - dLon;

    double distance = Math.Sqrt(dLat * dLat + q * q * dLon * dLon) * radius;

    return distance;
}
Dokug
  • 123
  • 8
  • You can't query a database using a client-side function. You can only use SQL functions. If you want to execute spatial queries with good performance you'd have to use a SQLite extension like SpatiaLite. You can use [SpatiaLite](https://learn.microsoft.com/en-us/ef/core/providers/sqlite/spatial) in EF Core too, through the `Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite` package – Panagiotis Kanavos Jul 07 '22 at 14:05
  • In general, NetTopologySuite has become the de-facto standard for spatial queries in .NET. It's used by the EF Core providers for PostgreSQL, SQL Server, MySQL and Sqlite – Panagiotis Kanavos Jul 07 '22 at 14:09
  • `the Sqlite-net-pcl OrderBy() method does not accept any "complex" lambdas.` that's not what happens. You can only use SQL to query databases like SQLite. The library maps lambdas to SQL, it doesn't execute the lambdas inside the database. If the lambda can't be converted to SQL you'll get a `NotSupportedException`. As for `avoid retrieving 100.000 items from the database.` SQLite is an embedded database. Everything runs in your own application already. To avoid processing 100K records you need proper indexes and spatial functions, otherwise SQLite will have to scan all 100K records – Panagiotis Kanavos Jul 07 '22 at 14:11

0 Answers0