23. Broncode trillingstool Atabix.txt

<?php

namespace App\Services\Trillingstool;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

/**
 * Calculates information required for the earthquake (trillings) tool.
 */
class CalculateService
{
    public static function calculateFromLocation(string $longitude, string $latitude, Carbon $from, Carbon $until, float $minimumMagnitude, $gasFieldId = null, int $limit = 15, int $offset = 0): array
    {
        // Order is important, because we can't mix named and non-named parameters, and we need to bind multiple of the same name
        $bindings = [
            $longitude,
            $latitude,
            $from->toDateTimeString(),
            $until->toDateTimeString(),
            $minimumMagnitude,
        ];

        if ($gasFieldId) {
            $bindings[] = $gasFieldId;
        }

        // Optional
        $gasField = $gasFieldId ? 'AND gasfield_id = ?' : '';

        // Create the earthquake location point
        $earthquakeLocation = <<<'SQL'
            POINT(longitude, latitude)
            SQL;

        // Create the home location point
        $homeLocation = <<<'SQL'
            POINT(?, ?)
            SQL;

        // Select the distance between the earthquake and the home
        $select = <<<SQL
            SELECT id, occurred_at, ST_Distance_Sphere({$earthquakeLocation}, {$homeLocation}) as distance, `magnitude`, depth, city, gasfield_id
            FROM trillingstool_earthquakes
            WHERE 1
            AND occurred_at >= ?
            AND occurred_at <= ?
            AND `magnitude` >= ?
            {$gasField}
            SQL;

        [$pgvForOnePercent, $onePercentBindings] = self::pgv(2.326347874); // 50% = 0 | 25% = 0.67448975 | 1% = 2.326347874
        [$pgvForTwentyFivePercent, $twentyFivePercentBindings] = self::pgv(0.67448975);
        [$pgvForFiftyPercent, $fiftyPercentBindings] = self::pgv(0);

        $bindings = \array_merge($onePercentBindings, $twentyFivePercentBindings, $fiftyPercentBindings, $bindings, [$limit, $offset]);

        // Select the pgv (vibration strength) depending on the distance
        $query = <<<SQL
            SELECT *,
            {$pgvForOnePercent} as pgv_1pct,
            {$pgvForTwentyFivePercent} as pgv_25pct,
            {$pgvForFiftyPercent} as pgv_50pct
            FROM ({$select}) as earthquakes
            ORDER BY pgv_1pct DESC
            LIMIT ? OFFSET ?
            SQL;

        return DB::select($query, $bindings);
    }

    public static function calculateFromPgv(string $pgv, Carbon $from, Carbon $until, string $minimumMagnitude, string $maximumMagnitude, $gasFieldId = null, int $limit = 15, int $offset = 0): array
    {
        // Order is important, because we can't mix named and non-named parameters, and we need to bind multiple of the same name
        $bindings = [
            $from->toDateTimeString(),
            $until->toDateTimeString(),
            $minimumMagnitude,
            $maximumMagnitude,
        ];

        if ($gasFieldId) {
            $bindings[] = $gasFieldId;
        }

        // Optional
        $gasField = $gasFieldId ? 'AND gasfield_id = ?' : '';

        [$distanceForOnePercent, $onePercentBindings] = self::distance(2.326347874, $pgv);
        [$pgvForTwentyFivePercent, $twentyFivePercentBindings] = self::distance(0.67448975, $pgv);
        [$distanceForFiftyPercent, $fiftyPercentBindings] = self::distance(0, $pgv);

        $bindings = \array_merge([$pgv], $onePercentBindings, $twentyFivePercentBindings, $fiftyPercentBindings, $bindings, [$limit, $offset]);

        $select = <<<SQL
            SELECT `longitude`,
            `latitude`,
            `occurred_at`,
            ? as `pgv`,
            {$distanceForOnePercent} as distance_1pct,
            {$pgvForTwentyFivePercent} as distance_25pct,
            {$distanceForFiftyPercent} as distance_50pct,
            `magnitude`,
            `depth`,
            `city`,
            `gasfield_id`
            FROM trillingstool_earthquakes
            WHERE 1
            AND occurred_at >= ?
            AND occurred_at <= ?
            AND `magnitude` >= ?
            AND `magnitude` <= ?
            {$gasField}
            SQL;

        $query = <<<SQL
            SELECT * FROM ({$select}) as earthquakes
            LIMIT ? OFFSET ?
            SQL;

        return DB::select($query, $bindings);
    }

