1.26 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.
Example 1-6 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 1-6 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 1-6:
-
It does not matter that the original table has the LATITUDE and LONGITUDE values in that order, as long as the column names are specified in the correct order in the geometry constructor (SDO_POINT in this case) in the UPDATE statement. (SDO_GEOMETRY objects have longitude first, then latitude for points.)
-
Geometry validation is not included in the example because validation is not relevant for points. However, if you spatially enable a table with other types of geometries, you should validate all initial and added geometries. (To perform validation, use SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT or SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT.)
Parent topic: Spatial Concepts