Create spatial columns

You can create a column of the SDO_GEOMETRY type and perform DDL operations on the column.

Create a table with a spatial column

Here is an example:

Create a table named test_tbl1 with the following three columns:

• id: a NUMBER column that serves as the primary key.
• name: a VARCHAR2(32) column that stores a string of up to 32 characters.
• shape: an SDO_GEOMETRY column that stores spatial geometry data.

CREATE TABLE test_tbl1 (id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY);

Insert spatial data

You can use the SDO_GEOMETRY constructor to create an instance of a spatial object.

Here is an example:

Insert a row of data into the test_tbl1 table using the following statement. The data to be inserted is as follows:

1. The value of the id column is 1.

2. The value of the name column is name1.

3. The value of the shape column is an SDO_GEOMETRY object that describes a polygon. The parameters of the SDO_GEOMETRY constructor are as follows:

   1. 2003: specifies the polygon type.
   2. NULL: indicates that no spatial reference identifier is specified.
   3. NULL: indicates that the coordinates of the points are not provided.
   4. SDO_ELEM_INFO_ARRAY(1,1003,1): specifies the geometric element information of the geometry. The first parameter, 1, indicates that the geometric elements start from the first member of the SDO_ORDINATE_ARRAY. The second parameter, 1003, indicates that the geometric element is an outer ring of the polygon. The third parameter, 1, indicates that the geometric elements are connected by straight lines.
   5. SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105): specifies the specific coordinates of the geometry. The coordinates of the polygon's vertices are (10,105), (15,105), (20,110), (10,110), and (10,105).

INSERT INTO test_tbl1 VALUES(1, 'name1',
  SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1), SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105))
);

Query spatial data field information

Here is an example:

Select the shape column information from the test_tbl1 table using the following statement.

obclient [SYS]> SELECT shape FROM test_tbl1;

The result is as follows:

+----------------------------------------------------------------------------------------------------------------------------------+
| SHAPE                                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------------------+
| SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(10, 105, 15, 105, 20, 110, 10, 110, 10, 105)) |
+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Add or drop spatial columns

You can also use the ALTER TABLE statement to add or drop a spatial column from an existing table.

Here is an example:

• Add a spatial column.

  ALTER TABLE test_tbl2 ADD shape SDO_GEOMETRY;
  

• Drop a spatial column.

  ALTER TABLE test_tbl2 DROP COLUMN shape;
  

References

• For more information about the syntax for creating tables, see CREATE TABLE.
• For more information about the syntax for modifying tables, see ALTER TABLE.
• For more information about spatial data types, see Overview of spatial data types.
• For more information about viewing data of spatial data types, see Access SDO_GEOMETRY properties.