[RESOLVED] using distance formula

[oswaler]

I know, I know, I know, this is a SQL question, but I think it might come down to the way the computer is interpreting the equation I'm using, so I thought it might be helpful to post it here. Please don't flame me to death if this is too far off topic, but I figure a lot of you guys probably also do a lot with SQL, and in the past I've gotten a lot of very intelligent help here (insert rear-kissing sound here).

This involves a query running against a MySQL database. I am new to MySQL (just started using it a couple days ago). I have a table with 2 columns that is something like:

x_coord
--------
65.34
71.4345
64

y_coord
--------
28.764
22.212
27.782

etc... there are approx. 2000 records

It's a bunch of x and y coordinates showing positions on a graph.

What I want to do is find all of the points that fall within a particular circle. So if you imagine 2000 points on a graph, and then you draw a small circle, I want to find all of the points that fall within the circle.

In my instance, I put the center of the circle at x_coord=69.75 and y_coord=26.5. The radius of the circle is 1.

I put this same data in Access and ran the same query I'll show below, and I got the correct results. All the points it brought back were within my circle, so I know the logic is right. I think it is a problem of understanding MySQL syntax. When I run this query in MySQL with the same data, I get a very strange result. Instead of the data from within the circle only, I get about 7 clumps of data, and they don't describe circles, they seem to describe rectangles.

Here's my query:

select tblMyTable.x_coord ,tblMyTable.y_coord
from tblMyTable
where
(((tblMyTable.x_coord - 69.75)^2 + (tblMyTable.y_coord - 26.5)^2)^(1/2))<=1

So I used the distance formula to figure out which points are within 1 unit of the point I specified. I also tried rewriting this using the sqrt() function instead of raising to the 1/2 power and got the same result.

I'd appreciate any help in getting the correct result from MySQL.

Thanks - Eric

[oswaler]

I found the answer, and it had nothing to do with math, it was purely an issue of syntax. MySQL wanted me to use the power() function rather than the ^ symbol.

Thanks - Eric