0

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

PhpMyAdmin sign with 2 red marker the point data type on the first line of the previous statment:

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 PhpMyAdmin don't know) because it mark red also this cration query:

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 PhpMyAdmin or what am I missing? Where this function should be stored?

AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • It is important to change the delimiter in MySQL. Otherwise the function definition ends at the first `;` which would make it incomplete. Add `delimiter ||` before the function definition and a `||` after the function definition – juergen d Nov 07 '16 at 14:14
  • @juergend what exactly have I to do? How is it possible? The example is taken directly from the MariaDB website – AndreaNobili Nov 07 '16 at 14:15
  • So you have taken another step backwards? – e4c5 Nov 07 '16 at 14:16
  • @e4c5 no I am only trying to perfrom this statment into my DB to create this funcion – AndreaNobili Nov 07 '16 at 14:20
  • @juergend Where have I to put this delimeter? Please can you give me a complete response showing me the code? So I will accept it :-) – AndreaNobili Nov 07 '16 at 14:22

1 Answers1

1

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 ;
juergen d
  • 201,996
  • 37
  • 293
  • 362