Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a mySQL table with user name, latitude and longitude of the user. I would like to get a list of user who are inside the circle or square of a given latitude and longitude with given distance. For example my input Lat= 78.3232 and Long = 65.3234 and distance = 30 miles. I would like to get the list of users who are inside 30 miles distance from the point 78.3232 and 65.3234. Is it possible to solve this with single query? Or can you give me a hint start solving this query? I am new to the geo based information.

share|improve this question
    
Why not PostGIS ? If you are starting geo project you can still change your stack –  simplexio Jan 14 '13 at 7:32

3 Answers 3

up vote 3 down vote accepted

The SQL statement that will find the closest 20 locations that are within a radius of 30 miles to the 78.3232, 65.3234 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 30 miles, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

SELECT
  id, (
    3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
  ) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

This is using the Google Maps API v3 with a MySQL backend which your already have.

https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

share|improve this answer

Here is my variant of query, seems a little bit easier (http://dexxtr.com/post/83498801191/how-to-determine-point-inside-circle-using-mysql)

SELECT 
    *
FROM 
    `locator`
WHERE
    SQRT(POW(X(`center`) - 49.843317 , 2) + POW(Y(`center`) - 24.026642, 2)) * 100 < `radius`
share|improve this answer

Mapperz's answer is invalid. Sinus must be calculated from latitude and NOT from longitude. So corect SQL statement is:

SELECT
    id, (
      3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.