Issue in getting distance between spatial points from two tables in mysql

Question

My query is

SELECT A.broker_property_id, A.broker_owner_id
FROM property_requirement_new AS A, 
     (SELECT X(latlong), Y(latlong) 
       FROM client_property_new 
       WHERE property_id = 132) AS B
WHERE (POW((A.X(latlong)-B.X(latlong))*111.12, 2) + POW((A.Y(latlong) - B.Y(latlong))*111.12, 2)) <= 4

Here, latlong is a field of mysql's POINT datatype. But this query is not being executed and is showing the following error:

FUNCTION a.X does not exist

Can anyone help me out with the correct method to do it or to spot the error in my code?

Try using back-tick marks around \`X(latlong)\` and \`Y(latlong)\`. — Ted Hopp, May 26 '14 at 15:27
Show us the version with the back-tick marks, or try using double quotes with ANSI_QUOTES enabled. (I'm assuming that "X(latlong)" and "Y(latlong)" are column names in both the `client_property_new` and `property_requirement_new` tables.) — Ted Hopp, May 26 '14 at 15:30
latlong is a spatial `POINT` datatype field. I am using `X(latlong)` to get the latitude and `Y(latlong)` to get the longitude of the point. Is there any other method using which I could get the distance between the latlong points? — Tushar, May 26 '14 at 15:32
X and Y extract the x and y value of a point. Does table A contain latlon at all? Why not use the built-in ST_Distance function, as of MySQL 5.6? — John Powell, May 26 '14 at 15:34
Because I am working on MySQL 5.5 version and not allowed to upgrade. — Tushar, May 26 '14 at 15:37
This is throwing the following error: `Unknown column 'B.latlong' in 'where clause'` PS: My `client_property_new ` has a `latlong` field — Tushar, May 26 '14 at 15:41
@TedHopp: Its working! Actually what I did wrong was that I selected `X(latlong)`, `Y(latlong)` from the table B. Thanks alot! — Tushar, May 26 '14 at 15:44
@Tushki, OK, fair enough. I see Tedd has given the same answer in the comments about X not being a function of table A, but you might be interested in the link I put in my answer for ways of doing distance in MySQL. — John Powell, May 26 '14 at 15:47
Well, John Barça seems to have posted the solution as an answer; you should mark his answer as the solution. — Ted Hopp, May 26 '14 at 15:47

score 3 · Accepted Answer · edited May 23 '17 at 11:50

X and Y are functions that take a Point (geometry) as an input. It is not a function of table A, as you have written it, ie, A.X(latlong) is throwing an error as it implies that X is a function of table A.

You would need to write

select X(latlon), Y(latlon) from A

or in your case, if you are using subqueries:

select X(A.latlon), Y(A.latlong) from (.....) A

There is apparently an ST_Distance function in MySQL 5.6, but I have never used it and it seems somewhat undocumented.

There is some discussion of distance functions in MySQL here: Fastest Way to Find Distance Between Two Lat/Long Points One of the general problems of spatial in MySQL is that it does not support projections properly, so you are either left doing Pythagoras on planar coordinates, or implementing some version of the haversine formula and assuming the world is a sphere -- which will work well for short distances if massive precision is not your biggest concern.

Issue in getting distance between spatial points from two tables in mysql

1 Answers1