如何根据给定条件选择最短的GPS坐标？

Question

I have table GPS_tbl in mysql database my_db. In GPS_tbl there are five columns 1)uid 2)latitude 3)longitude 4)regid 5)status. In column 5)status there are two types of values are stored as varchar one is free and other is move. What i am doing right now is when user send his/her gps coordinates in terms of latitude and longitude through POST(), so to select the shortest coordinate from columns 2)latitude 3)longitude i am using Pythagorean theorem so by using this i compare these user sent coordinate with already stored coordinates in columns 2)latitude 3)longitude and select the 4)regid against it. Now what i want is only select that 4)regid against shortest coordinate from 2)latitude 3)longitude whose 5)status value is free.

At the moment i am using this query, which is only selecting 4)regid against shortest coordinate and not taking care of status value:

$result = mysql_query("SELECT regid, sqrt(latitude-$lati)+sqrt(longitude-$longi) AS SQRT_DIST FROM GPS_tbl ORDER BY SQRT_DIST limit 1");

GPS_tbl

+--------------+------------+-------------+-------------+-------------+
|     uid      | latitude   | longitude   |   regid     |   status    |
+--------------+------------+-------------+-------------+-------------+
|      1       |  73.3433   |  18.5223    |APA91W-ClDoS |   move      |
|              |            |             |             |             |
+--- ----------+------------+-------------+-------------+-------------+
|      2       |  43.3433   |  24.5523    |BON91W-ClDoS |   free      |
|              |            |             |             |             |
+--- ----------+------------+-------------+-------------+-------------+
|      3       |  55.3433   |  37.5323    |WCD71P-ClDoS |   free      |
|              |            |             |             |             |
+--- ----------+------------+-------------+-------------+-------------+
|      4       |  65.3433   |  47.5323    |ZCF71D-ClDoS |   move      |
|              |            |             |             |             |
+--- ----------+------------+-------------+-------------+-------------+
|      5       |  85.3433   |  27.5323    |XVD73A-ClDoS |   free      |
|              |            |             |             |             |
+--- ----------+------------+-------------+-------------+-------------+

Answer 1

The Pythagorean distance between two points is calculated by:

distance = sqrt( (x1-x0)^2 + (y1-y0)^2 )

That's not what your query is calculating:

SELECT regid, sqrt(latitude-$lati)+sqrt(longitude-$longi) AS SQRT_DIST FROM GPS_tbl ORDER BY SQRT_DIST limit 1

You've got the square root of two values added together, not the square root of the sum. Try this instead:

SELECT regid, sqrt( (latitude-$lati)*(latitude-$lati) +(longitude-$longi)*(longitude-$longi) ) AS SQRT_DIST FROM GPS_tbl ORDER BY SQRT_DIST limit 1

Now, a couple of things.

1. This distance calculation is an adequate approximation if the distance between the two points is small, but once you get into larger values, it will get farther and farther from the true value. Why? Because the surface of the earth is the surface of a sphere, not a plane. To calculate the correct value, google "Great Circle Distance"

2. Somebody else will probably mention this, but here goes 1) don't use mysql_ functions: they're deprecated; and 2) your code is vulnerable to SQL injection attacks.