0

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

Johannes Kuhn
  • 14,778
  • 4
  • 49
  • 73
Nanosa
  • 3
  • 3

2 Answers2

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.
arheops
  • 15,544
  • 1
  • 21
  • 27
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
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Let me say you have 1billion points over the globe and you are calculating 1km distance. This one will search for whole dataset and for each one do complex math with table. Good luck. – arheops Jan 18 '23 at 20:22
  • he wants to get all user in 1 km distance not if the hole global, spatial indexes where introduced but as far as i can see nobody really got it to work properly, even then billions of rows will not be there, bt when you will need a fats server to process them all – nbk Jan 18 '23 at 20:25
  • I wanted to get all users within the radius so I think user_id > 1 will not give that as the point of user one is my center point – Nanosa Jan 19 '23 at 03:01
  • you didn't define the center point, s i assumed that you wanted a circle around user_id 1, so change the query to your liking adn exchange the POINT(31.000, 26.000) with your centerpoint – nbk Jan 19 '23 at 10:28