Spatial Functions

Describes functions provided by Tibero Spatial.

Some Spatial functions are provided by default, and other Spatial functions are provided only in a specific environment (C++11 or later, Linux, x86, 64-bit). Some default Spatial functions perform calculations taking into account a coordinate system by using given SRID as an argument.

In the following, functions provided only in a specific environment are marked with (#), and functions that perform calculations taking into account a coordinate system are marked with ($).

ST_AGGR_ASTWKB(#)

Returns a GEOMETRY object as in the Tiny Well Known Binary (TWKB) format. TWKB of a set with an identifier is returned for each GEOMETRY object.

• Syntax

[Figure 1] ST_AGGR_ASTWKB

[Figure 1] ST_AGGR_ASTWKB

• Components

Component	Description
geom_expr	Expression indicating a GEOMETRY object.
num_expr	Identifier for each geom_expr.
num1	Number of significant digits after the decimal point of coordinates to be expressed as TWKB. (Default value: 0)
num2	Determines whether to include the length of the encoded GEOMETRY object in TWKB to return. The length is not included by default. If set to 1, it is included.
num3	Determines whether to include the bounding box of the GEOMETRY object in TWKB to return. The bounding box is not included by default. If set to 1, it is included.

• Example

SQL> SELECT ST_AGGR_ASTWKB(GEOM, ID) FROM GIS;

ST_AGGR_ASTWKB(GEOM,ID)
-----------------------------------------------------------------------------------
07040ACA01CC01CE01D001D201D401D601D801DA01DC010100020204000202020202020002020202
02050002020202020202020202040300010502020200000201000001030003050000001818000017
1700050C14000206000001050005000D000606000005050006000201050202020000020100000101
05040400040400000303000700020100020202000204040202071007000201000202020002040402
02

SQL> SELECT ST_AGGR_ASTWKB(GEOM, ID, 3, 1, 1) FROM GIS;

ST_AGGR_ASTWKB(GEOM,ID,3,1,1)
-----------------------------------------------------------------------------------
6707DB0200C0BB0100C0BB010ACA01CC01CE01D001D201D401D601D801DA01DC0161030AD00F00D0
0F00D00FD00F640311D00FD00FD00FD00F02D00FD00FD00FD00F620311D00FD00FD00FD00F02D00F
D00FD00FD00F65031BD00FF02ED00FC03E0202D00FD00FD00FD00F02D00FD00FD00FA01F63031AD0
0FD00FD00FD00F0105D00FD00FD00F0000D00FCF0F0000CF0F63033E00C0BB0100C0BB0103050000
00C0BB01C0BB010000BFBB01BFBB010005E05DA09C0100D00FF02E0000CF0FEF2E000500AF6D00F0
2EF02E0000EF2EEF2E0066032DD00FC03ED00FC03E020105D00FD00FD00F0000D00FCF0F0000CF0F
0105A01FA01F00A01FA01F00009F1F9F1F0067032AD00FA01FD00FA01F0261030AD00F00D00F00D0
0FD00F620311A01FD00FA01FD00F02A01FA01FD00FD00F67120067032AD00FA01FD00FA01F026103
0AD00F00D00F00D00FD00F620311A01FD00FA01FD00F02A01FA01FD00FD00F

---

ST_AREA($)

Returns the area of a GEOMETRY object. For a POLYGON object, it calculates and returns its area. For a POINT or LINESTRING object, it returns 0. For a MULTI type GEOMETRY object, it calculates and returns the sum of the areas of internal GEOMETRY objects.

If the coordinate system is a rotating body, the area of the curved surface of the object is calculated in the unit of square meters. The calculation for the spheroid is the default. If you want faster calculations, change them to calculations for the spherical coordinate system.

• Syntax

[Figure 2] ST_AREA

[Figure 2] ST_AREA

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
default	Calculation method. For a rotating body coordinate system, the spheroid is calculated by default. If set to 0, a spherical coordinate system is calculated.

• Example

SQL> SELECT ST_AREA(GEOM), ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'MULTIPOLYGON';

ST_AREA(GEOM)
--------------
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
            5
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))

---

ST_ASBINARY

Returns a GEOMETRY object as in Well Known Binary (WKB) format.

• Syntax

[Figure 3] ST_ASBINARY

[Figure 3] ST_ASBINARY

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM), ST_ASBINARY(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POINT';
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_ASBINARY(GEOM)
-----------------------------------------------------------------------------------
POINT(1 1)
0101000000000000000000F03F000000000000F03F

---

ST_ASGEOJSON

Returns a GEOMETRY object as in GEOJSON format.

• Syntax

[Figure 4] ST_ASGEOJSON

[Figure 4] ST_ASGEOJSON

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num1	Number of significant digits in the coordinate. (Default value: 9)
num2	Option of the GEOJASON format to return. – 0: Adds no option. – 1: Adds the minimum bounding rectangle of the GEOMETRY. – 2: Displays the coordinate in Short CRS format. (Example: EPSG:4326) – 4: Displays the coordinate in Long CRS format.(Example: urn:ogc:def:crs:EPSG::4326) – 8: Displays the coordinate in Short CRS format if SRID is not 4326. (Default value)

• Example

SQL> SELECT ST_ASGEOJSON(GEOM) FROM GIS;

ST_ASGEOJSON(GEOM)
-----------------------------------------------------------------------------------
{"type":"Point","coordinates":[1,1]}
{"type":"MultiPoint","coordinates":[[1,1],[2,2]]}
{"type":"LineString","coordinates":[[1,1],[2,2]]}
{"type":"MultiLineString","coordinates":[[[1,1],[2,2]],[[3,3],[4,5]]]}
{"type":"Polygon","coordinates":[[[1,1],[2,1],[2,2],[1,2],[1,1]]]}
{"type":"Polygon","coordinates":[[[0,0],[0,12],[12,12],[12,0],[0,0]],[[6,10],[6,
11],[9,11],[9,10],[6,10]],[[6,3],[6,6],[9,6],[9,3],[6,3]]]}
{"type":"MultiPolygon","coordinates":[[[[1,1],[2,1],[2,2],[1,2],[1,1]]],[[[3,3],
[3,5],[5,5],[5,3],[3,3]]]]}
{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[1,1]},
{"type":"LineString","coordinates":[[2,2],[3,3]]}]}
{"type":"GeometryCollection","geometries":[]}
{"type":"GeometryCollection","geometries":[{"type":"Point","coordinates":[1,1]},
{"type":"LineString","coordinates":[[2,2],[3,3]]}]}

---

ST_ASGML

Returns a GEOMETRY object as in GML format.

• Syntax

[Figure 5] ST_ASGML

[Figure 5] ST_ASGML

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num1	Number of significant digits in the coordinate. (Default value: 15)
num2	Option of the GML format to return. – 0: Displays the coordinate in Short CRS format. (Default value) 1: Displays the coordinate in Ling CRS format.

• Example

SQL> SELECT ST_ASGML(GEOM) FROM GIS;

ST_ASGML(GEOM)
-----------------------------------------------------------------------------------
<gml:Point><coordinates>1,1</coordinates></gml:Point>
<gml:MultiPoint><pointMember><gml:Point><coordinates>1,1</coordinates></gml:Poin
t></pointMember><pointMember><gml:Point><coordinates>2,2</coordinates></gml:Poin
t></pointMember></gml:MultiPoint>
<gml:LineString><coordinates>1,1 2,2</coordinates></gml:LineString>
<gml:MultiLineString><lineStringMember><gml:LineString><coordinates>1,1 2,2</coo
rdinates></gml:LineString></lineStringMember><lineStringMember><gml:LineString><
coordinates>3,3 4,5</coordinates></gml:LineString></lineStringMember></gml:Multi
LineString>
<gml:Polygon><outerBoundaryIs><LinearRing><coordinates>1,1 2,1 2,2 1,2 1,1</coor
dinates></LinearRing></outerBoundaryIs></gml:Polygon>
<gml:Polygon><outerBoundaryIs><LinearRing><coordinates>0,0 0,12 12,12 12,0 0,0</
coordinates></LinearRing></outerBoundaryIs><innerBoundaryIs><LinearRing><coordin
ates>6,10 6,11 9,11 9,10 6,10</coordinates></LinearRing></innerBoundaryIs><inner
BoundaryIs><LinearRing><coordinates>6,3 6,6 9,6 9,3 6,3</coordinates></LinearRin
g></innerBoundaryIs></gml:Polygon>
<gml:MultiPolygon><polygonMember><gml:Polygon><outerBoundaryIs><LinearRing><coor
dinates>1,1 2,1 2,2 1,2 1,1</coordinates></LinearRing></outerBoundaryIs></gml:Po
lygon></polygonMember><polygonMember><gml:Polygon><outerBoundaryIs><LinearRing><
coordinates>3,3 3,5 5,5 5,3 3,3</coordinates></LinearRing></outerBoundaryIs></gm
l:Polygon></polygonMember></gml:MultiPolygon>
<gml:MultiGeometry><geometryMember><gml:Point><coordinates>1,1</coordinates></gm
l:Point></geometryMember><geometryMember><gml:LineString><coordinates>2,2 3,3</c
oordinates></gml:LineString></geometryMember></gml:MultiGeometry>

<gml:MultiGeometry><geometryMember><gml:Point><coordinates>1,1</coordinates></gm
l:Point></geometryMember><geometryMember><gml:LineString><coordinates>2,2 3,3</c
oordinates></gml:LineString></geometryMember></gml:MultiGeometry>

---

ST_ASKML

Returns a GEOMETRY object as in KML format.

• Syntax

[Figure 6] ST_ASKML

[Figure 6] ST_ASKML

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object. An error is returned if the object is GEOMETRYCOLLECTION type.
num	Number of significant digits in the coordinate. (Default value: 15)
str	Namespace prefix of KML to return. The default namespace has no prefix.

• Example

SQL> SELECT ST_ASKML(ST_SETSRID(GEOM,4326)) FROM GIS WHERE ST_GEOMETRYTYPE(GEOM)
     NOT LIKE 'GEOMETRYCOLLECTION';
     
ST_ASKML(GEOM)
-----------------------------------------------------------------------------------
<Point><coordinates>1,1</coordinates></Point>
<MultiGeometry><Point><coordinates>1,1</coordinates></Point><Point><coordinates>
2,2</coordinates></Point></MultiGeometry>
<LineString><coordinates>1,1 2,2</coordinates></LineString>
<MultiGeometry><LineString><coordinates>1,1 2,2</coordinates></LineString><LineS
tring><coordinates>3,3 4,5</coordinates></LineString></MultiGeometry>
<Polygon><outerBoundaryIs><LinearRing><coordinates>1,1 2,1 2,2 1,2 1,1</coordina
tes></LinearRing></outerBoundaryIs></Polygon>
<Polygon><outerBoundaryIs><LinearRing><coordinates>0,0 0,12 12,12 12,0 0,0</coor
dinates></LinearRing></outerBoundaryIs><innerBoundaryIs><LinearRing><coordinates
>6,10 6,11 9,11 9,10 6,10</coordinates></LinearRing></innerBoundaryIs><innerBoun
daryIs><LinearRing><coordinates>6,3 6,6 9,6 9,3 6,3</coordinates></LinearRing></
innerBoundaryIs></Polygon>
<MultiGeometry><Polygon><outerBoundaryIs><LinearRing><coordinates>1,1 2,1 2,2 1,
2 1,1</coordinates></LinearRing></outerBoundaryIs></Polygon><Polygon><outerBound
aryIs><LinearRing><coordinates>3,3 3,5 5,5 5,3 3,3</coordinates></LinearRing></o
uterBoundaryIs></Polygon></MultiGeometry>

