1

I believe I am getting some erroneous results when performing SDO_GEOM.RELATE with my spatial query.

Given the following query:

select SDO_GEOM.RELATE(
         MDSYS.SDO_GEOMETRY(2003, 4326, NULL,
                            MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                            MDSYS.SDO_ORDINATE_ARRAY(-90, 40.04443758460857,
                                                     -82.6171875, 40.04443758460857,
                                                     -82.6171875, 43.45291889355465,
                                                     -90, 43.45291889355465,
                                                     -90, 40.04443758460857)),
         'determine',
         MDSYS.SDO_GEOMETRY(2003, 4326, NULL,
                            MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),
                            MDSYS.SDO_ORDINATE_ARRAY(-114, -62,
                                                     61.5, -62,
                                                     61.5, 33.5,
                                                     -114, 33.5,
                                                     -114, -62)),
         0.00001) as relationship
from dual;

I get the following relationship value:

INSIDE

The first polygon covers part of Illinois to Michigan area. The second polygon goes from the South East Pacific Ocean (SW Corner) to Southwest United States (NW Corner) to South West Indian Ocean (SE Corner) to middle of Iran (NE Corner), so these geometries should be DISJOINT as shown on the picture.
enter image description here

I hope that I am just missing something completely obvious. Does anyone have any ideas as to what I'm missing here?

  • Strange result, couldn't find the reason. When comparing the MBRs of your geometries (built with SDO_GEOM.SDO_MBR) and not the geometries itself Oracle recognizes the correct relationship. – Mesa Feb 1 at 22:57
  • 3
    The geometries are in WGS84 geodetic coordinates. In this environment, the computations are done on the ellipsoidal representation of the earth. This implies that the top and bottom "horizontal" lines in your MBR are great circles, and that in your case, the shape formed with those great circles includes your small polygon in Illinois. The graphic representation you show is on a world mercator projection. It is misleading because it represents the "horizontal" lines following parallels, so making it look as if the Illinois polygon was outside. – Albert Godfrind Feb 2 at 8:50
  • If you densify the big polygon to have vertices for example at every 1 degree you should get a result that you expect. – user30184 Feb 3 at 20:46
  • @Mesa: MBRs return the results the OP expects because they are defined as rectangles (types 1003, with just two corners). In MBRs, the "horizontal" lines are interpreted as parallels instead of great circles. – Albert Godfrind Feb 12 at 18:30
  • At the end, everything depends on what the correct answer is. If one considers that a line formed by two points defines the shortest path between those two points, then that path is the one that follows a great circle. In the OP's example the top horizontal line starts somewhere in western Canada and ends somewhere in Siberia. A flight between those two points will pass very close to the north pole, and pass well north of the small rectangle over Illinois. – Albert Godfrind Feb 12 at 18:32
1

The geometries are in WGS84 geodetic coordinates.

In this environment, the computations are done on the ellipsoidal representation of the earth. This implies that the top and bottom "horizontal" lines in your MBR are great circles. And in your example, the shape formed with those great circles includes your small polygon in Illinois.

The graphic representation you show is on a world mercator projection. It is misleading because it represents the "horizontal" lines following parallels, so making it look as if the Illinois polygon was outside, whereas in reality it is inside.

As suggested you can try densifying the large rectangle. That will however NOT change the result. But it would be useful: use the densified shape to display the rectangle on the map. The horizontal lines will then be rendered as curves (approximating great circles) and you will then see that the large rectangle does indeed contain the smaller one.

To densify the shape:

sdo_util.densify_geometry (
  SDO_GEOMETRY(
    2003, 4326, NULL,
    SDO_ELEM_INFO_ARRAY(1, 1003, 1),
    SDO_ORDINATE_ARRAY(
      -114, -62,
      61.5, -62,
      61.5, 33.5,
      -114, 33.5,
      -114, -62
    )
  )
)

This will return the same shape with vertices added at regular intervals (every 5km by default).

  • I created a new sdo_geometry with SRID of 3857 and rebuilt my geometries for this different projection. I'm now getting good results, with the exception of those geometries crossing the antimeridian, but that I will post as a different question. I didn't densify the geometries, but I do like your suggestion, so I'll mark this as the answer. – js1983 Feb 7 at 16:15
  • Just to be clear: densifying the rectangle will NOT change the results. The only benefit is that when you display the densified shape, it will show that the horizontal lines are great circles. – Albert Godfrind Feb 9 at 8:53
  • 3857 is a global Mercator projection promoted by Google (but incorrect because it is based on a spherical representation, not ellipsoïdal as it should be). But either way those global projections are unable to handle anything crossing the terminator. – Albert Godfrind Feb 9 at 8:57
  • Like I said in another comment, it all depends on what a good result is. If one considers that a line formed by two points defines the shortest path between two points, then that path on a sphere is the one that follows a great circle. In your example the top horizontal line starts somewhere in western Canada and ends somewhere in Siberia. A flight between those two points will pass very close to the north pole, and pass well north of the small rectangle over Illinois. – Albert Godfrind Feb 12 at 18:41
  • On the other hand if you consider that all the shapes are on a flat projection (like the global pseudo-mercator), then the shortest path follows a parallel and passes south of the small rectangles over Illinois. That however is obviously not the shortest path if drawn on a sphere. – Albert Godfrind Feb 12 at 18:43

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.