ReferenceSQL ReferenceFunctions

Geo Polygon

Geo Polygon functions reference.

polygonAreaCartesian

Calculates the area of a polygon.

Syntax

polygonAreaCartesian(polygon)

Arguments

  • polygon — A polygon value Polygon

Returned value

Returns the area of the polygon Float64

Examples

Usage example

SELECT polygonAreaCartesian([[[(0., 0.), (0., 5.), (5., 5.), (5., 0.)]]])
25

Introduced in version 21.4.

polygonAreaSpherical

Calculates the surface area of a polygon.

Syntax

polygonAreaSpherical(polygon)

Arguments

  • polygon — A polygon value. Polygon

Returned value

Returns the surface area of the polygon Float64

Examples

Spherical example

SELECT round(polygonAreaSpherical([[[(4.346693, 50.858306), (4.367945, 50.852455), (4.366227, 50.840809), (4.344961, 50.833264), (4.338074, 50.848677), (4.346693, 50.858306)]]]), 14)
9.387704e-8

Introduced in version 21.4.

polygonConvexHullCartesian

Calculates a convex hull. Reference

Coordinates are in Cartesian coordinate system.

Syntax

polygonConvexHullCartesian(multipolygon)

Arguments

Returned value

Returns the convex hull as a Polygon. Polygon

Examples

Conve hull example

SELECT wkt(polygonConvexHullCartesian([[[(0., 0.), (0., 5.), (5., 5.), (5., 0.), (2., 3.)]]]))
POLYGON((0 0,0 5,5 5,5 0,0 0))

Introduced in version 21.4.

polygonPerimeterCartesian

Calculates the perimeter of a polygon.

Syntax

polygonPerimeterCartesian(polygon)

Arguments

  • polygon — A Polygon value. Polygon

Returned value

Returns the perimeter of the polygon. Float64

Examples

Usage example

SELECT polygonPerimeterCartesian([[[(0., 0.), (0., 5.), (5., 5.), (5., 0.)]]])
15

Introduced in version 21.4.

polygonPerimeterSpherical

Calculates the perimeter of the polygon.

Syntax

polygonPerimeterSpherical(polygon)

Arguments

  • polygon — A value of type Polygon

Returned value

The perimeter of the polygon on a sphere Float64

Examples

spherical_example

SELECT round(polygonPerimeterSpherical([[[(4.346693, 50.858306), (4.367945, 50.852455), (4.366227, 50.840809), (4.344961, 50.833264), (4.338074, 50.848677), (4.346693, 50.858306)]]]), 6)
0.045539

Introduced in version 21.4.

polygonsDistanceCartesian

Calculates distance between two polygons.

Syntax

polygonsDistanceCartesian(polygon1, polygon2)

Arguments

  • polygon1 — A Polygon value Polygon
  • polygon2 — A Polygon value Polygon

Returned value

Returns the minimal distance between the two polygons Float64

Examples

Distance example

SELECT polygonsDistanceCartesian([[[(0, 0), (0, 0.1), (0.1, 0.1), (0.1, 0)]]], [[[(10., 10.), (10., 40.), (40., 40.), (40., 10.), (10., 10.)]]])
14.000714267493642

Introduced in version 21.4.

polygonsDistanceSpherical

Calculates the minimal distance between two points where one point belongs to the first polygon and the second to another polygon. Spherical means that coordinates are interpreted as coordinates on a pure and ideal sphere, which is not true for the Earth. Using this type of coordinate system speeds up execution, but of course is not precise.

Syntax

