Products
  
  
  Crash Magic Online
  
  Intersection Magic
  
  Map Magic
  
  Knowledge Base
  
  
  
  Contact Us
  
  Meeting
  
  Shop
  
  Login
  

 

Knowledge Base Article:CMO317 


Registration keys moved to client area: Select login on the left menu bar.

Knowldegebase:
Search home page  

Crash Magic GIS Services Requirements for Oracle Database Servers

Article created: May 03 2019, updated: May 08 2019

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

Status:  Info - "how to" article
Keywords:  
Categories:   *Installation and setup*

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;

October 23, 2019 4:54AM

© 1999-2019 Pd' Programming, Inc - Lafayette, CO USA