CODEWITHSUNDEEP
mysqlsql
nad34
nad34

Reputation: 393

How to get values from another table and insert the result into another query

I am trying to to build a basic sql query where I have a table of petrol stations and another table of POI in my sql database and I would like to get all petrol stations within a radius of a POI

I have the following query:

        SELECT *, ( 6371000 * acos( cos( radians(15.4383252) )
 * cos( radians( petrol_stations.lat ) ) * cos( radians( petrol_stations.lng ) - radians(47.0450591) ) 
+ sin( radians(15.4383252) ) * sin(radians(petrol_stations.lat)) ) ) 
AS distance FROM petrol_stations HAVING distance < 500

and it seems to work fine, however I have to hard-code the coordinates of the POI into the query. Is it possible to adapt the query such that the POI coordinates are pulled from the other table if provided with the name such as main square?

Thanks in advance.

Upvotes: 1

Views: 71

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

assuming you have a georef poi table as 

  table_poi 

  id    name    lat           lng 
  1     my_poi  47.0450591 15.4383252

you could try a cross join for related all your petrol_stations with all your POI 

  SELECT *
    , ( 6371000 * acos( cos( radians(table_poi.lng) )
      * cos( radians( petrol_stations.lat ) ) * cos( radians( petrol_stations.lng ) 
           - radians(table_poi.lat) ) 
      + sin( radians(table_poi.lng) ) * sin(radians(petrol_stations.lat)) ) ) 
  AS distance 
  FROM petrol_stations  
  CROSS JOIN table_poi 
  HAVING distance < 500

or using where (as requested by MarlinPierce) 

  SELECT *
    , ( 6371000 * acos( cos( radians(table_poi.lng) )
      * cos( radians( petrol_stations.lat ) ) * cos( radians( petrol_stations.lng ) 
           - radians(table_poi.lat) ) 
      + sin( radians(table_poi.lng) ) * sin(radians(petrol_stations.lat)) ) ) 
  AS distance 
  FROM petrol_stations  
  CROSS JOIN table_poi 
  WHERE ( 6371000 * acos( cos( radians(table_poi.lng) )
      * cos( radians( petrol_stations.lat ) ) * cos( radians( petrol_stations.lng ) 
           - radians(table_poi.lat) ) 
      + sin( radians(table_poi.lng) ) * sin(radians(petrol_stations.lat)) ) ) < 500

and if you want filter for a POI name 

  SELECT *
    , ( 6371000 * acos( cos( radians(table_poi.lng) )
      * cos( radians( petrol_stations.lat ) ) * cos( radians( petrol_stations.lng ) 
           - radians(table_poi.lat) ) 
      + sin( radians(table_poi.lng) ) * sin(radians(petrol_stations.lat)) ) ) 
  AS distance 
  FROM petrol_stations  
  WHERE table_poi.name like '%your_poi_name%'
  CROSS JOIN table_poi 
  HAVING distance < 500

Upvotes: 1

Related Questions