Spatial Data in Oracle AI Database

Spatial data describes the shape, location, and relationships of objects or points – whether virtual or in the real world. It powers everything from mapping and logistics to proximity search and location-aware applications.

In Oracle AI Database, spatial is a first-class data type. You store it, index it, query it, and join it alongside your relational, JSON, and vector data. The database gives you geometry types, spatial operators, and optimized indexes so you can work with locations at scale.

Spatial can look intimidating, but it unlocks powerful capabilities. This article kicks off a series that will help you understand spatial concepts and start building applications that make real use of location intelligence.

Prefer code samples? Try my sample JDBC application using Oracle Spatial

Now, let’s explore spatial data in Oracle AI Database.

Object-Relational Model and Data Types

Oracle AI Database uses the Object-Relational Model to store spatial data. The Object-Relational Model and Oracle AI Database spatial types allow for a variety of geometric representations and operations, including:

Geometric indexes and index maintenance for optimal performance
Arcs, circles, and polygons modeled in a single geometry column
Spatial SQL queries and functions

To store a geometry (location, shape, etc.), use the MDSYS.SDO_GEOMETRY spatial data type. Oracle AI Database tables may have one or more SDO_GEOMETRY columns, co-located with any other database types.

Let’s take a look at the SDO_GEOMETRY type, an abstract data type:

-- Note that you do not need to create these types manually,
-- as they are provided in the MDSYS schema.
CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, -- The type of geometry object
 SDO_SRID NUMBER,  -- coordinate system reference number, like WGS84
 SDO_POINT SDO_POINT_TYPE, -- (x, y, [z]) point for location/point data
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, -- ordinate array metadata
 SDO_ORDINATES SDO_ORDINATE_ARRAY); -- ordinate array for shape representation
);

-- nested types
CREATE TYPE sdo_point_type AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

-- Note that you do not need to create these types manually,
-- as they are provided in the MDSYS schema.
CREATE TYPE sdo_geometry AS OBJECT (
 SDO_GTYPE NUMBER, -- The type of geometry object
 SDO_SRID NUMBER,  -- coordinate system reference number, like WGS84
 SDO_POINT SDO_POINT_TYPE, -- (x, y, [z]) point for location/point data
 SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, -- ordinate array metadata
 SDO_ORDINATES SDO_ORDINATE_ARRAY); -- ordinate array for shape representation
);

-- nested types
CREATE TYPE sdo_point_type AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

Oracle AI Database provides additional SDO_* metadata structures for various spatial use cases. We’ll cover these data types in detail with future examples/posts.

Loading Spatial Data

Creating a spatial table is simple. Just add the SDO_GEOMETRY data type as a column:

CREATE TABLE my_locations (
  id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  VARCHAR2(32),
  shape SDO_GEOMETRY
);

CREATE TABLE my_locations (
  id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  VARCHAR2(32),
  shape SDO_GEOMETRY
);

Example: insert a rectangular polygon

This query inserts a rectangular polygon, defined by only two points with SDO_ORDINATE_ARRAY: the lower-left corner (1,1) and the upper-right corner (5,7). The database expands the full polygon definition internally.

SDO_ELEM_INFO_ARRAY specifies where the shape begins, what sort of shape it is, and how the coordinates should be interpreted:

INSERT INTO my_locations (name, shape) VALUES(
  'simple rectangle',
  SDO_GEOMETRY(
    SDO_POLYGON2D,  -- Geometry type 2003
    NULL,
    NULL,
    -- 1 -> start offset of ordinates
    -- 1003 -> rectangle
    -- 3 -> interpret shape from two corners
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 5,7)
  )
);

INSERT INTO my_locations (name, shape) VALUES(
  'simple rectangle',
  SDO_GEOMETRY(
    SDO_POLYGON2D,  -- Geometry type 2003
    NULL,
    NULL,
    -- 1 -> start offset of ordinates
    -- 1003 -> rectangle
    -- 3 -> interpret shape from two corners
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(1,1, 5,7)
  )
);

Example: insert geographic locations

Let’s take a look at another example, this time using SDO_POINT_TYPE to represent a physical location with latitude and longitude.

We identify the geometry as a 2-dimensional point, and use SRID 4326 to identify the WGS84 lat/long coordinate system (a standard for geographic coordinates):

INSERT INTO my_locations (name, shape) VALUES (
  'coffee shop: front door',
  SDO_GEOMETRY(
    2001,           -- 2D point
    4326,           -- SRID for WGS84 lat/long
    SDO_POINT_TYPE(-122.4194, 37.7749, NULL), -- (lon, lat)
    NULL,
    NULL
  )
);

INSERT INTO my_locations (name, shape) VALUES (
  'coffee shop: front door',
  SDO_GEOMETRY(
    2001,           -- 2D point
    4326,           -- SRID for WGS84 lat/long
    SDO_POINT_TYPE(-122.4194, 37.7749, NULL), -- (lon, lat)
    NULL,
    NULL
  )
);

