Spatially Enabling a Table

If you have a regular Oracle table without an SDO_GEOMETRY column, but containing location-related information (such as latitude/longitude values for points), you can spatially enable the table by adding an SDO_GEOMETRY column and using existing (and future) location-related information in records to populate the SDO_GEOMETRY column values.

The following are the basic steps for spatially enabling a regular table. They assume that the regular table has columns that contain location-related values associated with each record in the table.

  1. Alter the table to add a geometry (SDO_GEOMETRY) column.

  2. Update the table to populate the SDO_GEOMETRY objects using existing location-related data values.

  3. Optionally, update the spatial metadata (USER_SDO_GEOM_METADATA).

    By default, Oracle Spatial will automatically create the metadata in the USER_SDO_GEOM_METADATA view when creating the spatial index, using a default tolerance value of 0.05. You must only ensure that the table is populated with atleast one non-NULL geometry row for Oracle Spatial to create the required metadata.

    Run this step only if you prefer to use a different tolerance value (other than the default 0.05).

  4. Create the spatial index on the table.

Example: Spatially Enabling a Table

-- Original table without a spatial geometry column.
CREATE TABLE city_points (
  city_id NUMBER PRIMARY KEY,
  city_name VARCHAR2(25),
  latitude NUMBER,
  longitude NUMBER);

-- Original data for the table.
-- (The sample coordinates are for a random point in or near the city.)
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (1, 'Boston', 42.207905, -71.015625);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (2, 'Raleigh', 35.634679, -78.618164);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (3, 'San Francisco', 37.661791, -122.453613);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (4, 'Memphis', 35.097140, -90.065918);

-- Add a spatial geometry column.
ALTER TABLE city_points ADD (shape SDO_GEOMETRY);

-- Update the table to populate geometry objects using existing
-- latutide and longitude coordinates.
UPDATE city_points SET shape =
  SDO_GEOMETRY(LONGITUDE, LATITUDE);

-- Optional Step: Update the USER_SDO_GEOM_METADATA view.
–- By default, Oracle Spatial will automatically create the metadata in
-- the USER_SDO_GEOM_METADATA view using a default tolerance value of 0.05.
–- Run this step only if you prefer a different tolerance value.
–- The following example uses a tolerance value of 0.5.
INSERT INTO user_sdo_geom_metadata VALUES (
  'city_points',
  'SHAPE',
  SDO_DIM_ARRAY(
    SDO_DIM_ELEMENT('Longitude',-180,180,0.5),
    SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
  ),
  4326
);

-- Create the spatial index.
CREATE INDEX city_points_spatial_idx on city_points(SHAPE)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;

-- Later, add new records to the table, using original INSERT format
-- (latitude and longitude, no spatial geometry object data).
-- Then update to include spatial geometry object information.
-- Tip: For efficiency, keep track of existing and new records, and use
-- a WHERE clause to restrict the UPDATE to new records (not shown here).

INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (5, 'Chicago', 41.848832, -87.648926);
INSERT INTO city_points (city_id, city_name, latitude, longitude)
  VALUES (6, 'Miami', 25.755043, -80.200195);

UPDATE city_points SET shape =
  SDO_GEOMETRY(LONGITUDE, LATITUDE);

Example: Spatially Enabling a Table creates a table (CITY_POINTS) that initially does not contain an SDO_GEOMETRY column but does contain latitude and longitude values for each record (a point in or near a specified city). It spatially enables the table, updating the existing records to include the SDO_GEOMETRY information, and it also inserts new records and updates those.

Notes on Example: Spatially Enabling a Table:

See Also: