DB2 10.5 for Linux, UNIX, and Windows

ST_Polygon function

The ST_Polygon constructs a polygon from a given input.

The input can be given in one of the following formats:
  • A closed linestring that defines the exterior ring of the resulting polygon
  • A well-known text representation
  • A well-known binary representation
  • A shape representation
  • A representation in the geography markup language (GML)
An optional spatial reference system identifier can be specified to identify the spatial reference system that the resulting polygon is in.

If the polygon is constructed from a linestring and the given linestring is null, then null is returned. If the given linestring is empty, then an empty polygon is returned. If the polygon is constructed from its well-known text representation, its well-known binary representation, its shape representation, or its GML representation, and if the representation is null, then null is returned.

This function can also be called as a method for the following cases only: ST_Polygon(linestring) and ST_Polygon(linestring, srs_id).

Syntax

Read syntax diagramSkip visual syntax diagram
>>-db2gse.ST_Polygon--(--+-linestring-+--+-----------+--)------><
                         +-wkt--------+  '-,--srs_id-'      
                         +-wkb--------+                     
                         +-shape------+                     
                         '-gml--------'                     

Parameters

linestring
A value of type ST_LineString that represents the linestring that defines the exterior ring for the outer boundary. If linestring is not closed and simple, an exception condition is raised (SQLSTATE 38SSL).
wkt
A value of type CLOB(2G) that contains the well-known text representation of the resulting polygon.
wkb
A value of type BLOB(2G) that contains the well-known binary representation of the resulting polygon.
shape
A value of type BLOB(2G) that represents the shape representation of the resulting polygon.
gml
A value of type CLOB(2G) that represents the resulting polygon using the geography markup language.
srs_id
A value of type INTEGER that identifies the spatial reference system for the resulting polygon.

If the polygon is constructed from a given linestring parameter and the srs_id parameter is omitted, then the spatial reference system from linestring is used implicitly. Otherwise, if the srs_id parameter is omitted, the spatial reference system with the numeric identifier 0 (zero) is used.

If srs_id does not identify a spatial reference system listed in the catalog view DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS, then an exception condition is raised (SQLSTATE 38SU1).

Return type

db2gse.ST_Polygon

Example

In the following example, the lines of results have been reformatted for readability. The spacing in your results will vary according to your online display.

This example illustrates how ST_Polygon can be used to create and insert polygons. Three polygons are created and inserted. All of them are geometries in spatial reference system 1.
  • The first polygon is created from a ring (a closed and simple linestring). The X and Y coordinates for this polygon are: (10, 20) (10, 40) (20, 30).
  • The second polygon is created using its well-known text representation. The X and Y coordinates for this polygon are: (110, 120) (110, 140) (120, 130).
  • The third polygon is a donut polygon. A donut polygon consists of an interior and an exterior polygon. This donut polygon is created using its well-known text representation. The X and Y coordinates for the exterior polygon are: (110, 120) (110, 140) (130, 140) (130, 120) (110, 120). The X and Y coordinates for the interior polygon are: (115, 125) (115, 135) (125, 135) (125, 135) (115, 125).
SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse
CREATE TABLE sample_polys (id INTEGER, geometry ST_Polygon)

INSERT INTO sample_polys
  VALUES (1100,
          ST_Polygon (ST_LineString ('linestring 
                     (10 20, 10 40, 20 30, 10 20)',1), 1))

INSERT INTO sample_polys
  VALUES (1101,
          ST_Polygon ('polygon 
                    ((110 120, 110 140, 120 130, 110 120))', 1))

INSERT INTO sample_polys
  VALUES (1102,
          ST_Polygon ('polygon 
                    ((110 120, 110 140, 130 140, 130 120, 110 120),
                    (115 125, 115 135, 125 135, 125 135, 115 125))', 1))
The following SELECT statement returns the polygons that were recorded in the table: 
SELECT id, CAST( ST_AsText( geometry ) AS VARCHAR(120) ) POLYGONS
  FROM sample_polys
Results: 
ID         POLYGONS
------- ----------------------------------------------------------------
   1110 POLYGON (( 10.00000000 20.00000000, 20.00000000 30.00000000
                   10.00000000 40.00000000, 10.00000000 20.00000000))

   1101 POLYGON (( 110.00000000 120.00000000, 120.00000000 130.00000000
                   110.00000000 140.00000000, 110.00000000 120.00000000))

   1102 POLYGON (( 110.00000000 120.00000000, 130.00000000 120.00000000
                   130.00000000 140.00000000, 110.00000000 140.00000000
                   110.00000000 120.00000000),
                 ( 115.00000000 125.00000000, 115.00000000 135.00000000
                   125.00000000 135.00000000, 125.00000000 135.00000000
                   115.00000000 125.00000000))