2.4 SDO_GEOMETRY Constructors

The SDO_GEOMETRY object type has constructors that create a geometry object from a well-known text (WKT or EWKT) string in CLOB or VARCHAR2 format, or from a well-known binary (WKB or EWKB) object in BLOB format, optionally hex-encoded.

The following constructor formats are available:

SDO_GEOMETRY(wkt CLOB, srid NUMBER DEFAULT NULL);
SDO_GEOMETRY(wkt VARCHAR2, srid NUMBER DEFAULT NULL);
SDO_GEOMETRY(wkb BLOB, srid NUMBER DEFAULT NULL);

Note that the optional srid parameter is used to specify the coordinate system of the WKT or WKB data; it overrides any SRID specification in an EWKT or EWKB input (no transformation is done). If the created geometry is inserted into a table, the srid value used with the constructor must match the SDO_SRID value of the geometries in the table.

The following simple example constructs a point geometry using a well-known text string. (In a WKT, spaces separate ordinates of a vertex, and commas separate vertices.)

SELECT SDO_GEOMETRY('POINT(-79 37)') FROM DUAL;
 
SDO_GEOMETRY('POINT(-7937)')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_I
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL)

Example 2-5 shows SDO_GEOMETRY constructors that create geometry objects, insert the objects into a table, and display the objects that were added to the table.

Example 2-5 SDO_GEOMETRY Constructors to Create Geometries

DECLARE
  cola_b_wkb  BLOB;
  cola_b_wkt_clob  CLOB;
  cola_b_wkt_varchar  VARCHAR2(255);
  cola_b_geom  SDO_GEOMETRY;

BEGIN
-- Get cola_b geometry into CLOB, VARCHAR2, and BLOB objects,
-- for use by the constructor.
SELECT c.shape.Get_WKT() INTO cola_b_wkt_clob
  FROM cola_markets c WHERE c.name = 'cola_b';
cola_b_wkt_varchar := cola_b_wkt_clob;
SELECT c.shape.Get_WKB() INTO cola_b_wkb
  FROM cola_markets c WHERE c.name = 'cola_b';

-- Use some SDO_GEOMETRY constructors;
-- insert 3 geometries into the table; display the geometries later.
cola_b_geom := SDO_GEOMETRY(cola_b_wkt_clob);
INSERT INTO cola_markets VALUES (101, 'cola_b_from_clob', cola_b_geom);
cola_b_geom := SDO_GEOMETRY(cola_b_wkt_varchar);
INSERT INTO cola_markets VALUES (102, 'cola_b_from_varchar', cola_b_geom);
cola_b_geom := SDO_GEOMETRY(cola_b_wkb);
INSERT INTO cola_markets VALUES (103, 'cola_b_from_wkb', cola_b_geom);
END;
/
 
PL/SQL procedure successfully completed.
 
-- Display the geometries created using SDO_GEOMETRY constructors.
-- All three geometries are identical.
SELECT name, shape FROM cola_markets WHERE mkt_id > 100;
 
NAME                                                                            
--------------------------------                                                
SHAPE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)    
--------------------------------------------------------------------------------
cola_b_from_clob                                                                
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))                                               
                                                                                
cola_b_from_varchar                                                             
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))                                               
                                                                                
cola_b_from_wkb                                                                 
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(5, 1, 8, 1, 8, 6, 5, 7, 5, 1))

Geometry Constructor for Inserting Longitude and Latitude Spatial Data

You can create a geometry object to store spatial data in longitude and latitude coordinate system using the SDO_GEOMETRY(longitude, latitude) constructor as shown in the following example:

–- Create a table
CREATE TABLE t1(i NUMBER, geom SDO_GEOMETRY);

–- Insert lon/lat spatial data using the following constructor
INSERT INTO t1 VALUES (1, SDO_GEOMETRY(-73.45, 45.2));

–- Display the inserted geometry
SQL> SELECT geom FROM t1;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-73.45, 45.2, NULL), NULL, NULL)

As seen in the displayed geometry output, the INSERT statement in the preceding example is equivalent to:

INSERT INTO t1 VALUES (1, SDO_GEOMETRY(2001, 4326, sdo_point_type(-73.45, 45.2, null), null, null);