Oracle Spatial Returns Different Intersection Results with SRID Defined

Question

I have a set of points and a set of polygons of which I am performing an intersection on to determine which polygon each point lies in. This was performed previously without the projection being defined in either the metadata table or within the geometry features themselves.

After defining the projections, different results are being returned than previously. Oddly enough, when displayed in SQLDeveloper and ArcGIS it is clear that the previous results were correct. When I drop the projection definition, I get the correct answer. I've tried with different projection definitions, all data is in the NAD 1983 geodetic coordinate system.

Oracle seems to be doing something more than checking that the projection definitions match. Anyone know what it could be doing?

Intersection was performed using: sdo_geom.relate, SDO_TOUCH, etc. all returning the same result. Additionally, When I measure distance, the distances are reversed giving a distance of zero to the polygon it is not in.

I have tried: - rebuilding the indexes - Checking geometry for validity - Changing the projection definitions

Below is the situation, given the point with a projection definition I get a value of 03 and 02 without. Obviously 02 is correct and 03 is not.

Visual Description from SQLDeveloper

I am under the impression that no transformations are being performed during this operation and the values are natively in the same coordinate reference system.

Ideas?

Here is an example with no relation found:

SELECT sdo_geom.relate(
 t1.shape
 , 'determine',
 mdsys.SDO_GEOMETRY(
 2001, 
 4269, 
 mdsys.SDO_point_type(-163.14667,56.17,NULL), 
 NULL,
 NULL )
 ,0.005
 )
 FROM 
(
select 
 MDSYS.SDO_GEOMETRY(
 2003
 ,4269
 ,NULL
 ,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 MDSYS.SDO_ORDINATE_ARRAY(-162,56.1666999980807,-162,57,-164,57,-164,56.1666999980807,-162,56.1666999980807)
 ) shape
from dual) t1;

Same thing with SRID defined and relation found:

SELECT sdo_geom.relate(
 t1.shape
 , 'determine',
 mdsys.SDO_GEOMETRY(
 2001, -- point data
 null, 
 mdsys.SDO_point_type(-163.14667,56.17,NULL), 
 NULL,
 NULL )
 ,0.005
 )
 FROM 
(
select 
 MDSYS.SDO_GEOMETRY(
 2003
 ,null
 ,NULL
 ,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),
 MDSYS.SDO_ORDINATE_ARRAY(-162,56.1666999980807,-162,57,-164,57,-164,56.1666999980807,-162,56.1666999980807)
 ) shape
from dual) t1;

Answer 1

The difference is probably because of the difference between geodetic vs cartesian computations.

When no SRID is specified in your geometries, the computation are plain cartesian.

When you (correctly) specify that the geometries are in a geodetic coordinate system, then all lines in your shapes are great circles. In particular what looks like a simple "horizontal" line on your picture is actually going further north: it does not correspond to a parallel, but to a great circle. If your point is close to that line, then you get different results.

• akthor: I considered that, but if the point lat/long fit within the min and max of the bounding "rectangle" it shouldn't matter if it is cartesian or spherical coordinates right? For example if this is the polygon: -164,57 -162,57 -164,56.1666999980807 -162,56.1666999980807 and this is the point: -163.14667,56.17 it has to be within the bounds of the polygon.
• akthor: If what you are saying is correct, that the boundary created is the great circle between two points, how would one find the locations within bounds of four coordinates, densify the lines? Go back to cartesian coordinates?
• akthor: This makes sense if you consider that boundary created is shortest path between two points. In a cartesian system that is a straight line, in a spherical system it's the great circle route.