---

ST_ASTEXT

Returns a GEOMETRY object as in Well Known Text (WKT) format.

• Syntax

[Figure 7] ST_ASTEXT

[Figure 7] ST_ASTEXT

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM) FROM GIS;

ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
POINT(1 1)
MULTIPOINT((1 1),(2 2))
LINESTRING(1 1,2 2)
MULTILINESTRING((1 1,2 2),(3 3,4 5))
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_ASTWKB(#)

Returns a GEOMETRY object as in Tiny Well Known Binary (TWKB) format.

• Syntax

[Figure 8] ST_ASTWKB

[Figure 8] ST_ASTWKB

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num1	Number of significant digits after the decimal point of the coordinate expressed as TWKB. (Default value: 0)
num2	Determines whether to include the length of the encoded GEOMETRY object in TWKB to return. The length is not included by default. If set to 1, it is included.
num3	Determines whether to include the bounding box of the GEOMETRY object in TWKB to return. The bounding box is not included by default. If set to 1, it is included.

• Example

SQL> SELECT ST_ASTWKB(GEOM) FROM GIS;

ST_ASTWKB(GEOM)
-----------------------------------------------------------------------------------
01000202
04000202020202
02000202020202
05000202020202020202020204
0300010502020200000201000001
0300030500000018180000171700050C14000206000001050005000D0006060000050500
060002010502020200000201000001010504040004040000030300
0700020100020202000204040202
0710
0700020100020202000204040202

SQL> SELECT ST_ASTWKB(GEOM, 3, 1, 1) FROM GIS;

ST_ASTWKB(GEOM,3,1,1)
-----------------------------------------------------------------------------------
61030AD00F00D00F00D00FD00F
640311D00FD00FD00FD00F02D00FD00FD00FD00F
620311D00FD00FD00FD00F02D00FD00FD00FD00F
65031BD00FF02ED00FC03E0202D00FD00FD00FD00F02D00FD00FD00FA01F
63031AD00FD00FD00FD00F0105D00FD00FD00F0000D00FCF0F0000CF0F
63033E00C0BB0100C0BB010305000000C0BB01C0BB010000BFBB01BFBB010005E05DA09C0100D00F
F02E0000CF0FEF2E000500AF6D00F02EF02E0000EF2EEF2E00
66032DD00FC03ED00FC03E020105D00FD00FD00F0000D00FCF0F0000CF0F0105A01FA01F00A01FA0
1F00009F1F9F1F00
67032AD00FA01FD00FA01F0261030AD00F00D00F00D00FD00F620311A01FD00FA01FD00F02A01FA0
1FD00FD00F
671200
67032AD00FA01FD00FA01F0261030AD00F00D00F00D00FD00F620311A01FD00FA01FD00F02A01FA0
1FD00FD00F

---

ST_AZIMUTH($)

Measures the angle between the line segment that connects POINT object 1 and POINT object 2 and the vertical upper line segment of the POINT object 1 in a clockwise direction and returns it in arc degree unit. This corresponds to the North Pole azimuth on a rotating body coordinate system. This function can take GEOMETRY on a rotating body coordinate system and GEOMETRY on a plane coordinate system as arguments. If the type of the GEOMETRY object is not POINT, an exception is returned.

• Syntax

[Figure 9] ST_AZIMUTH

[Figure 9] ST_AZIMUTH

• Components

Component	Description
geom1	GEOMETRY type POINT object.
geom2	GEOMETRY type POINT object.

• Example

SQL> SELECT ST_AZIMUTH(ST_GEOMFROMTEXT('POINT(1 1)', 4326),
     ST_GEOMFROMTEXT('POINT(1 0)',4326)) AZIMUTH FROM DUAL;
     
   AZIMUTH
----------
3.14159265

---

ST_BOUNDARY

Returns the boundary of a given GEOMETRY object. If the type of the GEOMETRY object is GEOMETRYCOLLECTION, an error is returned.

• Syntax

[Figure 10] ST_BOUNDARY

[Figure 10] ST_BOUNDARY

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_ASTEXT(ST_BOUNDARY(GEOM))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) NOT LIKE 'GEOMETRYCOLLECTION';
     
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(ST_BOUNDARY(GEOM))
-----------------------------------------------------------------------------------
POINT(1 1)
GEOMETRYCOLLECTION EMPTY

MULTIPOINT((1 1),(2 2))
GEOMETRYCOLLECTION EMPTY

LINESTRING(1 1,2 2)
MULTIPOINT((1 1),(2 2))

MULTILINESTRING((1 1,2 2),(3 3,4 5))
MULTIPOINT((1 1),(2 2),(3 3),(4 5))

POLYGON((1 1,2 1,2 2,1 2,1 1))
LINESTRING(1 1,2 1,2 2,1 2,1 1)

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))
MULTILINESTRING((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9
6,9 3,6 3))

MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))
MULTILINESTRING((1 1,2 1,2 2,1 2,1 1),(3 3,3 5,5 5,5 3,3 3))

---

ST_BUFFER($)

Returns a GEOMETRY object representing all points within a certain distance from a given GEOMETRY object. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information.

• Syntax

[Figure 11] ST_BUFFER

[Figure 11] ST_BUFFER

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
expr	Expression meaning the distance from the given GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_AREA(ST_BUFFER(GEOM,10))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POINT';
     
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_AREA(ST_BUFFER(GEOM,10))
--------------------------
POINT(1 1)
                312.144515

---

ST_BUILDAREA(#)

Returns a POLYGON object that can be formed with line segment components of a given GEOMETRY object. If the line segment components cannot form a POLYGON object, NULL is returned.

• Syntax

[Figure 12] ST_BUILDAREA

[Figure 12] ST_BUILDAREA

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_BUILDAREA(GEOM))FROM GIS;

ST_ASTEXT(ST_BUILDAREA(GEOM))
-----------------------------------------------------------------------------------
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
POLYGON((1 1,1 2,2 2,2 1,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,9 10,9 11,6 11,6 10),(6 3,9 3,9 6,6 6,6 3))
MULTIPOLYGON(((1 1,1 2,2 2,2 1,1 1)),((3 3,3 5,5 5,5 3,3 3)))
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY

---

ST_CENTROID($)

Returns the center of a given GEOMETRY object. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information. If you want faster calculations, change them to calculations for the spherical coordinate system.

• Syntax

[Figure 13] ST_CENTROID

[Figure 13] ST_CENTROID

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
default	Calculation method. For a rotating body coordinate system, the spheroid is calculated by default. If set to 0, a spherical coordinate system is calculated.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_ASTEXT(ST_CENTROID(GEOM))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON';
     
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(ST_CENTROID(GEOM))
-----------------------------------------------------------------------------------
POLYGON((1 1,2 1,2 2,1 2,1 1))
POINT(1.5 1.5)

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))
POINT(5.8636363636364 6)

---

ST_COLLECT

Groups GEOMETRY objects and returns the result as a MULTI type object. Since it is an aggregate function, it returns the result of grouping GEOMETRY objects. Its variant function can take two GEOMETRY objects as arguments.

• Syntax

[Figure 14] ST_COLLECT

[Figure 14] ST_COLLECT

• Components

Component	Description
geom	Expression indicating a GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_COLLECT(A.GEOM, B.GEOM))
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POINT'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';

ST_ASTEXT(ST_COLLECT(A.GEOM,B.GEOM))
-----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(1 1,2 2))

• Example The following uses a table's geometry column as an argument.

SQL> SELECT ST_ASTEXT(ST_COLLECT(A.GEOM, B.GEOM))
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POINT' 
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';

ST_ASTEXT(ST_COLLECT(A.GEOM,B.GEOM))
----------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(1 1,2 2))

SQL> SELECT ST_ASTEXT(ST_COLLECT(GEOM)) FROM GIS;

ST_ASTEXT(ST_COLLECT(GEOM))
-----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(1 1),MULTIPOINT((1 1),(2 2)),LINESTRING(1 1,2 2),MULTIL
INESTRING((1 1,2 2),(3 3,4 5)),POLYGON((1 1,2 1,2 2,1 2,1 1)),POLYGON((0 0,0 12,
12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3)),MULTIPOLYGON((
(1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3))))

---

ST_CONTAINS

Returns GEOMETRY object 1 if GEOMETRY object 2 does not exist outside of the GEOMETRY object 1 and at least one point exists inside the GEOMETRY object 1.

• Syntax

[Figure 15] ST_CONTAINS

[Figure 15] ST_CONTAINS

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object that can include geom2.
geom2	GEOMETRY type GEOMETRY object that can be included in geom1.

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_CONTAINS(A.GEOM,B.GEOM) >0
     AND ST_GEOMETRYTYPE(A.GEOM) LIKE 'MULTILINESTRING'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(A.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
-----------------------------------------------------------------------------------
MULTILINESTRING((1 1,2 2),(3 3,4 5))
LINESTRING(1 1,2 2)

---

ST_CONVEXHULL

Returns the convex hull of a given GEOMETRY object. A convex hull means the smallest convex closed curve containing a GEOMETRY object.

• Syntax

[Figure 16] ST_CONVEXHULL

[Figure 16] ST_CONVEXHULL

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_ASTEXT(ST_CONVEXHULL(GEOM))
    FROM GIS;
    
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(ST_CONVEXHULL(GEOM))
-----------------------------------------------------------------------------------
POINT(1 1)
POINT(1 1)

MULTIPOINT((1 1),(2 2))
LINESTRING(1 1,2 2)

LINESTRING(1 1,2 2)
LINESTRING(1 1,2 2)


MULTILINESTRING((1 1,2 2),(3 3,4 5))
POLYGON((1 1,4 5,3 3,1 1))	


POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,1 2,2 2,2 1,1 1))

POLYGON((0 0,0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))
POLYGON((0 0,0 0,0 12,12 12,12 0,0 0))

MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))
POLYGON((1 1,1 2,3 5,5 5,5 3,2 1,1 1))

GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
LINESTRING(1 1,3 3)

GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION EMPTY

GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
LINESTRING(1 1,3 3)

---

ST_CROSSES

Returns 1 if two given GEOMETRY objects intersect. If one object includes another or their boundaries are touched, this is not regarded as they intersect.

• Syntax

[Figure 17] ST_CROSSES

[Figure 17] ST_CROSSES

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object
geom2	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_CROSSES(A.GEOM,B.GEOM) >0
     AND ST_GEOMETRYTYPE(A.GEOM) NOT LIKE 'GEOMETRYCOLLECTION'
     AND ST_GEOMETRYTYPE(B.GEOM) NOT LIKE 'GEOMETRYCOLLECTION';
     
