Have been trying to calculate distance using PHP/mysql between a current point and other places in England using national grid references, which is easy using Pythagorus. However I want the five nearest other places listed in the database (1000 other places total) in ascending order preferably using one call to the table. Each reference contains 9 digits, the first 4 are the length along the x axis from an imaginary 0,0 and the final 5 are the length along the y axis from the 0,0
The initial starting place is derived from data already called in a query held in ‘start_point’ :-
$x1 = substr($row['start_point'], 0, 4); // split out x axis coordinates
$y1 = substr($row['start_point'], 4, 5); // split out y axis coordinates
Have tried this but have got errors -
$query7 = "SELECT place_name, new_point FROM places ORDER BY sqrt(($x1-sustr(new_point,0,4)^2 + ($y1 – substr(new_point,4,5)^2) AS distance LIMIT 5”;
while ($row7 = mysql_fetch_array ($result7, MYSQL_ASSOC)) {
echo '<p>'. $row7['place_name'] . ' - '. $row7['x2'] .' - ' . $row7['distance'] .'</p>';
} // end of while loop
Am I doing too much in one query? I guess I am doing something wrong but would like to know why so I don’t do it again especially if it is particularly stupid!
Database query problem to calculate distances
Linear Mode
