Part 1 was pretty straightforward. I extract the X and Y coordinates from each line and then using a self-join, I compare the areas of each possible rectangle. As with many other, similar, self-joins, I use the b.seq > a.seq filter to eliminate redundant, commutative calculations (i.e. rectangles of A & B corners are the same as rectangles of B & A corners.)
Sorting the areas in descending order and returning the first row yields the result.
WITH data
AS
(SELECT seq,
str,
TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 1), '99999') x,
TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 2), '99999') y
FROM advent.data2rows('advent2025-9'))
SELECT a.seq a, b.seq b, (ABS(a.x - b.x) + 1) * (ABS(a.y - b.y) + 1) area, '(' || a.str || ')-(' || b.str || ')' points
FROM data a JOIN data b ON b.seq > a.seq
ORDER BY area DESC
FETCH FIRST 1 ROW ONLY;
Part 2 adds a little more complexity and is the first time I’ve used the Oracle Spatial and Graph functionality to solve one of the Advent puzzles; which was an interesting twist.
The first change from part 1 is that the set of given points form a polygon when you connect them. I fill a spatial ordinate array with the X and Y values. These are unpivoted so the array is of the form x1, y1, x2, y2, x3, y3…, xN,yN, x1, y1. With the last point being equal to the first point to connect the polygon. The points were listed in counter clockwise order; which is what is required by the sdo_elem_info object. If they were listed in clockwise order I would sort by seq in descending order to reverse them back to what the syntax needed.
From part 1, I know I can generate all of the possible rectangles quickly. So I reused that logic and then in a cursor-for loop I check if each rectangle is contained within the perimeter of the polygon. Since the rectangles are defined, in part, by the corners and edges of the polygon the SDO relation is contained rather than inside, because the rectangle will always be touching the perimeter.
I could have included the sdo_relate condition in the driving query, but that would mean checking the relation for every rectangle, even the smaller ones we know we won’t use, which would consume time without adding value (I did test both ways in case my assumption was incorrect.) Checking within the loop which is sorted from largest to smallest area guarantees I’m only checking containment for the fewest number of rectangles.
It would have been nice to just use an IF statement on sdo_relate result, but that operator can only be used in a SQL where clause, so I used simple query from DUAL. If the query returns a row then print it and we’re done. If it doesn’t, then continue iterating through the rectangles.
DECLARE
c_data CONSTANT VARCHAR2(20) := 'advent2025-9';
v_polygon MDSYS.sdo_geometry;
v_ordinates MDSYS.sdo_ordinate_array := mdsys.sdo_ordinate_array();
v_found PLS_INTEGER := 0;
BEGIN
FOR rec
IN (SELECT TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 1), '99999') x,
TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 2), '99999') y
FROM advent.data2rows(c_data))
LOOP
v_ordinates.EXTEND(2);
v_ordinates(v_ordinates.COUNT - 1) := rec.x;
v_ordinates(v_ordinates.COUNT) := rec.y;
END LOOP;
-- The points in the polygon must be a loop ending with the starting point, so append the first point to the end.
v_ordinates.EXTEND(2);
v_ordinates(v_ordinates.COUNT - 1) := v_ordinates(1);
v_ordinates(v_ordinates.COUNT) := v_ordinates(2);
-- sdo_gtype 2003 is 2-dimensional polygon
-- sdo_elem_info (OFFSET 1 - start with first element, ETYPE 1003 - polygon ring in counterclockwise order, INTERPRETATION 1 - connect ring of points with straight lines)
v_polygon :=
mdsys.sdo_geometry(sdo_gtype => 2003,
sdo_srid => NULL,
sdo_point => NULL,
sdo_elem_info => mdsys.sdo_elem_info_array(1, 1003, 1),
sdo_ordinates => v_ordinates);
FOR r
IN (
WITH data
AS
(SELECT seq,
TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 1), '99999') x,
TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+', 1, 2), '99999') y
FROM advent.data2rows(c_data))
SELECT a.x xa, a.y ya, b.x xb, b.y yb, (ABS(a.x - b.x) + 1) * (ABS(a.y - b.y) + 1) area
FROM data a JOIN data b ON b.seq > a.seq
ORDER BY area DESC
)
LOOP
--sdo_relate operator can only be used in a SQL where-clause, so use a dummy query on dual to do the check
-- Use "covers" instead of "inside" since the rectangle will touch the border of the outer polygon
BEGIN
SELECT 1
INTO v_found
FROM DUAL
WHERE sdo_relate(
v_polygon,
mdsys.sdo_geometry(
sdo_gtype => 2003,
sdo_srid => NULL,
sdo_point => NULL,
sdo_elem_info => mdsys.sdo_elem_info_array(1, 1003, 1),
sdo_ordinates =>
mdsys.sdo_ordinate_array(LEAST(r.xa, r.xb),
LEAST(r.ya, r.yb), -- upper left corner
LEAST(r.xa, r.xb),
GREATEST(r.ya, r.yb), -- lower left corner
GREATEST(r.xa, r.xb),
GREATEST(r.ya, r.yb), -- lower right corner
GREATEST(r.xa, r.xb),
LEAST(r.ya, r.yb), -- upper right corner
LEAST(r.xa, r.xb),
LEAST(r.ya, r.yb) -- upper left corner again, completing rectangle
)),
'covers');
DBMS_OUTPUT.put_line('(' || r.xa || ',' || r.ya || ')(' || r.xb || ',' || r.yb || ') ' || r.area);
EXIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
END LOOP;
END;
This one was fun for me. I don’t have a lot of opportunities to use the Spatial and Graph functionality in my day-to-day job, so knocking the dust off some brain cells was enjoyable.