Tojó - 1 month ago 17

MySQL Question

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

Answer

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:

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

Source (Stackoverflow)

Comments