Crash Magic GIS Services Requirements for Oracle Database Servers

Issue

Crash Magic GIS Services require spatial database functionality. This article describes requirements for Oracle database servers.

Explanation

For agencies using an Oracle database server, GIS-based features in Crash Magic require Oracle Spatial and Graph be available on the same database server as the crash data. Oracle Spatial and Graph is a separately-licensed option and not included in the base Oracle installation.

During Crash Magic installation, we will create a number of GIS tables that require spatial data types and indexes. This requires permission to insert geometry metadata in the ÒUSER_SDO_GEOM_METADATAÓ view. Per the Oracle Spatial and Graph DeveloperÕs Guide, the ÒUSER_SDO_GEOM_METADATA view must contain an entry with the dimensions and coordinate boundary information for the table column to be spatially indexed.Ó The table name may not be prefixed with the schema during the metadata or index
creationÑit is associated directly with the user (schema).

References:
Oracle Spatial and Graph Developers Guide https://docs.oracle.com/database/121/SPATL
Geometry Metadata Views https://docs.oracle.com/database/121/SPATL/geometry-metadata-views.htm#SPATL545
Create Index https://docs.oracle.com/database/121/SPATL/create-index.htm

Solution
There are three options for configuring the Crash Magic GIS tables during installation:
  1. (preferred) GIS tables created in same schema as the crash data. We will provide a script to create the tables, metadata, and indexes (see sample below). The tables/metadata/indexes must be created by the crash data user (schema).
  2. GIS tables created in the CmSys schema.
  3. GIS tables created in a separate schema solely for Crash Magic GIS data. This schema must be able to be joined to the crash data.
 
Sample script for creating a table with two spatial fields, inserting geometry metadata, and creating the spatial indexes:
BEGIN
execute immediate '
CREATE TABLE CRASHSCHEMA.PDGLOCATIONS(
Id NUMBER(10) NOT NULL,
CandId varchar2(40) NULL,
XY ST_GEOMETRY NULL,
LatLong ST_GEOMETRY NULL,
PRIMARY KEY  (Id )
)';

execute immediate '
INSERT INTO USER_SDO_GEOM_METADATA
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  ''PDGLOCATIONS'',
  ''XY'',
  SDO_DIM_ARRAY( 
    SDO_DIM_ELEMENT(''X'', 5979001.2708, 7122754.0974, 0.001),
    SDO_DIM_ELEMENT(''Y'', 1770238.7032, 2341806.4367, 0.001)
     ),
  2230 )
  ';

execute immediate 'CREATE INDEX IX_PDGLOCATIONS_XY ON PDGLOCATIONS(XY) INDEXTYPE IS MDSYS.SPATIAL_INDEX';

execute immediate '
INSERT INTO USER_SDO_GEOM_METADATA
    (TABLE_NAME,
     COLUMN_NAME,
     DIMINFO,
     SRID)
  VALUES (
  ''PDGLOCATIONS'',
  ''LATLONG'',
  SDO_DIM_ARRAY( 
    SDO_DIM_ELEMENT(''LONGITUDE'', -180, 180, 0.005),
    SDO_DIM_ELEMENT(''LATITUDE'', -90, 90, 0.005)
     ),
  4326 )
  ';

execute immediate 'CREATE INDEX IX_PDGLOCATIONS_LATLONG ON PDGLOCATIONS(LATLONG) INDEXTYPE IS MDSYS.SPATIAL_INDEX';

END;
Was this article helpful?
0 out Of 5 Stars
5 Stars 0%
4 Stars 0%
3 Stars 0%
2 Stars 0%
1 Stars 0%
5
How can we improve this article?
How Can We Improve This Article?