Geometry constructor functions #

ST_AsWKB #

Returns the WKB representation of the geometry.
Syntax

ST_AsWKB(geometryA)

Parameter
geometryA— Geometry type
Return value
Binary string in WKB format
Example

SELECT 
    ST_AsWKB(ST_GeomFromText('POINT(0 1)')) AS g1, 
    ST_AsWKB(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')) AS g2, 
    ST_AsWKB(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')) AS g3

g1─┬─g2──┬─g3────┐ 
│ �? │ �?�?��? │ �?�?��?�?�? │ 
└────┴─────┴───────┘

ST_AsText #

Returns the WKT representation of the geometry.
Syntax

ST_AsText(geometryA)

Parameter
geometryA— Geometry type
Return value
Binary string in WKT format
Example

SELECT 
    ST_AsText(ST_GeomFromText('POINT(0 1)')), 
    ST_AsText(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')), 
    ST_AsText(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))'))

g1──────────┬─g2──────────────────────────┬─g3──────────────────────────────┐ 
│ POINT (0 1) │ LINESTRING (1 1, 0 0, -1 1) │ POLYGON ((1 1, 0 0, -1 1, 1 1)) │ 
└─────────────┴─────────────────────────────┴─────────────────────────────────┘

ST_GeometryFromText #

Constructs a geometry object from WKT.
Syntax

ST_GeometryFromText(WKT)

Parameter
WKT— WKT string
Return value
Geometry type
Example

SELECT 
    ST_GeomFromText('POINT(0 1)') g1, 
    ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)') g2, 
    ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))') g3

g1────┬─g2───────────────────┬─g3───────────────────────────┐ 
│ (0,1) │ [(1,1),(0,0),(-1,1)] │ [[(1,1),(0,0),(-1,1),(1,1)]] │ 
└───────┴──────────────────────┴──────────────────────────────┘

ST_GeomFromBinary #

Constructs a geometry object from WKB.
Syntax

ST_GeomFromBinary(WKB)

Parameter
WKB— WKB string
Return value
Geometry type
Example

