Spatial Data Types, Indexing, Geospatial Queries, and Location Analytics

Learning outcomes

ServiceHub dispatches technicians based on “within 10 km of this incident.” Storing latitude and longitude as two numbers is easy, but subtracting degrees and treating the result as kilometers is wrong: longitude scale changes by latitude and Earth is not a flat Cartesian plane. Oracle Spatial represents geometry together with a Spatial Reference System Identifier (SRID), provides indexed spatial operators, and distinguishes planar from geodetic calculations.

1. Geometry + coordinate system gives numbers meaning

SDO_GEOMETRY stores geometry type, SRID and coordinates. For a two-dimensional point, SDO_GTYPE=2001. SRID 4326 represents WGS 84 geographic longitude/latitude. In Oracle's X/Y point representation, X is longitude and Y is latitude.

2. Build ServiceHub technician locations

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh24_tech_location PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh24_tech_location (  technician_id NUMBER PRIMARY KEY,  technician_name VARCHAR2(80) NOT NULL,  region_code VARCHAR2(8) NOT NULL,  location MDSYS.SDO_GEOMETRY NOT NULL);INSERT INTO sh24_tech_location VALUES(  101,'Nigar Aliyeva','AZ-N',  SDO_GEOMETRY(    2001,4326,    SDO_POINT_TYPE(49.8671,40.4093,NULL),    NULL,NULL  ));INSERT INTO sh24_tech_location VALUES(  102,'Kamran Hasanov','AZ-N',  SDO_GEOMETRY(    2001,4326,    SDO_POINT_TYPE(49.9500,40.4500,NULL),    NULL,NULL  ));INSERT INTO sh24_tech_location VALUES(  103,'Leyla Karimova','AZ-S',  SDO_GEOMETRY(    2001,4326,    SDO_POINT_TYPE(50.1000,40.3000,NULL),    NULL,NULL  ));COMMIT;

3. Spatial metadata makes layer dimensions/tolerance explicit

DELETE FROM user_sdo_geom_metadataWHERE table_name='SH24_TECH_LOCATION'  AND column_name='LOCATION';INSERT INTO user_sdo_geom_metadata(  table_name,column_name,diminfo,srid)VALUES(  'SH24_TECH_LOCATION',  'LOCATION',  SDO_DIM_ARRAY(    SDO_DIM_ELEMENT('LONGITUDE',-180,180,0.005),    SDO_DIM_ELEMENT('LATITUDE', -90, 90,0.005)  ),  4326);COMMIT;SELECT table_name,column_name,srid,diminfoFROM user_sdo_geom_metadataWHERE table_name='SH24_TECH_LOCATION';

26ai can automatically create metadata when a spatial index is created, but declaring it explicitly makes coordinate bounds/tolerance visible in the lab and remains useful for validation/portable operational scripts.

4. Create the spatial index

CREATE INDEX sh24_tech_location_sxON sh24_tech_location(location)INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

For geodetic data, use a geodetic-capable R-tree. Spatial operators can use the index as a primary filter and then exact geometry evaluation as required.

5. Find technicians within 10 km

SELECT  technician_id,  technician_name,  region_codeFROM sh24_tech_location tWHERE SDO_WITHIN_DISTANCE(        t.location,        SDO_GEOMETRY(          2001,4326,          SDO_POINT_TYPE(49.8671,40.4093,NULL),          NULL,NULL        ),        'distance=10 unit=KM'      )ORDER BY technician_id;

In 26ai a spatial-operator expression can be used directly as a Boolean WHERE condition; the older = 'TRUE' form remains supported. SDO_WITHIN_DISTANCE requires an appropriate spatial index on the geometry column.

6. Compute an actual geodetic distance

SELECT  a.technician_name AS technician_a,  b.technician_name AS technician_b,  SDO_GEOM.SDO_DISTANCE(    a.location,    b.location,    0.005,    'unit=KM',    'ellipsoidal=true'  ) AS distance_kmFROM sh24_tech_location aJOIN sh24_tech_location b  ON a.technician_id=101 AND b.technician_id=102;

For indexed search, prefer SDO_WITHIN_DISTANCE. SDO_GEOM.SDO_DISTANCE is useful for exact pairwise calculations/verification but is not the same indexed operator path.

7. Deliberately wrong: treat longitude/latitude deltas as kilometers

SELECT  ABS(a.location.sdo_point.x-b.location.sdo_point.x) AS delta_longitude_degrees,  ABS(a.location.sdo_point.y-b.location.sdo_point.y) AS delta_latitude_degreesFROM sh24_tech_location aJOIN sh24_tech_location b  ON a.technician_id=101 AND b.technician_id=102;

Those are angular differences, not kilometers. One degree of longitude spans different distances at different latitudes. The safe repair is to preserve the correct SRID and use geodetic-aware operators/functions with explicit units.

8. Mixed coordinate systems are a correctness failure

Spatial functions expect compatible coordinate systems. Do not insert projected-meter coordinates while labeling them SRID 4326 or combine geometries with different SRIDs and hope Oracle “knows what you meant.” Transform deliberately with documented coordinate transformations, then validate.

SELECT  technician_id,  SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(    location,    0.005  ) AS validation_resultFROM sh24_tech_locationORDER BY technician_id;-- Expected valid geometry result: TRUE.

9. Verify index/plan evidence

SELECT /*+ gather_plan_statistics */       COUNT(*)FROM sh24_tech_location tWHERE SDO_WITHIN_DISTANCE(        t.location,        SDO_GEOMETRY(          2001,4326,          SDO_POINT_TYPE(49.8671,40.4093,NULL),          NULL,NULL        ),        'distance=10 unit=KM'      );SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

On three rows the optimizer/domain-index implementation may not demonstrate a realistic production selectivity advantage. The exercise proves semantics and inspectability, not a universal spatial-index speedup.

10. Licensing and advanced boundaries

The current 26ai matrix lists Oracle Spatial and Graph and Property Graph/RDF Graph Technologies as available in Free and all listed offerings—no extra-cost Spatial and Graph option. However, parallel spatial index builds are not available in Free, and partitioned spatial indexes differ by offering; EE/EE-ES use can require Oracle Partitioning for partitioned spatial indexes. Advanced routing/geocoding/Spatial Studio/topology may add deployment/components even when the core database feature is licensed.

11. Cleanup

DROP INDEX sh24_tech_location_sx;DELETE FROM user_sdo_geom_metadataWHERE table_name='SH24_TECH_LOCATION'  AND column_name='LOCATION';COMMIT;DROP TABLE sh24_tech_location PURGE;

12. Production judgment

Use Spatial when location/topology is part of the query semantics, not merely two display coordinates. Record SRID/axis/unit/tolerance rules in the data model and API. Validate imported data, index from workload evidence, and compare database geospatial latency/throughput with an external GIS only under equivalent coordinate/precision/feature semantics.

The Free lab requires no management pack, restart or COMPATIBLE increase. Spatial index creation needs ordinary index/table privileges and MDSYS Spatial components installed with the database. No parallel index build is used. Lesson 4 now treats relationships themselves as a first-class query pattern, projecting the same relational data into a SQL property graph without creating a second database.

Authoritative references

• SDO_GEOMETRY Object Type — geometry/SRID model and 26ai metadata behavior
• Creating a Spatial Index — SPATIAL_INDEX_V2 syntax
• SDO_WITHIN_DISTANCE — indexed distance predicate
• Spatial Operators/Functions — 26ai Boolean operator semantics
• Licensing Information — Spatial/Graph availability and restrictions