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
- (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).
- GIS tables created in the CmSys schema.
- 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.
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;
0 out Of 5 Stars
| 5 Stars | 0% | |
| 4 Stars | 0% | |
| 3 Stars | 0% | |
| 2 Stars | 0% | |
| 1 Stars | 0% |