CODEWITHSUNDEEP
phpmysqlgpscoordinates
user3593284
user3593284

Reputation: 89

How to select closest gps coordinate from database?

I have table GPS_tbl in mysql database my_db. In GPS_tbl there are four columns 1)uid 2)latitude 3)longitude 4)regid. In columns 2)latitude 3)longitude different values of latitude and longitude are already stored as float, now what i want is when user send his/her gps coordinates in terms of latitude and longitude through POST() compare these user sent coordinate with already stored coordinates in columns 2)latitude 3)longitude and the coordinate in columns 2)latitude 3)longitude which is closest to user sent coordinate select the regid against it. Please someone help me how to do this in php.

This is how user is sending his/her GPS coordinates. 

    $lati = $_POST['lati'];
    $longi = $_POST['longi'];
    $float_value_of_lati = floatval($lati);
    $float_value_of_longi = floatval($longi);

This is GPS_tbl 

+--------------+------------+-------------+-------------+
|     uid      | latitude   | longitude   |   regid     |
+--------------+------------+-------------+-------------+
|      1       |  73.3433   |  18.5223    |APA91W-ClDoS |
|              |            |             |             |
+--- ----------+------------+-------------+-------------+
|      2       |  43.3433   |  24.5523    |BON91W-ClDoS |
|              |            |             |             |
+--- ----------+------------+-------------+-------------+
|      3       |  55.3433   |  37.5323    |WCD71P-ClDoS |
|              |            |             |             |
+--- ----------+------------+-------------+-------------+

Upvotes: 3

Views: 1623

Answers (2)

Hristo Ivanov
Hristo Ivanov

Reputation: 719

I'm not going to explain to you how to connect to the MySQL server, get an instance of the database, and run an SQL query. There are plenty of tutorials for that. I'm going to help you with the query. For this purpose we will use the Pythagorean theorem. It's not the best approach because Earth in not flat, but I think it will work (not in middle of the Pacific where we pass from -180º to 180º, though)

    "SELECT uid,regid, sqrt(latitude-".$users_latitude.")+sqrt(longitude-".user_longitude.") AS SQRT_DIST 
FROM GPS_tbl ORDER BY SQRT_DIST LIMIT 1;

This is how you will get the closest point.

Note that the returned distance is distance^2.

Upvotes: 1

dhi_m
dhi_m

Reputation: 1265

http://www.geodatasource.com/developers/php please check the tutorials like this to calculate distance between user sent coordinates and stored ones .After calculating distances sort the result set to get the nearest one.

Upvotes: 0

Related Questions