Querying intersecting areas areas - Oracle Locator or Spatial?

Question

My spatial database is Oracle. We are wondering if we can use basic Oracle Locator or need to get the more expensive, full, Oracle Spatial.

My application's requirements include having to be able to query "which geometries in the spatial db intersect this geometry X".

The whole locator / spatial issue is a bit confusing since it looks like Oracle initially installs all Oracle Spatial features even if you are just using basic Oracle db and do not have license for full Oracle Spatial. And you may end up using the Spatial features by mistake: http://www.spatialdbadvisor.com/oracle_spatial_tips_tricks/55/oracle-locator-vs-enterprise-licensing-the-sdogeom-package

What I understand of Oracle documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_locator.htm#SPATL340 is that geometry function SDO_GEOM.SDO_INTERSECTION is not supported by Oracle Locator.

So does this mean that we really need full Oracle Spatial license to fulfill the requirements? Or is there some other way then "SDO_GEOM.SDO_INTERSECTION" of finding about whether areas intersect?

We are using GeoServer on top of Oracle (and accessing the geometries with WFS-T). Does this change anything, will the intersection be somehow calculated in GeoServer (doubt it?) or will the intesection-calculation have to be done in Oracle (probably?)?

Answer 1

The Oracle Spatial 10gR1 user guide lists supported Locator functionality here. This page links to another page that describes functions supported by Locator.

This list includes SDO_RELATE, which is another approach to checking for geometry intersections.

e.g. (from the SDO_RELATE section)

  SELECT c.mkt_id, c.name
  FROM cola_markets c
  WHERE SDO_RELATE(c.shape,
    SDO_GEOMETRY(2003, NULL, NULL,
      SDO_ELEM_INFO_ARRAY(1,1003,3),
      SDO_ORDINATE_ARRAY(4,6, 8,8)),
    'mask=anyinteract') = 'TRUE';

The intersection query will be handled by Oracle, not GeoServer. I once had to trace a GeoServer WMS query in Oracle and I'm pretty sure it used the SDO_RELATE approach, but you might want to verify this using your own trace, scanning through the GeoServer source, or posting a question on the GeoServer users mailing list

Answer 2

There is the big difference between SDO_GEOM.RELATE and SDO_RELATE.

The first argument of SDO_RELATE specifies a geometry column in a table. So, at least one geometry object must be located in a table. It is the limitation. On the other hand SDO_RELATE can use the spatial index, so it works fast.

SDO_GEOM.RELATE doesn’t care about the geometry location. Imagine, you created an application that don’t use Oracle table at all but uses another geometry sources, like user UI input or geometry created by application itself. Then you can use SDO_GEOM.RELATE directly, you don’t need to save the geometry in a temporally table and then run SDO_RELATE.