This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Geometry Functions

1: HAVERSINE
2: ST_ASBINARY
3: ST_ASEWKB
4: ST_ASEWKT
5: ST_ASGEOJSON
6: ST_ASTEXT
7: ST_ASWKB
8: ST_ASWKT
9: ST_CONTAINS
10: ST_DIMENSION
11: ST_DISTANCE
12: ST_ENDPOINT
13: ST_GEOHASH
14: ST_GEOM_POINT
15: ST_GEOMETRYFROMEWKB
16: ST_GEOMETRYFROMEWKT
17: ST_GEOMETRYFROMTEXT
18: ST_GEOMETRYFROMWKB
19: ST_GEOMETRYFROMWKT
20: ST_GEOMFROMEWKB
21: ST_GEOMFROMEWKT
22: ST_GEOMFROMGEOHASH
23: ST_GEOMFROMTEXT
24: ST_GEOMFROMWKB
25: ST_GEOMFROMWKT
26: ST_GEOMPOINTFROMGEOHASH
27: ST_LENGTH
28: ST_MAKE_LINE
29: ST_MAKEGEOMPOINT
30: ST_MAKELINE
31: ST_MAKEPOLYGON
32: ST_NPOINTS
33: ST_NUMPOINTS
34: ST_POINTN
35: ST_POLYGON
36: ST_SETSRID
37: ST_SRID
38: ST_STARTPOINT
39: ST_TRANSFORM
40: ST_X
41: ST_XMAX
42: ST_XMIN
43: ST_Y
44: ST_YMAX
45: ST_YMIN
46: TO_GEOMETRY
47: TO_STRING

This section provides reference information for geometry and distance functions in PlaidCloud Lakehouse.

1 - HAVERSINE

Calculates the great circle distance in kilometers between two points on the Earth’s surface, using the Haversine formula. The two points are specified by their latitude and longitude in degrees.

SQL Syntax

HAVERSINE(<lat1>, <lon1>, <lat2>, <lon2>)

Arguments

Arguments	Description
`<lat1>`	The latitude of the first point.
`<lon1>`	The longitude of the first point.
`<lat2>`	The latitude of the second point.
`<lon2>`	The longitude of the second point.

Return Type

Double.

SQL Examples

SELECT
  HAVERSINE(40.7127, -74.0059, 34.0500, -118.2500) AS distance

┌────────────────┐
│    distance    │
├────────────────┤
│ 3936.390533556 │
└────────────────┘

2 - ST_ASBINARY

Alias for ST_ASWKB.

3 - ST_ASEWKB

Converts a GEOMETRY object into a EWKB(extended well-known-binary) format representation.

SQL Syntax

