Create a spatial data column

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

Create a table with a spatial column

Example

Create a table named test_tbl1, which contains the following three columns:

• id: a NUMBER column, which is used as the primary key.
• name: a VARCHAR2(32) column, which is used to store strings at a maximum length of 32 characters.
• shape: a SDO_GEOMETRY column, which stores geometry data.

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

Insert GIS data

You can use the SDO_GEOMETRY() constructor function to create a GIS instance.

Example

Execute the following statement to insert a row of data into the test_tbl1 table. The inserted data 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 a SDO_GEOMETRY object that describes a polygon. The attributes in the SDO_GEOMETRY() constructor function are as follows:

   1. 2003: indicates that the geometry object is a polygon.
   2. NULL: indicates that there is no SRID.
   3. NULL: indicates that point coordinates are not given.
   4. SDO_ELEM_INFO_ARRAY(1,1003,1): the geometric element information of the geometry. In the triplet, the first number 1 indicates to start from the first member of SDO_ORDINATE_ARRAY, the second number 1003 indicates a polygon outer ring, and the third number 1 indicates connection by straight line segments.
   5. SDO_ORDINATE_ARRAY(10,105, 15,105, 20,110, 10,110, 10,105): the coordinate values of the geometry, which are vertex coordinates of the polygon here. The vertex coordinates 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 GIS data

Example

Execute the following statement to query data in the shape column from the test_tbl1 table.

obclient [SYS]> SELECT shape FROM test_tbl1;

The return 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 a spatial column

You can also execute an ALTER TABLE statement to add or drop a spatial column for an existing table.

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 a table, see CREATE TABLE.
• For more information about the syntax for modifying a table, see ALTER TABLE.
• For more information about spatial data types, see Overview.
• For more information about how to view spatial data, see SDO_GEOMETRY attribute access.