I am not so into database and GIS and I have the following problem.
I have defined this function that return the distance between 2 points in the space represented by 2 point fields:
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
It works pretty fine, I tryied passing to it 2 point defined by:
SET @point1= GeomFromText('POINT(41.772683, 12.241714)');
SET @point2 = GeomFromText('POINT(42.1212121, 13.1212121)');
The problem is that I want to use this fucntion into a query to calculate the distance between a setted point and another point represented by a record of my accomodation table.
So I have tryied something like this:
SET @fiumicino = GeomFromText('POINT(41.772683, 12.241714)');
SELECT a.accomodation_name as name,
AsText(a.geographical_position) as position,
X(a.geographical_position) as lat,
Y(a.geographical_position) as lon,
earth_circle_distance(@fiumicino, a.geographical_position)
FROM accomodation a
I want to calculate the distance of all the points represented by the geographical_position field of the accomodation table and the setted point @fiumicino.
The data type of the geographical_position field is GEOMETRY (because in Java I have some problem to interact with POINT data type using Hibernate, I can't change it).
The problem is that performing the previous query is that I obtain the following error message:
#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 '
(a.geographical_position),@Y(a.geographical_position), earth_circle_d' at line 3
The problem happens when the earth_circle_distance(@fiumicino, a.geographical_position) is performed.
Infact deleting this statment from my query, so performing this simplified query that don't calculate the ditance:
SET @fiumicino = GeomFromText('POINT(41.772683, 12.241714)');
SELECT a.accomodation_name as name,
AsText(a.geographical_position) as position,
X(a.geographical_position) as lat,
Y(a.geographical_position) as lon
FROM accomodation a
name position lat lon
---------------------------------------------------------------------------------------------------------------------------
Grand Hotel Marina de Ostia POINT(41.729086 12.278478) 41.729086 12.278478
Motel La Bettola POINT(41.883991 12.33544) 41.883991 12.33544
HOTEL 1 POINT(41.82703258287677 12.266217163303374) 41.82703258287677 12.266217163303374
HOTEL 2 POINT(41.816297452855096 12.239581452630633) 41.816297452855096 12.239581452630633
So, as you can see, the value of a.geographical_position field used into earth_circle_distance(@fiumicino, a.geographical_position) is something like POINT(41.883991 12.33544). But int this way it didn't work.
Why? What is wrong? What am I missing?
I also retrived the value of the latitude and of the longitude of my point, by @X(a.geographical_position) and @Y(a.geographical_position).
If I knew how, I could create a new point with the previous values as second parameter to pass to this earth_circle_distance() function. How do I obtain the distance between the fixed point and the point represented by the a.geographical_position of the current record?
