Jump to content

Archived

This topic is now archived and is closed to further replies.

virtual_odin

Is this possible?

Recommended Posts

I have a table including the x and y coordinates of a series of points on a grid and want to be able to select them in order of their distance from a given pair of x y coordinates (ie sqrt((x[sub]1[/sub]-x[sub]2[/sub])*(x[sub]1[/sub]-x[sub]2[/sub])+(y[sub]1[/sub]-y[sub]2[/sub])*(y[sub]1[/sub]-y[sub]2[/sub])).  I can select OK and show the distance with the formula above with the query below

[quote]SELECT * FROM `points` ORDER BY '".strtolower($sortby)."' ".$direction[/quote]

But sorting the results into the lowest and highest distances from the given point is proving beyond me for the moment!

It looks from the manual as if it should be possible.  Your help would be appreciated.

Share this post


Link to post
Share on other sites
[quote author=virtual_odin link=topic=112104.msg454854#msg454854 date=1161338738]
I have a table including the x and y coordinates of a series of points on a grid and want to be able to select them in order of their distance from a given pair of x y coordinates (ie sqrt((x[sub]1[/sub]-x[sub]2[/sub])*(x[sub]1[/sub]-x[sub]2[/sub])+(y[sub]1[/sub]-y[sub]2[/sub])*(y[sub]1[/sub]-y[sub]2[/sub])).  I can select OK and show the distance with the formula above with the query below

[quote]SELECT * FROM `points` ORDER BY '".strtolower($sortby)."' ".$direction[/quote]

But sorting the results into the lowest and highest distances from the given point is proving beyond me for the moment!

It looks from the manual as if it should be possible.  Your help would be appreciated.
[/quote]

[code]
SELECT p.*, SQRT((points.x - $x)*(points.x - $x) ...) AS distance FROM points ORDER BY distance
[/code]

EDIT: Note that there is a MYSQL function POW that can be used to square a value. eg: POW((x1-x2), 2).
http://dev.mysql.com/doc/refman/4.1/en/mathematical-functions.html

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.