sqlite-net-pcl order items by distance

95 Views Asked by At

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;
}
0

There are 0 best solutions below