Examples of inserting spatial data with constructor functions

These examples show the code for calling constructor functions to create spatial data and for inserting this spatial data into tables with spatial columns.

Example of inserting spatial data into spatial columns

The following example creates a spatial column that contains 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 ST_Point);

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

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

SELECT id, st_geometrytype(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 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, ST_Geometry(:wkt_buffer,1));

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

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

EXEC SQL INSERT INTO sample_geometry(id, geom)
         VALUES(:id, 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 format to specify the geometries.


String ins1 = "INSERT into sample_geometry (id, geom) 
              VALUES(?, 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(?, 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();