[SOLVED] Sorting distance from a point in a coordinate system (from a mysql table)

[mirana]

Hi, I've found many similar problems and solutions, but they're alot more advanced since they've all applied advanced formulas to calculate distances on earth. Also, way too advanced for me to understand most of it. My situation is in fact a flat 2d coordinate system.

 

My situation is:

I have a mysql db which has ID, X, Y (x and y are the coordinates)

 

goal:

 

write a php page which I can feed whichever x and y coordinate I need, and then sort the 10 closest coordinate points from the sql by distance, then ID if equal distance.

 

As far as I've understood, if I have at all, is that I could use mysql_fetch_array , and somehow sort it within the array.

 

Conjuring a formula to do the calculating of distance is the one thing I believe I AM able to achieve. it's the simple pythegorian theorem. Applying this inside an array to sort them by distance, is a problem.

 

SQRT((inputX - dbX)*(inputX - dbX) + (inputY - dbY)*(inputY - dbY))

 

If anyone is able to help me in the right direction with this, I would be humbly grateful.

[Ken2k7]

Why not do that in the SQL query?

[mirana]

can I put a formula after "ORDER by" ? I simply assumed in my ignorance, that I could only use sql field names, asc or desc.

[Barand]

SELECT id, x, y, SQRT(($inputX-x)*($inputX-x) + ($inputY-y)*($inputY-y)) as distance

FROM tablename

ORDER BY distance

[mirana]

ooh wow, thanks. i've been scratching my head trying to make my way to a solution, to no avail at all.

 

Thank you for your assistance, I shall try implementing it now.

[Barand]

NOTE:

 

You can't use the alias "distance" in a WHERE clause so this will fail

SELECT id, x, y, SQRT((9-x)*(9-x) + (8-y)*(8-y)) as distance
FROM tmp
WHERE distance < $maxdist
ORDER BY distance

 

This is OK

SELECT id, x, y, SQRT((9-x)*(9-x) + (8-y)*(8-y)) as distance
FROM tmp
WHERE SQRT((9-x)*(9-x) + (8-y)*(8-y)) < $maxdist
ORDER BY distance

[mirana]

Thank you both for your help so far, I'm so much closer now. I'd like to limit the search, yes, as the coordinate system is indeed a handfull.

 

allright, I've got it working with the first example, but one gives a bit weird results, but these two codes give me different results, which I can't even see the difference between:

 

code 1, working as should, sorting by distance:

 $query = "SELECT id, x, y, SQRT(($inputX - x)*($inputX - x) + ($inpuY - y)*($inputY - y)) as distance
FROM mytable
ORDER BY distance"; 

 

and the second one, which sorts in a pseudo-close to correct way:

 

$query = "SELECT id, x, y, SQRT(($inputX - x)*($inputX - x) + ($inputY - y)*($inputY - y)) as distance
FROM mytable
ORDER BY distance";

 

 

Though the one part I really want to get to work, is this , but i can't manage to get it to work, the WHERE line is the problem :

 

 $query = "SELECT id, x, y, SQRT(($inputX - x)*($inputX - x) + ($inputY - y)*($inputX - y)) as distance
FROM mytable
WHERE SQRT(($inputX - x)*($inputX - x) + ($inputY - y)*($inputY - y)) < $maxdist
ORDER BY distance"; 

[GingerRobot]

Well, as far as i can see, it's the first one that shouldn't be working. You've missing a t and have $inpuY rather than $inputY.

 

You say the past example isn't working. In what way? Do you get an error message? Have you tried echoing the query, to check all the variables are correct?

[mirana]

Well, as far as i can see, it's the first one that shouldn't be working. You've missing a t and have $inpuY rather than $inputY.

 

You say the past example isn't working. In what way? Do you get an error message? Have you tried echoing the query, to check all the variables are correct?

 

I must have changed it again while trying to figure something out, my bad. it was correct at same point.

 

On the very positive side, You guys have managed to solve my problem. Thank you very much.

 

Here's the code, sorting the points by distance, and limiting it by distance:

 

$query = "SELECT id, x, y, SQRT(($inputX - x)*($inputX - x) + ($inputY - y)*($inputY - y)) as distance
FROM table
WHERE SQRT(($inputX - x)*($inputX - x) + ($inputY - y)*($inputY - y)) < $maxdist
ORDER BY distance"; 

 

 

Just as you guys showed me. Thank you all for you patience, and you expert help. it works!

[Barand]

You're welcome. Don't forget the "Topic Solved" button.