Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Inconsistent results between SDO_RELATE and SDO_GEOM.RELATE

John O'TooleDec 19 2008 — edited Dec 19 2008
Maybe its the Friday syndrome, but I'm getting some results that I can't get my head around...

Let say I have a table with a single line geometry...
CREATE TABLE BUFFER_TEST (
	WHAT VARCHAR2(100), 
	GEOMETRY SDO_GEOMETRY);

INSERT INTO user_sdo_geom_metadata VALUES ('BUFFER_TEST','GEOMETRY',  
MDSYS.SDO_DIM_ARRAY(
	MDSYS.SDO_DIM_ELEMENT('X',400000,750000,0.0005), 
	MDSYS.SDO_DIM_ELEMENT('Y',500000,1000000,0.0005)), 
262152);

CREATE INDEX BUFFER_TEST_IDX ON BUFFER_TEST (GEOMETRY) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

INSERT INTO BUFFER_TEST (what, geometry) 
VALUES ('line',
SDO_GEOMETRY(2002, 262152, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), 
SDO_ORDINATE_ARRAY(713353.165, 736165.812, 713353.449, 736165.402, 713353.849,
 736164.203, 713353.85, 736162.252, 713353.087, 736149.092)));
COMMIT;
Now I want to buffer this line and check if the line is inside the buffer - the actual business need is to see if other lines are in the buffer, but we'll keep it simple for now...
So in the snippet below, I'm buffering the line by 50cm and then using SDO_INSIDE to see if the line is inside the buffer - it isn't.
Then if I use SDO_GEOM.RELATE to determine the relationship, it says INSIDE, which is correct.
Then if I increase the buffer size to 1m, then SDO_INSIDE and SDO_GEOM.RELATE both return the correct result.
SQL> DECLARE
  2   l_inside  NUMBER;
  3   l_small_buffer SDO_GEOMETRY;
  4   l_determine  VARCHAR2(100);
  5   l_buffer_size  NUMBER := 0.5;
  6  BEGIN
  7
  8     SELECT  SDO_GEOM.SDO_BUFFER(b.geometry, usgm.diminfo, l_buffer_size)
  9     INTO      l_small_buffer
 10     FROM    user_sdo_geom_metadata usgm, BUFFER_TEST b
 11     WHERE  usgm.table_name = 'BUFFER_TEST'
 12     AND      usgm.column_name = 'GEOMETRY'
 13     AND      b.what = 'line';
 14
 15     SELECT  COUNT(*)
 16     INTO    l_inside
 17     FROM   BUFFER_TEST
 18     WHERE SDO_INSIDE(geometry, l_small_buffer) = 'TRUE'
 19     AND     what = 'line';
 20
 21     SELECT SDO_GEOM.RELATE(geometry, 'determine', l_small_buffer, 0.0005) relationship
 22     INTO  l_determine
 23     FROM BUFFER_TEST
 24     WHERE what = 'line';
 25
 26     DBMS_OUTPUT.PUT_LINE('l_inside: ' || l_inside || ' relationship ' || l_determine);
 27
 28  END;
 29  /
l_inside: 0 relationship INSIDE
Any help would be much appreciated... I'm starting to pull my hair out on this.
This is on Oracle 10.2.0.3
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 16 2009
Added on Dec 19 2008
4 comments
653 views