ST_ASTEXT(A.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
-----------------------------------------------------------------------------------
POLYGON((1 1,2 1,2 2,1 2,1 1))
MULTILINESTRING((1 1,2 2),(3 3,4 5))

MULTILINESTRING((1 1,2 2),(3 3,4 5))
POLYGON((1 1,2 1,2 2,1 2,1 1))

---

ST_COVEREDBY(#)

Returns 1 if any point of GEOMETRY object 1 is not outside of GEOMETRY object 2. Its arguments must be GEOMETRY objects on a rotating body coordinate system. Otherwise, it returns an exception.

• Syntax

[Figure 18] ST_COVEREDBY

[Figure 18] ST_COVEREDBY

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object
geom2	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_COVEREDBY(ST_GEOMFROMTEXT('POINT(0 0)',4326),
     ST_BUFFER(ST_GEOMFROMTEXT('POINT(0 0)',4326),1)) COVEREDBY FROM DUAL;

COVEREDBY
---------
        1

---

ST_COVERS(#)

Returns 1 if any point of GEOMETRY object 2 is not outside of GEOMETRY object 1. Its arguments must be GEOMETRY objects on a rotating body coordinate system. Otherwise, it returns an exception.

• Syntax

[Figure 19] ST_COVERS

[Figure 19] ST_COVERS

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object
geom2	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_COVERS(ST_BUFFER(ST_GEOMFROMTEXT('POINT(0 0)',4326),1),
     ST_GEOMFROMTEXT('POINT(0 0)',4326)) COVERS FROM DUAL;
     
COVERS
------
     1

---

ST_DIFFERENCE($)

Returns the part of GEOMETRY object 1 that does not overlap with a GEOMETRY object 2. It can perform operations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information.

• Syntax

[Figure 20] ST_DIFFERENCE

[Figure 20] ST_DIFFERENCE

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object
geom2	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM),
     ST_ASTEXT(ST_DIFFERENCE(A.GEOM,B.GEOM)) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'LINESTRING'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';

ST_ASTEXT(A.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(ST_DIFFERENCE(A.GEOM,B.GEOM))
-----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)
LINESTRING(1 1,2 2)
GEOMETRYCOLLECTION EMPTY

---

ST_DIMENSION

Returns the dimension of a GEOMETRY object.

• Syntax

[Figure 21] ST_DIMENSION

[Figure 21] ST_DIMENSION

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_DIMENSION(GEOM), ST_ASTEXT(geom) FROM GIS;

ST_DIMENSION(GEOM)
----------------------
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
                 0
POINT(1 1)
                 0
MULTIPOINT((1 1),(2 2))

                 1
LINESTRING(1 1,2 2)

                 1
MULTILINESTRING((1 1,2 2),(3 3,4 5))

                 2
POLYGON((1 1,2 1,2 2,1 2,1 1))

                 2
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

                 2
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))

                 1
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

                 0
GEOMETRYCOLLECTION EMPTY

                 1
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_DISJOINT

Returns 1 if two GEOMETRY objects do not share any area.

• Syntax

[Figure 22] ST_DISJOINT

[Figure 22] ST_DISJOINT

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object
geom2	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_DISJOINT(A.GEOM,B.GEOM),
     ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'LINESTRING'
    AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';

ST_DISJOINT(A.GEOM,B.GEOM)
----------------------------
ST_ASTEXT(A.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
-----------------------------------------------------------------------------------
                        0
LINESTRING(1 1,2 2)
LINESTRING(1 1,2 2)

---

ST_DISTANCE($)

Returns the shortest distance between two GEOMETRY objects. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information. If you want faster calculations, change them to calculations for the spherical coordinate system.

• Syntax

[Figure 23] ST_DISTANCE

[Figure 23] ST_DISTANCE

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.
default	Calculation method. For a rotating body coordinate system, the spheroid is calculated by default. If set to 0, a spherical coordinate system is calculated.

• Example

SQL> SELECT ST_DISTANCE(ST_GEOMFROMTEXT('LINESTRING(2 2,0 0)'),
     ST_GEOMFROMTEXT('LINESTRING(4 2,6 0)')) DISTANCE FROM DUAL;
     
 DISTANCE
---------
       2

---

ST_DWITHIN #

Returns whether two GEOMETRY objects are within a specified distance. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information. If you want faster calculations, change them to calculations for the spherical coordinate system.

• Syntax

[Figure 24] ST_DWITHIN

[Figure 24] ST_DWITHIN

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.
num	Specified distance. For GEOMETRY, the unit of a set coordinate system is used. For GEOGRAPHY, meter is the unit.
default	Calculation method. For a rotating body coordinate system, the spheroid is calculated by default. If set to 0, a spherical coordinate system is calculated.

• Example

SQL> SELECT ST_DWITHIN(ST_GEOMFROMTEXT('LINESTRING(2 2,0 0)'),
     ST_GEOMFROMTEXT('LINESTRING(4 2,6 0)'), 3) DWITHIN FROM DUAL;

DWITHIN
-------
      1

---

ST_ENDPOINT

Returns the last point of a LINESTRING GEOMETRY object as a POINT object. If the argument is not LINESTRING, NULL is returned.

• Syntax

[Figure 25] ST_ENDPOINT

[Figure 25] ST_ENDPOINT

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_ASTEXT(ST_ENDPOINT(GEOM))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(ST_ENDPOINT(GEOM))
-----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)
POINT(2 2)

---

ST_ENVELOPE

Returns a rectangle that surrounds a given GEOMETRY object as a POLYGON object.

• Syntax

[Figure 26] ST_ENVELOPE

[Figure 26] ST_ENVELOPE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object

• Example

SQL> SELECT ST_ASTEXT(ST_ENVELOPE(GEOM)) FROM GIS;

ST_ASTEXT(ST_ENVELOPE(GEOM))
-----------------------------------------------------------------------------------
POLYGON((1 1,1 1,1 1,1 1,1 1))
POLYGON((1 1,1 2,2 2,2 1,1 1))
POLYGON((1 1,1 2,2 2,2	1,1 1))
POLYGON((1 1,1 5,4 5,4	1,1 1))
POLYGON((1 1,1 2,2 2,2	1,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0))
POLYGON((1 1,1 5,5 5,5 1,1 1))
POLYGON((1 1,1 3,3 3,3 1,1 1))
GEOMETRYCOLLECTION EMPTY
POLYGON((1 1,1 3,3 3,3 1,1 1))

---

ST_EQUALS

Returns 1 if two GEOMETRY objects express the same GEOMETRY.

• Syntax

[Figure 27] ST_EQUALS

[Figure 27] ST_EQUALS

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_EQUALS(A.GEOM,B.GEOM),
     ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POINT'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POINT';
     
ST_EQUALS(A.GEOM,B.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(A.GEOM)
-----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
-----------------------------------------------------------------------------------
POINT(1 1)
POINT(1 1)

---

ST_EXTERIORRING

Returns the outer ring of a POLYGON GEOMETRY object. If the argument is not POLYGON, NULL is returned.

• Syntax

[Figure 28] ST_EXTERIORRING

[Figure 28] ST_EXTERIORRING

• Components

Component	Description
geom	GEOMETRY type POLYGON object.

• Example

SQL> SELECT ST_ASTEXT(ST_EXTERIORRING(GEOM)) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON';

ST_ASTEXT(ST_EXTERIORRING(GEOM))
-----------------------------------------------------------------------------------
LINESTRING(1 1,2 1,2 2,1 2,1 1)
LINESTRING(0 0,0 12,12 12,12 0,0 0)

---

ST_EXPAND

Returns a POLYGON object of the bounding box where a given GEOMETRY object is extended in all directions. If the argument is an empty GEOMETRY object, NULL is returned regardless of its expansion.

• Syntax

[Figure 29] ST_EXPAND

[Figure 29] ST_EXPAND

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num1	Distance to extend in the X direction. Forms a POLYGON object extended to the left and right as much as this distance.
num2	Distance to extend in the Y direction. Forms a POLYGON object extended to the left and right as much as this distance.

• Example

SQL> SELECT ST_ASTEXT(ST_EXPAND(GEOM,1,2)) FROM GIS;

ST_ASTEXT(ST_EXPAND(GEOM,1,2))
-----------------------------------------------------------------------------------
POLYGON((0 -1,0	3,2 3,2	-1,0 -1))
POLYGON((0 -1,0	4,3 4,3	-1,0 -1))
POLYGON((0 -1,0	4,3 4,3	-1,0 -1))
POLYGON((0 -1,0	7,5 7,5	-1,0 -1))
POLYGON((0 -1,0	4,3 4,3	-1,0-1))
POLYGON((-1 -2,-1 14,13	14,13 -2,-1 -2))
POLYGON((0 -1,0 7,6 7,6	-1,0 -1))
POLYGON((0 -1,0 5,4 5,4	-1,0 -1))
GEOMETRYCOLLECTION EMPTY
POLYGON((0 -1,0 5,4 5,4 -1,0 -1))

---

ST_EXTENT

Returns a POLYGON object of the bounding box that includes a GEOMETRY object. Since it is an aggregate function, it returns the result of grouping GEOMETRY objects.

• Syntax

[Figure 30] ST_EXTENT

[Figure 30] ST_EXTENT

• Components

Component	Description
geom_expr	Expression indicating a GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_EXTENT(GEOM)) FROM GIS;

ST_ASTEXT(ST_EXTENT(GEOM))
-----------------------------------------------------------------------------------
POLYGON((0 0,0 12,12 12,12 0,0 0))

---

ST_GEOMCOLLFROMTEXT

Returns a GEOMETRYCOLLECTION object based on given WKT and SRID.

• Syntax

[Figure 31] ST_GEOMCOLLFROMTEXT

[Figure 31] ST_GEOMCOLLFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRYCOLLECTION object in WKT format.
num	Coordinate system information (SRID) of a GEOMETRYCOLLECTION object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(
     POINT(1 1),LINESTRING(2 2,3 3))')) GEOMCOLLFROMTEXT FROM DUAL;
     
GEOMCOLLFROMTEXT
-----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_GEOMCOLLFROMWKB

Returns a GEOMETRYCOLLECTION object based on given WKB and SRID.

• Syntax

[Figure 32] ST_GEOMCOLLFROMWKB

[Figure 32] ST_GEOMCOLLFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRYCOLLECTION object in WKB format.
num	Coordinate system information (SRID) of a GEOMETRYCOLLECTION object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMCOLLFROMWKB(ST_ASBINARY(GEOM)))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'GEOMETRYCOLLECTION';
     
ST_ASTEXT(ST_GEOMCOLLFROMWKB(ST_ASBINARY(GEOM)))
-----------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_GEOMETRYFROMTEXT

Returns a GEOMETRY object based on given WKB and SRID. TEXT in Extended Well-Known Text (EWKT) format that expresses 3D is also supported. Same as ST_GEOMFROMTEXT.

• Syntax

[Figure 33] ST_GEOMETRYFROMTEXT

[Figure 33] ST_GEOMETRYFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY object in WKT format.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMETRYFROMTEXT('MULTIPOINT(1 1,2 2)'))
     FROM DUAL;
     
ST_ASTEXT(ST_GEOMETRYFROMTEXT('MULTIPOINT(11,22)'))
-----------------------------------------------------------------------------------
MULTIPOINT((1 1),(2 2))

---

ST_GEOMETRYN

Returns the N-th GEOMETRY object from GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON objects. If a non-MULTI GEOMETRY object is given as an argument, the GEOMETRY object is returned if N is 1, or NULL is returned if N is not 1. If N is greater than the number of GEOMETRY objects, NULL is returned.

• Syntax

[Figure 34] ST_GEOMETRYN

[Figure 34] ST_GEOMETRYN

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num	Order of the object (N-th).

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMETRYN(GEOM,2)) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) IN
     ('MULTIPOLYGON','MULTILINESTRING','MULTIPOINT','GEOMETRYCOLLECTION');

