Tojó Tojó - 1 year ago 95
MySQL Question

Distance between two points in mysql not working?

EDIT: I have checked the other questions but none use this formula

I have rows with 2 values (x,y). I also have another point that is the center of a circunference with a certain radius. I want to check if the distance between this point and my rows' points coordinates is less that the radius. I am trying to apply the distance between two points formula to calculate the distance between the center point and the rows' point and decide wether the row location is inside the radius of the circle ( < radius):

var query = "SELECT * FROM table WHERE SQRT((POWER("+x+",2)-POWER(location_x,2))+(POWER("+y+",2)-POWER(location_y,2))) < " + radius;

For some reason this is not working and no rows are returned despite changing the rows' points coordinates. Any idea?

Coordinates in the formula might have a negative value. In our test sample they do.

Answer Source

I am pretty sure your Cartesian distance formula is incorrect.

The formula you need is:

sqrt( (x - location_x)^2 + (y - location_y)^2 )

This translates into SQL like so:

   FROM table
  WHERE SQRT(   (POWER("+ x + "-location_x,2))
               +(POWER("+ y + "-location_y,2))
            ) < " + radius
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download