polygonsDistanceSpherical(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon value. Polygon
  • polygon2 — The second Polygon value. Polygon

Returned value

Returns the minimal distance between the two polygons on a sphere Float64

Examples

Spherical distance

SELECT polygonsDistanceSpherical([[[(0, 0), (0, 0.1), (0.1, 0.1), (0.1, 0)]]], [[[(10., 10.), (10., 40.), (40., 40.), (40., 10.), (10., 10.)]]])
0.24372872211133834

Introduced in version 21.4.

polygonsEqualsCartesian

Returns true if two polygons are equal.

Syntax

polygonsEqualsCartesian(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon. Polygon

Returned value

Returns 1 if equal, otherwise 0. UInt8

Examples

Equality check example

SELECT polygonsEqualsCartesian([[[(1., 1.), (1., 4.), (4., 4.), (4., 1.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])
1

Introduced in version 21.4.

polygonsIntersectionCartesian

Calculates the intersection of polygons.

Syntax

polygonsIntersectionCartesian(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon. Polygon

Returned value

Returns the intersection of the polygons as a MultiPolygon. MultiPolygon

Examples

Intersection example

SELECT wkt(polygonsIntersectionCartesian([[[(0., 0.), (0., 3.), (1., 2.9), (2., 2.6), (2.6, 2.), (2.9, 1.), (3., 0.), (0., 0.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]]))
MULTIPOLYGON(((1 2.9,2 2.6,2.6 2,2.9 1,1 1,1 2.9)))

Introduced in version 21.4.

polygonsIntersectionSpherical

Calculates the intersection (AND) between polygons, coordinates are spherical.

Syntax

polygonsIntersectionSpherical(polygon1, polygon2)

Arguments

  • polygon1 — First Polygon with spherical coordinates. Polygon
  • polygon2 — Second Polygon with spherical coordinates. Polygon

Returned value

Returns the intersection of the polygons as a MultiPolygon. MultiPolygon

Examples

Spherical intersection example

SELECT wkt(arrayMap(a -> arrayMap(b -> arrayMap(c -> (round(c.1, 6), round(c.2, 6)), b), a), polygonsIntersectionSpherical([[[(4.3613577, 50.8651821), (4.349556, 50.8535879), (4.3602419, 50.8435626), (4.3830299, 50.8428851), (4.3904543, 50.8564867), (4.3613148, 50.8651279)]]], [[[(4.346693, 50.858306), (4.367945, 50.852455), (4.366227, 50.840809), (4.344961, 50.833264), (4.338074, 50.848677), (4.346693, 50.858306)]]]])))
MULTIPOLYGON(((4.3666 50.8434,4.36024 50.8436,4.34956 50.8536,4.35268 50.8567,4.36794 50.8525,4.3666 50.8434)))

Introduced in version 21.4.

polygonsSymDifferenceCartesian

The same as polygonsSymDifferenceSpherical, but the coordinates are in the Cartesian coordinate system; which is more close to the model of the real Earth.

Syntax

polygonsSymDifferenceCartesian(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon Polygon

Returned value

Returns the symmetric difference of the polygons as a MultiPolygon. MultiPolygon

Examples

Usage example

SELECT wkt(polygonsSymDifferenceCartesian([[[(0, 0), (0, 3), (1, 2.9), (2, 2.6), (2.6, 2), (2.9, 1), (3, 0), (0, 0)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]]))
MULTIPOLYGON(((1 2.9,1 1,2.9 1,3 0,0 0,0 3,1 2.9)),((1 2.9,1 4,4 4,4 1,2.9 1,2.6 2,2 2.6,1 2.9)))

Introduced in version 21.4.

polygonsSymDifferenceSpherical

Calculates the spatial set theoretic symmetric difference (XOR) between two polygons

Syntax

polygonsSymDifferenceSpherical(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon Polygon

Returned value

Returns the symmetric difference of the polygons as a MultiPolygon. MultiPolygon

Examples

Usage example

SELECT wkt(arraySort(polygonsSymDifferenceSpherical([[(50., 50.), (50., -50.), (-50., -50.), (-50., 50.), (50., 50.)], [(10., 10.), (10., 40.), (40., 40.), (40., 10.), (10., 10.)], [(-10., -10.), (-10., -40.), (-40., -40.), (-40., -10.), (-10., -10.)]], [[(-20., -20.), (-20., 20.), (20., 20.), (20., -20.), (-20., -20.)]])));
MULTIPOLYGON(((-20 -10.3067,-10 -10,-10 -20.8791,-20 -20,-20 -10.3067)),((10 20.8791,20 20,20 10.3067,10 10,10 20.8791)),((50 50,50 -50,-50 -50,-50 50,50 50),(20 10.3067,40 10,40 40,10 40,10 20.8791,-20 20,-20 -10.3067,-40 -10,-40 -40,-10 -40,-10 -20.8791,20 -20,20 10.3067)))

Introduced in version 21.4.

polygonsUnionCartesian

Calculates the union of polygons.

Syntax

polygonsUnionCartesian(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon. Polygon

Returned value

Returns the union of the polygons as a MultiPolygon. MultiPolygon

Examples

Usage example

SELECT wkt(polygonsUnionCartesian([[[(0., 0.), (0., 3.), (1., 2.9), (2., 2.6), (2.6, 2.), (2.9, 1), (3., 0.), (0., 0.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]]))
MULTIPOLYGON(((1 2.9,1 4,4 4,4 1,2.9 1,3 0,0 0,0 3,1 2.9)))

Introduced in version 21.4.

polygonsUnionSpherical

Calculates a union (OR) of polygons.

Syntax

polygonsUnionSpherical(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon. Polygon

Returned value

Returns the union of the polygons as a MultiPolygon. MultiPolygon

Examples

Usage example

SELECT wkt(polygonsUnionSpherical([[[(4.3613577, 50.8651821), (4.349556, 50.8535879), (4.3602419, 50.8435626), (4.3830299, 50.8428851), (4.3904543, 50.8564867), (4.3613148, 50.8651279)]]], [[[(4.346693, 50.858306), (4.367945, 50.852455), (4.366227, 50.840809), (4.344961, 50.833264), (4.338074, 50.848677), (4.346693, 50.858306)]]]))
MULTIPOLYGON(((4.36661 50.8434,4.36623 50.8408,4.34496 50.8333,4.33807 50.8487,4.34669 50.8583,4.35268 50.8567,4.36136 50.8652,4.36131 50.8651,4.39045 50.8565,4.38303 50.8429,4.36661 50.8434)))

Introduced in version 21.4.

polygonsWithinCartesian

Checks if a polygon is within another polygon.

Syntax

polygonsWithinCartesian(polygon1, polygon2)

Arguments

  • polygon1 — The first polygon. Polygon
  • polygon2 — The second polygon. Polygon

Returned value

Returns 1 if polygon1 is contained in polygon2, otherwise 0. UInt8

Examples

Usage example

SELECT polygonsWithinCartesian([[[(2., 2.), (2., 3.), (3., 3.), (3., 2.)]]], [[[(1., 1.), (1., 4.), (4., 4.), (4., 1.), (1., 1.)]]])
1

Introduced in version 21.4.

polygonsWithinSpherical

Returns true or false depending on whether or not one polygon lies completely inside another polygon. Reference

Syntax

polygonsWithinSpherical(polygon1, polygon2)

Arguments

  • polygon1 — The first Polygon. Polygon
  • polygon2 — The second Polygon. Polygon

Returned value

Returns 1 if polygon1 lies completely within polygon2, otherwise 0. UInt8

Examples

Usage example

SELECT polygonsWithinSpherical([[[(4.3613577, 50.8651821), (4.349556, 50.8535879), (4.3602419, 50.8435626), (4.3830299, 50.8428851), (4.3904543, 50.8564867), (4.3613148, 50.8651279)]]], [[[(4.346693, 50.858306), (4.367945, 50.852455), (4.366227, 50.840809), (4.344961, 50.833264), (4.338074, 50.848677), (4.346693, 50.858306)]]]);
0

Introduced in version 21.4.

readWKBLineString

Parses a Well-Known Binary (WKB) representation of a LineString geometry and returns it in the internal RawTree format.

Syntax

readWKBLineString(wkb_string)

Arguments

  • wkb_string — The input WKB string representing a LineString geometry. String

Returned value

Returns returns a RawTree internal representation of the linestring geometry. Geo

Examples

Usage example

SELECT readWKBLineString(unhex('010200000004000000000000000000f03f000000000000f03f0000000000000040000000000000004000000000000008400000000000000840000000000000f03f000000000000f03f'));
[(1,1),(2,2),(3,3),(1,1)]

Introduced in version 25.11.

readWKBMultiLineString

Parses a Well-Known Binary (WKB) representation of a MultiLineString geometry and returns it in the internal RawTree format.

Syntax

readWKBMultiLineString(wkb_string)

Arguments

  • wkb_string — The input WKB string representing a MultiLineString geometry. String

Returned value

Returns a RawTree internal representation of the multilinestring geometry. Geo

Examples

Usage example

SELECT readWKBMultiLineString(unhex('010500000002000000010200000003000000000000000000f03f000000000000f03f0000000000000040000000000000004000000000000008400000000000000840010200000003000000000000000000104000000000000010400000000000001440000000000000144000000000000018400000000000001840'));
[[(1,1),(2,2),(3,3)],[(4,4),(5,5),(6,6)]]

Introduced in version 25.11.

readWKBMultiPolygon

Parses a Well-Known Binary (WKB) representation of a MultiPolygon geometry and returns it in the internal RawTree format.

Syntax

readWKBMultiPolygon(wkb_string)

Arguments

  • wkb_string — The input WKB string representing a MultiPolygon geometry. String

Returned value

Returns a RawTree internal representation of the MultiPolygon geometry. Geo

Examples

Usage example

SELECT
    toTypeName(readWKBMultiPolygon(unhex('0106000000020000000103000000020000000500000000000000000000400000000000000000000000000000244000000000000000000000000000002440000000000000244000000000000000000000000000002440000000000000004000000000000000000500000000000000000010400000000000001040000000000000144000000000000010400000000000001440000000000000144000000000000010400000000000001440000000000000104000000000000010400103000000010000000400000000000000000024c000000000000024c000000000000024c000000000000022c000000000000022c0000000000000244000000000000024c000000000000024c0'))) AS type,
    readWKBMultiPolygon(unhex('0106000000020000000103000000020000000500000000000000000000400000000000000000000000000000244000000000000000000000000000002440000000000000244000000000000000000000000000002440000000000000004000000000000000000500000000000000000010400000000000001040000000000000144000000000000010400000000000001440000000000000144000000000000010400000000000001440000000000000104000000000000010400103000000010000000400000000000000000024c000000000000024c000000000000024c000000000000022c000000000000022c0000000000000244000000000000024c000000000000024c0')) FORMAT Vertical;
type:                     MultiPolygon
readWKBMulti~000024c0')): [[[(2,0),(10,0),(10,10),(0,10),(2,0)],[(4,4),(5,4),(5,5),(4,5),(4,4)]],[[(-10,-10),(-10,-9),(-9,10),(-10,-10)]]]

Introduced in version 25.11.

readWKBPoint

Parses a Well-Known Binary (WKB) representation of a Point geometry and returns it in the internal RawTree format.

Syntax

readWKBPoint(wkb_string)

Arguments

  • wkb_string — The input WKB string representing a Point geometry. String

Returned value

The function returns a RawTree internal representation of the point geometry. Geo

Examples

Usage example

SELECT toTypeName(readWKBPoint(unhex('0101000000333333333333f33f3333333333330b40')));
(1.2,3.4)

Introduced in version 25.11.

readWKBPolygon

Parses a Well-Known Binary (WKB) representation of a Polygon geometry and returns it in the internal RawTree format.

Syntax

readWKBPolygon(wkb_string)

Arguments

  • wkb_string — The input WKB string representing a Polygon geometry. String

Returned value

Returns a RawTree internal representation of the Polygon geometry. Geo

Examples

Usage example

SELECT
    toTypeName(readWKBPolygon(unhex('010300000001000000050000000000000000000040000000000000000000000000000024400000000000000000000000000000244000000000000024400000000000000000000000000000244000000000000000400000000000000000'))) AS type,
    readWKBPolygon(unhex('010300000001000000050000000000000000000040000000000000000000000000000024400000000000000000000000000000244000000000000024400000000000000000000000000000244000000000000000400000000000000000'));
Polygon [[(2,0),(10,0),(10,10),(0,10),(2,0)]]

Introduced in version 25.11.

readWKTLineString

Parses a Well-Known Text (WKT) representation of a LineString geometry and returns it in the internal RawTree format.

Syntax

readWKTLineString(wkt_string)

Arguments

  • wkt_string — The input WKT string representing a LineString geometry. String

Returned value

Returns a RawTree internal representation of the linestring geometry. Geo

Examples

Usage example

SELECT readWKTLineString('LINESTRING (1 1, 2 2, 3 3, 1 1)');
┌─readWKTLineString('LINESTRING (1 1, 2 2, 3 3, 1 1)')─┐
│ [(1,1),(2,2),(3,3),(1,1)]                            │
└──────────────────────────────────────────────────────┘

LineString example

SELECT toTypeName(readWKTLineString('LINESTRING (1 1, 2 2, 3 3, 1 1)'));
┌─toTypeName(readWKTLineString('LINESTRING (1 1, 2 2, 3 3, 1 1)'))─┐
│ LineString                                                       │
└──────────────────────────────────────────────────────────────────┘

Introduced in version 21.4.

readWKTMultiLineString

Parses a Well-Known Text (WKT) representation of a MultiLineString geometry and returns it in the internal RawTree format.

Syntax

readWKTMultiLineString(wkt_string)

Arguments

  • wkt_string — The input WKT string representing a MultiLineString geometry. String

Returned value

Returns the function returns a RawTree internal representation of the multilinestring geometry. Geo

Examples

Usage example

SELECT readWKTMultiLineString('MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5, 6 6))');
┌─readWKTMultiLineString('MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5, 6 6))')─┐
│ [[(1,1),(2,2),(3,3)],[(4,4),(5,5),(6,6)]]                                    │
└──────────────────────────────────────────────────────────────────────────────┘

MultiLineString example

SELECT toTypeName(readWKTLineString('MULTILINESTRING ((1 1, 2 2, 3 3, 1 1))'));
┌─toTypeName(readWKTLineString('MULTILINESTRING ((1 1, 2 2, 3 3, 1 1))'))─┐
│ MultiLineString                                                         │
└─────────────────────────────────────────────────────────────────────────┘

Introduced in version 21.4.

readWKTMultiPolygon

Converts a WKT (Well Known Text) MultiPolygon into a MultiPolygon type.

Syntax

readWKTMultiPolygon(wkt_string)

Arguments

  • wkt_string — String starting with MULTIPOLYGON String

Returned value

Returns a MultiPolygon MultiPolygon

Examples

Usage example

SELECT
    toTypeName(readWKTMultiPolygon('MULTIPOLYGON(((2 0,10 0,10 10,0 10,2 0),(4 4,5 4,5 5,4 5,4 4)),((-10 -10,-10 -9,-9 10,-10 -10)))')) AS type,
    readWKTMultiPolygon('MULTIPOLYGON(((2 0,10 0,10 10,0 10,2 0),(4 4,5 4,5 5,4 5,4 4)),((-10 -10,-10 -9,-9 10,-10 -10)))') AS output
[[[(2,0),(10,0),(10,10),(0,10),(2,0)],[(4,4),(5,4),(5,5),(4,5),(4,4)]],[[(-10,-10),(-10,-9),(-9,10),(-10,-10)]]]

Introduced in version 21.4.

readWKTPoint

The readWKTPoint function in RawTree parses a Well-Known Text (WKT) representation of a Point geometry and returns a point in the internal RawTree format.

Syntax

readWKTPoint(wkt_string)

Arguments

  • wkt_string — The input WKT string representing a Point geometry. String

Returned value

Returns a RawTree internal representation of the Point geometry. Geo

Examples

Usage example

SELECT readWKTPoint('POINT (1.2 3.4)');
(1.2,3.4)

Introduced in version 21.4.

readWKTPolygon

Converts a WKT (Well Known Text) MultiPolygon into a Polygon type.

Syntax

readWKTPolygon(wkt_string)

Arguments

  • wkt_string — String starting with POLYGON String

Returned value

Returns a Polygon Polygon

Examples

Usage example

SELECT
    toTypeName(readWKTPolygon('POLYGON((2 0,10 0,10 10,0 10,2 0))')) AS type,
    readWKTPolygon('POLYGON((2 0,10 0,10 10,0 10,2 0))') AS output
[[(2,0),(10,0),(10,10),(0,10),(2,0)]]

Introduced in version 21.4.

readWKTRing

Parses a Well-Known Text (WKT) representation of a Polygon geometry and returns a ring (closed linestring) in the internal RawTree format.

Syntax

readWKTRing(wkt_string)

Arguments

  • wkt_string — The input WKT string representing a Polygon geometry. String

Returned value

Returns a RawTree internal representation of the ring (closed linestring) geometry. Geo

Examples

Usage example

SELECT readWKTRing('POLYGON ((1 1, 2 2, 3 3, 1 1))');
[(1,1),(2,2),(3,3),(1,1)]

Introduced in version 21.4.

Geo

Geo functions reference.

Hash

Hash functions reference.

On this page

polygonAreaCartesianpolygonAreaSphericalpolygonConvexHullCartesianpolygonPerimeterCartesianpolygonPerimeterSphericalpolygonsDistanceCartesianpolygonsDistanceSphericalpolygonsEqualsCartesianpolygonsIntersectionCartesianpolygonsIntersectionSphericalpolygonsSymDifferenceCartesianpolygonsSymDifferenceSphericalpolygonsUnionCartesianpolygonsUnionSphericalpolygonsWithinCartesianpolygonsWithinSphericalreadWKBLineStringreadWKBMultiLineStringreadWKBMultiPolygonreadWKBPointreadWKBPolygonreadWKTLineStringreadWKTMultiLineStringreadWKTMultiPolygonreadWKTPointreadWKTPolygonreadWKTRing