Querying Geometric Data

Oracle AI Database includes a variety of tools for using and querying spatial data – spatial operators and filters. Use these tools to perform operations like calculating the intersection of two shapes, finding the distance between two points, calculating topological intersections, and more.

Example: calculate the area of a geometry

In this simple example, we use SDO_GEOM.SDO_AREA to calculate the area of a shape:

-- 0.005 is the distance tolerance, indicating the level of precision.
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM my_locations
where name = 'simple rectangle';

-- 0.005 is the distance tolerance, indicating the level of precision.
SELECT name, SDO_GEOM.SDO_AREA(shape, 0.005) FROM my_locations
where name = 'simple rectangle';

Example: Find the distance between two points

Here, we calculate the distance between two points – a common operation for physical locations:

-- Point A at (1, 1)
INSERT INTO my_locations (name, shape)
VALUES (
  'Point A',
  SDO_GEOMETRY(
    2001,         -- 2D point
    NULL,         -- SRID (null or set your SRID)
    SDO_POINT_TYPE(1, 1, NULL),
    NULL,
    NULL
  )
);

-- Point B at (5, 4)
INSERT INTO my_locations (name, shape)
VALUES (
  'Point B',
  SDO_GEOMETRY(
    2001,
    NULL,
    SDO_POINT_TYPE(5, 4, NULL),
    NULL,
    NULL
  )
);


-- now that the table contains two points, 
-- we can query the distance between them
SELECT SDO_GEOM.SDO_DISTANCE(l_a.shape, l_b.shape, 0.005)
FROM my_locations l_a, my_locations l_b
WHERE l_a.name = 'Point A' AND l_b.name = 'Point B';

-- Point A at (1, 1)
INSERT INTO my_locations (name, shape)
VALUES (
  'Point A',
  SDO_GEOMETRY(
    2001,         -- 2D point
    NULL,         -- SRID (null or set your SRID)
    SDO_POINT_TYPE(1, 1, NULL),
    NULL,
    NULL
  )
);

-- Point B at (5, 4)
INSERT INTO my_locations (name, shape)
VALUES (
  'Point B',
  SDO_GEOMETRY(
    2001,
    NULL,
    SDO_POINT_TYPE(5, 4, NULL),
    NULL,
    NULL
  )
);


-- now that the table contains two points, 
-- we can query the distance between them
SELECT SDO_GEOM.SDO_DISTANCE(l_a.shape, l_b.shape, 0.005)
FROM my_locations l_a, my_locations l_b
WHERE l_a.name = 'Point A' AND l_b.name = 'Point B';

Example: Find geometries close to a given shape

In this example, we search for geometries within a specified distance of a given shape – useful if you want to find objects close to a specific object:

SELECT l.name FROM my_locations l WHERE SDO_WITHIN_DISTANCE(
  l.shape,
  -- input shape to find nearby geometries
  SDO_GEOMETRY(
    SDO_POLYGON2D, 
    NULL, 
    NULL, 
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,6, 8,8)
  ),
  'distance=10' -- distance filter
) AND l.shape.SDO_GTYPE = SDO_POLYGON2D; -- only scan polygons

SELECT l.name FROM my_locations l WHERE SDO_WITHIN_DISTANCE(
  l.shape,
  -- input shape to find nearby geometries
  SDO_GEOMETRY(
    SDO_POLYGON2D, 
    NULL, 
    NULL, 
    SDO_ELEM_INFO_ARRAY(1,1003,3),
    SDO_ORDINATE_ARRAY(4,6, 8,8)
  ),
  'distance=10' -- distance filter
) AND l.shape.SDO_GTYPE = SDO_POLYGON2D; -- only scan polygons

Oracle AI Database includes a variety of spatial operators like those used in the examples. You can write or customize your own spatial operators as needed using SDO_GEOMETRY and other types.

Spatial Indexing

Let’s create a spatial index on our shape SDO_GEOMETRY column. This SQL statement creates a R-tree index which can index up to 4 dimensions:

CREATE INDEX my_locations_idx
   ON my_locations(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

CREATE INDEX my_locations_idx
   ON my_locations(shape)
   INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

Spatial indexes are similar to any other database index, providing performance improvements to searches. These indexes facilitate different kinds of queries, like:

Run a window query to find objects around a point or in an area.
Join objects from different locations that interact spatially.

So far, you should have a basic understanding of spatial data in Oracle AI Database – but we’ve just scratched the surface.

References

Response

Get Started with Spatial Data in Oracle AI Database Using JDBC – andersswanson.dev

April 6, 2026

[…] Spatial data is a database feature that sounds niche right up until you need it. When your application must answer questions like “what landmarks fall inside this map window?”, “what is the exact distance between these two places?”, or “what objects are near this location?”, you’re working with spatial data. […]

Loading…

andersswanson.dev