SELECT
    id
FROM
    polygon_shapes
WHERE
    sdo_contains(
        shape,
        sdo_geometry('POINT(6.37616 74.335136111)', 4326)
    ) = 'TRUE';

Can we have a complete test case please, including

- a row that would match your current criteria
- a row that does not match your current criteria but *would* match under the more relaxed criteria

I can then pass this onto our Spatial team for a look

======================

I got this back from the Spatial team - big thanks to Hans/Albert for this. I also strongly recommend you attend their regular Office Hours sessions

All spatial predicates include the concept of “fuzziness”. That is a fundamental requirement because by its very nature, all spatial information (coordinates) imply a level of accuracy. In Oracle Spatial, it is expressed by the tolerance setting specified for each table. It is specified in the spatial metadata that is required for all tables that contain spatial data, and is left up to the end user to specify correctly, in accordance with the accuracy of the spatial data they deal with.

It is set by inserting information in the USER_SDO_GEOM_METADATA view:

insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'LAND_PARCELS',
  'SHAPE',
  sdo_dim_array(
    sdo_dim_element ('Long',-180, 180, 0.5),
    sdo_dim_element ('Lat',-90, 90, 0.5)
  ),
  4326
);


insert into user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
values (
  'MARKERS',
  'LOCATION',
  sdo_dim_array(
    sdo_dim_element ('Long',-180, 180, 0.5),
    sdo_dim_element ('Lat',-90, 90, 0.5)
  ),
  4326
);

In this example, we assume the coordinates are geographic (longitude/latitude GPS coordinates). The tolerance is specified in the last argument to sdo_dim_element(): 0.5, which here means 50cm (½ m). For projected data, the tolerance is in the unit of the coordinates in that projection (generally meters, but some projections use feet).

So, this query:

select l.parcel_id, ...
from land_parcels l, markers m
where m.marker_id = 'MK21-HR'
and sdo_anyinteract (l.shape, m.location) = 'TRUE';

will return all the land parcels that contain the selected marker - or more accurately, those parcels whose shape is within a buffer of 50cm around that marker. This also means it may return multiple matches, since multiple land parcels intersect that

But more importantly, notice this is using the sdo_anyinteract predicate. Using the sdo_contains predicate will return different results: it will only return those polygons that strictly contain the point, i.e. where the point with its virtual 50cm “buffer” is fully contained inside the polygon.

So it is important to understand, not only the concept of tolerance, but also the meaning of the spatial predicates. Those predicates are defined based on a well known concept called the Nine Intersection Model that formalizes the way geometric shape interact with each other by describing how their interior, boundary and exterior interact. See https://en.wikipedia.org/wiki/DE-9IM for details.

In a nutshell:

- INSIDE(A,B) / CONTAINS(B,A) means that A is entirely inside B, i.e it does not touch the boundary of B. CONTAINS(B,A) is the reverse of INSIDE (A,B)
- COVEREDBY(A,B) / COVERS (B,A) means that A is inside B but that its boundary touches the boundary of B
- TOUCH(A,B) means that A and B are adjacent: only their boundaries touch
- OVERLAPS(A,B) means that the boundaries of A and B intersect in some way
- EQUAL(A,B) means that A and B are topologically identical.
- ANYINTERACT(A,B) means that A and B interact in any of the above ways.

All of those execute within the assigned tolerance. For example, two adjacent parcels are found to touch as long as they are within the specified tolerance. This means for example that two parcels separated by a strip 40cm wide will be considered as touching if tolerance is set to 50cm. If tolerance is set to 20cm, then they will be considered as disjoint.

Matching points and polygons can be tricky. We may think that given a coverage of polygons (non-overlapping) and points, a point will always be in exactly one polygon. That is however not necessarily the case once you introduce tolerance. Some points will obviously be clearly inside a polygon, and some clearly outside. But some are ambiguous: they are “on the boundary” of the polygon - or more accurately, they fall in a small buffer around the boundary, whose width is set by the tolerance (say 50cm). This implies the following:

- When using INSIDE() or CONTAINS() those points will not be in the polygon, neither will they be in the adjacent polygon. A query that counts how many points are in each polygon will not count them
- When using ANYINTERACT() those points will be in the polygon, and also in the adjacent polygon. A query that counts how many points are in each polygon will count them twice (or more if the point happens to be near where three or more polygons meet).

Applications must be ready to handle those oddities. For example: using ANYINTERACT(), allocate the point to the first polygon matched. Or the largest one. Or any other condition based on some other column …

See https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/spatial-concepts.html#GUID-7469388B-6D23-4294-904F-78CA3B7191D3 for details on the tolerance concept and how it is used.

See https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/spatial-concepts.html#GUID-FA71CB03-FFF0-4529-BA07-AD0605DA7A89 for explanations about the spatial operators and the Nine Intersection model implemented in Oracle.