ST_ASTEXT(ST_GEOMETRYN(GEOM,2))
-----------------------------------------------------------------------------------
POINT(2 2)
LINESTRING(3 3,4 5)
POLYGON((3 3,3 5,5 5,5 3,3 3))
LINESTRING(2 2,3 3)

LINESTRING(2 2,3 3)

---

ST_GEOMETRYTYPE

Returns the type of a GEOMETRY object.

• Syntax

[Figure 35] ST_GEOMETRYTYPE

[Figure 35] ST_GEOMETRYTYPE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_GEOMETRYTYPE(GEOM) FROM GIS;

ST_GEOMETRYTYPE(GEOM)
-----------------------------------------------------------------------------------
POINT
MULTIPOINT
LINESTRING
MULTILINESTRING
POLYGON
POLYGON
MULTIPOLYGON
GEOMETRYCOLLECTION
GEOMETRYCOLLECTION
GEOMETRYCOLLECTION

---

ST_GEOMFROMGEOJSON

Creates a GEOMETRY object from a given GEOJSON expression and returns it. Only JSON figure fragments can be taken as an argument. A complete JSON document cannot be taken as an argument.

• Syntax

[Figure 36] ST_GEOMFROMGEOJSON

[Figure 36] ST_GEOMFROMGEOJSON

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY object in GEOJSON format.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMGEOJSON('{"type":
     "LineString","coordinates": [[30, 10], [10, 30], [40, 40]]}')) FROM DUAL;
     
     
     
ST_ASTEXT(ST_GEOMFROMGEOJSON('{"TYPE":"LINESTRING","COORDINATES":[[30,10],[10,30
-----------------------------------------------------------------------------------
LINESTRING(30 10,10 30,40 40)

---

ST_GEOMFROMGML

Creates a GEOMETRY object from a given GML expression and returns it. Only GML figure fragments can be taken as an argument. A complete GML document cannot be taken as an argument.

• Syntax

[Figure 37] ST_GEOMFROMGML

[Figure 37] ST_GEOMFROMGML

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY object in GML format.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMGML(
     '<gml:LineString><coordinates>1,1 2,2</coordinates></gml:LineString>'))
     FROM DUAL;

ST_ASTEXT(ST_GEOMFROMGML('<GML:LINESTRING><GML:COORDINATES>1,12,2</GML:
-----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

---

ST_GEOMFROMKML

Creates a GEOMETRY object from a given KML expression and returns it. Only KML figure fragments can be taken as an argument. A complete KML document cannot be taken as an argument.

• Syntax

[Figure 38] ST_GEOMFROMKML

[Figure 38] ST_GEOMFROMKML

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY object in KML format.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMKML('<LineString>
     <coordinates>1,1 2,2</coordinates></LineString>')) FROM DUAL;
     
ST_ASTEXT(ST_GEOMFROMKML('<LINESTRING><COORDINATES>1,12,2</COORDINATES>
-----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

---

ST_GEOMFROMTEXT

Returns a GEOMETRY object based on given WKB and SRID. TEXT in Extended Well-Known Text (EWKT) format that expresses 3D is also supported. Same as ST_GEOMETRYFROMTEXT.

• Syntax

[Figure 39] ST_GEOMFROMTEXT

[Figure 39] ST_GEOMFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY object in WKT format.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'))
     FROM DUAL;
     
ST_ASTEXT(ST_GEOMFROMTEXT('MULTIPOINT(11,22)'))
-----------------------------------------------------------------------------------
MULTIPOINT((1 1),(2 2))

---

ST_GEOMFROMTWKB(#)

Returns a given TWKB as a GEOMETRY object.

• Syntax

[Figure 40] ST_GEOMFROMTWKB

[Figure 40] ST_GEOMFROMTWKB

• Components

Component	Description
str	Value of a binary type (BLOB or RAW) where TWKB format for GEOMETRY objects is saved.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMTWKB('02000202020202'))
     FROM DUAL;
     
ST_ASTEXT(ST_GEOMFROMTWKB('02000202020202'))
----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

---

ST_GEOMFROMWKB

Returns given WKB and SRID as a GEOMETRY object.

• Syntax

[Figure 41] ST_GEOMFROMWKB

[Figure 41] ST_GEOMFROMWKB

• Components

Component	Description
str	Value of a binary type (BLOB or RAW) where TWKB format for GEOMETRY objects is saved.
num	Coordinate system information (SRID) of a GEOMETRY object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(GEOM)))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'MULTIPOINT';
     
ST_ASTEXT(ST_GEOMFROMWKB(ST_ASBINARY(GEOM)))
----------------------------------------------------------------------------------
MULTIPOINT((1 1),(2 2))

---

ST_INTERIORRINGN

Returns the N-th inner ring of a POLYGON object. If the argument is not POLYGON, NULL is returned. If N is greater than the number of the inner rings, NULL is returned.

• Syntax

[Figure 42] ST_INTERIORRINGN

[Figure 42] ST_INTERIORRINGN

• Components

Component	Description
geom	GEOMETRY type POLYGON object.
num	Order of the inner ring (N-th).

• Example

SQL> SELECT ST_ASTEXT(ST_INTERIORRINGN(GEOM,1))FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON'
     AND ST_NUMINTERIORRING(GEOM) > 1;
     
ST_ASTEXT(ST_INTERIORRINGN(GEOM,1))
----------------------------------------------------------------------------------
LINESTRING(6 10,6 11,9 11,9 10,6 10)

---

ST_INTERSECTION($)

Returns a GEOMETRY object representing the part shared between GEOMETRY object 1 and GEOMETRY object 2. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information.

• Syntax

[Figure 43] ST_INTERSECTION

[Figure 43] ST_INTERSECTION

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM),
     ST_ASTEXT(ST_INTERSECTION(A.GEOM,B.GEOM))
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POLYGON'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';
     
ST_ASTEXT(A.GEOM)
----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
----------------------------------------------------------------------------------
ST_ASTEXT(ST_INTERSECTION(A.GEOM,B.GEOM))
----------------------------------------------------------------------------------

POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((2 1,1 1,1 2,2 2,2 1))


POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9	6,9 3,6 3))													
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,1 2,2 2,2 1,1 1))

POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9	6,9 3,6 3))
POLYGON((1 1,1 2,2 2,2 1,1 1))

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 11,6 10,9 10,9 11,6 11),(6 6,6 3,9	3,9 6,6 6))
													

---

ST_INTERSECTS($)

Returns 1 if there is a part shared by two GEOMETRY objects. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information. If a given GEOMETRY argument is the GEOMETRYCOLLECTION type, a runtime error occurs.

• Syntax

[Figure 44] ST_INTERSECTS

[Figure 44] ST_INTERSECTS

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_INTERSECTS(A.GEOM,B.GEOM),
     ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POLYGON'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';

ST_INTERSECTS(A.GEOM,B.GEOM)
-----------------------------
ST_ASTEXT(A.GEOM)
----------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
----------------------------------------------------------------------------------
	
                           1
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,2 1,2 2,1 2,1 1))
					
                           1
POLYGON((1 1,2 1,2 2,1 2,1 1))

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))			

                           1										
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((1 1,2 1,2 2,1 2,1 1))

                           1	
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))									
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))													

---

ST_ISCLOSED

Returns 1 if the first point and the last point of a LINESTRING object are the same. It always returns 1 for a POINT or POLYGON object. For MULTI type GEOMETRY objects, it returns 1 when all internal GEOMETRY values are 1.

• Syntax

[Figure 45] ST_ISCLOSED

[Figure 45] ST_ISCLOSED

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.

• Example

SQL> SELECT ST_ISCLOSED(GEOM), ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ISCLOSED(GEOM)
--------------------
ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
                0
LINESTRING(1 1,2 2)

---

ST_ISCOLLECTION

Returns 1 if a given GEOMETRY object is GEOMETRYCOLLECTION or MULTI GEOMETRY.

• Syntax

[Figure 46] ST_ISCOLLECTION

[Figure 46] ST_ISCOLLECTION

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ISCOLLECTION(GEOM), ST_ASTEXT(GEOM) FROM GIS;

ST_ISCOLLECTION(GEOM)
----------------------
ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
                    0
POINT(1 1)

                    1
MULTIPOINT((1 1),(2 2))

                    0
LINESTRING(1 1,2 2)

                    1
MULTILINESTRING((1 1,2 2),(3 3,4 5))

                    0
POLYGON((1 1,2 1,2 2,1 2,1 1))

                    0
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

                    1
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))

                    1
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

                    1
GEOMETRYCOLLECTION EMPTY

                    1
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_ISEMPTY

Returns 1 if a given GEOMETRY object is the EMPTY type.

• Syntax

[Figure 47] ST_ISEMPTY

[Figure 47] ST_ISEMPTY

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(GEOM)
     FROM GIS WHERE ST_ISEMPTY(GEOM) > 0;

ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
GEOMETRYCOLLECTION EMPTY

---

ST_ISRING

Returns 1 if a given LINESTRING object is closed (the first point and the last point are the same) and simple. If a non-LINESTRING GEOMETRY object is given as an argument, 0 is returned

• Syntax

[Figure 48] ST_ISRING

[Figure 48] ST_ISRING

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.

• Example

SQL> SELECT ST_ISRING(GEOM),ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ISRING(GEOM)
------------------
ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
              0
LINESTRING(1 1,2 2)

---

ST_ISSIMPLE

Returns 1 if a GEOMETRY object has no self-intersection or self-contact points. It returns an exception for GEOMETRYCOLLECTION.

• Syntax

[Figure 49] ST_ISSIMPLE

[Figure 49] ST_ISSIMPLE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ISSIMPLE(GEOM),ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) NOT LIKE 'GEOMETRYCOLLECTION';
     
ST_ISSIMPLE(GEOM)
--------------------
ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
                1
POINT(1 1)

                1
MULTIPOINT((1 1),(2 2))

                1
LINESTRING(1 1,2 2)

                1
MULTILINESTRING((1 1,2 2),(3 3,4 5))

                1
POLYGON((1 1,2 1,2 2,1 2,1 1))

                1
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

                1
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))

---

ST_ISVALID

Checks whether a GEOMETRY object is spatially valid (well-formed). It returns 1 if spatially valid, otherwise it returns 0. It returns 1 for EMPTY spatial objects. For invalid objects, it returns the reason why the object is not spatially valid and the location.

• Syntax

[Figure 50] ST_ISVALID

[Figure 50] ST_ISVALID

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ISVALID(ST_GEOMFROMTEXT('POLYGON((0 0,1 1,0 1,1 0,0 0))'))
     FROM DUAL;
     
ST_ISVALID(ST_GEOMFROMTEXT('POLYGON((00,11,01,10,00))'))
--------------------------------------------------------
                                                      0

SQL> SELECT ST_ISVALID(ST_GEOMFROMTEXT('POLYGON((0 0,1 0,1 1,0 1,0 0))'))
     FROM DUAL;
     
ST_ISVALID(ST_GEOMFROMTEXT('POLYGON((00,10,11,01,00))'))
---------------------------------------------------------
                                                      1

---

ST_LENGTH($)

Returns the length of a LINESTRING object. It returns 0 for POINT and POLYGON. For a MULTI type GEOMETRY object, the sum of the calculation results of the internal GEOMETRY objects is returned. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information. If you want faster calculations, change them to calculations for the spherical coordinate system.

