DB2 Version 9.7 for Linux, UNIX, and Windows

ST_Buffer

ST_Buffer takes a geometry, a distance, and, optionally, a unit as input parameters and returns the geometry that surrounds the given geometry by the specified distance, measured in the given unit.

Each point on the boundary of the resulting geometry is the specified distance away from the given geometry. The resulting geometry is represented in the spatial reference system of the given geometry.

For geodetic data, if you specify a negative distance, ST_Buffer returns a region that is further than the specified distance away from all points of the input geometry. In other words, a negative distance returns the complementary region.

Any circular curve in the boundary of the resulting geometry is approximated by linear strings. For example, the buffer around a point, which would result in a circular region, is approximated by a polygon whose boundary is a linestring.

If the given geometry is null or is empty, null will be returned.

This function can also be called as a method.

Syntax


>>-db2gse.ST_Buffer--(--geometry--,--distance--+---------+--)--><
                                               '-,--unit-'

Parameter

geometry: A value of type ST_Geometry or one of its subtypes that represents the geometry to create the buffer around. For geodetic data, ST_Buffer supports only ST_Point and ST_MultiPoint data types.
distance: A DOUBLE PRECISION value that specifies the distance to be used for the buffer around geometry. For geodetic data, the distance must not be greater than the Earth's equatorial radius. For the WGS-84 ellipsoid, this length is 6378137.0 meters.
unit: A VARCHAR(128) value that identifies the unit in which distance is measured. The supported units of measure are listed in the DB2GSE.ST_UNITS_OF_MEASURE catalog view.

If the unit parameter is omitted, the following rules are used to determine the unit of measure used for distance:

If geometry is in a projected or geocentric coordinate system, the linear unit associated with this coordinate system is the default.
If geometry is in a geographic coordinate system, but is not in a geodetic spatial reference system (SRS), the angular unit associated with this coordinate system is the default.
If geometry is in a geodetic SRS, the default unit of measure is meters.

Restrictions on unit conversions: An error (SQLSTATE 38SU4) is returned if any of the following conditions occur:

The geometry is in an unspecified coordinate system and the unit parameter is specified.
The geometry is in a projected coordinate system and an angular unit is specified.
The geometry is in a geographic coordinate system, but is not in a geodetic SRS, and a linear unit is specified.
The geometry is in a geographic coordinate system, is in a geodetic SRS, and an angular unit is specified.

Return type

db2gse.ST_Geometry

Examples

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

Example 1

The following code creates a spatial reference system, creates the SAMPLE_GEOMETRIES table, and populates it.

db2se create_srs se_bank  -srsId 4000 -srsName new_york1983 
    -xOffset 0 -yOffset 0 -xScale 1 -yScale 1 
    -coordsysName NAD_1983_StatePlane_New_York_East_FIPS_3101_Feet

SET CURRENT FUNCTION PATH = CURRENT FUNCTION PATH, db2gse 

CREATE TABLE 
    sample_geometries (id INTEGER, spatial_type varchar(18), 
    geometry ST_GEOMETRY)

INSERT INTO sample_geometries(id, spatial_type, geometry)
VALUES
    (1, 'st_point', ST_Point(50, 50, 4000)),
    (2, 'st_linestring',
         ST_LineString('linestring(200 100, 210 130, 
         220 140)',  4000)),
    (3, 'st_polygon', 
         ST_Polygon('polygon((110 120, 110 140, 130 140, 
         130 120, 110 120))',4000)),
    (4, 'st_multipolygon', 
         ST_MultiPolygon('multipolygon(((30 30, 30 40, 
         35 40, 35 30, 30 30),(35 30, 35 40, 45 40, 
         45 30, 35 30)))', 4000))

Example 2

The following SELECT statement uses the ST_Buffer function to apply a buffer of 10.

SELECT id, spatial_type,
       cast(geometry..ST_Buffer(10)..ST_AsText AS varchar(470)) AS buffer_10
FROM   sample_geometries

Results:

