AndreaNobili - 3 months ago 21

MySQL Question

I am not so into database and I have the following doubt implementing this tutorial: https://mariadb.org/jquery-and-gis-distance-in-mariadb/

So basically my doubt is relate to this SQL statment that create a function to calculate the distance between two **point** on a MariaDB database:

`CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double`

DETERMINISTIC

begin

declare lon1, lon2 double;

declare lat1, lat2 double;

declare td double;

declare d_lat double;

declare d_lon double;

declare a, c, R double;

set lon1 = X(GeomFromText(AsText(point1)));

set lon2 = X(GeomFromText(AsText(point2)));

set lat1 = Y(GeomFromText(AsText(point1)));

set lat2 = Y(GeomFromText(AsText(point2)));

set d_lat = radians(lat2 - lat1);

set d_lon = radians(lon2 - lon1);

set lat1 = radians(lat1);

set lat2 = radians(lat2);

set R = 6372.8; -- in kilometers

set a = sin(d_lat / 2.0) * sin(d_lat / 2.0) + sin(d_lon / 2.0) * sin(d_lon / 2.0) * cos(lat1) * cos(lat2);

set c = 2 * asin(sqrt(a));

return R * c;

end

My problem is that performing the previous statment I obtain the following error message:

`Error`

----------------------------------------------------

Static analysis:

2 errors were found during analysis.

1. Unrecognized data type. (near "point" at position 45)

2. Unrecognized data type. (near "point" at position 59)

SQL query:

CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double DETERMINISTIC begin declare lon1, lon2 double

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

`CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double`

but I don't think that it could be the real problem (I think that maybe is something that

`CREATE TABLE gis_point (g POINT);`

but it works fine, give me no error and correctly create the table.

So, whta is wrong? Could depend by

Where this function should be stored?

Answer

Change the delimiter. Otherwise the function definition ends at the first `;`

which would make it incomplete.

```
delimiter ||
CREATE FUNCTION earth_circle_distance(point1 point, point2 point) RETURNS double
DETERMINISTIC
begin
declare lon1, lon2 double;
declare lat1, lat2 double;
declare td double;
declare d_lat double;
declare d_lon double;
declare a, c, R double;
set lon1 = X(GeomFromText(AsText(point1)));
set lon2 = X(GeomFromText(AsText(point2)));
set lat1 = Y(GeomFromText(AsText(point1)));
set lat2 = Y(GeomFromText(AsText(point2)));
set d_lat = radians(lat2 - lat1);
set d_lon = radians(lon2 - lon1);
set lat1 = radians(lat1);
set lat2 = radians(lat2);
set R = 6372.8; -- in kilometers
set a = sin(d_lat / 2.0) * sin(d_lat / 2.0) + sin(d_lon / 2.0) * sin(d_lon / 2.0) * cos(lat1) * cos(lat2);
set c = 2 * asin(sqrt(a));
return R * c;
end
||
delimiter ;
```