1.4 Spatial Studio Best Practices

This section describes the best practice recommendations when working with Spatial Studio.

Standardize Longitude/Latitude Data on SRID 4326

In Oracle Database, spatial data includes a SRID (spatial reference identifier), which uniquely identifies the coordinate system associated with the data. If you want to perform any spatial analysis operations involving multiple data sets, it is recommended that the data sets have the same SRID.

Many SRIDs can be used for longitude/latitude data, but probably the most widely used is 4326. Oracle recommends that you use SRID 4326 for longitude/latitude geometries unless there is information to indicate otherwise. Also, if you have Oracle tables with longitude/latitude geometries and if the SRID is not consistent across the tables, you are advised to standardize on SRID 4326.

(For detailed information about SRIDs and coordinate system support in Oracle Spatial, see the Coordinate Systems (Spatial Reference Systems) chapter in the Spatial manual.)

The following example transforms geometries that do not use SRID 4326 so that they use SRID 4326. You would need to do these actions for each existing table that does not use SRID 4326. The example assumes a geometry table named TABLE_1 with a geometry column named GEOM. It also drops the existing spatial index named table_1_idx, and later re-creates it.

-- Drop the existing spatial index.
DROP INDEX table_1_idx FORCE;

-- Update the spatial metadata.
INSERT INTO user_sdo_geom_metadata
  VALUES (
  'TABLE_1',  -- table name
  'GEOM',     -- geometry column name
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude', -180, 180, 0.05),  -- 0.05 meter tolerance
    SDO_DIM_ELEMENT('Latitude', -90, 90, 0.05)  -- 0.05 meter tolerance
     ),
  4326   -- SRID for 'Longitude / Latitude (WGS 84)' coordinate system
);

-- Update (transform) geometry data to use SRID 4326.
update table_1 set geom = sdo_cs.transform(geom, 4326);

-- Re-create the spatial index.
CREATE INDEX table_1_idx
ON table_1(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

Note:

In the CREATE INDEX statement, SPATIAL_INDEX_V2 is recommended if the Oracle Database is Release 18 or later. If it is earlier than Release 18, specify just SPATIAL_INDEX.