• Syntax

[Figure 51] ST_LENGTH

[Figure 51] ST_LENGTH

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.
default	Calculation method. For a rotating body coordinate system, the spheroid is calculated by default. If set to 0, a spherical coordinate system is calculated.

• Example

SQL> SELECT ST_ASTEXT(GEOM), ST_LENGTH(GEOM) FROM GIS
WHERE ST_GEOMETRYTYPE(GEOM) IN ('LINESTRING','MULTILINESTRING');

ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
ST_LENGTH(GEOM)
-------------------
LINESTRING(1 1,2 2)
     1.41421356
     
MULTILINESTRING((1 1,2 2),(3 3,4 5))
     3.65028154

---

ST_LINEFROMTEXT

Returns a LINESTRING object based on given WKT and SRID.

• Syntax

[Figure 52] ST_LINEFROMTEXT

[Figure 52] ST_LINEFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type LINESTRING object in WKT format.
num	Coordinate system information (SRID) of a LINESTRING object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_LINEFROMTEXT('LINESTRING(1 1,2 2)'))
     FROM DUAL; 
     
ST_ASTEXT(ST_LINEFROMTEXT('LINESTRING(11,22)'))

----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

---

ST_LINEFROMWKB

Returns a LINESTRING object based on given WKB and SRID.

• Syntax

[Figure 53] ST_LINEFROMWKB

[Figure 53] ST_LINEFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type LINESTRING object in WKB format.
num	Coordinate system information (SRID) of a LINESTRING object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_LINEFROMWKB(ST_ASBINARY(GEOM)))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(ST_LINEFROMWKB(ST_ASBINARY(GEOM)))

----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

---

ST_MAKEENVELOPE

Returns a rectangular POLYGON object formed with given minimum and maximum values.

• Syntax

[Figure 54] ST_MAKEENVELOPE

[Figure 54] ST_MAKEENVELOPE

• Components

Component	Description
num1	X coordinate of the minimum point (bottom left) of a rectangular POLYGON object.
num2	Y coordinate of the minimum point (bottom left) of a rectangular POLYGON object.
num3	X coordinate of the maximum point (top right) of a rectangular POLYGON object.
num4	Y coordinate of the maximum point (top right) of a rectangular POLYGON object.

• Example

SQL> SELECT ST_ASTEXT(ST_MAKEENVELOPE(-1,1,2,3)) FROM DUAL;

ST_ASTEXT(ST_MAKEENVELOPE(-1,1,2,3))
----------------------------------------------------------------------------------
POLYGON((-1 1,-1 3,2 3,2 1,-1 1))

---

ST_MAKELINE

Takes POINT, MULTIPOINT, or LINESTRING as an argument and returns a LINESTRING object. Since it is an aggregate function, it returns the result of grouping GEOMETRY objects. You can set the type of the LINESTRING object by using an ORDER BY statement.

• Syntax

[Figure 55] ST_MAKELINE

[Figure 55] ST_MAKELINE

• Components

Component	Description
geom_expr	Expression indicating a GEOMETRY object. Must be a POINT, LINESTRING, or MULTIPOINT type GEOMETRY object.
order_by_clause	How to sort GEOMETRY objects.

• Example

SQL> SELECT ST_ASTEXT(ST_MAKELINE(A.GEOM, B.GEOM)) FROM GIS A, GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POINT'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(ST_MAKELINE(A.GEOM,B.GEOM))
----------------------------------------------------------------------------------
LINESTRING(1 1,2 2)

• Example The following uses a table's geometry column as an argument.

SQL> SELECT ST_ASTEXT(ST_MAKELINE(GEOM)) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) IN ('POINT','MULTIPOINT','LINESTRING');

ST_ASTEXT(ST_MAKELINE(GEOM))
----------------------------------------------------------------------------------
LINESTRING(1 1,1 1,2 2,1 1,2 2)

• Example The following uses order_by_clause.

SQL> SELECT ST_ASTEXT(ST_MAKELINE(GEOM ORDER BY ID DESC))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) IN ('POINT','LINESTRING','MULTIPOINT');
     
ST_ASTEXT(ST_MAKELINE(GEOMORDERBYIDDESC))
----------------------------------------------------------------------------------
LINESTRING(1 1,2 2,1 1,2 2,1 1)

---

ST_MAKEPOINT

Returns a 2D POINT object.

• Syntax

[Figure 56] ST_MAKEPOINT

[Figure 56] ST_MAKEPOINT

• Components

Component	Description
num1	X coordinate of a POINT object.
num2	Y coordinate of a POINT object.

• Example

SQL> SELECT ST_ASTEXT(ST_MAKEPOINT(1,2)) FROM DUAL;

ST_ASTEXT(ST_MAKEPOINT(1,2))
----------------------------------------------------------------------------------
POINT(1 2)

---

ST_MAKEPOLYGON

Returns a POLYGON object formed with a given structure. The argument must be a closed LINESTRING.

• Syntax

[Figure 57] ST_MAKEPOLYGON

[Figure 57] ST_MAKEPOLYGON

• Components

Component	Description
geom1	GEOMETRY type LINESTRING object representing the SHELL of a POLYGON object to be formed.
geom2	GEOMETRY type LINESTRING object representing the INTERIOR RING of a POLYGON object to be formed. If there are multiple INTERIOR RINGs, it must be a GEOMETRY type MULTILINESTRING object.

• Example

SQL> SELECT ST_ASTEXT(ST_MAKEPOLYGON(ST_GEOMFROMTEXT(
     'LINESTRING(0 0,1 0,1 1,0 1,0 0)'))) FROM DUAL;
     
ST_ASTEXT(ST_MAKEPOLYGON(ST_GEOMFROMTEXT('LINESTRING(00,10,11,01,00)')))
----------------------------------------------------------------------------------
POLYGON((0 0,1 0,1 1,0 1,0 0))

SQL> SELECT ST_ASTEXT(ST_MAKEPOLYGON(
     ST_GEOMFROMTEXT('LINESTRING(0 0,10 0,10 10,0 10,0 0)'),
     ST_GEOMFROMTEXT('MULTILINESTRING((0 0,4 0,4 4,0 4,0 0),(5 5,7 5,7 7,5 7,5 5))
     '))) MAKEPOLYGON FROM DUAL;
     
MAKEPOLYGON
----------------------------------------------------------------------------------
POLYGON((0 0,10 0,10 10,0 10,0 0),(0 0,4 0,4 4,0 4,0 0),(5 5,7 5,7 7,5 7,5 5))

---

ST_MAKEVALID(#)

Changes an invalid GEOMETRY object to a valid object without loss of points and returns it. If a given object is already valid, it is returned as is.

• Syntax

[Figure 58] ST_MAKEVALID

[Figure 58] ST_MAKEVALID

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_MAKEVALID(ST_GEOMFROMTEXT(
     'POLYGON ((0 0,1 0,0 1,1 1,0 0))'))) FROM DUAL;
     
ST_ASTEXT(ST_MAKEVALID(ST_GEOMFROMTEXT('POLYGON((00,10,01,11,00))')))
----------------------------------------------------------------------------------
MULTIPOLYGON(((0.5 0.5,1 0,0 0,0.5 0.5)),((0.5 0.5,0 1,1 1,0.5 0.5)))

SQL> SELECT ST_ASTEXT(ST_MAKEVALID(ST_GEOMFROMTEXT(
     'POLYGON((0 0,1 0,1 1,0 1,0 0))'))) FROM DUAL;
     
ST_ASTEXT(ST_MAKEVALID(ST_GEOMFROMTEXT('POLYGON((00,10,11,01,00))')))
----------------------------------------------------------------------------------
POLYGON((0 0,1 0,1 1,0 1,0 0))

---

ST_MAXX

Returns the larger value of the X coordinate values of the minimum bounding rectangle that surrounds a given spatial object.

• Syntax

[Figure 59] ST_MAXX

[Figure 59] ST_MAXX

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_MAXX(ST_GEOMFROMTEXT('LINESTRING(-1 0,3 2)')) FROM DUAL;

ST_MAXX(ST_GEOMFROMTEXT('LINESTRING(00,32)'))
---------------------------------------------
                                            3

---

ST_MAXY

Returns the larger value of the Y coordinate values of the minimum bounding rectangle that surrounds a given spatial object.

• Syntax

[Figure 60] ST_MAXY

[Figure 60] ST_MAXY

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_MAXY(ST_GEOMFROMTEXT('LINESTRING(-1 0,3 2)')) FROM DUAL;

ST_MAXY(ST_GEOMFROMTEXT('LINESTRING(00,32)'))
---------------------------------------------
                                            2

---

ST_MINX

Returns the smaller value of the X coordinate values of the minimum bounding rectangle that surrounds a given spatial object.

• Syntax

[Figure 61] ST_MINX

[Figure 61] ST_MINX

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_MINX(ST_GEOMFROMTEXT('LINESTRING(-1 0,3 2)')) FROM DUAL;

ST_MINX(ST_GEOMFROMTEXT('LINESTRING(-10,32)'))
----------------------------------------------
                                            -1

---

ST_MINY

Returns the smaller value of the Y coordinate values of the minimum bounding rectangle that surrounds a given spatial object.

• Syntax

[Figure 62] ST_MINY

[Figure 62] ST_MINY

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_MINY(ST_GEOMFROMTEXT('LINESTRING(0 -1,3 2)')) FROM DUAL;

ST_MINY(ST_GEOMFROMTEXT('LINESTRING(0-1,32)'))
----------------------------------------------
                                            -1

---

ST_MLINEFROMTEXT

Returns a MULTILINESTRING object based on given WKT and SRID.

• Syntax

[Figure 63] ST_MLINEFROMTEXT

[Figure 63] ST_MLINEFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTILINESTRING object in WKT format.
num	Coordinate system information (SRID) of a MULTILINESTRING object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MLINEFROMTEXT(
     'MULTILINESTRING((1 1,2 2),(3 3,4 5))')) FROM DUAL;
     
ST_ASTEXT(ST_MLINEFROMTEXT('MULTILINESTRING((11,22),(33,45))'))
----------------------------------------------------------------------------------
MULTILINESTRING((1 1,2 2),(3 3,4 5))

---

ST_MLINEFROMWKB

Returns a MULTILINESTRING object based on given WKB and SRID.

• Syntax

[Figure 64] ST_MLINEFROMWKB

[Figure 64] ST_MLINEFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTILINESTRING object in WKB format.
num	Coordinate system information (SRID) of a MULTILINESTRING object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MLINEFROMWKB(ST_ASBINARY(GEOM)))
      FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'MULTILINESTRING';

ST_ASTEXT(ST_MLINEFROMWKB(ST_ASBINARY(GEOM)))
----------------------------------------------------------------------------------
MULTILINESTRING((1 1,2 2),(3 3,4 5))

---

ST_MPOINTFROMTEXT

Returns a MULTIPOINT object based on given WKT and SRID.

• Syntax

[Figure 65] ST_MPOINTFROMTEXT

[Figure 65] ST_MPOINTFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTIPOINT object in WKT format.
num	Coordinate system information (SRID) of a MULTIPOINT object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MPOINTFROMTEXT('MULTIPOINT(10 10,20 20)'))
     FROM DUAL;
     
