My Oracle Support Banner

Sdo_Relate Function Does Not Work Without Adding Trim(Sdo_Relate) (Doc ID 1542323.1)

Last updated on FEBRUARY 26, 2019

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

SDO_RELATE FUNCTION DOES NOT WORK WITHOUT ADDING TRIM(SDO_RELATE)

CREATE OR REPLACE function GPS.transform_long_lat(longitude in number,
  latitude in number)
return MDSYS.SDO_GEOMETRY deterministic is
begin
  return mdsys.sdo_geometry(2001, 8307,
  mdsys.sdo_point_type(longitude, latitude, NULL),NULL, NULL);
end;
/

Now, Follow below steps to reproduce..
Step 1:
A simple insert in gps.current_location table.

insert into current_location
  (<colums>)
  values
  (<values>);

CREATE OR REPLACE function GPS.transform_rectangle (nw_latitude in number,
  nw_longitude in number,
  se_latitude in number,
  se_longitude in number)
return MDSYS.SDO_GEOMETRY deterministic is
junk MDSYS.SDO_GEOMETRY;
begin
junk := mdsys.sdo_geometry(
  2003, -- 2-dimensional polygon
  8307, -- coordinate system
  null,
  MDSYS.SDO_ELEM_INFO_ARRAY(1, 2003, 1), -- a simple polygon with four points
-- 1003 = ext and numbers below mark
-- counterclockwise vertices
-- 2003 = int and numbers below mark
-- clockwise vertices
  MDSYS.SDO_ORDINATE_ARRAY(nw_longitude,nw_latitude, se_longitude,nw_latitude,
se_longitude,se_latitude, nw_longitude,se_latitude,
nw_longitude,nw_latitude));

return;
end;
/


Step 2:
Selecting on this new inserted row.
select count(1) from gps.current_location
where seq_id='<value>'
and sdo_relate(spatial_point,gps.transform_rectangle('44.465527','-92.595369','44.990445','-93.410661'), 'mask=anyinteract querytype=window') = 'TRUE'

returns 0

Step 3:
Select using trim()
select count(1) from gps.current_location
where seq_id='<value>'
and trim(sdo_relate(spatial_point,gps.transform_rectangle('44.465527','-92.595369','44.990445','-93.410661'), 'mask=anyinteract querytype=window')) = 'TRUE'

returns 1

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.