Importing spatial data from a data exchange format

Before you can insert a WKT, WKB, ESRI shape, or GML format of spatial data into a table, you must first use a spatial constructor function to convert that data into a geometry object.

Before you begin

Ensure that your user ID has the necessary authorities or privileges to issue the INSERT statement for the target table.

About this task

A geometry object is a data object of type ST_Geometry, or of one of its subtypes. A spatial constructor function is a function that constructs a geometry object as its output.

To import spatial data that uses one of the following formats, use one of the spatial constructor functions described in db2sb169.html#db2sb-gen168__fun-constr-geom:

Well-known text (WKT) format
Well-known binary (WKB) format
ESRI shape format
Geography Markup Language (GML) format

Procedure

To import a geometry object using a spatial constructor function:

Create the target relational database table in which the data is to be stored.
Issue an INSERT statement that uses a spatial constructor function to generate the geometry object that is to be imported:
```
INSERT INTO table-name (col1-name,col2-name, ...colspatial-name...)
       VALUES (col1-value, col2-value, ...spatial-constructor-function-name()...)
```
Input values can be entered explicitly or selected from existing table columns. Attempting to insert data of a type that is not appropriate for the target column results in an SQL error.
Query the target table to verify that the data was imported correctly.

Examples of inserting geometry objects into spatial columns

Examples of inserting geometry objects using SQL

Assume that there are two tables with the names SAMPLE_POINTS1 and SAMPLE_POINTS2, and that each has the following two columns:

The first column (named id) is an integer column for an ID to identify each point.
The second column (named geom) is a spatial column of type ST_Point.

The following examples insert values into these tables:

This INSERT statement imports, into SAMPLE_POINTS1, a point based on a WKT format, and gives it the ID 100. The default spatial reference system (WGS84_ SRS_4326) is used.
```
INSERT INTO SAMPLE_POINTS1(id, geom) VALUES(100, ST_Point('point(30 40)'));
```
This INSERT statement imports, into SAMPLE_POINTS2, a point based on a WKT format, and gives it the ID 101. The spatial reference system NAD83_SRS_4269 is used.
```
INSERT INTO SAMPLE_POINTS2(id, geom) VALUES(101, ST_Point('point(31 41)', 4269));
```

Example of inserting geometry objects using an embedded SQL application

The following example uses embedded SQL to import geometry objects into an existing table with the name SAMPLE_GEOMETRY. An application must fill the data areas with the appropriate geometry values.

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 geometry objects using a Java application

The following example shows the code of a Java application that uses JDBC to import point geometries into an existing table with the name SAMPLE_GEOMETRY. The first INSERT statement imports a point based on a WKT format. The second INSERT statement imports a point based on numeric coordinate values.

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

Example of inserting geometry objects based on spatial data from an external table

A WKT format of a polygon that is based on the spatial reference system WGS 84 (which has the SRS ID 4326) is stored in a simple text file with the name data_snip.txt:

3649|"48"|"TX"|"Texas"|"029"|"Bexar"|+7.88684883817E-004|+1.60553612595460E-001|POLYGON ((-98.69178 29.462851, -98.692282 29.46382, ..., -98.69178 29.462851))

The following statement creates a table in which this data can be stored:

CREATE TABLE TEST.COUNTIES (
          OBJECTID INTEGER ,
          STATE VARCHAR(20 OCTETS),
          STUSAB VARCHAR(80 OCTETS) ,
          STATE_NAME VARCHAR(80 OCTETS) ,
          COUNTY VARCHAR(80 OCTETS) ,
          COUNTY_NAM VARCHAR(80 OCTETS) ,
          SHAPE_AREA DOUBLE ,
          SHAPE_LEN DOUBLE ,
          GEOM    ST_POLYGON )
         DISTRIBUTE BY HASH(STATE, COUNTY)
           IN USERSPACE1
         ORGANIZE BY COLUMN;

The following statement inserts the polygon data into this table using the SRS ID 4326.

INSERT INTO TEST.COUNTIES
SELECT OBJECTID, STATE, STUSAB, STATE_NAME, COUNTY, COUNTY_NAM, Shape_Area, Shape_Len,
 ST_POLYGON(GEOM, 4326)
FROM external '/tmp/data_snip.txt'
(
       OBJECTID INTEGER,
       STATE VARCHAR(80),
       STUSAB VARCHAR(80),
       STATE_NAME VARCHAR(80),
       COUNTY VARCHAR(80),
       COUNTY_NAM VARCHAR(80),
       Shape_Area DOUBLE,
       Shape_Len DOUBLE,
       GEOM varchar(30000)
) USING ( DELIMITER '|' escapechar '\' maxerrors 1000 REMOTESOURCE 'Yes');