I've a SDO_GEOMETRY column containing quite large multi polygons, defined like this:
INSERT INTO t1 (i, d, g)
VALUES (
25,
'Multipolygon - multi-touch',
sdo_geometry (2007, null, null, sdo_elem_info_array (1,1003,1, 17,1003,1),
sdo_ordinate_array (50,95, 55,95, 53,96, 55,97, 53,98, 55,99, 50,99, 50,95, 55,100, 55,95, 60,95, 60,100, 55,100))
);
Instead of two polygons as in the example above, one column contains > 100 polygons.
I'd like to filter this column so it only will return a relevant subset (using a bbox?), something like:
SELECT filter(Geometry, bbox) from Table Where Id = 1
A first attempt at brute force solution might look something like this:
CREATE OR REPLACE FUNCTION FILTER_MULTI_POLYGONS
(
udtGeometry IN SDO_GEOMETRY,
udtMask IN SDO_GEOMETRY,
dTolerance IN NUMBER
)
RETURN SDO_GEOMETRY
AS
iElements INTEGER;
udtElement SDO_GEOMETRY;
udtResult SDO_GEOMETRY := NULL;
iCount INTEGER;
BEGIN
IF udtGeometry IS NOT NULL THEN
iElements := SDO_UTIL.GETNUMELEM(udtGeometry);
FOR iElement IN 1..iElements
LOOP
udtElement := SDO_UTIL.EXTRACT(udtGeometry, iElement);
IF SDO_GEOM.SDO_DISTANCE(udtElement, udtMask, dTolerance) <= dTolerance THEN
IF udtResult IS NULL THEN
udtResult := udtElement;
ELSE
udtResult := SDO_UTIL.APPEND(udtResult, udtElement);
END IF;
END IF;
END LOOP;
END IF;
RETURN udtResult;
END;
I say brute force because:
The individual sub-polygons are not indexed, so this solution does not leverage spatial indexing. If performance is important enough, it may be worthwhile to break your multi-polygons into their constitutent sub-polygons (with one row in your source table per sub-polygon) so that you can use a different solution that takes advantage of spatial indexing. You could do this either up front (by changing the design of your source table) or behind the scenes (maybe using a materialized view based on your original table).
It looks like you are on Oracle XE, and so are limited to the Locator subset of the Oracle Spatial functionality.
Items 1 and 2 mean that your only built-in choice for determining which sub-polygons interact with the mask appears to be via SDO_GEOM.SDO_DISTANCE. This is going to be resource intensive (since it will be called for every sub-polygon), and gives you only one type of interaction vs. the many that are possible with the built-in spatial operators (that rely on spatial indexing).
SDO_UTIL.APPEND may not be the most performant (or correct, particularly if your multi-polygons are not disjoint) way of building up the "filtered" multi-polygon result, but it illustrates the concept.
Anyway, this is what I get running your sample geometry against some sample masks. You should make sure the function returns the expected results against your real geometries.
SQL> REM Example mask that overlaps first polygon only
SQL> SELECT
2 FILTER_MULTI_POLYGONS
3 (
4 T1.G,
5 SDO_GEOMETRY
6 (
7 2003,
8 NULL,
9 NULL,
10 SDO_ELEM_INFO_ARRAY(1, 1003, 3),
11 SDO_ORDINATE_ARRAY(0, 0, 53, 96)
12 ),
13 0.1
14 ) AS RESULT
15 FROM T1;
RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(50, 95, 55, 95, 53, 96, 55, 97, 53, 98, 55, 99, 50, 99, 50, 95))
SQL> REM Example mask that overlaps second polygon only
SQL> SELECT
2 FILTER_MULTI_POLYGONS
3 (
4 T1.G,
5 SDO_GEOMETRY
6 (
7 2003,
8 NULL,
9 NULL,
10 SDO_ELEM_INFO_ARRAY(1, 1003, 3),
11 SDO_ORDINATE_ARRAY(56, 0, 60, 96)
12 ),
13 0.1
14 ) AS RESULT
15 FROM T1;
RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(55, 100, 55, 95, 60, 95, 60, 100, 55, 100))
SQL> REM Example mask that overlaps both polygons
SQL> SELECT
2 FILTER_MULTI_POLYGONS
3 (
4 T1.G,
5 SDO_GEOMETRY
6 (
7 2003,
8 NULL,
9 NULL,
10 SDO_ELEM_INFO_ARRAY(1, 1003, 3),
11 SDO_ORDINATE_ARRAY(0, 0, 100, 100)
12 ),
13 0.1
14 ) AS RESULT
15 FROM T1;
RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2007, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1, 17, 1003, 1), SDO
_ORDINATE_ARRAY(50, 95, 55, 95, 53, 96, 55, 97, 53, 98, 55, 99, 50, 99, 50, 95,
55, 100, 55, 95, 60, 95, 60, 100, 55, 100))
SQL> REM Example mask that overlaps neither polygon
SQL> SELECT
2 FILTER_MULTI_POLYGONS
3 (
4 T1.G,
5 SDO_GEOMETRY
6 (
7 2003,
8 NULL,
9 NULL,
10 SDO_ELEM_INFO_ARRAY(1, 1003, 3),
11 SDO_ORDINATE_ARRAY(0, 0, 10, 10)
12 ),
13 0.1
14 ) AS RESULT
15 FROM T1;
RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
Hope this helps.