Jump to content


Photo

Is this possible?


  • Please log in to reply
2 replies to this topic

#1 virtual_odin

virtual_odin
  • Members
  • PipPipPip
  • Advanced Member
  • 47 posts
  • LocationLondon, England

Posted 20 October 2006 - 10:05 AM

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((x1-x2)*(x1-x2)+(y1-y2)*(y1-y2)).  I can select OK and show the distance with the formula above with the query below

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


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.

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 October 2006 - 11:17 AM

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((x1-x2)*(x1-x2)+(y1-y2)*(y1-y2)).  I can select OK and show the distance with the formula above with the query below

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


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.


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

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...-functions.html

#3 virtual_odin

virtual_odin
  • Members
  • PipPipPip
  • Advanced Member
  • 47 posts
  • LocationLondon, England

Posted 20 October 2006 - 11:51 AM

Brill, thanks shoz.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users