ST_Area function

The ST_Area function takes a geometry and, optionally, a unit as input parameters and returns the area covered by the geometry in either the default or given unit of measure.

If the geometry is a polygon or multipolygon, then the area covered by the geometry is returned. The area of points, linestrings, multipoints, and multilinestrings is 0 (zero). If the geometry is null or is an empty geometry, null is returned.

This function can also be called as a method.

Syntax

Read syntax diagramSkip visual syntax diagramdb2gse.ST_Area(geometry ,unit )

Parameters

geometry
A value of type ST_Geometry or one of its subtypes that represents the geometry that determines the area.
unit
A VARCHAR(128) value that identifies the units in which the area 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 in which the area is measured:
  • If geometry is in a projected or geocentric coordinate system, the linear unit associated with this coordinate system is used.
  • If geometry is in a geographic coordinate system, the angular unit associated with this coordinate system is used.
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, and a linear unit is specified.

Return type

DOUBLE

Examples

Example 1
The spatial analyst needs a list of the area covered by each sales region. The sales region polygons are stored in the SAMPLE_POLYGONS table. The area is calculated by applying the ST_Area function to the geometry column. 

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

CREATE TABLE sample_polygons (id INTEGER, geometry ST_POLYGON)

INSERT INTO sample_polygons (id, geometry)
VALUES
  (1, ST_Polygon('polygon((0 0, 0 10, 10 10, 10 0, 0 0))', 4000) ),
  (2, ST_Polygon('polygon((20 0, 30 20, 40 0, 20 0 ))', 4000) ),
  (3, ST_Polygon('polygon((20 30, 25 35, 30 30, 20 30))', 4000))
The following SELECT statement retrieves the sales region ID and area: 

SELECT id, ST_Area(geometry) AS area
FROM   sample_polygons
Results: 

ID        AREA
-------- ------------------------
       1   +1.00000000000000E+002
       2   +2.00000000000000E+002
       3   +2.50000000000000E+001
Example 2
The following SELECT statement retrieves the sales region ID and area in various units: 

SELECT id,
       ST_Area(geometry) square_feet,
       ST_Area(geometry, 'METER') square_meters,
       ST_Area(geometry, 'STATUTE MILE') square_miles
FROM   sample_polygons
Results: 

ID SQUARE_FEET            SQUARE_METERS          SQUARE_MILES
-- ---------------------- ---------------------- ----------------------
 1 +1.00000000000000E+002 +9.29034116132748E+000 +3.58702077598427E-006
 2 +2.00000000000000E+002 +1.85806823226550E+001 +7.17404155196855E-006
 3 +2.50000000000000E+001 +2.32258529033187E+000 +8.96755193996069E-007
Example 3

This example finds the area of a polygon defined in State Plane coordinates.

The State Plane spatial reference system with an ID of 3 is created with the following command: 

db2se create_srs SAMP_DB  -srsId 3 -srsName z3101a -xOffset 0 
    -yOffset 0 -xScale 1 -yScale 1                       	
    -coordsysName NAD_1983_StatePlane_New_York_East_FIPS_3101_Feet
The following SQL statements add the polygon, in spatial reference system 3, to the table and determines the area in square feet, square meters, and square miles. 

SET current function path db2gse;
 CREATE TABLE Sample_Poly3 (id integer, geometry ST_Polygon);
 INSERT into Sample_Poly3 VALUES
    (1, ST_Polygon('polygon((567176.0 1166411.0,
                             567176.0 1177640.0,
                             637948.0 1177640.0,
                             637948.0 1166411.0,
                             567176.0 1166411.0 ))', 3));
 SELECT id, ST_Area(geometry) "Square Feet",
            ST_Area(geometry, 'METER') "Square Meters",
            ST_Area(geometry, 'STATUTE MILE') "Square Miles"
 FROM Sample_Poly3;
Results: 

ID  SQUARE FEET           SQUARE METERS          SQUARE MILES
-- ---------------------- ---------------------- ----------------------
 1 +7.94698788000000E+008 +7.38302286101346E+007 +2.85060106320552E+001