AndreaNobili - 1 year ago 73

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?

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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 ;
```

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**