    public static function pgv($n): array
    {
        $bindings = [
            $n,
            $n,
            $n,
        ];

        $acc = <<<'SQL'
            sqrt(POWER(distance / 1000, 2) + POWER(exp(0.4233 * `magnitude` - 0.6083), 2) )
            SQL;

        $a = <<<SQL
            -1.9446*ln({$acc})
            SQL;

        $b = <<<SQL
            -1.9446*ln(6.32) + -1.2077*ln({$acc}/6.32)
            SQL;

        $c = <<<SQL
            -1.9446*ln(6.32) + -1.2077*ln(11.62/6.32) + -1.6533*ln({$acc}/11.62)
            SQL;

        $result = static function ($var) {
            return "exp(-5.0006 + 2.2770 * `magnitude` + {$var} + 0.5777 * ?) * 10";
        };

        return [<<<SQL
            CASE
                WHEN {$acc} <= 6.32 THEN
                    {$result($a)}
                WHEN {$acc} <= 11.62 THEN
                    {$result($b)}
                ELSE
                    {$result($c)}
            END
            SQL, $bindings];
    }

    public static function distance($n, $pgv): array
    {
        $bindings = [
            $pgv,
            $n,
            $pgv,
            $n,
            $pgv,
            $n,
            $pgv,
            $n,
            $pgv,
            $n,
        ];

        $acc = <<<'SQL'
            log(? / 10) - 0.5777 * ? - (-5.0006 + 2.2770 * `magnitude`)
            SQL;

        $a = <<<SQL
            exp(({$acc})/ -1.9446)
            SQL;

        $b = <<<SQL
            exp(({$acc} - (-1.9446*ln(6.32))) / -1.2077) * 6.32
            SQL;

        $c = <<<SQL
            exp(({$acc} - (-1.9446*LN(6.32)) - (-1.2077*LN(11.62/6.32))) / -1.6533) * 11.62
            SQL;

        $result = static function ($var) {
            return "SQRT(POWER({$var}, 2)- POWER(EXP(0.4233 * `magnitude` - 0.6083), 2)) * 1000";
        };

        $case = static function ($var) {
            return "sqrt(POWER(($var) / 1000, 2) + POWER(exp(0.4233 * `magnitude` - 0.6083), 2) )";
        };

        return [<<<SQL
            CASE
                WHEN {$case($result($a))} <= 6.32 THEN
                    {$result($a)}
                WHEN {$case($result($b))} <= 11.62 THEN
                    {$result($b)}
                ELSE
                    {$result($c)}
            END
            SQL, $bindings];
    }

    /**
     * When no earthquakes are involved, we want to calculate the radius for a given pgv and magnitude.
     *
     * @param float $n
     *
     * @return float
     */
    public static function calculateDistanceFromPgvAndMagnitude($pgv, float $magnitude, $n = 2.326347874): ?float
    {
        [$distance, $extraBindings] = self::distance($n, $pgv);

        // Order is important, because we can't mix named and non-named parameters, and we need to bind multiple of the same name
        $bindings = [$pgv, ...$extraBindings, $magnitude];

        $distance = \str_replace('`magnitude`', (string) $magnitude, $distance);

        $query = <<<SQL
            SELECT ? as pgv, {$distance} as distance
            FROM (SELECT ? as `magnitude`) as distance_calculation
            SQL;

        return DB::select($query, $bindings)[0]->distance ?? null;
    }

    /** Get a human readable formula. */
    public static function getPrettyPgvFormula(float $pgv, float $magnitude, float $n = 2.326347874): string
    {
        [$distance, $extraBindings] = self::distance($n, $pgv);

        // Order is important, because we can't mix named and non-named parameters, and we need to bind multiple of the same name
        $bindings = [$pgv, ...$extraBindings, $magnitude];

        $query = <<<SQL
            SELECT ? as pgv, {$distance} as distance, `magnitude`
            FROM (SELECT ? as `magnitude`) as distance_calculation
            SQL;

        // Replace the bindings
        $result = \str_replace(PHP_EOL, ' ', $query);
        foreach ($bindings as $binding) {
            $pos = \strpos($result, '?');
            if ($pos !== false) {
                $result = \substr_replace($result, $binding, $pos, \strlen('?'));
            }
        }

        // Replace excessive spaces
        return \strtolower(\preg_replace('/[ ]+/', ' ', $result));
    }
}