Method Example II (Cont.)
- Find the minimum areas of the covering rectangles for each point.
|
|
†The GROUP BY clause is to guarantee that the min set function runs over one point at a time and not the entire set of points.
- Identify the minimal rectangles as well by using a correlated subquery to calculate the minimal area rectangle for a point and use that in the outer
SELECT.
†The outer SELECT may find more than one rectangle of the same minimal size, for some point.
SQL> SELECT p.x, p.y, min(r.area( )) FROM rects r, points p
2 WHERE r.inside( value(p) ) > 0
3 GROUP BY p.x, p.y;
X Y MIN(R.AREA())
---------- ---------- -------------
1 4 9
2 3 9
4 4 9
SQL> SELECT value(p), value(r), r.area( ) FROM rects r, points p
2 WHERE r.area( ) = (
3 SELECT min(r1.area( )) FROM rects r1
4 WHERE r1.inside( value(p) ) > 0 )
5 AND r.inside( value(p) ) > 0
6 ORDER BY p.x, p.y;
value(P)(X, Y) value(R)(PT1(X, Y), PT2(X, Y)) R.AREA( )
--------------- ------------------------------------------ ---------
POINT_T(1, 4) RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5)) 9
POINT_T(2, 3) RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5)) 9
POINT_T(4, 4) RECTANGLE_T(POINT_T(1, 2), POINT_T(4, 5)) 9
|
|
†Like standard PL/SQL procedures and functions, method functions can be called from within SQL methods, as long as they do not write to the database, create any objects, or write to any object attributes.
‡The methods of
rectangle_t object did not actually extend the power of the queries.
However, without using the methods, the above query is extremely long and complicated if interactive SQL is used.
Undertaking a Masters program ensures that you always
have a lot on your plate (are too busy).
|