Inserting spatial data with constructor functions

You can populate your tables with spatial columns by inserting spatial data with constructor functions.

Before you begin

Ensure that your user ID holds authorities or privileges to issue the INSERT statement on the spatial table.

About this task

Db2® Spatial Extender provides functions that construct geometries from coordinates and data exchange formats. Using these functions, you can generate spatial data to insert into your spatial columns.

Procedure

To insert spatial data with constructor functions:

Issue the INSERT statement and specify a constructor function to provide the spatial data for the spatial column.

INSERT INTO table-name (col1-name,col2-name, ...colspatial-name...)
       VALUES (col1-value, col2-value, ...db2se.function-name()...)

Query your table to verify and validate the spatial data generated.

Example of inserting spatial data into spatial columns

Example of inserting a specific geometry type into a spatial column

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.

Example of inserting different geometry types into a spatial column

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"

Example of inserting spatial data in an embedded SQL application

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));

Example of inserting spatial data in a Java application

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();