by Jeff Stonacek, Principal Architect

Oracle Spatial and Graph is a product that allows for the storage and processing of geospatial objects inside of the database. From the Oracle Technology Global Pricing List, “Oracle Spatial is an integrated set of functions and procedures that enables spatial data to be stored, accessed, and analyzed quickly and efficiently in an Oracle database.”

Oracle Spatial and Graph is a separately licensed feature, similar to Partitioning or Advanced Compression. From the Oracle Technology Price List:

The Spatial and Graph option lists for $17,500 per processor license and $350 per Named User Plus (NUP) license. Prices may change, so review the online price list from Oracle for accuracy.

Spatial vs. Locator

The Oracle Standard Edition (SE) database comes with a set of geospatial features called Locator. From the Oracle 12c Spatial and Graph Developer’s Guide, “Oracle Locator (also referred to as Locator) is a feature of Oracle Database Standard Edition. Locator provides core features and services available in Oracle Spatial and Graph. It provides significant capabilities typically required to support Internet and wireless service-based applications and partner-based GIS solutions.”

The following is a list of database code that demonstrates using Locator with an Oracle database.

Adding the column to the database table:

EXECUTE IMMEDIATE 'ALTER TABLE "TEST_LOC" ADD "LOC" "MDSYS"."SDO_GEOMETRY"';

Adding the column to the geometry metadata so that locator knows it exists:

Select count(*) into matching_row from USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_LOC' and COLUMN_NAME = 'LOC';

if (matching_row = 0)
then
EXECUTE IMMEDIATE 'INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES(''TEST_LOC'', ''LOC'', SDO_DIM_ARRAY(SDO_DIM_ELEMENT(''LONG'', -180, 190, .5), SDO_DIM_ELEMENT(''LAT'', -90, 90, .5)), 8307)';

end if;

Adding an index for the new column:

EXECUTE IMMEDIATE 'CREATE INDEX "TEST_LOC_IDX" ON "TEST_LOC" ("LOC")

INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" PARAMETERS (''layer_gtype=POINT'')';

The Oracle Spatial Developer’s Guide for Oracle database 11.2.0, lists what features require licensing of the Spatial and Graph add-on option. Appendix B of the Oracle Spatial Developer’s Guide lists the features that must be licensed with Spatial and Graph.

Appendix B in the Oracle 12c guide, also lists the features that are included with Locator versus the paid features with Spatial.

False Positive

The following query is an example of how to identify geospatial objects stored in an Oracle database.

select * from mdsys.sdo_geom_metadata_table where sdo_owner not in ('MDSYS','OE');

The problem is that this query identifies all geospatial objects for both Locator and Spatial and Graph. This has the potential to flag false positives for licensed features if you are using only Locator, and not Spatial and Graph. If this happens during an audit, you can explain that only Locator is in use and therefore Spatial and Graph does not need to be licensed.

Locator Installation

There is a way to install only Locator in an Oracle EE or SE database while leaving Spatial and Graph uninstalled. This is the recommended configuration if Spatial and Graph is not licensed, and will eliminate the possibility of running unlicensed features.

Install from Scratch

The cleanest way to run Locator without Spatial and Graph is to install Locator manually. To do this, uncheck Spatial from the list of options when creating a new database. Then run the following steps to install locator without Spatial and Graph.

Install JVM (if not installed):

@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql

Install Multimedia (if not installed):

@?/ord/admin/ordinst.sql SYSAUX SYSAUX
@?/ord/im/admin/catim.sql

Install Locator:

@?/md/admin/mdprivs.sql
alter user mdsys account unlock;
alter user mdsys identified by <pw>;
connect mdsys/<pw>
@?/md/admin/catmdloc
connect / as sysdba
alter user mdsys account lock password expire;

Remove Spatial and Graph

Spatial and Graph can also be removed from an existing Oracle EE database where it has been installed. This process leaves Locator functions in place, but removes the Spatial and Graph features and can be accomplished with the following command.

@?/md/admin/mddins.sql

This command uninstalls the Spatial and Graph functions. Querying DBA_REGISTRY after running this script shows that Spatial has been removed.

col comp_name format a40
select comp_name, status from dba_registry;

COMP_NAME                                STATUS
---------------------------------------- -----------
Spatial                                  REMOVED
Oracle Multimedia                        VALID
Oracle Expression Filter                 VALID
Oracle XML Database                      VALID
Oracle Workspace Manager                 VALID
Oracle Database Catalog Views            VALID
Oracle Database Packages and Types       VALID
JServer JAVA Virtual Machine             VALID
Oracle XDK                               VALID
Oracle Database Java Packages            VALID

Conclusion

In this blog we have tried to make sense of the difference between Locator, the free add-on to Oracle Database Enterprise and Standard Editions, and Spatial and Graph, which is a paid feature of Enterprise Edition only. This can cause confusion during an audit, so work with your application and development teams to know exactly what features are being used.