ST_ASEWKB(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Binary.

SQL Examples

SELECT
  ST_ASEWKB(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_ewkb;

┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                                        pipeline_ewkb                                       │
├────────────────────────────────────────────────────────────────────────────────────────────┤
│ 0102000020E61000000200000000000000006A18410000000060E3564100000000A07918410000000024ED5641 │
└────────────────────────────────────────────────────────────────────────────────────────────┘

SELECT
  ST_ASEWKB(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;POINT(-122.35 37.55)'
    )
  ) AS pipeline_ewkb;

┌────────────────────────────────────────────────────┐
│                    pipeline_ewkb                   │
├────────────────────────────────────────────────────┤
│ 0101000020E61000006666666666965EC06666666666C64240 │
└────────────────────────────────────────────────────┘

4 - ST_ASEWKT

Converts a GEOMETRY object into a EWKT(extended well-known-text) format representation.

SQL Syntax

ST_ASEWKT(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

String.

SQL Examples

SELECT
  ST_ASEWKT(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_ewkt;

┌─────────────────────────────────────────────────────┐
│                    pipeline_ewkt                    │
├─────────────────────────────────────────────────────┤
│ SRID=4326;LINESTRING(400000 6000000,401000 6010000) │
└─────────────────────────────────────────────────────┘

SELECT
  ST_ASEWKT(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;POINT(-122.35 37.55)'
    )
  ) AS pipeline_ewkt;

┌────────────────────────────────┐
│          pipeline_ewkt         │
├────────────────────────────────┤
│ SRID=4326;POINT(-122.35 37.55) │
└────────────────────────────────┘

5 - ST_ASGEOJSON

Converts a GEOMETRY object into a GeoJSON representation.

SQL Syntax

ST_ASGEOJSON(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Variant.

SQL Examples

SELECT
  ST_ASGEOJSON(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geojson;

┌─────────────────────────────────────────────────────────────────────────┐
│                             pipeline_geojson                            │
├─────────────────────────────────────────────────────────────────────────┤
│ {"coordinates":[[400000,6000000],[401000,6010000]],"type":"LineString"} │
└─────────────────────────────────────────────────────────────────────────┘

6 - ST_ASTEXT

Alias for ST_ASWKT.

7 - ST_ASWKB

Converts a GEOMETRY object into a WKB(well-known-binary) format representation.

SQL Syntax

ST_ASWKB(<geometry>)

Aliases

ST_ASBINARY

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Binary.

SQL Examples

SELECT
  ST_ASWKB(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_wkb;

┌────────────────────────────────────────────────────────────────────────────────────┐
│                                    pipeline_wkb                                    │
├────────────────────────────────────────────────────────────────────────────────────┤
│ 01020000000200000000000000006A18410000000060E3564100000000A07918410000000024ED5641 │
└────────────────────────────────────────────────────────────────────────────────────┘

SELECT
  ST_ASBINARY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;POINT(-122.35 37.55)'
    )
  ) AS pipeline_wkb;

┌────────────────────────────────────────────┐
│                pipeline_wkb                │
├────────────────────────────────────────────┤
│ 01010000006666666666965EC06666666666C64240 │
└────────────────────────────────────────────┘

8 - ST_ASWKT

Converts a GEOMETRY object into a WKT(well-known-text) format representation.

SQL Syntax

ST_ASWKT(<geometry>)

Aliases

ST_ASTEXT

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

String.

SQL Examples

SELECT
  ST_ASWKT(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_wkt;

┌───────────────────────────────────────────┐
│                pipeline_wkt               │
├───────────────────────────────────────────┤
│ LINESTRING(400000 6000000,401000 6010000) │
└───────────────────────────────────────────┘

SELECT
  ST_ASTEXT(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;POINT(-122.35 37.55)'
    )
  ) AS pipeline_wkt;

┌──────────────────────┐
│     pipeline_wkt     │
├──────────────────────┤
│ POINT(-122.35 37.55) │
└──────────────────────┘

9 - ST_CONTAINS

Returns TRUE if the second GEOMETRY object is completely inside the first GEOMETRY object.

SQL Syntax

ST_CONTAINS(<geometry1>, <geometry2>)

Arguments

Arguments	Description
`<geometry1>`	The argument must be an expression of type GEOMETRY object that is not a GeometryCollection.
`<geometry2>`	The argument must be an expression of type GEOMETRY object that is not a GeometryCollection.

:::note

The function reports an error if the two input GEOMETRY objects have different SRIDs. :::

Return Type

Boolean.

SQL Examples

SELECT ST_CONTAINS(TO_GEOMETRY('POLYGON((-2 0, 0 2, 2 0, -2 0))'), TO_GEOMETRY('POLYGON((-1 0, 0 1, 1 0, -1 0))')) AS contains

┌──────────┐
│ contains │
├──────────┤
│ true     │
└──────────┘

SELECT ST_CONTAINS(TO_GEOMETRY('POLYGON((-2 0, 0 2, 2 0, -2 0))'), TO_GEOMETRY('LINESTRING(-1 1, 0 2, 1 1)')) AS contains

┌──────────┐
│ contains │
├──────────┤
│ false    │
└──────────┘

SELECT ST_CONTAINS(TO_GEOMETRY('POLYGON((-2 0, 0 2, 2 0, -2 0))'), TO_GEOMETRY('LINESTRING(-2 0, 0 0, 0 1)')) AS contains

┌──────────┐
│ contains │
├──────────┤
│ true     │
└──────────┘

10 - ST_DIMENSION

Return the dimension for a geometry object. The dimension of a GEOMETRY object is:

Geospatial Object Type	Dimension
Point / MultiPoint	0
LineString / MultiLineString	1
Polygon / MultiPolygon	2

SQL Syntax

ST_DIMENSION(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

UInt8.

SQL Examples

SELECT
  ST_DIMENSION(
    ST_GEOMETRYFROMWKT(
      'POINT(-122.306100 37.554162)'
    )
  ) AS pipeline_dimension;

┌────────────────────┐
│ pipeline_dimension │
├────────────────────┤
│                  0 │
└────────────────────┘

SELECT
  ST_DIMENSION(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(-124.20 42.00, -120.01 41.99)'
    )
  ) AS pipeline_dimension;

┌────────────────────┐
│ pipeline_dimension │
├────────────────────┤
│                  1 │
└────────────────────┘

SELECT
  ST_DIMENSION(
    ST_GEOMETRYFROMWKT(
      'POLYGON((-124.20 42.00, -120.01 41.99, -121.1 42.01, -124.20 42.00))'
    )
  ) AS pipeline_dimension;

┌────────────────────┐
│ pipeline_dimension │
├────────────────────┤
│                  2 │
└────────────────────┘

11 - ST_DISTANCE

Returns the minimum Euclidean distance between two GEOMETRY objects.

SQL Syntax

ST_DISTANCE(<geometry1>, <geometry2>)

Arguments

Arguments	Description
`<geometry1>`	The argument must be an expression of type GEOMETRY and must contain a Point.
`<geometry2>`	The argument must be an expression of type GEOMETRY and must contain a Point.

:::note

Returns NULL if one or more input points are NULL.
The function reports an error if the two input GEOMETRY objects have different SRIDs. :::

Return Type

Double.

SQL Examples

SELECT
  ST_DISTANCE(
    TO_GEOMETRY('POINT(0 0)'),
    TO_GEOMETRY('POINT(1 1)')
  ) AS distance

┌─────────────┐
│   distance  │
├─────────────┤
│ 1.414213562 │
└─────────────┘

12 - ST_ENDPOINT

Returns the last Point in a LineString.

SQL Syntax

ST_ENDPOINT(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY that represents a LineString.

Return Type

Geometry.

SQL Examples

SELECT
  ST_ENDPOINT(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(1 1, 2 2, 3 3, 4 4)'
    )
  ) AS pipeline_endpoint;

┌───────────────────┐
│ pipeline_endpoint │
├───────────────────┤
│ POINT(4 4)        │
└───────────────────┘

13 - ST_GEOHASH

Return the geohash for a GEOMETRY object. A geohash is a short base32 string that identifies a geodesic rectangle containing a location in the world. The optional precision argument specifies the precision of the returned geohash. For example, passing 5 for `precision returns a shorter geohash (5 characters long) that is less precise.

SQL Syntax

ST_GEOHASH(<geometry> [, <precision>])

Arguments

Arguments	Description
`geometry`	The argument must be an expression of type GEOMETRY.
`[precision]`	Optional. specifies the precision of the returned geohash, default is 12.

Return Type

String.

SQL Examples

SELECT
  ST_GEOHASH(
    ST_GEOMETRYFROMWKT(
      'POINT(-122.306100 37.554162)'
    )
  ) AS pipeline_geohash;

┌──────────────────┐
│ pipeline_geohash │
├──────────────────┤
│ 9q9j8ue2v71y     │
└──────────────────┘

SELECT
  ST_GEOHASH(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;POINT(-122.35 37.55)'
    ),
    5
  ) AS pipeline_geohash;

┌──────────────────┐
│ pipeline_geohash │
├──────────────────┤
│ 9q8vx            │
└──────────────────┘

14 - ST_GEOM_POINT

Alias for ST_MAKEGEOMPOINT.

15 - ST_GEOMETRYFROMEWKB

Alias for ST_GEOMTRYFROMWKB.

16 - ST_GEOMETRYFROMEWKT

Alias for ST_GEOMTRYFROMWKT.

17 - ST_GEOMETRYFROMTEXT

Alias for ST_GEOMETRYFROMWKT.

18 - ST_GEOMETRYFROMWKB

Parses a WKB(well-known-binary) or EWKB(extended well-known-binary) input and returns a value of type GEOMETRY.

SQL Syntax

ST_GEOMETRYFROMWKB(<string>, [<srid>])
ST_GEOMETRYFROMWKB(<binary>, [<srid>])

Aliases

Arguments

Arguments	Description
`<string>`	The argument must be a string expression in WKB or EWKB in hexadecimal format.
`<binary>`	The argument must be a binary expression in WKB or EWKB format.
`<srid>`	The integer value of the SRID to use.

Return Type

Geometry.

SQL Examples

SELECT
  ST_GEOMETRYFROMWKB(
    '0101000020797f000066666666a9cb17411f85ebc19e325641'
  ) AS pipeline_geometry;

┌────────────────────────────────────────┐
│            pipeline_geometry           │
├────────────────────────────────────────┤
│ SRID=32633;POINT(389866.35 5819003.03) │
└────────────────────────────────────────┘

SELECT
  ST_GEOMETRYFROMWKB(
    FROM_HEX('0101000020797f000066666666a9cb17411f85ebc19e325641'), 4326
  ) AS pipeline_geometry;

┌───────────────────────────────────────┐
│           pipeline_geometry           │
├───────────────────────────────────────┤
│ SRID=4326;POINT(389866.35 5819003.03) │
└───────────────────────────────────────┘

19 - ST_GEOMETRYFROMWKT

Parses a WKT(well-known-text) or EWKT(extended well-known-text) input and returns a value of type GEOMETRY.

SQL Syntax

ST_GEOMETRYFROMWKT(<string>, [<srid>])

Aliases

Arguments

Arguments	Description
`<string>`	The argument must be a string expression in WKT or EWKT format.
`<srid>`	The integer value of the SRID to use.

Return Type

Geometry.

SQL Examples

SELECT
  ST_GEOMETRYFROMWKT(
    'POINT(1820.12 890.56)'
  ) AS pipeline_geometry;

┌───────────────────────┐
│   pipeline_geometry   │
├───────────────────────┤
│ POINT(1820.12 890.56) │
└───────────────────────┘

SELECT
  ST_GEOMETRYFROMWKT(
    'POINT(1820.12 890.56)', 4326
  ) AS pipeline_geometry;

┌─────────────────────────────────┐
│        pipeline_geometry        │
│             Geometry            │
├─────────────────────────────────┤
│ SRID=4326;POINT(1820.12 890.56) │
└─────────────────────────────────┘

20 - ST_GEOMFROMEWKB

Alias for ST_GEOMTRYFROMWKB.

21 - ST_GEOMFROMEWKT

Alias for ST_GEOMTRYFROMWKT.

22 - ST_GEOMFROMGEOHASH

Returns a GEOMETRY object for the polygon that represents the boundaries of a geohash.

SQL Syntax

ST_GEOMFROMGEOHASH(<geohash>)

Arguments

Arguments	Description
`<geohash>`	The argument must be a geohash.

Return Type

Geometry.

SQL Examples

SELECT
  ST_GEOMFROMGEOHASH(
    '9q60y60rhs'
  ) AS pipeline_geometry;

┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                     st_geomfromgeohash('9q60y60rhs')                                                                                     │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ POLYGON((-120.66230535507202 35.30029535293579,-120.66230535507202 35.30030071735382,-120.66229462623596 35.30030071735382,-120.66229462623596 35.30029535293579,-120.66230535507202 35.30029535293579)) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

23 - ST_GEOMFROMTEXT

Alias for ST_GEOMTRYFROMWKT.

24 - ST_GEOMFROMWKB

Alias for ST_GEOMTRYFROMWKB.

25 - ST_GEOMFROMWKT

Alias for ST_GEOMTRYFROMWKT.

26 - ST_GEOMPOINTFROMGEOHASH

Returns a GEOMETRY object for the point that represents center of a geohash.

SQL Syntax

ST_GEOMPOINTFROMGEOHASH(<geohash>)

Arguments

Arguments	Description
`<geohash>`	The argument must be a geohash.

Return Type

Geometry.

SQL Examples

SELECT
  ST_GEOMPOINTFROMGEOHASH(
    's02equ0'
  ) AS pipeline_geometry;

┌──────────────────────────────────────────────┐
│               pipeline_geometry              │
│                   Geometry                   │
├──────────────────────────────────────────────┤
│ POINT(1.0004425048828125 2.0001983642578125) │
└──────────────────────────────────────────────┘

27 - ST_LENGTH

Returns the Euclidean length of the LineString(s) in a GEOMETRY object.

SQL Syntax

ST_LENGTH(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY containing linestrings.

:::note

If <geometry> is not a LineString, MultiLineString, or GeometryCollection containing linestrings, returns 0.
If <geometry> is a GeometryCollection, returns the sum of the lengths of the linestrings in the collection. :::

Return Type

Double.

SQL Examples

SELECT
  ST_LENGTH(TO_GEOMETRY('POINT(1 1)')) AS length

┌─────────┐
│  length │
├─────────┤
│       0 │
└─────────┘

SELECT
  ST_LENGTH(TO_GEOMETRY('LINESTRING(0 0, 1 1)')) AS length

┌─────────────┐
│    length   │
├─────────────┤
│ 1.414213562 │
└─────────────┘

SELECT
  ST_LENGTH(
    TO_GEOMETRY('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')
  ) AS length

┌─────────┐
│  length │
├─────────┤
│       0 │
└─────────┘

28 - ST_MAKE_LINE

Alias for ST_MAKELINE.

29 - ST_MAKEGEOMPOINT

Constructs a GEOMETRY object that represents a Point with the specified longitude and latitude.

SQL Syntax

ST_MAKEGEOMPOINT(<longitude>, <latitude>)

Aliases

ST_GEOM_POINT

Arguments

Arguments	Description
`<longitude>`	A Double value that represents the longitude.
`<latitude>`	A Double value that represents the latitude.

Return Type

Geometry.

SQL Examples

SELECT
  ST_MAKEGEOMPOINT(
    7.0, 8.0
  ) AS pipeline_point;

┌────────────────┐
│ pipeline_point │
├────────────────┤
│ POINT(7 8)     │
└────────────────┘

SELECT
  ST_MAKEGEOMPOINT(
    -122.3061, 37.554162
  ) AS pipeline_point;

┌────────────────────────────┐
│       pipeline_point       │
├────────────────────────────┤
│ POINT(-122.3061 37.554162) │
└────────────────────────────┘

30 - ST_MAKELINE

Constructs a GEOMETRY object that represents a line connecting the points in the input two GEOMETRY objects.

SQL Syntax

ST_MAKELINE(<geometry1>, <geometry2>)

Aliases

ST_MAKE_LINE

Arguments

Arguments	Description
`<geometry1>`	A GEOMETRY object containing the points to connect. This object must be a Point, MultiPoint, or LineString.
`<geometry2>`	A GEOMETRY object containing the points to connect. This object must be a Point, MultiPoint, or LineString.

Return Type

Geometry.

SQL Examples

SELECT
  ST_MAKELINE(
    ST_GEOMETRYFROMWKT(
      'POINT(-122.306100 37.554162)'
    ),
    ST_GEOMETRYFROMWKT(
      'POINT(-104.874173 56.714538)'
    )
  ) AS pipeline_line;

┌───────────────────────────────────────────────────────┐
│                     pipeline_line                     │
├───────────────────────────────────────────────────────┤
│ LINESTRING(-122.3061 37.554162,-104.874173 56.714538) │
└───────────────────────────────────────────────────────┘

31 - ST_MAKEPOLYGON

Constructs a GEOMETRY object that represents a Polygon without holes. The function uses the specified LineString as the outer loop.

SQL Syntax

ST_MAKEPOLYGON(<geometry>)

Aliases

ST_POLYGON

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Geometry.

SQL Examples

SELECT
  ST_MAKEPOLYGON(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)'
    )
  ) AS pipeline_polygon;

┌────────────────────────────────┐
│        pipeline_polygon        │
├────────────────────────────────┤
│ POLYGON((0 0,1 0,1 2,0 2,0 0)) │
└────────────────────────────────┘

32 - ST_NPOINTS

Returns the number of points in a GEOMETRY object.

SQL Syntax

ST_NPOINTS(<geometry>)

Aliases

ST_NUMPOINTS

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY object.

Return Type

UInt8.

SQL Examples

SELECT ST_NPOINTS(TO_GEOMETRY('POINT(66 12)')) AS npoints

┌─────────┐
│ npoints │
├─────────┤
│       1 │
└─────────┘

SELECT ST_NPOINTS(TO_GEOMETRY('MULTIPOINT((45 21),(12 54))')) AS npoints

┌─────────┐
│ npoints │
├─────────┤
│       2 │
└─────────┘

SELECT ST_NPOINTS(TO_GEOMETRY('LINESTRING(40 60,50 50,60 40)')) AS npoints

┌─────────┐
│ npoints │
├─────────┤
│       3 │
└─────────┘

SELECT ST_NPOINTS(TO_GEOMETRY('MULTILINESTRING((1 1,32 17),(33 12,73 49,87.1 6.1))')) AS npoints

┌─────────┐
│ npoints │
├─────────┤
│       5 │
└─────────┘

SELECT ST_NPOINTS(TO_GEOMETRY('GEOMETRYCOLLECTION(POLYGON((-10 0,0 10,10 0,-10 0)),LINESTRING(40 60,50 50,60 40),POINT(99 11))')) AS npoints

┌─────────┐
│ npoints │
├─────────┤
│       8 │
└─────────┘

33 - ST_NUMPOINTS

Alias for ST_NPOINTS.

34 - ST_POINTN

Returns a Point at a specified index in a LineString.

SQL Syntax

ST_POINTN(<geometry>, <index>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY that represents a LineString.
`<index>`	The index of the Point to return.

:::note The index is 1-based, and a negative index is uesed as the offset from the end of LineString. If index is out of bounds, the function returns an error. :::

Return Type

Geometry.

SQL Examples

SELECT
  ST_POINTN(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(1 1, 2 2, 3 3, 4 4)'
    ),
    1
  ) AS pipeline_pointn;

┌─────────────────┐
│ pipeline_pointn │
├─────────────────┤
│ POINT(1 1)      │
└─────────────────┘

SELECT
  ST_POINTN(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(1 1, 2 2, 3 3, 4 4)'
    ),
    -2
  ) AS pipeline_pointn;

┌─────────────────┐
│ pipeline_pointn │
├─────────────────┤
│ POINT(3 3)      │
└─────────────────┘

35 - ST_POLYGON

Alias for ST_MAKEPOLYGON.

36 - ST_SETSRID

Returns a GEOMETRY object that has its SRID (spatial reference system identifier) set to the specified value. This Function only change the SRID without affecting the coordinates of the object. If you also need to change the coordinates to match the new SRS (spatial reference system), use ST_TRANSFORM instead.

SQL Syntax

ST_SETSRID(<geometry>, <srid>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY object.
`<srid>`	The SRID integer to set in the returned GEOMETRY object.

Return Type

Geometry.

SQL Examples

SET GEOMETRY_OUTPUT_FORMAT = 'EWKT'

SELECT ST_SETSRID(TO_GEOMETRY('POINT(13 51)'), 4326) AS geometry

┌────────────────────────┐
│        geometry        │
├────────────────────────┤
│ SRID=4326;POINT(13 51) │
└────────────────────────┘

37 - ST_SRID

Returns the SRID (spatial reference system identifier) of a GEOMETRY object.

SQL Syntax

ST_SRID(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

INT32.

:::note If the Geometry don't have a SRID, a default value 4326 will be returned. :::

SQL Examples

SELECT
  ST_SRID(
    TO_GEOMETRY(
      'POINT(-122.306100 37.554162)',
      1234
    )
  ) AS pipeline_srid;

┌───────────────┐
│ pipeline_srid │
├───────────────┤
│          1234 │
└───────────────┘

SELECT
  ST_SRID(
    ST_MAKEGEOMPOINT(
      37.5, 45.5
    )
  ) AS pipeline_srid;

┌───────────────┐
│ pipeline_srid │
├───────────────┤
│          4326 │
└───────────────┘

38 - ST_STARTPOINT

Returns the first Point in a LineString.

SQL Syntax

ST_STARTPOINT(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY that represents a LineString.

Return Type

Geometry.

SQL Examples

SELECT
  ST_STARTPOINT(
    ST_GEOMETRYFROMWKT(
      'LINESTRING(1 1, 2 2, 3 3, 4 4)'
    )
  ) AS pipeline_endpoint;

┌───────────────────┐
│ pipeline_endpoint │
├───────────────────┤
│ POINT(1 1)        │
└───────────────────┘

39 - ST_TRANSFORM

Converts a GEOMETRY object from one spatial reference system (SRS) to another. If you just need to change the SRID without changing the coordinates (e.g. if the SRID was incorrect), use ST_SETSRID instead.

SQL Syntax

ST_TRANSFORM(<geometry> [, <from_srid>], <to_srid>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY object.
`<from_srid>`	Optional SRID identifying the current SRS of the input GEOMETRY object, if this argument is omitted, use the SRID specified in the input GEOMETRY object.
`<to_srid>`	The SRID that identifies the SRS to use, transforms the input GEOMETRY object to a new object that uses this SRS.

Return Type

Geometry.

SQL Examples

SET GEOMETRY_OUTPUT_FORMAT = 'EWKT'

SELECT ST_TRANSFORM(ST_GEOMFROMWKT('POINT(389866.35 5819003.03)', 32633), 3857) AS transformed_geom

┌───────────────────────────────────────────────┐
│                transformed_geom               │
├───────────────────────────────────────────────┤
│ SRID=3857;POINT(1489140.093766 6892872.19868) │
└───────────────────────────────────────────────┘

SELECT ST_TRANSFORM(ST_GEOMFROMWKT('POINT(4.500212 52.161170)'), 4326, 28992) AS transformed_geom

┌──────────────────────────────────────────────┐
│               transformed_geom               │
├──────────────────────────────────────────────┤
│ SRID=28992;POINT(94308.670475 464038.168827) │
└──────────────────────────────────────────────┘

40 - ST_X

Returns the longitude (X coordinate) of a Point represented by a GEOMETRY object.

SQL Syntax

ST_X(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY and must contain a Point.

Return Type

Double.

SQL Examples

SELECT
  ST_X(
    ST_MAKEGEOMPOINT(
      37.5, 45.5
    )
  ) AS pipeline_x;

┌────────────┐
│ pipeline_x │
├────────────┤
│       37.5 │
└────────────┘

41 - ST_XMAX

Returns the maximum longitude (X coordinate) of all points contained in the specified GEOMETRY object.

SQL Syntax

ST_XMAX(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Double.

SQL Examples

SELECT
  ST_XMAX(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(40 10),LINESTRING(10 10,20 20,10 40),POINT EMPTY)'
    )
  ) AS pipeline_xmax;

┌───────────────┐
│ pipeline_xmax │
├───────────────┤
│            40 │
└───────────────┘

SELECT
  ST_XMAX(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(40 10),LINESTRING(10 10,20 20,10 40),POLYGON((40 40,20 45,45 30,40 40)))'
    )
  ) AS pipeline_xmax;

┌───────────────┐
│ pipeline_xmax │
├───────────────┤
│            45 │
└───────────────┘

42 - ST_XMIN

Returns the minimum longitude (X coordinate) of all points contained in the specified GEOMETRY object.

SQL Syntax

ST_XMIN(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Double.

SQL Examples

SELECT
  ST_XMIN(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(180 10),LINESTRING(20 10,30 20,40 40),POINT EMPTY)'
    )
  ) AS pipeline_xmin;

┌───────────────┐
│ pipeline_xmin │
├───────────────┤
│            20 │
└───────────────┘

SELECT
  ST_XMIN(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(40 10),LINESTRING(20 10,30 20,10 40),POLYGON((40 40,20 45,45 30,40 40)))'
    )
  ) AS pipeline_xmin;

┌───────────────┐
│ pipeline_xmin │
├───────────────┤
│            10 │
└───────────────┘

43 - ST_Y

Returns the latitude (Y coordinate) of a Point represented by a GEOMETRY object.

SQL Syntax

ST_Y(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY and must contain a Point.

Return Type

Double.

SQL Examples

SELECT
  ST_Y(
    ST_MAKEGEOMPOINT(
      37.5, 45.5
    )
  ) AS pipeline_y;

┌────────────┐
│ pipeline_y │
├────────────┤
│       45.5 │
└────────────┘

44 - ST_YMAX

Returns the maximum latitude (Y coordinate) of all points contained in the specified GEOMETRY object.

SQL Syntax

ST_YMAX(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Double.

SQL Examples

SELECT
  ST_YMAX(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(180 50),LINESTRING(10 10,20 20,10 40),POINT EMPTY)'
    )
  ) AS pipeline_ymax;

┌───────────────┐
│ pipeline_ymax │
├───────────────┤
│            50 │
└───────────────┘

SELECT
  ST_YMAX(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(40 10),LINESTRING(10 10,20 20,10 40),POLYGON((40 40,20 45,45 30,40 40)))'
    )
  ) AS pipeline_ymax;

┌───────────────┐
│ pipeline_ymax │
├───────────────┤
│            45 │
└───────────────┘

45 - ST_YMIN

Returns the minimum latitude (Y coordinate) of all points contained in the specified GEOMETRY object.

SQL Syntax

ST_YMIN(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

Double.

SQL Examples

SELECT
  ST_YMIN(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(-180 -10),LINESTRING(-179 0, 179 30),POINT EMPTY)'
    )
  ) AS pipeline_ymin;

┌───────────────┐
│ pipeline_ymin │
├───────────────┤
│           -10 │
└───────────────┘

SELECT
  ST_YMIN(
    TO_GEOMETRY(
      'GEOMETRYCOLLECTION(POINT(180 0),LINESTRING(-60 -30, 60 30),POLYGON((40 40,20 45,45 30,40 40)))'
    )
  ) AS pipeline_ymin;

┌───────────────┐
│ pipeline_ymin │
├───────────────┤
│           -30 │
└───────────────┘

46 - TO_GEOMETRY

Parses an input and returns a value of type GEOMETRY.

TRY_TO_GEOMETRY returns a NULL value if an error occurs during parsing.

SQL Syntax

TO_GEOMETRY(<string>, [<srid>])
TO_GEOMETRY(<binary>, [<srid>])
TO_GEOMETRY(<variant>, [<srid>])
TRY_TO_GEOMETRY(<string>, [<srid>])
TRY_TO_GEOMETRY(<binary>, [<srid>])
TRY_TO_GEOMETRY(<variant>, [<srid>])

Arguments

Arguments	Description
`<string>`	The argument must be a string expression in WKT, EWKT, WKB or EWKB in hexadecimal format, GeoJSON format.
`<binary>`	The argument must be a binary expression in WKB or EWKB format.
`<variant>`	The argument must be a JSON OBJECT in GeoJSON format.
`<srid>`	The integer value of the SRID to use.

Return Type

Geometry.

SQL Examples

SELECT
  TO_GEOMETRY(
    'POINT(1820.12 890.56)'
  ) AS pipeline_geometry;

┌───────────────────────┐
│   pipeline_geometry   │
├───────────────────────┤
│ POINT(1820.12 890.56) │
└───────────────────────┘

SELECT
  TO_GEOMETRY(
    '0101000020797f000066666666a9cb17411f85ebc19e325641', 4326
  ) AS pipeline_geometry;

┌───────────────────────────────────────┐
│           pipeline_geometry           │
├───────────────────────────────────────┤
│ SRID=4326;POINT(389866.35 5819003.03) │
└───────────────────────────────────────┘

SELECT
  TO_GEOMETRY(
    FROM_HEX('0101000020797f000066666666a9cb17411f85ebc19e325641'), 4326
  ) AS pipeline_geometry;

┌───────────────────────────────────────┐
│           pipeline_geometry           │
├───────────────────────────────────────┤
│ SRID=4326;POINT(389866.35 5819003.03) │
└───────────────────────────────────────┘

SELECT
  TO_GEOMETRY(
    '{"coordinates":[[389866,5819003],[390000,5830000]],"type":"LineString"}'
  ) AS pipeline_geometry;

┌───────────────────────────────────────────┐
│             pipeline_geometry             │
├───────────────────────────────────────────┤
│ LINESTRING(389866 5819003,390000 5830000) │
└───────────────────────────────────────────┘

SELECT
  TO_GEOMETRY(
    PARSE_JSON('{"coordinates":[[389866,5819003],[390000,5830000]],"type":"LineString"}')
  ) AS pipeline_geometry;

┌───────────────────────────────────────────┐
│             pipeline_geometry             │
├───────────────────────────────────────────┤
│ LINESTRING(389866 5819003,390000 5830000) │
└───────────────────────────────────────────┘

47 - TO_STRING

Converts a GEOMETRY object into a String representation. The display format of the output data is controlled by the geometry_output_format setting, which contains the following types:

Parameter	Description
GeoJSON (default)	The GEOMETRY result is rendered as a JSON object in GeoJSON format.
WKT	The GEOMETRY result is rendered as a String in WKT format.
WKB	The GEOMETRY result is rendered as a Binary in WKB format.
EWKT	The GEOMETRY result is rendered as a String in EWKT format.
EWKB	The GEOMETRY result is rendered as a Binary in EWKB format.

SQL Syntax

TO_STRING(<geometry>)

Arguments

Arguments	Description
`<geometry>`	The argument must be an expression of type GEOMETRY.

Return Type

String.

SQL Examples

SET geometry_output_format='GeoJSON';

SELECT
  TO_GEOMETRY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geometry;

┌────────────────────────────────────────────────────────────────────────────┐
│                              pipeline_geometry                             │
├────────────────────────────────────────────────────────────────────────────┤
│ {"type": "LineString", "coordinates": [[400000,6000000],[401000,6010000]]} │
└────────────────────────────────────────────────────────────────────────────┘

SET geometry_output_format='WKT';

SELECT
  TO_GEOMETRY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geometry;

┌───────────────────────────────────────────┐
│             pipeline_geometry             │
├───────────────────────────────────────────┤
│ LINESTRING(400000 6000000,401000 6010000) │
└───────────────────────────────────────────┘

SET geometry_output_format='EWKT';

SELECT
  TO_GEOMETRY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geometry;

┌─────────────────────────────────────────────────────┐
│                  pipeline_geometry                  │
├─────────────────────────────────────────────────────┤
│ SRID=4326;LINESTRING(400000 6000000,401000 6010000) │
└─────────────────────────────────────────────────────┘

SET geometry_output_format='WKB';

SELECT
  TO_GEOMETRY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geometry;

┌────────────────────────────────────────────────────────────────────────────────────┐
│                                  pipeline_geometry                                 │
├────────────────────────────────────────────────────────────────────────────────────┤
│ 01020000000200000000000000006A18410000000060E3564100000000A07918410000000024ED5641 │
└────────────────────────────────────────────────────────────────────────────────────┘

SET geometry_output_format='EWKB';

SELECT
  TO_GEOMETRY(
    ST_GEOMETRYFROMWKT(
      'SRID=4326;LINESTRING(400000 6000000, 401000 6010000)'
    )
  ) AS pipeline_geometry;

┌────────────────────────────────────────────────────────────────────────────────────────────┐
│                                      pipeline_geometry                                     │
├────────────────────────────────────────────────────────────────────────────────────────────┤
│ 0102000020E61000000200000000000000006A18410000000060E3564100000000A07918410000000024ED5641 │
└────────────────────────────────────────────────────────────────────────────────────────────┘