Clever Geek Handbook

SQL Radius Search in one table related to another

Please tell me how to join two tables when I want to get all stores from table one based on a specific city_id which has its own geo_lat and geo_long in table 2? I want to get all shops within a 25 km radius of my shop.

Table one - stores: shop_name, store description, city_id

Table 2 - cities: city_id, city_name, geo_lat, geo_long

I have tried many examples and methods but no result.

I recognize this code for distance:

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;

I will be very grateful for each of your answers. Thank you very much.

+3


source to share


1 answer 


    select s.shop_name, s.shop_description, s.town_id, c.city_id, c.city_name, c.geo_lat, c.geo_long from shops s
    right join cities c on c.city_id = s.town_id 
and (
    3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians(c.geo_lat ) )
      * cos( radians( c.geo_long ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( c.geo_lat ) )
    )
  ) <= 25
    where c.city_name = 'Prague'


0


source






More articles:


All Articles