Create MySQL function in Laravel 5.5

I have MySQL 5.6 and I need to use MySQL 5.7 function ST_Distance_Sphere (for geolocalization purposes).

I found this hack and it looks correct to me (?).

Now I’m trying to use it in Laravel 5.5.

I put it in app\Providers\AppServiceProvider.php in boot() function.

I tried with DB::statement, DB::raw, DB::unprepared.

I tried removing DELIMITER and $$.

$sql = '
        DELIMITER $$

        DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$

        CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT)

            RETURNS FLOAT
            no sql deterministic
            BEGIN
                declare R INTEGER DEFAULT 6371000;
                declare `φ1` float;
                declare `φ2` float;
                declare `Δφ` float;
                declare `Δλ` float;
                declare a float;
                declare c float;
                set `φ1` = radians(y(point1));
                set `φ2` = radians(y(point2));
                set `Δφ` = radians(y(point2) - y(point1));
                set `Δλ` = radians(x(point2) - x(point1));

                set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2);
                set c = 2 * atan2(sqrt(a), sqrt(1-a));

                return R * c;
            END$$

        DELIMITER ;
    ';

DB::statement($sql);

I have a syntax error everytime. For example:

``Illuminate \ Database \ QueryException (42000)SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe près de 'DELIMITER $$ DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$ ' à la ligne 1 (SQL: DELIMITER $$ DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$ CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT) RETURNS FLOAT no sql deterministic BEGIN declare R INTEGER DEFAULT 6371000; declare `φ1` float; declare `φ2` float; declare `Δφ` float; declare `Δλ` float; declare a float; declare c float; set `φ1` = radians(y(point1)); set `φ2` = radians(y(point2)); set `Δφ` = radians(y(point2) - y(point1)); set `Δλ` = radians(x(point2) - x(point1)); set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2); set c = 2 * atan2(sqrt(a), sqrt(1-a)); return R * c; END$$ DELIMITER ; )``

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s