Eliminate subquery for numerical value

Question

Quest

The query selects all points starting with Vancouver and is within a 5-minute area from the center of all places starting with Vancouver. For example, Vancouver South Fraser, Vancouver Fairview and Vancouver Ballantree Place W have latitudes and longitudes within 5 minutes of their average latitude and longitude. Latitude and longitude are stored as (4915, 12311) whole pairs (which means 49.15'N and 123.11'W).

SQL code

The following aversion to SQL does the trick:

SELECT NAME FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%' AND LATITUDE BETWEEN (SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%') and (SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%') AND LONGITUDE BETWEEN (SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)-5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%') and (SELECT round((min(LONGITUDE) + max(LONGITUDE)) / 2)+5 FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%') ORDER BY LATITUDE 

Question

How to simplify this query to remove redundancy without using a view?

Limitations

The database is MySQL, but ANSI SQL is always good.

Thanks!

Answer 1

 select name from (select round((min(LATITUDE) + max(LATITUDE)) / 2) as LATITUDE, round((min(LONGITUDE) + max(LONGITUDE)) / 2) as LONGITUDE from STATION where DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%') AS center inner join STATION s where s.DISTRICT_ID = '110' and s.NAME like 'Vancouver%' and s.LATITUDE between center.LATITUDE - 5 and center.LATITUDE + 5 and s.LONGITUDE between center.LONGITUDE - 5 and center.LONGITUDE + 5 

Answer 2

First of all, note that your definition of “within 5 minutes of each other” does not determine the only solution and that your (MIN () + MAX ()) / 2 is not average, but simply average and minimal. You can search for AVG () in your subqueries.

Secondly, you do not get results within 5 seconds of each other, but records whose longitude and latitude are no more than 10 seconds (which diagonally can be closer to 14).

In mysql, you can use session variables such as:

 SET @avg_lat := (SELECT round(avg(LATITUDE)) FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%'); SET @avg_long := (SELECT round(avg(LONGITUDE)) FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%'); SELECT NAME FROM STATION WHERE DISTRICT_ID = '110' AND pow( LATITUDE-@avg _lat,2)+pow( LONGITUDE-@avg _long,2)<25 ORDER BY LATITUDE 

Despite the fact that this is optional (as in the request written above, both variables occur only once).

EDIT: Oh, skip the question. This is the center radius - so replace 25 with 100 (and until it is decided whether you want to use less or equal). Also, if the center is the center of the bounding box, your (min () + max ()) / 2 is the correct formula, not my suggestion. However, the “center of all places” is a bit vague, so I leave my answer (it’s easy to change).

EDIT2: just noticed that the units in my request are not correct, if the latitude is stored in centimines, then the comparison should also be in centimines (10 * 100) ^ 2 = 1000000

And finally, your decision to stick with (min () + max ()) / 2 will lead to examples where you can have one line that is far from the maximum and minimum, which may cause the query to skip any of the results (and may to happen, as a rule, all locations with similar names are next to each other, but it’s not uncommon to have another place starting with the same name, which is an isolated place away from a conglomerate of locations).

As for the 5-minute area, to be completely accurate, it’s better to say that it is a 10x10 minute area to return requests.

EDIT3: The above formula for distance is not very accurate if you move away from the equator. Here is the best approximation of distance formula For serious work you may need something like this

Answer 3

Use a common table expression ...

 with cte as ( SELECT round((min(LATITUDE) + max(LATITUDE)) / 2)-5 min_lat , round((min(LATITUDE) + max(LATITUDE)) / 2)+5 max_lat , round((min(LONGITUDE) + max(LONGITUDE)) / 2)-5 min_long , round((min(LONGITUDE) + max(LONGITUDE)) / 2)+5 max_long , DISTRICT_ID , 'Vancouver%' AS NAME FROM STATION WHERE DISTRICT_ID = '110' AND NAME LIKE 'Vancouver%' group by DISTRICT_ID, 'Vancouver%') SELECT NAME FROM STATION , cte WHERE station.DISTRICT_ID = cte.DISTRICT_ID AND station.NAME LIKE cte.NAME AND station.LATITUDE BETWEEN cte.min_lat AND cte.max_lat AND station.LONGITUDE BETWEEN cte.min_long AND cte.max_long ORDER BY station.LATITUDE 

NB: I do not have access to the database right now, so I could not verify this. Therefore, I can not guarantee that it works. I will check it when I can. The principle holds.

Answer 4

I assume the original query yielded the exact result for your purpose. If so, then you can consolidate the query by putting endpoint calculation in a subquery.

 Select ... From Station As S Cross Join ( Select Round( (Min(S1.Latitude) + Max(S1.Latitude)) / 2 ) As Latitude , Round( (Min(S1.Longitude) + Max(S1.Longitude)) / 2 ) As Longitude From Station As S1 Where S1.District_Id = '110' And S1.Name Like 'Vancouver%' ) As S2 Where S.District_Id = '110' And S.Name Like 'Vancouver%' And S.Latitude Between (S2.Latitude - 5) And (S2.Latitude + 5) And S.Longitude Between (S2.Longitude - 5) And (S2.Longitude + 5) Order By S.Latitude 

Answer 5

Whatever happens to the good old Pythagoras (well, I know that this really does not apply to curved surfaces - but it should be a good enough approximation). If you are looking for the center (in fact, the centroid based on the interpretation used by the physicist, not the geometers) of the set of coordinate pairs, then you should not use MIN and MAX, although you can consider limiting the search based on MIN and MAX). The only remaining fly in the ointment is that you keep the integer representation of the string representation of the coordinate angle.

Consider:

 SELECT b.name FROM (SELECT AVG(CALC(a.lattitude)) AS c_lat, AVG(CALC(a.longitude)) AS c_long FROM station a WHERE a.district_id='110' AND a.name like 'VANCOUVER%' ) AS ilv, station b WHERE b.district_id='110' AND b.name LIKE 'VANCOUVER%' AND POW(ilv.c_lat-CALC(b.lattitude),2) + POW(olv.c_long-CALC(b.longitude),2)<=25; 

If the CALC function converts the stored value to longitude / power in minutes, i.e.

 CALC(x)=(FLOOR(x/100)*60+MOD(x,100)) 

FROM.