Spatial Illusions in APEX: Trusting SDO_ANYINTERACT Can Be a Mistake

Today we’ll take a closer look at functions like SDO_ANYINTERACT, SDO_RELATE, and SDO_INSIDE. I’m not going to rewrite the documentation or explain all the differences between them - definitely not! Instead, I want to focus on one interesting case that recently confused me a lot. So, what happened? Let me explain. In APEX, I created a Map region that showed the entire area of the United States. I added an extra layer with some points. Next to the map, I made a report showing the list of points that are visible on the map. When I changed the visible area (bounding box), the number of points and the report content also changed. To show the correct points in the report, I used the SDO_ANYINTERACT function in the report’s source. And that’s where the problems began…

Introduction

First, I created a polygon layer with this SQL:

select
    mdsys.sdo_util.from_geojson('{"type":"Polygon","coordinates":[[
    [-126.51462009328223,28.38314514939684],
    [-69.800481013822,28.38314514939684],
    [-69.800481013822,49.42111619932274],
    [-126.51462009328223,49.42111619932274],
    [-126.51462009328223,28.38314514939684]
    ]]}') as polygon_geom
from dual;

Then, I added two Point layers: one at 30° latitude (Point 30), and one at 32° (Point 32):

select
    mdsys.sdo_util.from_geojson('{"type":"Point","coordinates":
    [-95.42931980540513, 30.944769651994008]
    }') as point_geom
from dual;

select
    mdsys.sdo_util.from_geojson('{"type":"Point","coordinates":
    [-95.42931980540513, 32.944769651994008]
    }') as point_geom
from dual;

At this stage, everything looked fine. The map showed the polygon, and both points inside it.
Or so I thought.

The Problem

Next, I used SDO_ANYINTERACT to get the points for the report.

select
    sdo_anyinteract(
        mdsys.sdo_util.from_geojson('{"type":"Point","coordinates":
            [-95.42931980540513, 30.944769651994008]
        }'),
        mdsys.sdo_util.from_geojson('{"type":"Polygon","coordinates":[[
            [-126.51462009328223,28.38314514939684],
            [-69.800481013822,28.38314514939684],
            [-69.800481013822,49.42111619932274],
            [-126.51462009328223,49.42111619932274],
            [-126.51462009328223,28.38314514939684]
    ]]}')
    ) WK
from dual;

For Point 30, the function returns FALSE, but for Point 32, it returns TRUE. But why? On the map, both points clearly appear inside the polygon! What’s going on?

Explanation

When you first look at it, it seems like the point should be inside the polygon - that’s how it looks on a regular map. But the shortest path between two points on Earth follows something called a great circle. If you look at this on a flat map, the line from (-126.5, 28.4) to (-69.8, 28.4) will appear curved upwards (north). On a globe, however, this is the straightest and shortest path. Because of this, the point (-95.4, 30.9), which seems to be inside the polygon, is actually outside of it.

To make polygons match how they look on a 2D map, we can use: SDO_UTIL.DENSIFY_GEOMETRY. This function adds more points to the shape, making it closer to what we see on a flat map.

In the end… the Earth isn’t flat, it’s a sphere - and that’s why this happens! So, I created a new polygon using DENSIFY_GEOMETRY:

WITH base_polygon AS (
    SELECT SDO_GEOMETRY(
        2003, 4326, NULL, 
        SDO_ELEM_INFO_ARRAY(1,1003,1), 
        SDO_ORDINATE_ARRAY(
            -126.51462009328223,28.38314514939684,
            -69.800481013822,28.38314514939684,
            -69.800481013822,49.42111619932274,
            -126.51462009328223,49.42111619932274,
            -126.51462009328223,28.38314514939684
        )
    ) AS geom
    FROM dual
)
SELECT SDO_UTIL.TO_GEOJSON(
    SDO_UTIL.DENSIFY_GEOMETRY(
        geom, 
        (SDO_GEOM.SDO_LENGTH(geom, 0.05) / 20) -- 20 segments
    )
) AS geojson_polygon
FROM base_polygon;

Now, the map shows the polygon correctly, and it turns out:

Point 30 is actually outside of the polygon. So yes - SDO_ANYINTERACT was right all along! But wait! Let’s imagine that the polygon isn’t custom, but just the visible area on the map (bounding box). Then Point 30 should be visible, and we’d expect it to be in the report. But SDO_ANYINTERACT still says FALSE, because it uses real Earth geometry. Depending on how you look at it, this can be both correct or... a problem.

The Solution

If you want both points to show up, even with the bounding box polygon, you can do something simple: set SRID = NULL.

select
    sdo_anyinteract(
        mdsys.sdo_util.from_geojson('{"type":"Point","coordinates":
            [-95.42931980540513, 30.944769651994008]
        }', srid => null),
        mdsys.sdo_util.from_geojson('{"type":"Polygon","coordinates":[[
            [-126.51462009328223,28.38314514939684],
            [-69.800481013822,28.38314514939684],
            [-69.800481013822,49.42111619932274],
            [-126.51462009328223,49.42111619932274],
            [-126.51462009328223,28.38314514939684]
    ]]}', srid => null)
    ) WK
from dual;

What does this do? SDO_ANYINTERACT returns TRUE. It makes all spatial calculations happen in flat Euclidean space, not on a sphere.

So what should you do? The simplest solution is often the best. If you want the bottom of your polygon to follow a specific latitude (like 28.3°), you need to add more points along that line - not just the 4 corners. You can even use PL/SQL to generate these points automatically. That way, you’ll get a polygon that works both in a flat map and with the right spatial logic.

Final Thoughts

Map problems are more complicated than they seem. What we see on the screen doesn’t always match the actual geometry. SDO_UTIL.DENSIFY_GEOMETRY helped show that clearly. The final solution (or adaptation) depends on what you're really trying to achieve.