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

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

CodePudding user response：

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

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

CodePudding user response：

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