SDO_INSIDE returns zero records

Question

So I have a table village:

CREATE TABLE village (
  building_id integer PRIMARY KEY,
  name VARCHAR2(30),
  visitors integer,
  building SDO_GEOMETRY
);

And a table visitors:

create table visitors(
  id integer,
  position SDO_GEOMETRY 
);

Here are the inserts:

INSERT INTO village VALUES(2,'KircheV2', 4,
  SDO_GEOMETRY(
      2003,
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(100,100, 100,120, 120,100, 120,120)
  )
);


INSERT INTO visitors VALUES (1,
  SDO_GEOMETRY(
      2001,
      NULL,
      SDO_POINT_TYPE(110, 110, NULL),
      NULL,
      NULL
  )
);

For some reason when I try to get all visitors which are INSIDE the "KircheV2" the SQL statement always returns zero records:

SELECT * FROM visitors,village WHERE village.name like 'KircheV2' and (SDO_INSIDE(village.building,visitors.POSITION) = 'TRUE');

What can be the reason behind it? The coordinates 110;110 should actually be right in the middle of the building, so it should be inside the building.

Answer 1

If error is ORA-13348: polygon boundary is not closed, it can be fixed by using

SQL> Update village set SDO_UTIL.RECTIFY_GEOMETRY(building,0.005) where building_id=2;

This function help to fix broken geometry on Oracle.

Answer 2

Your data is incorrect. You can verify it like this:

SQL> select sdo_geom.validate_geometry_with_context (building,0.005) from village;

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(BUILDING,0.005)
-------------------------------------------------------------------------------
13348 [Element <1>] [Ring <1>]

1 row selected.

That error means:

ORA-13348: polygon boundary is not closed

In Oracle (actually all storage systems) according to OGC rules, the polygons must close, i.e. the first vertex must repeat as last vertex. So:

INSERT INTO village VALUES(2,'KircheV2', 4,
  SDO_GEOMETRY(
      2003,
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(100,100, 100,120, 120,100, 120,120, 100,100)
  )
);

But the select still fails to return any result. Why is that ?

SQL> select sdo_geom.validate_geometry_with_context (building,0.005) from village;

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(BUILDING,0.005)
-------------------------------------------------------------------------------
13349 [Element <1>] [Ring <1>][Edge <2>][Edge <4>]

1 row selected.

This error means:

ORA-13349: polygon boundary crosses itself

That makes sense: the vertices clearly form a butterfly shape:

100,100, 100,120, 120,100, 120,120, 100,100

Assuming you want to form a simple rectangle, then the proper shape is:

100,100, 100,120, 120,120, 120,100, 100,100

INSERT INTO village VALUES(2,'KircheV2', 4,
  SDO_GEOMETRY(
      2003,
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(100,100, 100,120, 120,120, 120,100, 100,100)
  )
);

Still no result. Why ?

SQL> select sdo_geom.validate_geometry_with_context (building,0.005) from village;

SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(BUILDING,0.005)
-------------------------------------------------------------------------------
13367 [Element <1>] [Ring <1>]

1 row selected.

This means:

ORA-13367: wrong orientation for interior/exterior rings

Rings in polygons must be properly orientated. Outer rings must be counter-clockwise, inner rings (holes) must be clockwise. So you need to write it like this:

100,100, 120,100, 120,120, 100,120, 100,100

INSERT INTO village VALUES(2,'KircheV2', 4,
  SDO_GEOMETRY(
      2003,
      NULL,
      NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,1),
      SDO_ORDINATE_ARRAY(100,100, 120,100, 120,120, 100,120, 100,100)
  )
);

Still no results! But that is because your query is incorrectly formulated. SDO_INSIDE(a,b) finds all occurrences of A that are fully inside B. In your case, it is like asking for buildings that are inside a visitor. Clearly you want the reverse, so either say: SDO_INSIDE(visitor, building) or SDO_CONTAINS (building,visitor), like this:

SELECT * FROM visitors,village WHERE village.name like 'KircheV2' and SDO_INSIDE(visitors.POSITION,village.building) = 'TRUE';
SELECT * FROM visitors,village WHERE village.name like 'KircheV2' and SDO_CONTAINS(village.building,visitors.POSITION) = 'TRUE';

Some additional comments:

1. Your example is purely artificial, I imagine ? In real life, your polygons will come from some GIS systems - like ESRI shapefiles that you load into the database, or shapes captured from some GIS tool. Either way, both will produce correct shapes since all tools apply then OGC rules for shape closure and orientation. If not the validation function will tell you the errors, and the sdo_util.rectify_geometry function will correct the fundamental errors.

2. You also need to understand the spatial operators (INSIDE vs CONTAINS etc) and their effects. The documentation explains what they mean. Note that the set of SDO_xxx operators is slightly different from the ST_xxx functions defined by the OGC.

3. You do not specify any coordinate system (SDO_SRID is NULL). While that works in your artificial example, in real life you should always use the proper coordinate system. In particular if your shapes are geodetic (in long/lat), you must say so by using the proper SRID: 4326. This guarantees that all computations are done within the context of the ellipsoidal shape of the earth. That is especially important if you want to perform distance-based queries or measure lengths, distances or areas. Using the proper SRID for projected data is equally important. It lets you perform queries irrespective of the coordinate systems used: for example find which parcel (in a local projection) a GPS point is located.

4. Performance and indexing. Make sure you have spatial indexes in place. While Oracle 12.2 lets you do queries without defining an index, prior releases always require one (and fail if none exists). And performing a query on a table without a spatial index can be really slow if that table is even moderately large. For example, consider your villages and visitors example. Say you want to find out all visitors in one specific building, from a table of 10 million visitors. Without any index on the visitors table, the database will need to compare each and every visitor with the selected building. That will be expensive in CPU (I/Os are not a real issue).

5. Finally, it is important to write the queries properly. In an operator like F(a,b), b is used to search a so b should be the smaller set. For example finding all visitors in this building must be written as SDO_INSIDE(visitors, buildings). The reverse (in which building is this customer ?) write as SDO_CONTAINS(buildings, visitors).