ID          SPATIAL_TYPE       BUFFER_10                                 
----------- ------------------ ------------------------------------------
1           st_point           POLYGON (( 60.00000000 50.00000000, 
   59.00000000 55.00000000, 54.00000000 59.00000000, 49.00000000 
   60.00000000, 44.00000000 58.00000000, 41.00000000 53.00000000, 
   40.00000000 48.00000000,42.00000000 43.00000000, 47.00000000
   41.00000000, 52.00000000 40.00000000, 57.00000000 42.00000000, 
   60.00000000 50.00000000))    

2             st_linestring    POLYGON (( 230.00000000 
   140.00000000, 229.00000000 145.00000000, 224.00000000 
   149.00000000, 219.00000000 150.00000000, 213.00000000 147.00000000,
   203.00000000 137.00000000, 201.00000000 133.00000000, 191.00000000
   103.00000000, 191.00000000 99.00000000, 192.00000000  95.00000000, 
   196.00000000 91.00000000, 200.00000000 91.00000000,204.00000000 
   91.00000000, 209.00000000 97.00000000, 218.00000000 124.00000000,
   227.00000000 133.00000000, 230.00000000 140.00000000))

3              st_polygon         POLYGON (( 140.00000000 120.00000000, 
   140.00000000 140.00000000, 139.00000000  145.00000000, 130.00000000 
   150.00000000, 110.00000000 150.00000000, 105.00000000 149.00000000,
   100.00000000 140.00000000,100.00000000 120.00000000, 101.00000000 
   115.00000000, 110.00000000 110.00000000,130.00000000 110.00000000, 
   135.00000000 111.00000000, 140.00000000 120.00000000))  
                                      
4              st_multipolygon    POLYGON (( 55.00000000 30.00000000, 
   55.00000000 40.00000000, 54.00000000 45.00000000, 45.00000000
   50.00000000, 30.00000000 50.00000000, 25.00000000 49.00000000, 
   20.00000000 40.00000000, 20.00000000 30.00000000, 21.00000000
   25.00000000, 30.00000000 20.00000000, 45.00000000 20.00000000, 
   50.00000000 21.00000000, 55.00000000 30.00000000))

Example 3

The following SELECT statement uses the ST_Buffer function to apply a negative buffer of 5.

SELECT id, spatial_type,
       cast(ST_AsText(ST_Buffer(geometry, -5)) AS varchar(150)) 
       AS buffer_negative_5
FROM   sample_geometries
WHERE  id = 3

Results:

ID          SPATIAL_TYPE       BUFFER_NEGATIVE_5  
----------- ------------------ --------------------------------------         
3           st_polygon         POLYGON (( 115.00000000 125.00000000, 
   125.00000000 125.00000000, 125.00000000 135.00000000, 115.00000000 
   135.00000000, 115.00000000 125.00000000))

Example 4

The following SELECT statement shows the result of applying a buffer with the unit parameter specified.

SELECT id, spatial_type,
    cast(ST_AsText(ST_Buffer(geometry, 10, 'METER')) AS varchar(680)) 
    AS buffer_10_meter
FROM   sample_geometries
WHERE  id = 3

Results:

ID          SPATIAL_TYPE       BUFFER_10_METER
----------- ------------------ --------------------------------------         
3           st_polygon         POLYGON (( 163.00000000 120.00000000, 
   163.00000000 140.00000000, 162.00000000 149.00000000, 159.00000000 
   157.00000000, 152.00000000 165.00000000, 143.00000000 170.00000000, 
   130.00000000 173.00000000, 110.00000000 173.00000000, 101.00000000 
   172.00000000, 92.00000000 167.00000000, 84.00000000 160.00000000, 
   79.00000000 151.00000000, 77.00000000 140.00000000, 77.00000000 
   120.00000000, 78.00000000 111.00000000, 83.00000000 102.00000000, 
   90.00000000 94.00000000, 99.00000000 89.00000000, 110.00000000 
   87.00000000, 130.00000000 87.00000000, 139.00000000 88.00000000,
   147.00000000 91.00000000, 155.00000000 98.00000000, 160.00000000 
   107.00000000, 163.00000000 120.00000000))