SELECT 
    ST_AsText(ST_GeomFromWKB(ST_AsWKB(ST_GeomFromText('POINT(0 1)')))) AS g1, 
    ST_AsText(ST_GeomFromWKB(ST_AsWKB(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)')))) AS g2, 
    ST_AsText(ST_GeomFromWKB(ST_AsWKB(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))')))) AS g3

g1──────────┬─g2──────────────────────────┬─g3──────────────────────────────┐ 
│ POINT (0 1) │ LINESTRING (1 1, 0 0, -1 1) │ POLYGON ((1 1, 0 0, -1 1, 1 1)) │ 
└─────────────┴─────────────────────────────┴─────────────────────────────────┘

Spatial relationship functions #

ST_Contains #

If the geometry geometryA completely contains geometryB (all points of geometryB are in geometryA and at least one point is in the interior of geometryA), then return true; otherwise, return false.
Syntax

ST_Contains(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('POINT(5 5)')) AS contains1, 
    ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('POINT(0 0)')) AS contains2, 
    ST_Contains(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('POINT(-1 0)')) AS contains3

┌─contains1─┬─contains2─┬─contains3─┐ 
│         1 │         0 │         0 │ 
└───────────┴───────────┴───────────┘

ST_ContainsProperly #

If the geometry geometryA strictly contains geometryB, that is, all points of geometryB are in the interior of geometryA (excluding the boundary), then return true; otherwise, return false.
Syntax

ST_ContainsProperly(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_ContainsProperly(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(5 5),LINESTRING(1 1,2 2))')) AS containsproperly1, 
    ST_ContainsProperly(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(10 10), LINESTRING(0 0, 0 10))')) AS containsproperly2, 
    ST_ContainsProperly(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(-1 0),LINESTRING(11 5,12 5))')) AS containsproperly3

containsproperly1─┬─containsproperly2─┬─containsproperly3─┐ 
│                 1 │                 0 │                 0 │ 
└───────────────────┴───────────────────┴───────────────────┘

ST_Crosses #

If the two input geometry objects partially intersect but do not contain each other, this function returns true.
Syntax

ST_Crosses(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Crosses(ST_GeomFromText('LINESTRING(0 10, 0 -10)'), ST_GeomFromText('LINESTRING(0 0, 1 1)')) AS crosses1, 
    ST_Crosses(ST_GeomFromText('LINESTRING(0 10, 0 -10)'), ST_GeomFromText('LINESTRING(-4 0, 1 1)')) AS crosses2, 
    ST_Crosses(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(5 5),LINESTRING(1 1,2 2))')) AS crosses3

─crosses1─┬─crosses2─┬─crosses3─┐ 
│        0 │        1 │        0 │ 
└──────────┴──────────┴──────────┘

ST_Disjoint #

If the geometry objects geometryA and geometryB have no intersection at all (are completely disjoint), then return true; otherwise, return false.
Syntax

ST_Disjoint(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Disjoint(ST_GeomFromText('POINT(5 5)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS disjoint1, 
    ST_Disjoint(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS disjoint2, 
    ST_Disjoint(ST_GeomFromText('POINT(-1 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS disjoint3

disjoint1─┬─disjoint2─┬─disjoint3─┐ 
│         0 │         0 │         1 │ 
└───────────┴───────────┴───────────┘

ST_Equals #

If the geometry objects geometryA and geometryB are completely identical in shape and position, then return true; otherwise, return false.
Syntax

ST_Equals(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Equals(ST_GeomFromText('LINESTRING(0 0, 1 1)'), ST_GeomFromText('LINESTRING(1 1, 0 0)')) AS quals1, 
    ST_Equals(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)),LINESTRING(0 2, 0 5))')) AS quals2, 
    ST_Equals(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POLYGON((0 0, 10 0, 10 10, 0 10, 0 0)),MULTIPOINT(0 2, 0 5))')) AS quals3

quals1─┬─quals2─┬─quals3─┐ 
│      1 │      1 │      1 │ 
└────────┴────────┴────────┘

ST_Intersects #

If the geometry objects geometryA and geometryB have any intersection (including boundary and interior), then return true; otherwise, return false.
Syntax

ST_Intersects(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Intersects(ST_GeomFromText('POINT(5 5)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS intersects1, 
    ST_Intersects(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS intersects2, 
    ST_Intersects(ST_GeomFromText('POINT(-1 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS intersects3

intersects1─┬─intersects2─┬─intersects3─┐ 
│           1 │           1 │           0 │ 
└─────────────┴─────────────┴─────────────┘

ST_Overlaps #

If the geometry objects geometryA and geometryB partially overlap (that is, they intersect, and the result set is neither geometryA nor geometryB), then return true; otherwise, return false.
Syntax

ST_Overlaps(geometryA, geometryB)

Parameter
geometryA, geometryB— Geometry type
Return value
Boolean value
Example

SELECT 
    ST_Overlaps(ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'), ST_GeomFromText('POLYGON((1 1, 1 3, 3 3, 3 1, 1 1))')) AS overlaps1, 
    ST_Overlaps(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(5 5),LINESTRING(1 1,2 2))')) AS overlaps2

┌─overlaps1─┬─overlaps2─┐ 
│         1 │         0 │ 
└───────────┴───────────┘

ST_Relate #

A function for describing spatial relationships between two geometry objects. If the relationship matches the specified pattern, returns true; otherwise, returns false.
Syntax

ST_Relate(geometryA, geometryB) 
ST_Relate(geometryA, geometryB, pattern_matrix)

Parameter
geometryA, geometryB— Geometry type
pattern_matrix — String specifying the relationship Mode
Return value
Boolean Value
Example

SELECT 
    ST_Relate(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING(0 0, 1 1)')) AS relate1, 
    ST_Relate(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'F0FFFF*02') AS relate2, 
    ST_Relate(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('LINESTRING(0 0, 1 1)'), 'F0FFF0*02') AS relate3

┌─relate1───┬─relate2─┬─relate3─┐ 
│ F0FFFF102 │       1 │       0 │ 
└───────────┴─────────┴─────────┘

ST_Touches #

If the boundaries of geometry objects geometryA and geometryB touch but their interiors do not intersect, Back true; Otherwise, Back false.
Syntax

ST_Touches(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Boolean Value
Example

SELECT 
    ST_Touches(ST_GeomFromText('LINESTRING(0 10, 0 -10)'), ST_GeomFromText('LINESTRING(0 0, 1 1)')) AS touches1, 
    ST_Touches(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('GEOMETRYCOLLECTION (POINT(5 5),LINESTRING(1 1,2 2))')) AS touches2

─touches1─┬─touches2─┐ 
│        1 │        0 │ 
└──────────┴──────────┘

ST_Within #

If geometry object geometryA is completely within the interior of geometry object geometryB (including the boundary), Back true; Otherwise, Back false. Full
Key points:
Syntax

ST_Within(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Boolean Value
Example

SELECT 
    ST_Within(ST_GeomFromText('POINT(0 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS within1, 
    ST_Within(ST_GeomFromText('POINT(521513 5377804)', 32631), ST_GeomFromText('POLYGON((521526 5377783, 521481 5377811, 521494 5377832, 521539 5377804, 521526 5377783))', 32631)) AS within2

┌─within1─┬─within2─┐ 
│       0 │       1 │ 
└─────────┴─────────┘

ST_DWithin #

This function is used to Judge whether the distance between two geometry objects is within the given threshold. If the shortest distance between two geometry objects is less than or equal to the given distance threshold, Back true; Otherwise, Back false.
Syntax

ST_DWithin(geometryA, geometryB, distance)

Param
geometryA, geometryB— Type
distance— indicates the distance threshold
Return value
Boolean Value
Example

SELECT 
    ST_DWithin(ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('POINT(2 2)'), 2.) AS dwithin1, 
    ST_DWithin(ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('POINT(5 5)'), 2.) AS dwithin2

dwithin1─┬─dwithin2─┐ 
│        1 │        0 │ 
└──────────┴──────────┘

ST_Covers #

If geometry object geometryA completely Coverage geometryB (that is, geometryB is completely within the interior of geometryA, and the boundary of geometryA can coincide with the boundary of geometryB), Back true; Otherwise, Back false.
Syntax

ST_Covers(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Boolean Value
Example

SELECT 
    ST_Covers(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('LINESTRING(1 10, 9 10, 9 8)')) AS covers1, 
    ST_Covers(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'), ST_GeomFromText('POINT(-1 0)')) AS covers2

┌─covers1─┬─covers2─┐ 
│       1 │       0 │ 
└─────────┴─────────┘

ST_CoveredBy #

If geometry object geometryA is completely Coverage by geometry object geometryB (that is, geometryA is completely within the interior of geometryB, and the boundary of geometryB can coincide with the boundary of geometryA), Back true; Otherwise, Back false.
Syntax

ST_CoveredBy(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Boolean Value
Example

SELECT 
    ST_CoveredBy(ST_GeomFromText('LINESTRING(1 10, 9 10, 9 8)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS coveredby1, 
    ST_CoveredBy(ST_GeomFromText('POINT(-1 0)'), ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS coveredby2

┌─coveredby1─┬─coveredby2─┐ 
│          1 │          0 │ 
└────────────┴────────────┘

Geometry Actions functions #

ST_Boundary #

This function is used to obtain the boundary of a geometry object.
Syntax

ST_Boundary(geometryA)

Param
geometryA— Type
Return value
Back a geometry object representing the boundary of the Input geometry object.
Example

SELECT 
    ST_AsText(ST_Boundary(ST_GeomFromText('POINT(0 1)'))) AS boundary01, 
    ST_AsText(ST_Boundary(ST_GeomFromText('MULTIPOINT(0 0, 1 1)'))) AS boundary02, 
    ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(1 1,0 0, -1 1)'))) AS boundary03

─boundary01───────────────┬─boundary02───────────────┬─boundary03─────────────────┐ 
│ GEOMETRYCOLLECTION EMPTY │ GEOMETRYCOLLECTION EMPTY │ MULTIPOINT ((1 1), (-1 1)) │ 
└──────────────────────────┴──────────────────────────┴────────────────────────────┘

ST_Buffer #

This function is used to create a buffer for a geometry object.
Syntax

ST_Buffer(geometryA, distance)

Param
geometryA— Type
distance— The distance of the buffer, which can be a positive number or a negative number. A positive number means to expand outward, and a negative number means to shrink inward.
Return value
Back a new geometry object representing the buffer of the Input geometry object.
Example

SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(100 90)'), 50.)) AS buffer

buffer─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
│ POLYGON ((150 90, 149.0392640201615 80.24548389919359, 146.19397662556435 70.86582838174552, 141.57348061512727 62.22148834901989, 135.35533905932738 54.64466094067263, 127.77851165098011 48.42651938487274, 119.1341716182545 43.80602337443566, 109.75451610080641 40.960735979838475, 100 40, 90.24548389919359 40.960735979838475, 80.86582838174552 43.80602337443566, 72.2214883490199 48.42651938487273, 64.64466094067262 54.64466094067262, 58.426519384872734 62.22148834901989, 53.80602337443566 70.8658283817455, 50.960735979838475 80.24548389919357, 50 90, 50.960735979838475 99.75451610080641, 53.80602337443566 109.13417161825448, 58.42651938487273 117.7785116509801, 64.64466094067262 125.35533905932738, 72.22148834901989 131.57348061512727, 80.86582838174549 136.19397662556432, 90.24548389919357 139.0392640201615, 99.99999999999999 140, 109.75451610080641 139.0392640201615, 119.1341716182545 136.19397662556435, 127.7785116509801 131.57348061512727, 135.35533905932738 125.35533905932738, 141.57348061512727 117.77851165098011, 146.19397662556432 109.13417161825453, 149.0392640201615 99.75451610080644, 150 90)) │ 
└────────────────────────────────────────────────────────────────────────────────────────────

ST_Difference #

This function is used to Calculate the difference between two geometry objects.
Syntax

ST_Difference(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Back a new geometry object representing the part of geometryA after removing the overlapping part with geometryB.
Example

SELECT ST_AsText(ST_Difference(ST_GeomFromText('LINESTRING(50 100, 50 200)'), ST_GeomFromText('LINESTRING(50 50, 50 150)'))) AS difference

┌─difference──────────────────┐ 
│ LINESTRING (50 150, 50 200) │ 
└─────────────────────────────┘

ST_Envelope #

Function for obtaining the minimum bounding rectangle of a geometric object. The minimum bounding rectangle is the smallest rectangle that can completely contain the Input geometric object.
Syntax

ST_Envelope(geometryA)

Param
geometryA— Type
Return value
Back a polygon geometric object representing the minimum bounding rectangle of the Input geometric object
Example

SELECT 
    ST_AsText(ST_Envelope(ST_GeomFromText('POINT(521513 5377804)'))) AS envelope_1, 
    ST_AsText(ST_Envelope(ST_GeomFromText('LINESTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)'))) AS envelope_2, 
    ST_AsText(ST_Envelope(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(2 2, 2 4, 4 4, 4 2, 2 2))'))) AS envelope_3

┌─envelope_1─────────────┬─envelope_2─────────────────────────────┬─envelope_3──────────────────────────────┐ 
│ POINT (521513 5377804) │ POLYGON ((-1 0, 2 0, 2 2, -1 2, -1 0)) │ POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)) │ 
└────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────┘

ST_ExteriorRing #

Function for obtaining the external boundary ring (that is, the exterior ring) of a polygon geometric object.
Syntax

ST_ExteriorRing(polygon_geometry)

Param
polygon_geometry— Type
Return value
Back a linestring geometric object representing the external boundary ring of the polygon
Example

SELECT ST_AsText(ST_ExteriorRing(ST_GeomFromText('POLYGON((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18))'))) AS exteriorring

┌─exteriorring─────────────────────────────────┐ 
│ LINESTRING (52 18, 66 23, 73 9, 48 6, 52 18) │ 
└──────────────────────────────────────────────┘

ST_Intersection #

Function for calculating the Part of intersection between two geometric objects.
Syntax

ST_Intersection(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Back a geometric object representing the Part of intersection between geometryA and geometryB
Example

SELECT ST_AsText(ST_Intersection(ST_GeomFromText('LINESTRING(0 10, 0 -10)'), ST_GeomFromText('LINESTRING(0 0, 1 1)'))) AS intersection

┌─intersection─┐ 
│ POINT (0 0)  │ 
└──────────────┘

ST_Union #

Function for merging two or more geometric objects.
Syntax

ST_Union(geometryA, geometryB)

Param
geometryA, geometryB— Type
Return value
Back a new geometric object that has merged the Input geometric objects
Example

SELECT ST_AsText(ST_Union(ST_GeomFromText('POINT(1 2)'), ST_GeomFromText('POINT(1 2)'))) AS union_1

─union_1─────┐ 
│ POINT (1 2) │ 
└─────────────

ST_Collect #

Function for combining multiple geometric objects into a collection.
Syntax

ST_Collect(geometryA, geometryB,...)

Param
Usually, Back is a geometric object of GEOMETRYCOLLECTION Type
Return value
Boolean Value
Example

SELECT ST_AsText(ST_Collect(ST_GeomFromText('POINT(-1 -1)', 4326), ST_GeomFromText('LINESTRING ( 0 0, 0 2 )', 4326), ST_GeomFromText('POLYGON((0 0, 0 10, 0 51, 50 20, 30 20, 7 32, 0 0), (1 1, 1 3, 18 18, 1 1))', 4326))) AS collect

┌─collect─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ 
│ GEOMETRYCOLLECTION (POINT (-1 -1), LINESTRING (0 0, 0 2), POLYGON ((0 0, 0 10, 0 51, 50 20, 30 20, 7 32, 0 0), (1 1, 1 3, 18 18, 1 1))) │ 
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

ST_Normalize #

Function for normalizing a geometric object
Syntax

ST_Normalize(geometryA)

Param
geometryA— Type
Return value
Boolean Value
Example

SELECT ST_AsText(ST_Normalize(ST_GeomFromText('POLYGON((0 10,0 0,10 0,10 10,0 10),(4 2,2 2,2 4,4 4,4 2),(6 8,8 8,8 6,6 6,6 8))'))) AS normalize

┌─normalize─────────────────────────────────────────────────────────────────────────────────────┐ 
│ POLYGON ((0 0, 0 10, 10 10, 10 0, 0 0), (6 6, 8 6, 8 8, 6 8, 6 6), (2 2, 4 2, 4 4, 2 4, 2 2)) │ 
└───────────────────────────────────────────────────────────────────────────────────────────────┘

ST_RemoveRepeatedPoints #

Function for removing Repeated vertices from the boundary of a linestring or polygon.
Syntax

ST_RemoveRepeatedPoints(geometryA)

Param
geometryA— Type
Return value
Back a new geometric object in which the Repeated vertices in the Input geometric object have been Removed
Example

SELECT 
    ST_AsText(ST_RemoveRepeatedPoints(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 1, 2 2)'))) AS remove1, 
    ST_AsText(ST_RemoveRepeatedPoints(ST_GeomFromText('MULTILINESTRING((0 0, 1 1, 1 1, 2 2, 2 2, 2 2, 2 2, 3 3, 3 3),(5 5, 5 5, 5 5, 4 4, 2 2))'))) AS remove2, 
    ST_AsText(ST_RemoveRepeatedPoints(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 10 10, 0 10, 0 10, 0 10, 0 0, 0 0, 0 0),(5 5, 5 5, 5 8, 8 8, 8 8, 8 8, 8 5,8 5, 5 5, 5 5, 5 5, 5 5, 5 5))'))) AS remove3

remove1────────────────────┬─remove2─────────────────────────────────────────────────┬─remove3────────────────────────────────────────────────────────────┐ 
│ LINESTRING (0 0, 1 1, 2 2) │ MULTILINESTRING ((0 0, 1 1, 2 2, 3 3), (5 5, 4 4, 2 2)) │ POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (5 5, 5 8, 8 8, 8 5, 5 5)) │ 
└────────────────────────────┴─────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────┘

ST_Simplify #

Function for simplifying a geometric object.
Syntax

ST_Simplify(geometryA, tolerance)

Param
geometryA— Type
tolerance: Simplification tolerance, used to control the degree of simplification. The larger the Value, the higher the degree of simplification.
Return value
Back a simplified geometric object
Example

SELECT 
    ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0, 0 10, 0 51, 50 20, 30 20, 7 32)'), 2.)) AS simplify1, 
    ST_AsText(ST_Simplify(ST_GeomFromText('POLYGON((0 0, 0 10, 0 51, 50 20, 30 20, 7 32, 0 0), (1 1, 1 3, 18 18, 1 1))'), 20.)) AS simplify2

─simplify1──────────────────────────────────┬─simplify2───────────────────────────────┐ 
│ LINESTRING (0 0, 0 51, 50 20, 30 20, 7 32) │ POLYGON ((0 0, 0 51, 50 20, 7 32, 0 0)) │ 
└────────────────────────────────────────────┴─────────────────────────────────────────┘

ST_Translate #

Function for translating a geometric object.
Syntax

ST_Translate(geometryA, x_delta, y_delta)

Param
geometryA, geometryB— Type
x_delta: Translation distance in the X axis direction
y_delta: Translation distance in the Y axis direction
Return value
Back a new geometric object after translation
Example

SELECT 
    ST_AsText(ST_Translate(ST_GeomFromText('POINT (0 0)'), 1., -1.)) AS trans1, 
    ST_AsText(ST_Translate(ST_GeomFromText('LINESTRING (0 0, 0 1, 1 2)'), 2., -2.)) AS trans2, 
    ST_AsText(ST_Translate(ST_GeomFromText('POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))'), 1., -1.)) AS trans3

─trans1───────┬─trans2───────────────────────┬─trans3─────────────────────────────────┐ 
│ POINT (1 -1) │ LINESTRING (2 -2, 2 -1, 3 0) │ POLYGON ((1 -1, 2 -1, 2 0, 1 0, 1 -1)) │ 
└──────────────┴──────────────────────────────┴────────────────────────────────────────┘

ST_Dump #

Function for decomposing composite geometry objects (such as MULTIPOINT, MULTILINESTRING, and MULTIPOLYGON) into individual geometry objects.
Syntax

ST_Dump(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a result set containing rows, each row contains a single geometry object and the access path.
Example

SELECT 
    ST_AsText(col2.1) AS geom, 
    col2.2 AS path 
FROM 
( 
    SELECT arrayJoin(col) AS col2 
    FROM 
    ( 
        SELECT ST_Dump(ST_GeomFromText('GEOMETRYCOLLECTION (POINT (1 1), LINESTRING (0 0, 1 1), POLYGON ((0 0, 1 1, 1 0, 0 0)), MULTIPOLYGON (((0 0, 1 1, 1 0, 0 0)), ((2 2, 3 3, 3 2, 2 2))), GEOMETRYCOLLECTION (POINT (3 3)))')) AS col 
    ) 
) AS dump

─geom───────────────────────────┬─path──┐ 
│ POINT (1 1)                    │ [1]   │ 
│ LINESTRING (0 0, 1 1)          │ [2]   │ 
│ POLYGON ((0 0, 1 1, 1 0, 0 0)) │ [3]   │ 
│ POLYGON ((0 0, 1 1, 1 0, 0 0)) │ [4,1] │ 
│ POLYGON ((2 2, 3 3, 3 2, 2 2)) │ [4,2] │ 
│ POINT (3 3)                    │ [5,1] │ 
└────────────────────────────────┴───────┘

ST_Transform #

Function for converting geometry objects from one spatial reference system to another spatial reference system.
Syntax

ST_Transform(geometryA, target_srid) 
ST_Transform(geometryA, from_srid_text, to_srid_text)

Param
geometryA— Geometry Type
target_srid: Identifier (SRID) of the target spatial reference system
from_srid_text,to_srid_text: String representation of the spatial reference system identifier
Return value
Returns the geometry object converted to the specified spatial reference system.
Example

SELECT ST_AsText(p), 
    ST_SRID(p) 
from ( 
        SELECT ST_Transform( 
                ST_GeomFromText('POINT(52.3676 4.9041)', 4326), 
                3857 
            ) as p 
    );

─ST_AsText(p)────────────────────────────────┬─ST_SRID(p)─┐ 
│ POINT (5829534.566065834 546589.7188300652) │       3857 │ 
└─────────────────────────────────────────────┴────────────┘

SELECT ST_AsText(p), 
    ST_SRID(p) 
from ( 
        SELECT ST_Transform( 
                ST_GeomFromText('POINT(52.3676 4.9041)'), 
                'EPSG:4326', 
                'EPSG:3857' 
            ) as p 
    );

─ST_AsText(p)────────────────────────────────┬─ST_SRID(p)─┐ 
│ POINT (5829534.566065834 546589.7188300652) │       3857 │ 
└─────────────────────────────────────────────┴────────────┘

Geometry access functions #

ST_Centroid #

Function for calculating the centroid (geometric center) of a geometry object.
Syntax

ST_CoveredBy(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a point geometry object representing the centroid of the input geometry object.
Example

SELECT 
    ST_AsText(ST_Centroid(ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0),(2 2, 2 4, 4 4, 4 2, 2 2))'))) AS centroid1, 
    ST_AsText(ST_Centroid(ST_GeomFromText('LINESTRING(0 2, 0 0, 1 0, 1 2)'))) AS centroid2, 
    ST_AsText(ST_Centroid(ST_GeomFromText('MULTIPOINT(0 2, 0 0, 1 0, 1 2)'))) AS centroid3

┌─centroid1───────────────────────────────────┬─centroid2───────┬─centroid3─────┐ 
│ POINT (5.083333333333333 5.083333333333333) │ POINT (0.5 0.8) │ POINT (0.5 1) │ 
└─────────────────────────────────────────────┴─────────────────┴───────────────┘

ST_ConvexHull #

Function for calculating the convex hull of a geometry object, where the convex hull is the minimum convex polygon that contains the given geometry object.
Syntax

ST_ConvexHull(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a geometry object representing the convex hull of the input geometry object.
Example

SELECT 
    ST_AsText(ST_ConvexHull(ST_GeomFromText('MULTIPOINT(0 0, 1 1, 2 0)'))) AS convexhull1, 
    ST_AsText(ST_ConvexHull(ST_GeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))'))) AS convexhull

┌─convexhull1────────────────────┬─convexhull──────────────────────────┐ 
│ POLYGON ((0 0, 1 1, 2 0, 0 0)) │ POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0)) │ 
└────────────────────────────────┴─────────────────────────────────────┘

ST_PointOnSurface #

Function for obtaining a point on the surface of a geometry object.
Syntax

ST_PointOnSurface(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a point geometry object located on the surface of the input geometry object.
Example

SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'))) AS p

┌─p───────────────┐ 
│ POINT (2.5 2.5) │ 
└─────────────────┘

ST_Dimension #

Function for obtaining the dimension of a geometry object.
The dimension of a point is 0.
The dimension of a line is 1.
The dimension of a surface (polygon) is 2.
Syntax

ST_Dimension(geometryA)

Param
geometryA— Geometry Type
Return value
Returns an integer representing the dimension of the input geometry object.
Example

SELECT 
    ST_Dimension(ST_GeomFromText('POINT(521513 5377804)')) AS dimension1, 
    ST_Dimension(ST_GeomFromText('LINESTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)')) AS dimension2, 
    ST_Dimension(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(2 2, 2 4, 4 4, 4 2, 2 2))')) AS dimension3

┌─dimension1─┬─dimension2─┬─dimension3─┐ 
│          0 │          1 │          2 │ 
└────────────┴────────────┴────────────┘

ST_Distance #

Function for calculating the shortest distance between two geometry objects.
Syntax

ST_Distance(geometryA, geometryB)

Param
geometryA, geometryB— Geometry Type
Return value
Returns a numerical value representing the shortest distance between two geometry objects.
Example

SELECT 
    ST_Distance(ST_GeomFromText('POINT(1 2)'), ST_GeomFromText('POINT(1 2)')) AS distance_1, 
    ST_Distance(ST_GeomFromText('POINT(5 0)'), ST_GeomFromText('POINT(10 12)')) AS distance_2

┌─distance_1─┬─distance_2─┐ 
│          0 │         13 │ 
└────────────┴────────────┘

ST_GeometryType #

Function for obtaining the type of a geometry object.
Syntax

ST_GeometryType(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a string representing the type of the geometry object.
Example

SELECT 
    ST_GeometryType(ST_GeomFromText('POINT(521513 5377804)')) AS geom_type_1, 
    ST_GeometryType(ST_GeomFromText('LINESTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)')) AS geom_type_2, 
    ST_GeometryType(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(2 2, 2 4, 4 4, 4 2, 2 2))')) AS geom_type_3

┌─geom_type_1─┬─geom_type_2─┬─geom_type_3─┐ 
│ Point       │ LineString  │ Polygon     │ 
└─────────────┴─────────────┴─────────────┘

ST_IsClosed #

Function for determining whether a geometry object is closed.
For a linestring (LINESTRING), if the start and end points are the same, it is considered closed.
For a polygon (POLYGON), its boundary must form a continuous and closed ring.
Syntax

ST_IsClosed(geometryA)

Param
geometryA— Geometry Type
Return value
Return true if the geometry object is closed; otherwise, return false.
Example

SELECT 
    ST_IsRing(geom), 
    ST_IsClosed(geom), 
    ST_IsSimple(geom) 
FROM 
( 
    SELECT ST_GeomFromText('LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)') AS geom 
) AS foo

─ST_IsRing(geom)─┬─ST_IsClosed(geom)─┬─ST_IsSimple(geom)─┐ 
│               0 │                 1 │                 0 │ 
└─────────────────┴───────────────────┴───────────────────┘

ST_IsSimple #

A function that determines whether a geometry object is simple.
For a linestring (LINESTRING), simple means it does not self-intersect (except that the start and end points may coincide).
For a polygon (POLYGON), simple means its boundary does not self-intersect and its Internal has no holes.
Syntax

ST_IsSimple(geometryA)

Param
geometryA— Geometry Type
Return value
Return true if the geometry object is simple; otherwise, return false.
Example

SELECT 
    ST_IsRing(geom), 
    ST_IsClosed(geom), 
    ST_IsSimple(geom) 
FROM 
( 
    SELECT ST_GeomFromText('LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)') AS geom 
) AS foo

─ST_IsRing(geom)─┬─ST_IsClosed(geom)─┬─ST_IsSimple(geom)─┐ 
│               0 │                 1 │                 0 │ 
└─────────────────┴───────────────────┴───────────────────┘

ST_IsRing #

A function that determines whether a linestring forms a ring. The requirements for a ring are not only that the start and end points coincide, but also that there is no self-intersection.
Syntax

ST_IsRing(geometryA)

Param
geometryA— Geometry Type
Return value
Return true if the input linestring geometry object forms a ring (that is, the start and end points are the same and it does not self-intersect); otherwise, return false.
Example

SELECT 
    ST_IsRing(geom), 
    ST_IsClosed(geom), 
    ST_IsSimple(geom) 
FROM 
( 
    SELECT ST_GeomFromText('LINESTRING(0 0, 0 1, 1 0, 1 1, 0 0)') AS geom 
) AS foo

─ST_IsRing(geom)─┬─ST_IsClosed(geom)─┬─ST_IsSimple(geom)─┐ 
│               0 │                 1 │                 0 │ 
└─────────────────┴───────────────────┴───────────────────┘

ST_IsValid #

A function that determines whether a geometry object is valid. Validity is determined based on a series of spatial Rules and Constraint, such as the boundary of a polygon must not self-intersect, the vertex order of a linestring must be reasonable, and others.
Syntax

ST_IsValid(geometryA)

Param
geometryA— Geometry Type
Return value
Return true if the geometry object is valid in space; otherwise, return false.
Example

SELECT 
    'valid_1', 
    ST_IsValid(ST_GeomFromText('LINESTRING(0 0, 1 1)')) AS good_line, 
    ST_IsValid(ST_GeomFromText('POLYGON((0 0, 1 1, 1 2, 1 1, 0 0))')) AS bad_poly

─'valid_1'─┬─good_line─┬─bad_poly─┐ 
│ valid_1   │         1 │        0 │ 
└───────────┴───────────┴──────────┘

ST_Length #

A function that calculates the length of a linestring or the boundary of a polygon.
For a linestring, directly Calculate the length of the linestring.
For a polygon, Calculate the length of its boundary.
Syntax

ST_Length(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a Numerical value representing the length of the input geometry object.
Example

SELECT ST_Length(ST_Transform(ST_GeomFromText('LINESTRING(-72.1260 42.45, -72.1240 42.45666, -72.123 42.1546)', 4326), 3857)) len;

┌───────────────len─┐ 
│ 46495.61309808517 │ 
└───────────────────┘

ST_Area #

A function that calculates the area of a polygon geometry object. The calculated area is based on the spatial reference System and coordinate Unit of the polygon.
Syntax

ST_Area(geometryA)

Param
geometryA— Geometry Type
Return value
Returns a Numerical value representing the area of the input polygon geometry object.
Example

SELECT ST_Area(geom) AS sqft 
FROM 
( 
    SELECT ST_GeomFromText('POLYGON((743238 2967416,743238 2967450, 743265 2967450,743265.625 2967416,743238 2967416))', 2249) AS geom 
) AS subquery

┌────sqft─┐ 
│ 928.625 │ 
└─────────┘

ST_NumPoints #

Function for obtaining the number of points in a linestring or polygon.
For a linestring, returns the number of points it contains.
For a polygon, returns the number of points on its boundary.
Syntax

ST_NumPoints(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns an integer indicating the number of points in the input geometry object.
Example

SELECT 
    ST_NumPoints(ST_GeomFromText('POINT(521513 5377804)')) AS numpoints_1, 
    ST_NumPoints(ST_GeomFromText('LINESTRING(0 2, -1 1,0 0, 0.5 0, 1 0, 2 1, 1 2, 0.5 2, 0 2)')) AS numpoints_2

┌─numpoints_1─┬─numpoints_2─┐ 
│           1 │           9 │ 
└─────────────┴─────────────┘

ST_SetSRID #

Function for setting the Spatial Reference Identifier (SRID) for a geometry object.
Syntax

ST_SetSRID(geometryA, srid)

Parameter
geometryA— Geometry type
srid— The specified Spatial Reference Identifier, an integer
Return value
Returns a new geometry object with the specified SRID.
Example

SELECT ST_SRID(ST_SetSRID(ST_GeomFromText('POINT(521513 5377804)'), 3986)) AS set_srid

─set_srid─┐ 
│     3986 │ 
└──────────┘

ST_SRID #

Function for obtaining the Spatial Reference Identifier (SRID) of a geometry object.
Syntax

ST_SRID(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns an integer indicating the SRID of the input geometry object.
Example

SELECT ST_SRID(ST_GeomFromText('POINT(521513 5377804)', 4326)) AS srid

─srid─┐ 
│ 4326 │ 
└──────

ST_X #

Function for obtaining the X coordinate value of a point geometry object.
Syntax

ST_X(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a numerical value representing the X coordinate of the point geometry object.
Example

SELECT 
    ST_X(ST_GeomFromText('POINT(521513 5377804)')) AS x, 
    ST_Y(ST_GeomFromText('POINT(521513 5377804)')) AS y

┌──────x─┬───────y─┐ 
│ 521513 │ 5377804 │ 
└────────┴─────────┘

ST_Y #

Function for obtaining the Y coordinate value of a point geometry object.
Syntax

ST_Y(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a numerical value representing the Y coordinate of the point geometry object.
Example

SELECT 
    ST_X(ST_GeomFromText('POINT(521513 5377804)')) AS x, 
    ST_Y(ST_GeomFromText('POINT(521513 5377804)')) AS y

┌──────x─┬───────y─┐ 
│ 521513 │ 5377804 │ 
└────────┴─────────┘

ST_XMIN #

Function for obtaining the minimum coordinate value in the X axis direction of a geometry object.
Syntax

ST_XMIN(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a numerical value representing the minimum coordinate in the X axis direction of the geometry object.
Example

SELECT 
    ST_XMIN(p), 
    ST_XMAX(p), 
    ST_YMIN(p), 
    ST_YMAX(p) 
FROM 
( 
    SELECT ST_GeomFromText('POLYGON((-5 -8, 10 0, 10 15, 0 15, -5 -8),(-6 2, 2 40, 4 40, 4 2, -6 2))') AS p 
) AS t

┌─ST_XMIN(p)─┬─ST_XMAX(p)─┬─ST_YMIN(p)─┬─ST_YMAX(p)─┐ 
│         -5 │         10 │         -8 │         15 │ 
└────────────┴────────────┴────────────┴────────────

ST_XMAX #

Function for obtaining the maximum coordinate value in the X axis direction of a geometry object.
Syntax

ST_XMAX(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a numerical value representing the maximum coordinate in the X axis direction of the geometry object.
Example

SELECT 
    ST_XMIN(p), 
    ST_XMAX(p), 
    ST_YMIN(p), 
    ST_YMAX(p) 
FROM 
( 
    SELECT ST_GeomFromText('POLYGON((-5 -8, 10 0, 10 15, 0 15, -5 -8),(-6 2, 2 40, 4 40, 4 2, -6 2))') AS p 
) AS t

┌─ST_XMIN(p)─┬─ST_XMAX(p)─┬─ST_YMIN(p)─┬─ST_YMAX(p)─┐ 
│         -5 │         10 │         -8 │         15 │ 
└────────────┴────────────┴────────────┴────────────

ST_YMIN #

Function for obtaining the minimum coordinate value in the Y axis direction of a geometry object.
Syntax

ST_YMIN(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a numerical value representing the minimum coordinate in the Y axis direction of the geometry object.
Example

SELECT 
    ST_XMIN(p), 
    ST_XMAX(p), 
    ST_YMIN(p), 
    ST_YMAX(p) 
FROM 
( 
    SELECT ST_GeomFromText('POLYGON((-5 -8, 10 0, 10 15, 0 15, -5 -8),(-6 2, 2 40, 4 40, 4 2, -6 2))') AS p 
) AS t

┌─ST_XMIN(p)─┬─ST_XMAX(p)─┬─ST_YMIN(p)─┬─ST_YMAX(p)─┐ 
│         -5 │         10 │         -8 │         15 │ 
└────────────┴────────────┴────────────┴────────────

ST_YMAX #

Function for obtaining the maximum coordinate value in the Y axis direction of a geometry object.
Syntax

ST_YMAX(geometryA)

Parameter
geometryA— Geometry type
Return value
Returns a Numerical value representing the Max coordinate of the geometry object in the Y axis direction
Example

SELECT 
    ST_XMIN(p), 
    ST_XMAX(p), 
    ST_YMIN(p), 
    ST_YMAX(p) 
FROM 
( 
    SELECT ST_GeomFromText('POLYGON((-5 -8, 10 0, 10 15, 0 15, -5 -8),(-6 2, 2 40, 4 40, 4 2, -6 2))') AS p 
) AS t

┌─ST_XMIN(p)─┬─ST_XMAX(p)─┬─ST_YMIN(p)─┬─ST_YMAX(p)─┐ 
│         -5 │         10 │         -8 │         15 │ 
└────────────┴────────────┴────────────┴────────────