Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting similar longitude and latitude from database

I am creating a mobile app using Phonegap and JQuery.

The app collects the longitude and latitude of users and stores them in a database (using a PHP script - web service), and with some algorithm it detects whether there is traffic or not. What I need is to grab all the longitude and latitude values from the database which are close to each other, say in the same street/200m range.

Lets say you have a list in the database with 5 lon/lat which are near each other (in the same street A), 3 lon/lat which are near each other in some Street B, and some other representing users randomly in other streets. Can anyone tell me how I can detect the lon/lat values that are near each other? The values I am storing them as separate values in the MySQL database, ie one field for longitude and another for latitude.

Once I obtain the lon/lat which are near each other, then I would need to find the center point between the users in street A, and users in street B to mark as a traffic congestion (based on some other detections).

like image 219
user1809790 Avatar asked Dec 19 '12 11:12

user1809790


People also ask

How do you represent latitude and longitude in a database?

Latitude & longitude values can be represented & stored in a SQL database using decimal points (Decimal degrees) rather than degrees (or Degrees Minutes Seconds). Depending on the accuracy you may require, you can decide how many decimal points you need to keep latitude & longitude values.

What is the datatype of latitude and longitude in SQL?

Spatial Types - geography This type represents data in a round-earth coordinate system. The SQL Server geography data type stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.


1 Answers

You should look into the Haversine formula. Please see below:

SELECT id, (3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM markers 
HAVING distance < 25 
ORDER BY distance;

This will return all records that are within 25 miles of the input long / lat pair.

like image 107
BenM Avatar answered Sep 28 '22 18:09

BenM