Question

Query the apartment table and find nearby restaurants with in 200 meters distance. I have two tables one gives the home adressses and second has near by businesses with latitude and longitude given. I need to find restaurants nearby the adress with in 200 meters.

Each business also has longitude/latitude information, we can get the database to calculate distances to various useful points close by to each apartment. So we have two tables 1. Apartments and no 2 is business. Need to find restaurants near by downtown lets suppose. Below are table structures.

select * from apartments where listing =120;
+---------+--------------+----------------+-----------+-------+-------------+----------+-----------+
| listing | neighborhood | address | city | state | postal_code | latitude | longitude |
+---------+--------------+----------------+-----------+-------+-------------+----------+-----------+
| 120 | Centennial | 7050 N Durango | Las Vegas | NV | 89149 | 36.2889 | -115.287 |

describe business;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | varchar(22) | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| neighborhood | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
| state | varchar(255) | YES | | NULL | |
| postal_code | varchar(255) | YES | | NULL | |
| latitude | float | YES | | NULL | |
| longitude | float | YES | | NULL | |
| stars | float | YES | | NULL | |
| review_count | int(11) | YES | | NULL | |
| is_open | tinyint(4) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+

select * from business where city='Pittsburg';
+------------------------+------------------------------------+--------------+----------------------------+-----------+-------+-------------+----------+-----------+-------+--------------+---------+
| id | name | neighborhood | address | city | state | postal_code | latitude | longitude | stars | review_count | is_open |
+------------------------+------------------------------------+--------------+----------------------------+-----------+-------+-------------+----------+-----------+-------+--------------+---------+
| DtCB2hyCDcXi6AOFCl8mpA | Paint Nite | Downtown | | Pittsburg | PA | 15270 | 40.4425 | -79.9958 | 4.5 | 9 | 1 |
| FJm4h4vOfb3tEEGC3uMdOQ | Louis Vuitton Pittsburgh Ross Park | | 1000 Ross Park Mall Dr | Pittsburg | PA | 15237 | 40.5446 | -80.0088 | 5 | 6 | 1 |
| TfCke3QtOXHjWvmGmEsiTA | Classic Chevrolet | Bellevue | 500 Lincoln Avenue | Pittsburg | PA | 15202 | 40.496 | -80.0558 | 2.5 | 8 | 1 |
| tm3pi4PAk-5Mxs_ZnTgqUA | Picture People | | Sears, 300 South Hills Vlg | Pittsburg | PA | 15241 | 40.3401 | -80.0548 | 3 | 8 | 1 |

describe business;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id | varchar(22) | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| neighborhood | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| city | varchar(255) | YES | | NULL | |
| state | varchar(255) | YES | | NULL | |
| postal_code | varchar(255) | YES | | NULL | |
| latitude | float | YES | | NULL | |
| longitude | float | YES | | NULL | |
| stars | float | YES | | NULL | |
| review_count | int(11) | YES | | NULL | |
| is_open | tinyint(4) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+

In mysql we can calculate distance in meters by

select
ST_Distance_Sphere( point(151.20208, -33.883741), point(151.195986, -33.87266))
from dual;

max distance between nearby businesses to home is 200 meters. we need to use both tables as well as lat n long to calculate.

The program then queries the businesses in Las Vegas, NV and, for each restaurant within 200 meters of the apartment, displays the name, rating, and number of reviews of each such restaurant, but only if there are at least 10 reviews

Answer 1

Below is the SQL query for finding the restaurant with in 200 meters of the apartment. Both apartment and business tables are joined based on their latitude and longitude. Conditions are given in where clause to filter the result.

SQL query-

SELECT a.listing, a.neighborhood, b.name, b.stars, b.review_count
FROM apartment AS a
INNER JOIN business AS b
ON ST_Distance_Sphere( point(a.latitude, a.longitute), point(b.latitude, b.longitude)) <= 200
WHERE b.city = 'Las Vegas' AND state = 'NV' AND b.review_count > 10;