How do I select all points which are in a specific circle?

I have an oracle database which has a table with visitors and their positions. Now I want to select all points which are in a circle with a position and radius.

I am not too familiar with all the spatial functions but i tried sdo_contains with the first parameter the position of the visitor. The second one is the circle with the type SDO_GEOMETRY. I don't know exactly how my select should look like because I get the error "interface not supported without a spatial index". Am I missing some index within the visitors?

My select looks like this:

SELECT * FROM visitors v
WHERE SDO_INSIDE(v.position,
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,4), SDO_ORDINATE_ARRAY(8,7, 10,9, 8,11))) = 'TRUE';

Solution

You need to define a spatial index on your position column. For that your need first to define the spatial metadata needed. Like this:

insert into user_sdo_geom_metadata (table_name, column_name, dim info, srid)
values (
  'visitors',
  'position',
  sdo_dim_array (
     sdo_dim_element ('x',-1000,1000,0.05),
     sdo_dim_element ('y',-1000,1000,0.05)
  ),
  null
);
commit;

Then you can create the spatial index:

create index visitors_sx on visitors(position)
  indextype is mdsys.spatial_index;

Then your query will complete.

NOTES

(1) The above assumes that your spatial data (the positions) are in abstract cartesian coordinates (i.e. do not contain any explicit coordinate reference system id: SDO_SRID is NULL). That is also the case for your query window. However in real world applications you should always use explicit coordinate reference systems, i.e. always populate SDO_SRID. This is especially important if your coordinates are geodetic (i.e. longitude/latitude). Matching those in a cartesian way will return incorrect results. Furthermore, not having explicit SRIDs means you will be unable to do any measurements or match the data with data in other coordinate systems.

(2) Since Oracle 12.2, spatial indexes are optional. Since you get an error, I assume you are running an old (probably outdated) version of the Oracle database. The latest release is Oracle 19c. However for real-world applications, you should always have a spatial index - except if your data is really tiny: like just a few objects. Anything from 200 objects or more will need a spatial index in order to get the right level of performance.