Hello Oracle Spatial Experts,
We are designing a data model for a spatial data repository and need to store multiple geometry types in Oracle Database 19c (or higher) with the Spatial option. Our primary challenge is choosing between a single-table and a multi-table design to achieve the best performance for complex spatial queries.
1. Data Characteristics:
- Volume: Large (millions of records).
- Geometry Types & Requirements:
- 2D Polygons (SRID: 4326): Require a 2D Spatial Index for fast queries (SDO_RELATE, SDO_WITHIN_DISTANCE).
- 2D Polygons (SRID: UTM - with curve support): No spatial index required for this specific type, but curve support is essential.
- 3D Polygons (SRID: 4326): Require a 3D Spatial Index for 3D analyses.
- Solid Geometries (SRID: 4326): Require a 3D Spatial Index.
- Non-Spatial Data: Includes numeric attributes, IDs, and descriptive text.
Option A: Single Table
CREATE TABLE SPATIAL_SOURCES (
id NUMBER PRIMARY KEY,
feature_name VARCHAR2(255),
attributes CLOB, -- or separate columns
geom_type VARCHAR2(50), -- To distinguish geometry type, e.g., '2D_POLYGON', '3D_SOLID'
geom_2d SDO_GEOMETRY, -- For 2D WGS84
geom_utm SDO_GEOMETRY, -- For UTM with curves
geom_3d SDO_GEOMETRY, -- For 3D and Solid geometries
-- ... Separate Spatial Indexes on geom_2d and geom_3d
);
Option B: Multiple Tables (Partitioned by Geometry Type)
-- Table for 2D indexed geometries
CREATE TABLE SPATIAL_SOURCES_2D (...);
-- Table for UTM curves (no index)**
CREATE TABLE SPATIAL_SOURCES_UTM (...);
-- Table for 3D & Solid geometries
CREATE TABLE SPATIAL_SOURCES_3D (...);
Which design (Option A or Option B) is generally preferred for high-performance spatial queries and analysis on large datasets with mixed geometry types?
Any insights, performance benchmarks, or references to official best practices for such a scenario would be immensely helpful.
Thank you for your time and expertise.