0

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;
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.

  • 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 Dec 24 '14 at 2:20
  • 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 Dec 24 '14 at 2:33
  • 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. – akthor Dec 24 '14 at 2:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.