ST_ASTEXT(ST_MPOINTFROMTEXT('MULTIPOINT(1010,2020)'))
----------------------------------------------------------------------------------
MULTIPOINT((10 10),(20 20))

---

ST_MPOINTFROMWKB

Returns a MULTIPOINT object based on given WKB and SRID.

• Syntax

[Figure 66] ST_MPOINTFROMWKB

[Figure 66] ST_MPOINTFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTIPOINT object in WKB format.
num	Coordinate system information (SRID) of a MULTIPOINT object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MPOINTFROMWKB(ST_ASBINARY(GEOM)))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'MULTIPOINT';
     
ST_ASTEXT(ST_MPOINTFROMWKB(ST_ASBINARY(GEOM)))
----------------------------------------------------------------------------------
MULTIPOINT((1 1),(2 2))

---

ST_MPOLYFROMTEXT

Returns a MULTIPOLYGON object based on given WKT and SRID.

• Syntax

[Figure 67] ST_MPOLYFROMTEXT

[Figure 67] ST_MPOLYFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTIPOLYGON object in WKT format.
num	Coordinate system information (SRID) of a MULTIPOLYGON object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MPOLYFROMTEXT('MULTIPOLYGON
     (((10 10,10 20,20 20,20 15,10 10)), ((60 60,70 70,80 60,60 60)))'))
     MPOLYFROMTEXT FROM DUAL;
     
MPOLYFROMTEXT
----------------------------------------------------------------------------------
MULTIPOLYGON(((10 10,10 20,20 20,20 15,10 10)),((60 60,70 70,80 60,60 60)))

---

ST_MPOLYFROMWKB

Returns a MULTIPOLYGON object based on given WKB and SRID.

• Syntax

[Figure 68] ST_MPOLYFROMWKB

[Figure 68] ST_MPOLYFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type MULTIPOLYGON object in WKB format.
num	Coordinate system information (SRID) of a MULTIPOLYGON object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_MPOLYFROMWKB(ST_ASBINARY(GEOM)))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'MULTIPOLYGON';

ST_ASTEXT(ST_MPOLYFROMWKB(ST_ASBINARY(GEOM)))
----------------------------------------------------------------------------------
MULTIPOLYGON(((1 1,2 1,2 2,1 2,1 1)),((3 3,3 5,5 5,5 3,3 3)))

---

ST_MULTI

Returns GEOMETRY as MULTI type GEOMETRY. If a MULTI type object is given as an argument, it is returned as is.

• Syntax

[Figure 69] ST_MULTI

[Figure 69] ST_MULTI

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_MULTI(GEOM)) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(ST_MULTI(GEOM))
----------------------------------------------------------------------------------
MULTILINESTRING((1 1,2 2))

---

ST_NN($)

Used in a conditional clause. It returns 1 for rows that include a GEOMETRY object that is closest to a GEOMETRY object given as an argument for a table's GEOMETRY column. Rows are returned in order of shortest distance to longest.

• Syntax

[Fiture 70] ST_NN

[Figure 70] ST_NN

• Components

Component	Description
geom_expr	GEOMETRY type GEOMETRY object. Must be a GEOMETRY type column where RTREE is built.
geom	GEOMETRY type GEOMETRY object.
str	Additional information for this function. CHAR, VARCAHR, NCHAR, or NVARCHAR type in WKT format. str consists of the following 3 parameters delimited by a comma (,). – res_num: Number of GEOMETRY objects for which 1 is returned. If not specified, 1 is returned for all columns. – batch_size: Number of GEOMETRY objects to search from RTREE at once. Specify a value determined to be able to perform an efficient operation on a specific value. If not specified, the default value set for the system is used. – distance: Distance set as a bottleneck. 1 is returned for rows within a distance less than or equal to this value. – unit : Unit of the distance set as a bottleneck.

• Example

SQL> SELECT ID FROM GIS WHERE ST_NN(GEOM,
     ST_GEOMFROMTEXT('POINT(0 0)'),'RES_NUM=3,BATCH_SIZE=10')=1;

         ID
------------
         106
         101
         102

---

ST_NPOINTS

Returns the number of POINTs in GEOMETRY. Same as ST_NUMPOINTS.

• Syntax

[Figure 71] ST_NPOINTS

[Figure 71] ST_NPOINTS

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_NPOINTS(GEOM) FROM GIS;

ST_NPOINTS(GEOM)
----------------
              1
              2
              2
              4
              5
             15
             10
              3
              0
              3

---

ST_NUMGEOMETRIES

Returns the number of GEOMETRY objects from GEOMETRYCOLLECTION, MULTIPOING, MULTILINESTRING, and MULTIPOLYGON objects. If a non-MULTI GEOMETRY object is given as an argument, 1 is returned.

• Syntax

[Figure 72] ST_NUMGEOMETRIES

[Figure 72] ST_NUMGEOMETRIES

• Components

Component	Description
geom	GEOMETRY type COLLECTION object.

• Example

SQL> SELECT ST_NUMGEOMETRIES(GEOM),ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'GEOMETRYCOLLECTION';

ST_NUMGEOMETRIES(GEOM)
------------------------------
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
                     2
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

                     0
GEOMETRYCOLLECTION EMPTY

                     2
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))

---

ST_NUMINTERIORRING

Returns the number of inner rings of a POLYGON object. If the argument is not POLYGON, NULL is returned.

• Syntax

[Figure 73] ST_NUMINTERIORRING

[Figure 73] ST_NUMINTERIORRING

• Components

Component	Description
geom	GEOMETRY type POLYGON object.

• Example

SQL> SELECT ST_NUMINTERIORRING(GEOM), ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON';
     
ST_NUMINTERIORRING(GEOM)
------------------------------
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
                     0
POLYGON((1 1,2 1,2 2,1 2,1 1))

                     2
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

---

ST_NUMPOINTS

Returns the number of POINTs in GEOMETRY. Same as ST_NPOINTS.

• Syntax

[Figure 74] ST_NUMPOINTS

[Figure 74] ST_NUMPOINTS

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_NUMPOINTS(GEOM),ST_ASTEXT(GEOM) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';

ST_NUMPOINTS(GEOM)
-------------------
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
                 2
LINESTRING(1 1,2 2)

---

ST_OVERLAPS

Returns 1 if two given GEOMETRY objects have the same dimension, an overlapping part exists, and one object is not completely included in another object.

• Syntax

[Figure 75] ST_OVERLAPS

[Figure 75] ST_OVERLAPS

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_OVERLAPS(A.GEOM,B.GEOM), ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'LINESTRING'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'MULTILINESTRING';
     
ST_OVERLAPS(A.GEOM,B.GEOM)
----------------------------
ST_ASTEXT(A.GEOM)
---------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
---------------------------------------------------------------------------------
                         0
LINESTRING(1 1,2 2)
MULTILINESTRING((1 1,2 2),(3 3,4 5))

---

ST_POINT

Returns a 2D POINT object.

• Syntax

[Figure 76] ST_POINT

[Figure 76] ST_POINT

• Components

Component	Description
num1	X coordinate of a POINT object.
num2	Y coordinate of a POINT object.

• Example

SQL> SELECT ST_ASTEXT(ST_POINT(1,2)) FROM DUAL;

ST_ASTEXT(ST_POINT(1,2))
---------------------------------------------------------------------------------
POINT(1 2)

---

ST_POINTFROMTEXT

Returns a POINT object based on given WKT and SRID.

• Syntax

[Figure 77] ST_POINTFROMTEXT

[Figure 77] ST_POINTFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type POINT object in WKT format.
num	Coordinate system information (SRID) of a POINT object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_POINTFROMTEXT('POINT(10 10)')) FROM DUAL;

ST_ASTEXT(ST_POINTFROMTEXT('POINT(1010)'))
---------------------------------------------------------------------------------
POINT(10 10)

---

ST_POINTFROMWKB

Returns a POINT object based on given WKB and SRID.

• Syntax

[Figure 78] ST_POINTFROMWKB

[Figure 78] ST_POINTFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type POINT object in WKB format.
num	Coordinate system information (SRID) of a POINT object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_POINTFROMWKB(ST_ASBINARY(GEOM))) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POINT';
     
ST_ASTEXT(ST_POINTFROMWKB(ST_ASBINARY(GEOM)))
---------------------------------------------------------------------------------
POINT(1 1)

---

ST_POINTN

Returns the N-th point of a LINESTRING object as a POINT object. If the argument is not LINESTRING, NULL is returned. -1 means the last POINT of the LINESTRING object. It is counted in the opposite direction, and a POINT object matching the negative argument is returned.

• Syntax

[Figure 79] ST_POINTN

[Figure 79] ST_POINTN

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.
num	Order of the point (N-th).

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_ASTEXT(ST_POINTN(GEOM,1)) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
ST_ASTEXT(ST_POINTN(GEOM,1))
---------------------------------------------------------------------------------
LINESTRING(1 1,2 2)
POINT(1 1)

---

ST_POINTONSURFACE

Returns an arbitrary POINT object guaranteed to be placed on a spatial object.

• Syntax

[Figure 80] ST_POINTONSURFACE

[Figure 80] ST_POINTONSURFACE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_POINTONSURFACE(GEOM)),ST_ASTEXT(GEOM)
     FROM GISWHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON';
     
ST_ASTEXT(ST_POINTONSURFACE(GEOM))
---------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
POINT(1.5 1.5)
POLYGON((1 1,2 1,2 2,1 2,1 1))

POINT(6 8)
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

---

ST_POLYFROMTEXT

Returns a POLYGON object based on given WKT and SRID.

• Syntax

[Figure 81] ST_POLYFROMTEXT

[Figure 81] ST_POLYFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type GEOMETRY POLYGON object in WKT format.
num	Coordinate system information (SRID) of a POLYGON object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_POLYFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'))
     FROM DUAL;

ST_ASTEXT(ST_POLYFROMTEXT('POLYGON((11,21,22,12,11))')) \
---------------------------------------------------------------------------------
POLYGON((1 1,2 1,2 2,1 2,1 1))

---

ST_POLYFROMWKB

Returns a POLYGON object based on given WKB and SRID.

• Syntax

[Figure 82] ST_POLYFROMWKB

[Figure 82] ST_POLYFROMWKB

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type POLYGON object in WKB format.
num	Coordinate system information (SRID) of a POLYGON object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_POLYFROMWKB(ST_ASBINARY(GEOM))) FROM GIS
     WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'POLYGON';
     
ST_ASTEXT(ST_POLYFROMWKB(ST_ASBINARY(GEOM)))
---------------------------------------------------------------------------------
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

---

ST_POLYGON

Returns a POLYGON object formed with given LINESTRING and SRID. The argument must be a closed LINESTRING.

• Syntax

[Figure 83] ST_POLYGON

[Figure 83] ST_POLYGON

• Components

Component	Description
geom	GEOMETRY type LINESTRING object representing the outer SHELL of a POLYGON object to be formed. To form POLYGON with an internal hole, use the ST_MAKEPOLYGON function.
num	Coordinate system information (SRID) of a POLYGON object to be formed.

• Example

SQL> SELECT ST_ASTEXT(ST_POLYGON(ST_GEOMFROMTEXT(
     'LINESTRING(0 0,1 0,1 1,0 1,0 0)'), 4326)) FROM DUAL;
     
ST_ASTEXT(ST_POLYGON(ST_GEOMFROMTEXT('LINESTRING(00,10,11,01,00)'),4326))
---------------------------------------------------------------------------------
POLYGON((0 0,1 0,1 1,0 1,0 0))

---

ST_POLYGONFROMTEXT

Returns a POLYGON object based on given WKT and SRID.

• Syntax

[Figure 84] ST_POLYGONFROMTEXT

[Figure 84] ST_POLYGONFROMTEXT

• Components

Component	Description
str	CHAR, VARCAHR, NCHAR, or NVARCHAR type POLYGON object in WKT format.
num	Coordinate system information (SRID) of a POLYGON object. If SRID is not given, 0 (system's default coordinate system) is used.

• Example

SQL> SELECT ST_ASTEXT(ST_POLYGONFROMTEXT('POLYGON((1 1,2 1,2 2,1 2,1 1))'))
     FROM DUAL;
     
ST_ASTEXT(ST_POLYGONFROMTEXT('POLYGON((11,21,22,12,11))'))
---------------------------------------------------------------------------------
POLYGON((1 1,2 1,2 2,1 2,1 1))

---

ST_POLYGONIZE(#)

Returns POLYGON objects that can be formed from segment components of GEOMETRY sets. Since it is an aggregate function, it returns the result of grouping GEOMETRY objects.

• Syntax

[Figure 85] ST_POLYGONIZE

[Figure 85] ST_POLYGONIZE

• Components

Component	Description
geom_expr	Expression indicating a GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_POLYGONIZE(GEOM)) FROM GIS;

ST_ASTEXT(ST_POLYGONIZE(GEOM))
---------------------------------------------------------------------------------
GEOMETRYCOLLECTION(POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,9 10,9 11,6 11,6 10),
(6 3,9 3,9 6,6 6,6 3),(3 3,5 3,5 5,3 5,3 3)),POLYGON((6 10,6 11,9 11,9 10,6 10))
,POLYGON((6 3,6 6,9 6,9 3,6 3)),POLYGON((3 3,3 5,5 5,5 3,3 3)))

---

ST_PROJECT(#)

Returns a POINT object projected from a POINT object in a given distance in meters and in a direction (azimuth) in arc degrees. Since the direction of the arc is set based on the azimuth, the east is expressed as /2, the south as , and the west as 3/2. Its arguments must be GEOMETRY objects on a rotating body coordinate system. Otherwise, it returns an exception. If a GEOMETRY object of a non-POINT type is inserted, an exception is returned.

• Syntax

[Figure 86] ST_PROJECT

[Figure 86] ST_PROJECT

• Components

Component	Description
geom	GEOMETRY type POINT object.
num1	Distance in meters.
num2	Azimuth in arc degrees.

• Example

SQL> SELECT ST_ASTEXT(ST_PROJECT(
     ST_GEOMFROMTEXT('POINT(0 0)',4326),100000, 3.141592)) FROM DUAL;
     
ST_ASTEXT(ST_PROJECT(ST_GEOMFROMTEXT('POINT(00)',4326),100000,3.141592))
---------------------------------------------------------------------------------
POINT(5.87178138550817e-07 -0.90436872291257)

---

ST_RELATE

Returns 1 if GEOMETRY object 1 and GEOMETRY object 2 satisfy a given relationship. If a given GEOMETRY argument is the GEOMETRYCOLLECTION type, a runtime error occurs.

• Syntax

[Figure 87] ST_RELATE

[Figure 87] ST_RELATE

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.
str	CHAR, VARCAHR, NCHAR, or NVARCHAR string that represents the relationship between two objects.

• Example

SQL> SELECT ST_RELATE(A.GEOM,B.GEOM,'TTTTTTTTT'),
     ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'LINESTRING'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'LINESTRING';

ST_RELATE(A.GEOM,B.GEOM,'TTTTTTTTT')
-------------------------------------
ST_ASTEXT(A.GEOM)
---------------------------------------------------------------------------------
ST_ASTEXT(B.GEOM)
---------------------------------------------------------------------------------
                                   0
LINESTRING(1 1,2 2)
LINESTRING(1 1,2 2)

---

ST_REVERSE

Returns a GEOMETRY object whose vertex order is reversed for a given GEOMETRY object.

• Syntax

[Figure 88] ST_REVERSE

[Figure 88] ST_REVERSE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(ST_REVERSE(GEOM)) FROM GIS;

ST_ASTEXT(ST_REVERSE(GEOM))
---------------------------------------------------------------------------------
POINT(1 1)
MULTIPOINT((1 1),(2 2))
LINESTRING(2 2,1 1)
MULTILINESTRING((2 2,1 1),(4 5,3 3))
POLYGON((1 1,1 2,2 2,2 1,1 1))
POLYGON((0 0,12 0,12 12,0 12,0 0),(6 10,9 10,9 11,6 11,6 10),(6 3,9 3,9 6,6 6,6 3))
MULTIPOLYGON(((1 1,1 2,2 2,2 1,1 1)),((3 3,5 3,5 5,3 5,3 3)))
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(3 3,2 2))
GEOMETRYCOLLECTION EMPTY
GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(3 3,2 2))

---

ST_SETSRID

Sets and returns SRID of a given GEOMETRY object.

• Syntax

[Figure 89] ST_SETSRID

[Figure 89] ST_SETSRID

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num	Coordinate system information (SRID) to set.

• Example

SQL> SELECT ST_SRID(ST_SETSRID(ST_GEOMFROMTEXT('POINT(0 0)'), 4326))
     FROM DUAL;
     
ST_SRID(ST_SETSRID(ST_GEOMFROMTEXT('POINT(00)'),4326))
------------------------------------------------------
                                                4326

---

ST_SPLIT(#)

Returns a GEOMETRYCOLLECTION object obtained by dividing GEOMETRY by base GEOMETRY. LINESTRING can be divided by (MULTI)POINT, (MULTI)LINESTRING, (MULTI)POLYGON, and POLYGON can be divided by (MULTI)LINESTRING.

• Syntax

[Figure 90] ST_SPLIT

[Figure 90] ST_SPLIT

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object. GEOMETRY to be divided.
geom2	GEOMETRY type GEOMETRY object. Base GEOMETRY.

• Example

SQL> SELECT ST_ASTEXT(ST_SPLIT(ST_GEOMFROMTEXT(
     'LINESTRING(0 0,2 2)'),ST_GEOMFROMTEXT('POINT(1 1)'))) SPLIT FROM DUAL;

SPLIT
---------------------------------------------------------------------------------
GEOMETRYCOLLECTION(LINESTRING(0 0,1 1),LINESTRING(1 1,2 2))

---

ST_SRID

Returns SRID of a given GEOMETRY object.

• Syntax

[Figure 91] ST_SRID

[Figure 91] ST_SRID

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ID,ST_SRID(GEOM) FROM GIS;

         ID	ST_SRID(GEOM)
------------    --------------
         101		    0
         102		    0
         103		    0
         104		    0
         105		    0
         106		    0
         107		    0
         108		    0
         109		    0
         110		    0

---

ST_STARTPOINT

Returns the first POINT of LINESTRING. If the argument is not LINESTRING, NULL is returned.

• Syntax

[Figure 92] ST_STARTPOINT

[Figure 92] ST_STARTPOINT

• Components

Component	Description
geom	GEOMETRY type LINESTRING object.

• Example

SQL> SELECT ST_astext(geom),ST_ASTEXT(ST_STARTPOINT(GEOM))
     FROM GIS WHERE ST_GEOMETRYTYPE(GEOM) LIKE 'LINESTRING';
     
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
ST_ASTEXT(ST_STARTPOINT(GEOM))
---------------------------------------------------------------------------------
LINESTRING(1 1,2 2)
POINT(1 1)

---

ST_SYMDIFFERENCE($)

Returns a GEOMETRY object representing the area excluding intersection of two given GEOMETRY objects. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information.

• Syntax

[Figure 93] ST_SYMDIFFERENCE

[Figure 93] ST_SYMDIFFERENCE

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM),
     ST_ASTEXT(ST_SYMDIFFERENCE(A.GEOM,B.GEOM)) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POLYGON'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';

ST_ASTEXT(A.GEOM)
---------------------------------------------------------------------------------     
ST_ASTEXT(B.GEOM)
---------------------------------------------------------------------------------
ST_ASTEXT(ST_SYMDIFFERENCE(A.GEOM,B.GEOM))
---------------------------------------------------------------------------------

POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,2 1,2 2,1 2,1 1))
GEOMETRYCOLLECTION EMPTY	


POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))													
POLYGON((1 1,2 1,2 2,1 2,1 1))										
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3)),(1 1,2 1,2 2,1 2,1 1)	



POLYGON((1 1,2 1,2 2,1 2,1 1))	
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(1 1,2 1,2 2,1 2,1 1),(6 10,9 10,9 11,6 11,6 1 0),(6 3,6 6,9 6,9 3,6 3)				

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
GEOMETRYCOLLECTION EMPTY				

---

ST_TOUCHES

Returns 1 when two given GEOMETRY objects share one or more points, but there is no intersection. If a given GEOMETRY argument is the GEOMETRYCOLLECTION type, a runtime error occurs.

• Syntax

[Figure 94] ST_TOUCHES

[Figure 94] ST_TOUCHES

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_TOUCHES(A.GEOM,B.GEOM), ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'LINESTRING'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';
     
ST_TOUCHES(A.GEOM,B.GEOM)
---------------------------
ST_ASTEXT(A.GEOM)
--------------------------------------------------------------------------------- 
ST_ASTEXT(B.GEOM)
--------------------------------------------------------------------------------- 
                         0
LINESTRING(1 1,2 2)
POLYGON((1 1,2 1,2 2,1 2,1 1))

                         0
LINESTRING(1 1,2 2)
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6 3,6 6,9 6,9 3,6 3))

---

ST_TRANSFORM(#)

Returns a GEOMETRY object that has an input GEOMETRY's coordinate converted to another spatial reference system. SRID and PROJ4TEXT are used as arguments. ST_TRANSFORM converts coordinates, but ST_SETSRI only sets SRID without converting coordinates.

• Syntax

[Figure 95] ST_TRANSFORM

[Figure 95] ST_TRANSFORM

• Components

- case1

Component	Description
geom	GEOMETRY type GEOMETRY object.
num	Coordinate system information (SRID) to convert.

- case2

Component	Description
geom	GEOMETRY type GEOMETRY object.
str	PROJ4TEXT of coordinate system information to convert.

- case3

Component	Description
geom	GEOMETRY type GEOMETRY object.
str	PROJ4TEXT of coordinate system information before conversion.
str	PROJ4TEXT of coordinate system information to convert.

- case4

Component	Description
geom	GEOMETRY type GEOMETRY object.
str	PROJ4TEXT of coordinate system information before conversion.
num	Coordinate system information (SRID) to convert.

• Example

SQL> SELECT ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT(
     'LINESTRING(0 0,1 1)',4326),2249)) FROM DUAL;
     
ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('LINESTRING(00,11)',4326),2249))
--------------------------------------------------------------------------------- 
LINESTRING(30250865.9714116 -610981.481754334,30214669.4455592 41989.5268075016)

SQL> SELECT ST_ASTEXT(ST_TRANSFORM(ST_GEOMFROMTEXT('LINESTRING(0 0,1 1)'),
     '+proj=longlat +ellps=WGS72 +towgs84=0,0,1.9,0,0,0.814,-0.38 +no_defs',2249))
     
     TRANSFORM FROM DUAL;
     
TRANSFORM
--------------------------------------------------------------------------------- 
LINESTRING(30250930.6174208 -610897.702286165,30214732.3446824 42073.100013002)

---

ST_TRANSLATE(#)

Returns GEOMETRY shifted by a given offset.

• Syntax

[Figure 96] ST_TRANSLATE

[Figure 96] ST_TRANSLATE

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
num1	Offset to shift in the X direction.
num2	Offset to shift in the Y direction.

• Example

SQL> SELECT ST_ASTEXT(ST_TRANSLATE(
     ST_GEOMFROMTEXT('LINESTRING(0 0,1 0)'),1,2)) FROM DUAL;
     
ST_ASTEXT(ST_TRANSLATE(ST_GEOMFROMTEXT('LINESTRING(00,10)'),1,2))
--------------------------------------------------------------------------------- 
LINESTRING(1 2,2 2)

---

ST_UNION($)

Returns a GEOMETRY object that sums the areas of two given GEOMETRY objects. It can perform calculations on GEOMETRY objects on the rotating body coordinate system by using coordinate system information.

• Syntax

[Figure 97] ST_UNION

[Figure 97] ST_UNION

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM),
     ST_ASTEXT(ST_UNION(A.GEOM,B.GEOM)) FROM GIS A,GIS B
     WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POLYGON'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';
     
ST_ASTEXT(A.GEOM)
--------------------------------------------------------------------------------- 
ST_ASTEXT(B.GEOM)
--------------------------------------------------------------------------------- 
ST_ASTEXT(ST_SYMDIFFERENCE(A.GEOM,B.GEOM))
--------------------------------------------------------------------------------- 
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((1 1,2 1,2 2,1 2,1 1))
GEOMETRYCOLLECTION EMPTY

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))													
POLYGON((1 1,2 1,2 2,1 2,1 1))										
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,9 10,9 11,6 11,6 10),(6	3,9 3,9	6,6 6,6 3),(1 1,2 1,2 2,1 2,1 1))


POLYGON((1 1,2 1,2 2,1 2,1 1))	
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))	
POLYGON((0 0,0 12,12 12,12 0,0 0),(1 1,2 1,2 2,1 2,1 1),(6 10,9 10,9 11,6 11,6 1 0),(6 3,9 3,9 6,6 6,6 3))				

POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
GEOMETRYCOLLECTION EMPTY				

---

ST_WITHIN

Takes two GEOMETRY objects and returns 1 if the first object completely contains the second object.

• Syntax

[Figure 98] ST_WITHIN

[Figure 98] ST_WITHIN

• Components

Component	Description
geom1	GEOMETRY type GEOMETRY object.
geom2	GEOMETRY type GEOMETRY object.

• Example

SQL> SELECT ST_WITHIN(A.GEOM,B.GEOM), ST_ASTEXT(A.GEOM),ST_ASTEXT(B.GEOM)
     FROM GIS A,GIS B WHERE ST_GEOMETRYTYPE(A.GEOM) LIKE 'POLYGON'
     AND ST_GEOMETRYTYPE(B.GEOM) LIKE 'POLYGON';

ST_WITHIN(A.GEOM,B.GEOM)
------------------------ 
ST_ASTEXT(A.GEOM)
--------------------------------------------------------------------------------- 
ST_ASTEXT(B.GEOM)
--------------------------------------------------------------------------------- 
                       1
POLYGON((1 1,2 1,2 2,1 2,1 1))	
POLYGON((1 1,2 1,2 2,1 2,1 1))	
		
                       0		
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((1 1,2 1,2 2,1 2,1 1))
		
                       1	
POLYGON((1 1,2 1,2 2,1 2,1 1))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
		
                       1											
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))
POLYGON((0 0,0 12,12 12,12 0,0 0),(6 10,6 11,9 11,9 10,6 10),(6	3,6 6,9	6,9 3,6 3))														

---

ST_X

Returns the X coordinate of a POINT object. If the argument is not POINT, NULL is returned.

• Syntax

[Figure 99] ST_X

[Figure 99] ST_X

• Components

Component	Description
point	GEOMETRY type POINT object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_X(GEOM) FROM GIS WHERE ST_GEOMETRYTYPE(GEOM)
     LIKE 'POINT';
     
ST_ASTEXT(GEOM)
--------------------------------------------------------------------------------- 
ST_X(GEOM)
------------
POINT(1 1)
        1

---

ST_Y

Returns the Y coordinate of a POINT object. If the argument is not POINT, NULL is returned.

• Syntax

[Figure 100] ST_Y

[Figure 100] ST_Y

• Components

Component	Description
point	GEOMETRY type POINT object.

• Example

SQL> SELECT ST_ASTEXT(GEOM),ST_Y(GEOM) FROM GIS WHERE ST_GEOMETRYTYPE(GEOM)
      LIKE 'POINT';
      
ST_ASTEXT(GEOM)
---------------------------------------------------------------------------------
ST_Y(GEOM)
-----------
POINT(1 1)
        1

---

DBMS_GEOM Package

Tibero Spatial supports additional functions through the DBMS_GEOM package.

ST_DUMPPOINTS

Returns a set consisting of GEOMETRY and an integer array (path).

Sets all POINTs of an argument GEOMETRY object and an integer array. For example, if LINESTRING is given as an argument, a path is set to {i}, where i is the i-th POINT of the LINESTRING. If POLYGON is given as argument, a path is set to {i,j}, where i is the i-th ring, and j is the j-th POINT of the i-th ring. If the MULTI type is given as an argument, an integer indicating that it is the n-th GEOMETRY is added to the front of each path.

• Syntax

[Figure 101] ST_DUMPPOINTS

[Figure 101] ST_DUMPPOINTS

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.

• Example

SQL>SELECT PATH, ST_ASTEXT(GEOM) FROM TABLE(
DBMS_GEOM.ST_DUMPPOINTS(ST_GEOMFROMTEXT('LINESTRING(1 1,2 2,3 3)')));

PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------
{1}
POINT(1 1)

{2}
POINT(2 2)

{3}
POINT(3 3)


3 rows selected.

SQL> SELECT PATH, ST_ASTEXT(GEOM) FROM TABLE(DBMS_GEOM.ST_DUMPPOINTS(
ST_GEOMFROMTEXT('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1,3 1,3 3,1 3,1 1))')));

PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------
{1,1}
POINT(0 0)

{1,2}
POINT(4 0)

{1,3}
POINT(4 4)

{1,4}
POINT(0 4)

{1,5}
POINT(0 0)

{2,1}
POINT(1 1)

{2,2}
POINT(3 1)

PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------
 
{2,3}
POINT(3 3)

{2,4}
POINT(1 3)

{2,5}
POINT(1 1)


10 rows selected.

SQL> SELECT PATH, ST_ASTEXT(GEOM) FROM TABLE(DBMS_GEOM.ST_DUMPPOINTS(
     ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 1,2 2),
     POLYGON((3 3,4 3,4 4,3 4,3 3)),POLYGON((1 1,1 4,4 4,4 1,1 1),
     (2 2,2 3,3 3,3 2,2 2)))')));

PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------
{1,1}
POINT(0	0)
		
{2,1}
POINT(1	1)
		
{2,2}
POINT(2	2)
		
{3,1,1}
POINT(3	3)
		
{3,1,2}
POINT(4	3)
		
{3,1,3}
POINT(4	4)		

{3,1,4}
POINT(3 4)



PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------

{3,1,5}
POINT(3	3)

{4,1,1}
POINT(1	1)

{4,1,2}
POINT(1	4)

{4,1,3}
POINT(4	4)

{4,1,4}
POINT(4	1)

{4,1,5}
POINT(1	1)

{4,2,1}
POINT(2	2)


PATH
------------------------------------------------------------------------------------
ST_ASTEXT(GEOM)
------------------------------------------------------------------------------------
{4,2,2}
POINT(2 3)

{4,2,3}
POINT(3 3)

{4,2,4}
POINT(3 2)

{4,2,5}
POINT(2 2)

18 rows selected.

FROM_WGS84

Converts a GEOMETRY object expressed with latitude and longitude in wgs84 format into a GEOMETRY object expressed with horizontal and vertical axes based on a given point. At this time, the coordinate of the GEOMETRY object is analyzed in the order of longitude and latitude, but this function takes the base point in the order of latitude and longitude.

The FROM_WGS84 and TO_WGS84 functions repeatedly perform floating-point operations. Therefore, distortion of the coordinate information of GEOMETRY type objects may occur. In addition, there is a difference in output results for each execution environment depending on floating-point operation methods.

• Syntax

[Figure 102] FROM_WGS84

[Figure 107] FROM_WGS84

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
latitude	NUMBER type latitude value used as a base for conversion.
longitude	NUMBER type longitude value used as a base for conversion.

• Example

SQL> SELECT ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT
     ('POINT(0 1)'), 0, 0)) FROM DUAL;

ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT('POINT(01)'),0,0))
---------------------------------------------------------------------------------
POINT(0 110.94633302763)

1 row selected.

SQL> SELECT ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT
     ('POINT(0 1)'), 15, 30)) FROM DUAL;
     
ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT('POINT(01)'),15,30))
---------------------------------------------------------------------------------
POINT(-3331.5555052183 -1553.7714201354)

1 row selected.

SQL> SELECT ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT
     ('POINT(30 40)'), 40, 30)) FROM DUAL;
     
ST_ASTEXT(DBMS_GEOM.FROM_WGS84(ST_GEOMFROMTEXT('POINT(3040)'),40,30))
---------------------------------------------------------------------------------
POINT(-0 0)

1 row selected.

TO_WGS84

Converts a GEOMETRY object expressed with horizontal and vertical axes into a GEOMETRY object expressed with latitude and longitude in wgs84 format based on a given point. At this time, the coordinate of the GEOMETRY object is analyzed in the order of longitude and latitude, but this function takes the base point in the order of latitude and longitude.

The FROM_WGS84 and TO_WGS84 functions repeatedly perform floating-point operations. Therefore, distortion of the coordinate information of GEOMETRY type objects may occur. In addition, there is a difference in output results for each execution environment depending on floating-point operation methods.

• Syntax

[Figure 103] TO_WGS84

[Figure 114] TO_WGS84

• Components

Component	Description
geom	GEOMETRY type GEOMETRY object.
latitude	NUMBER type latitude value used as a base for conversion.
longitude	NUMBER type longitude value used as a base for conversion.

• Example

SQL> SELECT ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT
     ('POINT(0 110.9463302763)'), 0, 0)) FROM DUAL;
     
ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT('POINT(0110.9463302763)'),0,0))
------------------------------------------------------------------------------------
POINT(0 0.99999997520128)
/* Not equal to the actual value {POINT(0 1)} */

1 row selected.

SQL> SELECT ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT
     ('POINT(-3331.5555052183 -1553.7714201354)'),15,30)) FROM DUAL;
     
ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT('POINT(-3331.5555052183-1553.771420
------------------------------------------------------------------------------------
POINT(0 1.00000000000027)
/* Not equal to the actual value {POINT(0 1)} */

1 row selected.

SQL> SELECT ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT
     ('POINT(0 0)'), 40, 30)) FROM DUAL;
     
ST_ASTEXT(DBMS_GEOM.TO_WGS84(ST_GEOMFROMTEXT('POINT(00)'),40,30))
------------------------------------------------------------------------------------
POINT(30 40)

1 row selected.