Question 21198 in Mysql Question posted in
The official MySQL documentation can be found here.

I want all retrieve all data of customer within a radius with longtude and latitude of 1 km – Mysql

I’m using redash and my table looks something like this. I’m just learning sql. This is very advanced for me. I searched many questions, no queries worked for me

|user_id | long | lat  |
|1       |31.000|26.000|
|2       |30.000|25,000|
|3       |30.003|25,007|

I need to get all the customers that used my service in this point and a radius of 1 km as well my table looks something like this one written above
adding that point of user 1 is my cennter point

Tags:

2

Answers


  1. 0

    It is hard task just because longitude 1 is the different number of kilometers on different areas(zero on pole, 111km on equator).

    So most likely you have first filter your data using some values in the range for your country, after that use some specialized library to calculate the exact distance.

    If you have exact point before search, you have to

    1. Ask library outside MySQL what is distance 1km in longitude and latitude in the area
    2. Filter your dataset with pointX-deltaX,pointX-deltaX and pointY- deltaY, pointY+deltaY. After this you will get square which contain your circle
    3. calculate distance via specialized library or using math formula to ensure it is circle.
    Login or Signup to reply.
  2. 0

    you can use spatial functions

    SELECT 
    ppl.user_id,
    ST_DISTANCE_SPHERE(POINT(31.000, 26.000),
            POINT(ppl.Long, ppl.Lat))  AS distance
FROM
    `mtytable` AS ppl
WHERE
  user_id  > 1 
HAVING distance < 1000
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search