2

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?

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.

  • 1
    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
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.