Examples of inserting spatial data with constructor functions

The following example inserts a row into the SAMPLE_GEOMETRY table with ID 100 and a point value with an X coordinate of 30, a Y coordinate of 40, and in spatial reference system 1 using the coordinate representation and well-known text (WKT) representation. It then inserts another row with ID 200 and a linestring value with the coordinates indicated.

CREATE TABLE sample_geometry (id INT, geom db2gse.ST_Geometry);

INSERT INTO sample_geometry(id, geom)
       VALUES(100,db2gse.ST_Geometry('point(30 40)', 1));

INSERT INTO sample_geometry(id, geom)
       VALUES(200,db2gse.ST_Geometry('linestring(50 50, 100 100', 1));

SELECT id, TYPE_NAME(geom) FROM sample_geometry

ID      2
------  --------------------------------
   100  "ST_POINT"
   200  "ST_LINESTRING"

The following example creates a spatial column that contain only ST_Point values and then inserts two points. The first INSERT statement creates a point geometry from the well-known-text representation (WKT). The second INSERT statement creates a point geometry from numeric coordinate values. Note that these input values could also be selected from existing table columns.

CREATE TABLE sample_points (id INT, geom db2gse.ST_Point);

INSERT INTO sample_points(id, geom)
       VALUES(100,db2gse.ST_Point('point(30 40)', 1));

INSERT INTO sample_points(id, geom)
       VALUES(101,db2gse.ST_Point(50, 50, 1));

SELECT id, TYPE_NAME(geom) FROM sample_geometry

ID      2
------  --------------------------------
   100 "ST_POINT"
   101 "ST_POINT"

Attempting to insert a linestring or any other type which is not a point results in an SQL error.

The following example uses embedded SQL and assumes that the application fills the data areas with the appropriate values for any of the supported geometries.

EXEC SQL BEGIN DECLARE SECTION;
   sqlint32 id = 0;
   SQL TYPE IS CLOB(10000) wkt_buffer;
   SQL TYPE IS CLOB(10000) gml_buffer;
   SQL TYPE IS BLOB(10000) wkb_buffer;
   SQL TYPE IS BLOB(10000) shape_buffer;
EXEC SQL END DECLARE SECTION;

// * Application logic to read into buffers goes here */

EXEC SQL INSERT INTO sample_geometry(id, geom)
         VALUES(:id, db2gse.ST_Geometry(:wkt_buffer,1));

EXEC SQL INSERT INTO sample_geometry(id, geom)
         VALUES:id, db2gse.ST_Geometry(:wkb_buffer,1));

EXEC SQL INSERT INTO sample_geometry(id, geom)
         VALUES(:id, db2gse.ST_Geometry(:gml_buffer,1));

EXEC SQL INSERT INTO sample_geometry(id, geom)
         VALUES(:id, db2gse.ST_Geometry(:shape_buffer,1));

The following example shows the code in a Java application that uses JDBC to insert point geometries using X, Y numeric coordinate values and uses the WKT representation to specify the geometries.

String ins1 = "INSERT into sample_geometry (id, geom) 
              VALUES(?, db2gse.ST_PointFromText(CAST( ? 
              as VARCHAR(128)), 1))";
PreparedStatement pstmt = con.prepareStatement(ins1);
pstmt.setInt(1, 100);                    // id value
pstmt.setString(2, "point(32.4 50.7)");  // wkt value
int rc = pstmt.executeUpdate();

String ins2 = "INSERT into sample_geometry (id, geom)                 
              VALUES(?, db2gse.ST_Point(CAST( ? as double), 
              CAST(? as double), 1))";
pstmt = con.prepareStatement(ins2);
pstmt.setInt(1, 200);         // id value
pstmt.setDouble(2, 40.3);     // lat
pstmt.setDouble(3, -72.5);    // long
rc = pstmt.executeUpdate();