Importing coordinate spatial data

You can use a spatial constructor function to import a point object by specifying its coordinates directly in an INSERT statement.

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 point object is a data object of type ST_Point. A spatial constructor function is a function that constructs a geometry object as its output. For more information, see db2sb169.html#db2sb-gen168__fun-new-geom.

Procedure

To insert a point object using coordinates:

  1. Issue an INSERT statement that uses a point constructor function to generate a point from coordinate values: 
    INSERT INTO table-name (col1, col2, ..., spatialcol, ...)
       VALUES (col1-value, col2-value, ..., ST_Point(x,y[,z[,m]])...)
  2. Query your table to verify that the spatial data was generated correctly.

Example

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:
  • The following INSERT statement imports, into SAMPLE_POINTS1, a point based on 2 position coordinate values (x and y), and gives it the ID 101. The default spatial reference system (WGS84_ SRS_4326) is used.
    INSERT INTO SAMPLE_POINTS1(id, geom) VALUES (101, ST_Point(10.1, -3.7));
  • The following INSERT statement inserts, into SAMPLE_POINTS2, a point based on 3 position coordinate values (x, y, and z), and gives it the ID 102. The spatial reference system NAD83_SRS_4269 is used.
    INSERT INTO SAMPLE_POINTS(id, geom) VALUES (102, ST_Point(14.1, -2.1, 15.5), 4269);
  • The following INSERT statement inserts, into SAMPLE_POINTS2, a point based on 3 position and 1 measurement values (x, y, z, and m), and gives it the ID 103. The spatial reference system NAD83_SRS_4269 is used.
    INSERT INTO SAMPLE_POINTS(id, geom) VALUES (103, ST_Point(14.1, -2.1, 15.5, 3.0), 4269);
  • The following INSERT statement inserts, into SAMPLE_POINTS2, a point based on 2 position and 1 measurement coordinate values (x, y, and m), and gives it the ID 103. The spatial reference system NAD83_SRS_4269 is used.
    INSERT INTO SAMPLE_POINTS(id, geom) VALUES (103, ST_Point(14.1, -2.1, 15.5, NULL), 4269);