Databases Reference
In-Depth Information
mysql>
UPDATE locations
->
SET lat_floor = FLOOR(lat), lon_floor = FLOOR(lon);
Now we need to search for a range of coordinates from floor to ceiling, both north and
south. Here is a query that shows the range of degrees we're looking for we're using
the query only for demonstration purposes; you should perform this math in the ap-
plication code, not in MySQL:
mysql>
SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
->
CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
->
FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
->
CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
+--------+--------+--------+--------+
| lat_lb | lat_ub | lon_lb | lon_ub |
+--------+--------+--------+--------+
| 36 | 40 | −80 | −77 |
+--------+--------+--------+--------+
Now we generate
IN()
lists with all integers between the floor and ceiling of each range.
Here's the query with the extra
WHERE
conditions added:
SELECT * FROM locations
WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);
Using a floor and ceiling introduces some extra slack into the computation, so the query
can actually find points that lie outside the square. That's why we still need the filters
on
lat
and
lon
, to discard the results that shouldn't be included. This is similar to the
technique we showed in the previous chapter for simulating a hash index with a CRC32
column: create an index on a value that isn't the whole truth but nevertheless gets us
close to the truth cheaply, and then post-filter to remove the few imposters.
In fact, at this point it makes sense to mention that instead of searching for a crude
square and then trimming the results to fit a precise square, we could search for a square
and then filter the results down with the great circle formula or the Pythagorean
theorem:
SELECT * FROM locations
WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
AND 3979 * ACOS(
COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
+ SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;
So we're back to the beginning—a precise circle—but we're doing it better now.
23
As
long as you pre-filter the result set with efficient techniques such as the auxiliary integer
columns and indexes, it's usually not bad at all to post-filter with the more costly math.
Just don't make the great-circle formula the first hoop the query has to jump through,
or everything will be slow!
23. Again, though, you should use application code to compute expressions such as
COS(RADIANS(38.03))
.
