Functions for Working with Geometry

Geometry

Geometry functions allow you to calculate perimeter and area for geometric types such as POLYGON, LINESTRING, MULTIPOLYGON, MULTILINESTRING, RING, and POINT. Use geometries in Geometry type. If the input value is NULL, all functions below will return 0.

perimeterCartesian

Calculates the perimeter of the given Geometry object in the Cartesian (flat) coordinate system. Syntax

perimeterCartesian(geom)

Arguments

geom — Geometry object. Geometry.

Returned values

Number — Perimeter of the object in the coordinate system units. Float64.

Example

Query

CREATE TABLE IF NOT EXISTS geo_dst (geom Geometry) ENGINE = Memory();
INSERT INTO geo_dst SELECT readWKT('POLYGON((0 0,1 0,1 1,0 1,0 0))');
SELECT perimeterCartesian(geom) FROM geo_dst;

Response

┌─perimeterCartesian(geom)─┐
│ 4.0                      │
└──────────────────────────┘

areaCartesian

Calculates the area of the given Geometry object in the Cartesian coordinate system. Syntax

areaCartesian(geom)

Arguments

geom — Geometry object. Geometry.

Returned values

Number — Area of the object in coordinate system units. Float64.

Example

Query

CREATE TABLE IF NOT EXISTS geo_dst (geom Geometry) ENGINE = Memory();
INSERT INTO geo_dst SELECT readWKT('POLYGON((0 0,1 0,1 1,0 1,0 0))');
SELECT areaCartesian(geom) FROM geo_dst;

Response

┌─areaCartesian(geom)─┐
│ -1                  │
└─────────────────────┘

perimeterSpherical

Calculates the perimeter of a Geometry object on the surface of a sphere. Syntax

perimeterSpherical(geom)

Arguments

geom — Geometry object. Geometry.

Returned values

Number — Perimeter on a sphere of radius 1, in radians (coordinates are longitude and latitude in degrees). Multiply by the radius of the sphere to get a length, for example by 6371 for kilometers on the Earth. Float64.

Example

Query

CREATE TABLE IF NOT EXISTS geo_dst (geom Geometry) ENGINE = Memory();
INSERT INTO geo_dst SELECT readWKT('LINESTRING(0 0,1 0,1 1,0 1,0 0)');
SELECT perimeterSpherical(geom) FROM geo_dst;

Response

┌─perimeterSpherical(geom)─┐
│      0.06981051179132045 │
└──────────────────────────┘

areaSpherical

Calculates the area of a Geometry object on the surface of a sphere. Syntax

areaSpherical(geom)

Arguments

geom — Geometry. Geometry.

Returned values

Number — Area on a sphere of radius 1, in steradians (coordinates are longitude and latitude in degrees). Multiply by the square of the radius of the sphere to get an area. Float64.

Example

Query

CREATE TABLE IF NOT EXISTS geo_dst (geom Geometry) ENGINE = Memory();
INSERT INTO geo_dst SELECT readWKT('POLYGON((0 0,1 0,1 1,0 1,0 0))');
SELECT areaSpherical(geom) FROM geo_dst;

Response

┌─areaSpherical(geom)────┐
│ -0.0003046096848622019 │
└────────────────────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Geometry

perimeterCartesian

areaCartesian

perimeterSpherical

areaSpherical

​Geometry

​perimeterCartesian

​areaCartesian

​perimeterSpherical

​areaSpherical

Geometry

perimeterCartesian

areaCartesian

perimeterSpherical

areaSpherical