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

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial - 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_ilm_location table.

insert into current_ilm_location
  (seq_id,
  pvt_type,
  latitude,
  longitude,
  location_date,
  location_timezone,
  serial_no,
  wfa_tech_ec,
  wfa_dispatch_center,
  wfa_supervisor_group_id,
  last_activity,
  landmark_id,
  landmark_type_sc)
  values
  ('91111111',
  'IP CHANGE',
  '44.89520417',
  '-93.07360306',
  sysdate,
  -6,
  'AD009999',
  '507',
  'MPLSMNPTA03',
  'MDE',
  sysdate,
  '929',
  '929');

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_ilm_location
where seq_id='91111111'
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_ilm_location
where seq_id='91111111'
and trim(sdo_relate(spatial_point,gps.transform_rectangle('44.465527','-92.595369','44.990445','-93.410661'), 'mask=anyinteract querytype=window')) = 'TRUE'

returns 1

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms