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
Components
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
02SQL> 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
0AD00F00D00F00D00FD00F620311A01FD00FA01FD00F02A01FA01FD00FD00FST_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
Components
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
Components
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)
0101000000000000000000F03F000000000000F03FST_ASGEOJSON
Returns a GEOMETRY object as in GEOJSON format.
Syntax
[Figure 4] ST_ASGEOJSON
Components
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
Components
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
Components
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
Components
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
Components
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
0700020100020202000204040202SQL> 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
1FD00FD00FST_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
Components
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.14159265ST_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
Components
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
Components
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.144515ST_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
Components
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 EMPTYST_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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
---------
1ST_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
Components
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
------
1ST_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
Components
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 EMPTYST_DIMENSION
Returns the dimension of a GEOMETRY object.
Syntax
[Figure 21] ST_DIMENSION
Components
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
Components
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
Components
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
---------
2ST_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
Components
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
-------
1ST_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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
GEOMETRYCOLLECTIONST_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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
geom
GEOMETRY type GEOMETRY object.
Example
SQL> SELECT ST_ASTEXT(GEOM)
FROM GIS WHERE ST_ISEMPTY(GEOM) > 0;
ST_ASTEXT(GEOM)
----------------------------------------------------------------------------------
GEOMETRYCOLLECTION EMPTYST_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
Components
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
Components
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
Components
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))'))
--------------------------------------------------------
0SQL> 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))'))
---------------------------------------------------------
1ST_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
Components
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.65028154ST_LINEFROMTEXT
Returns a LINESTRING object based on given WKT and SRID.
Syntax
[Figure 52] ST_LINEFROMTEXT
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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)'))
---------------------------------------------
3ST_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
Components
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)'))
---------------------------------------------
2ST_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
Components
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)'))
----------------------------------------------
-1ST_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
Components
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)'))
----------------------------------------------
-1ST_MLINEFROMTEXT
Returns a MULTILINESTRING object based on given WKT and SRID.
Syntax
[Figure 63] ST_MLINEFROMTEXT
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
102ST_NPOINTS
Returns the number of POINTs in GEOMETRY. Same as ST_NUMPOINTS.
Syntax
[Figure 71] ST_NPOINTS
Components
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
3ST_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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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
Components
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))
------------------------------------------------------
4326ST_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
Components
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
Components
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 0ST_STARTPOINT
Returns the first POINT of LINESTRING. If the argument is not LINESTRING, NULL is returned.
Syntax
[Figure 92] ST_STARTPOINT
Components
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
Components
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
Components
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
Components
- case1
geom
GEOMETRY type GEOMETRY object.
num
Coordinate system information (SRID) to convert.
- case2
geom
GEOMETRY type GEOMETRY object.
str
PROJ4TEXT of coordinate system information to convert.
- case3
geom
GEOMETRY type GEOMETRY object.
str
PROJ4TEXT of coordinate system information before conversion.
str
PROJ4TEXT of coordinate system information to convert.
- case4
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
Components
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
Components
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
Components
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
Components
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)
1ST_Y
Returns the Y coordinate of a POINT object. If the argument is not POINT, NULL is returned.
Syntax
[Figure 100] ST_Y
Components
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)
1DBMS_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
Components
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.
Syntax
[Figure 102] FROM_WGS84
Components
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.
Syntax
[Figure 103] TO_WGS84
Components
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.Last updated