Benutzung der Distanzfunktion verbessern

[MaxWies]

In DemandService gibt es eine Funktion computeDistance, die die Distanz zwischen zwei Koordinaten in km berechnet. In unseren Abfragen führen wir erst die Query aus und dann mit der Liste der Ergebnisse nochmal separat die Distanzfunktion. Bessere ist es, die Query auch für die Distanzberechnung zu benutzen.

Die Idee: Aus den Addressdaten und der Distanz (km), die das FE schickt, berechnen wir die vier Eckkoordinaten (die ein Quadrat bilden). In der Query fragen wir dann jeweils mit ab, ob die Koordinaten aus dem DB Eintrag in diesem Quadrat liegen.

[Timo-Weike]

Turns out that because the way how the entity frameworks translates a query to sql it might be impossibto refactor the logic for creating the where clausels into a generic methode.

This is an example on how it could look in the QueryDemandsAsync method:

var query = 
    from demand in _context.demand as IQueryable<DemandEntity>
    join deviceDemand in _context.demand_device on demand.id equals deviceDemand.demand_id
    join address in _context.address on demand.address_id equals address.id into tmp
    from address in tmp.DefaultIfEmpty()
    where device.category == deviceDemand.category && !deviceDemand.is_deleted
    select new {demand, deviceDemand, address};

#region Adding checks for a bouding box
if (maxDistance > 0.0 && centerLocation.HasValue)
{
    var boundingBox
        = new BoundingBox(centerLocation.Value, maxDistance);

    //query = boundingBox.AddToQuery(query);
    if ((boundingBox.Type & BoundingBoxType.WrappsNorthPole) != 0)
    {
        var south = Convert.ToDecimal(Math.Min(
            boundingBox.NorthEastCorner.Latitude,
            boundingBox.SouthEastCorner.Latitude
        ));
        query = query
            .Where(a => south <= a.address.latitude);
    }
    else if ((boundingBox.Type & BoundingBoxType.WrappsSouthPole) != 0)
    {
        var north = Convert.ToDecimal(Math.Min(
            boundingBox.NorthEastCorner.Latitude,
            boundingBox.SouthEastCorner.Latitude
        ));
        query = query
            .Where(a => a.address.latitude <= north);
    }
    else // wrapps around the horizontal
    {
        var west = Convert.ToDecimal(Math.Min(
                    boundingBox.SouthWestCorner.Longitude,
                    boundingBox.NorthWestCorner.Longitude
                ));
        var east = Convert.ToDecimal(Math.Max(
            boundingBox.NorthEastCorner.Longitude,
            boundingBox.SouthEastCorner.Longitude
        ));
        var south
            = Convert.ToDecimal(boundingBox.SouthWestCorner.Latitude);
        var north
            = Convert.ToDecimal(boundingBox.NorthWestCorner.Latitude);

        query = query
            .Where(a => south <= a.address.latitude)
            .Where(a => a.address.latitude <= north);

        if (boundingBox.Type == BoundingBoxType.Normal)
        {
            query = query
                .Where(a => west <= a.address.longitude)
                .Where(a => a.address.longitude <= east);
        }
        else
        {
            query = query
                .Where(a =>
                    west <= a.address.longitude
                    || a.address.longitude <= east
                );
        }
    }
}
#endregion

if (!string.IsNullOrEmpty(device.name))
{
    query = query.Where(collection => device.name == collection.deviceDemand.name);
}

but this would need to be repeated in every methode that wants this feature.

Refere to this SO question on the problematic

There might be a way to optimize this logic by matching on some return type from a methode of bounding box to move some of the logic to the BoundingBox class. But for now I will invest my time to something else (and will return if for example someone answers my SO question).

@chaoran-chen and @MaxWies what is your opinion on this matter.

I would say it is currently better so just filter for the location on the server rather in the DB.