Oracle Spatial - SDO_BUFFER does not work?

Question

I have a table which has SDO_Geometries and I query all the geometries to find their start and end point, then I insert these points to another table called ORAHAN. Now my main purpose is for each point in orahan I must find if it is intersects with another point in orahan when giving 2 cm buffer to points. So I write some pl sql using Relate and Bufer functions but when I check some records in Map Info, I saw there is points within 1 cm area from itself but no record in intersections table called ORAHANCROSSES. Am I use these functions wrongly or what?

Note: I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production and PL/SQL Release 11.2.0.1.0 - and SDO_PACKAGE

ORAHAN has approximately 400 thousands records.(points and other columns.)

declare
BEGIN
 for curs in (select * from ORAHAN t) loop
   for curs2 in (select *
                   from ORAHAN t2
                  where SDO_RELATE(t2.geoloc,SDO_GEOM.SDO_BUFFER(curs.geoloc,0.02,0.5) , 
                  'mask=ANYINTERACT') = 'TRUE'
                    and t2.mi_prinx <> curs.mi_prinx) loop                    
     Insert INTO ORAHANCROSSES
     values
       (curs.Mip, curs.Startmi, curs2.Mip, curs2.Startmi);
     commit;
   end loop;
 end loop;
END;

And this is MapInfo map image that shows 3 points which are close to each other aproximately 1 centimeter. But in the orahancrosses there is no record matching these 3.

Note: 0,00001000km equals 1cm

Orahan Metadata:

select * from user_sdo_geom_metadata where table_name = 'ORAHAN';

And diminfo:

Answer 1

What is the coordinate system of your data ? And, most important, what tolerance have you set in your metadata ?

Some other comments:

1) Don't use a relate with buffer approach. Just use a within-distance approach.

2) You don't need a PL/SQL loop for that sort of query just use a simple CTAS:

create table orahancrosses as
select c1.mip mip_1, c1.startmi startmi_1, c2.mip mip_2, c2.startmi startmi_2
from orahan c1, orahan c2
where sdo_within_distance (c2.geoloc, c1.geoloc, 'distance=2 unit=cm') = 'TRUE'
and c2.mi_prinx <> c1.mi_prinx;

3) As written, couples of points A and B that are within 2 cm will be returned twice: once as (A,B) and once again as (B,A). To avoid that (and only return one of the cases), then write the query like this:

create table orahancrosses as
select c1.mip mip_1, c1.startmi startmi_1, c2.mip mip_2, c2.startmi startmi_2
from orahan c1, orahan c2
where sdo_within_distance (c2.geoloc, c1.geoloc, 'distance=2 unit=cm') = 'TRUE'
and c1.rowid < c2.rowid;

3) Processing the number of points you mention (400000+) should run better using the SDO_JOIN technique, like this:

create table orahancrosses as
select c1.mip mip_1, c1.startmi startmi_1, c2.mip mip_2, c2.startmi startmi_2
from  table (
        sdo_join (
          'ORAHAN','GEOLOC',
          'ORAHAN','GEOLOC',
          'DISTANCE=2 UNIT=CM'
        )
      ) j,
      orahan c1, 
      orahan c2
where j.rowid1 < j.rowid2
and c1.rowid = j.rowid1
and c2.rowid = j.rowid2;

This will probably still take time to process - depending on the capacity of your database server. If you are licences for Oracle Enterprise Edition and your hardware has the proper capacity (# of cores) then parallelism can reduce the elapsed time.

4) You say you are using Oracle 11g. What exact version ? Version 11.2.0.4 is the terminal release for 11gR2. Anything older is no longer supported. By now you should really be on 12cR1 (12.1.0.2). The major benefit of 12.1.0.2 in your case s the Vector Performance Accelerator feature that speeds up a number of spatial functions and operators (only if you own the proper Oracle Spatial licenses - it is not available with the free Oracle Locator feature).

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

Using the two points in your example. Let's compute the distance:

select sdo_geom.sdo_distance(
  sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.08336913,null),null,null), 
  sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.07336716,null),null,null),
  0.005
) distance
from dual;


  DISTANCE
----------
 .01000197

1 row selected.

Notice I don't specify any SRID. Assuming the coordinates are expressed in meters, the distance between them is indeed a little more than 1 cm.

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

The reason why your original syntax does not work is, as you noticed, because of the tolerance you specify for the SDO_BUFFER() call. You pass it as 0.5 (=50cm) to produce a buffer with a radius of 0.02 (2cm). The effect is that the buffer produced effectively dissolves into the point itself.

For example at tolerance 0.5:

select sdo_geom.sdo_buffer(sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.08336913,null),null,null),0.02,0.5) from dual;

Produces:

SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(521554.782, 4230983.08, NULL), NULL, NULL)

At tolerance 0.005:

select sdo_geom.sdo_buffer(sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.08336913,null),null,null),0.02,0.005) from dual;

You get the proper buffer:

SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 2), SDO_ORDINATE_ARRAY(521554.782, 4230983.06, 521554.802, 4230983.08, 521554.782, 4230983.1, 521554.762, 4230983.08, 521554.782, 4230983.06))

And the very close point now matches with that buffer:

select sdo_geom.relate(
  sdo_geom.sdo_buffer(sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.08336913,null),null,null),0.02,0.005),
  'determine',
  sdo_geometry (2001,null,sdo_point_type(521554.782174622,4230983.07336716,null),null,null),
  0.005
) relation
from dual;

RELATION
------------------------- 
CONTAINS

1 row selected.

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

Now the fact that your data does not have a proper explicit SRID means that the use of explicit units in measurements or distance-based searches will not work. Because the database does not know what coordinate system your data is in, it does not know how to determine that two points are less than a set number of cm or m apart. All you can do is assume the coordinates are in meters.

So in the examples I give above, replace 'DISTANCE=2 UNIT=CM' with 'DISTANCE=0.02'