Why I can't calculate the distance between a fixed point and a geometry field of a MySql table using this distance function? [closed]

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 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?

closed as off-topic by Evan Carroll, hot2use, Michael Green, Paul White♦Nov 13 '18 at 10:52

This question appears to be off-topic. The users who voted to close gave this specific reason:

• "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Evan Carroll, hot2use, Michael Green, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

• Just from a quick scan of your question, try the following. define your POINT for @fiumicino without the `,`. `POINT(41.772683 12.241714)`. Make sure you don't get LAT and LON mixed up. In the function you are setting LAT to Y (correct) and in the query select clause you are aliasing X to LAT. example of point definition – MickyT Nov 9 '16 at 19:11
• Why is this tagged postgis? – Evan Carroll Feb 3 '17 at 5:51

That error is just complaining because there is no `@Y()` function, it's just `ST_Y` (preferred) or `Y()` (older crap)