Geometry Functions

This section describes the built-in functions for examining and manipulating GEOMETRY values.

Geometry operations run on Boost.Geometry. Function names and argument order follow PostGIS, but the set of functions is smaller and a few behaviours differ; see Differences from PostGIS at the end of this page.

Geometry Operators

The table below lists the operators that can be used with GEOMETRY values.

Operator	Description	Example	Result
&&	Returns true if the geometries bounding boxes intersect. Equivalent to ST_IntersectsExtent.	'POINT(5 5)'::GEOMETRY && 'LINESTRING(0 0, 10 20)'::GEOMETRY	true

Constructing Geometries

Name	Description
ST_Point	Creates a point from an X and a Y coordinate
ST_MakeLine	Creates a line through the given points
ST_MakePolygon	Creates a polygon from a closed ring, optionally with holes
ST_MakeEnvelope	Creates a rectangular polygon from minimum and maximum bounds
ST_Collect	Collects a list of geometries into a multi-geometry
ST_Points	Collects every vertex of a geometry into a MULTIPOINT
ST_Multi	Wraps a single geometry in the matching multi-geometry

ST_Point function

Creates a point from an X and a Y coordinate.

Query

SELECT ST_AsText(ST_Point(1, 2)) AS geom;

Result

 geom------------- POINT (1 2)

ST_MakeLine function

Creates a line through the given points. Accepts either two point arguments or a list of points.

Query

SELECT ST_AsText(ST_MakeLine('POINT(0 0)'::GEOMETRY, 'POINT(2 2)'::GEOMETRY)) AS geom;

Result

 geom----------------------- LINESTRING (0 0, 2 2)

ST_MakePolygon function

Creates a polygon from a closed ring. A second argument supplies a list of interior rings (holes).

Query

SELECT ST_AsText(ST_MakePolygon('LINESTRING(0 0,4 0,4 4,0 0)'::GEOMETRY)) AS geom;

Result

 geom-------------------------------- POLYGON ((0 0, 4 0, 4 4, 0 0))

ST_MakeEnvelope function

Creates a rectangular polygon from min_x, min_y, max_x and max_y.

Query

SELECT ST_AsText(ST_MakeEnvelope(0, 0, 2, 1)) AS geom;

Result

 geom------------------------------------- POLYGON ((0 0, 0 1, 2 1, 2 0, 0 0))

ST_Collect function

Collects an array of geometries into one geometry. Geometries of one type become the matching multi-geometry, mixed types become a GEOMETRYCOLLECTION. NULL elements are skipped, and an empty array or an array of only NULLs yields NULL, as in PostGIS.

Query

SELECT ST_AsText(ST_Collect(ARRAY['POINT(0 0)'::GEOMETRY, 'POINT(1 1)'::GEOMETRY])) AS geom;

Result

 geom----------------------- MULTIPOINT (0 0, 1 1)

ST_Points function

Collects every vertex of a geometry into a MULTIPOINT.

Query

SELECT ST_AsText(ST_Points('LINESTRING(0 0,1 1)'::GEOMETRY)) AS geom;

Result

 geom----------------------- MULTIPOINT (0 0, 1 1)

ST_Multi function

Wraps a single geometry in the matching multi-geometry. A geometry that is already a multi-geometry is returned unchanged.

Query

SELECT ST_AsText(ST_Multi('POINT(1 2)'::GEOMETRY)) AS geom;

Result

 geom------------------ MULTIPOINT (1 2)

Reading and Writing Formats

Name	Description
ST_GeomFromText	Creates a geometry from Well-Known Text (WKT)
ST_GeomFromWKB	Creates a geometry from Well-Known Binary (WKB)
ST_GeomFromGeoJSON	Creates a geometry from a GeoJSON object
ST_AsWKT	Returns the Well-Known Text (WKT) representation
ST_AsWKB	Returns the Well-Known Binary (WKB) representation
ST_AsHEXWKB	Returns the WKB representation as a hexadecimal string
ST_AsGeoJSON	Returns the GeoJSON representation
ST_AsSVG	Returns the SVG path data for the geometry

ST_GeomFromText function

Creates a geometry from Well-Known Text (WKT). A second argument of true returns NULL for invalid input instead of raising an error.

Query

SELECT ST_AsText(ST_GeomFromText('POINT(1 2)')) AS geom;

Result

 geom------------- POINT (1 2)

ST_GeomFromWKB function

Creates a geometry from Well-Known Binary (WKB) representation.

Query

SELECT ST_AsText(ST_GeomFromWKB('\x0101000000000000000000f03f0000000000000040'::BLOB)) AS geom;

Result

 geom------------- POINT (1 2)

ST_GeomFromGeoJSON function

Creates a geometry from a GeoJSON object.

Query

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[1,2]}')) AS geom;

Result

 geom------------- POINT (1 2)

ST_AsWKT function

Returns the Well-Known Text (WKT) representation of the geometry. Alias: ST_AsText.

Query

SELECT ST_AsText('POINT(1 2)'::GEOMETRY) AS wkt;

Result

 wkt------------- POINT (1 2)

ST_AsWKB function

Returns the Well-Known Binary (WKB) representation of the geometry. Alias: ST_AsBinary.

Query

SELECT ST_AsWKB('POINT(1 2)'::GEOMETRY) AS wkb;

Result

 wkb----------------------------------------------- \\x0101000000000000000000f03f0000000000000040

ST_AsHEXWKB function

Returns the WKB representation as a hexadecimal string.

Query

SELECT ST_AsHEXWKB('POINT(1 2)'::GEOMETRY) AS hexwkb;

Result

 hexwkb-------------------------------------------- 0101000000000000000000F03F0000000000000040

ST_AsGeoJSON function

Returns the GeoJSON representation of the geometry.

Query

SELECT ST_AsGeoJSON('POINT(1 2)'::GEOMETRY) AS geojson;

Result

 geojson------------------------------------------ {"type":"Point","coordinates":[1.0,2.0]}

ST_AsSVG function

Returns the SVG path data for the geometry. The second argument selects relative moves, the third sets the coordinate precision.

Query

SELECT ST_AsSVG('POINT(1 2)'::GEOMETRY, false, 2) AS svg;

Result

 svg---------------- cx="1" cy="-2"

Accessing Properties

Name	Description
ST_GeometryType	Returns the geometry type
ST_Dimension	Returns the topological dimension (0, 1 or 2)
ST_X	Returns the X (and ST_Y the Y) coordinate of a point
ST_Z	Returns the Z (and ST_M the M) coordinate of a point
ST_XMin	Returns a bound of the geometry's extent
ST_NPoints	Returns the number of vertices. Alias: ST_NumPoints
ST_NumGeometries	Returns the number of geometries in a collection
ST_NumInteriorRings	Returns the number of interior rings of a polygon
ST_ExteriorRing	Returns the exterior ring of a polygon
ST_InteriorRingN	Returns the nth interior ring of a polygon
ST_PointN	Returns the nth vertex of a line
ST_StartPoint	Returns the first (and ST_EndPoint the last) vertex of a line
ST_Dump	Expands a collection into its parts with their paths
ST_CollectionExtract	Extracts the elements of one dimension from a collection
ST_HasZ	Reports the vertex dimensions. Also ST_HasM, ST_ZMFlag
ST_Extent	Returns the bounding box of the geometry

ST_GeometryType function

Returns the geometry type, such as POINT or LINESTRING.

Query

SELECT ST_GeometryType('LINESTRING(0 0,1 1)'::GEOMETRY) AS type;

Result

 type------------ LINESTRING

ST_Dimension function

Returns the topological dimension: 0 for points, 1 for lines, 2 for areas.

Query

SELECT ST_Dimension('LINESTRING(0 0,2 2)'::GEOMETRY) AS dim;

Result

 dim-----   1

ST_X function

Returns the X coordinate of a point; ST_Y returns the Y coordinate.

Query

SELECT ST_X('POINT(30 10)'::GEOMETRY) AS x, ST_Y('POINT(30 10)'::GEOMETRY) AS y;

Result

 x  | y----+---- 30 | 10

ST_Z function

Returns the Z coordinate of a point; ST_M returns the M coordinate. Both return NULL when the geometry does not carry that dimension.

Query

SELECT ST_Z('POINT Z(1 2 3)'::GEOMETRY) AS z, ST_M('POINT ZM(1 2 3 4)'::GEOMETRY) AS m;

Result

 z | m---+--- 3 | 4

ST_XMin function

Returns a bound of the geometry's extent. The full set is ST_XMin, ST_XMax, ST_YMin, ST_YMax, ST_ZMin and ST_ZMax.

Query

SELECT ST_XMin('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS xmin, ST_XMax('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS xmax, ST_YMin('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS ymin, ST_YMax('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS ymax;

Result

 xmin | xmax | ymin | ymax------+------+------+------    0 |    4 |    0 |    4

ST_NPoints function

Returns the number of vertices in the geometry. Alias: ST_NumPoints.

Query

SELECT ST_NPoints('LINESTRING(0 0,2 2,4 4)'::GEOMETRY) AS npoints;

Result

 npoints---------       3

ST_NumGeometries function

Returns the number of geometries in a collection. A single geometry counts as one.

Query

SELECT ST_NumGeometries('MULTIPOINT(0 0,1 1)'::GEOMETRY) AS n;

Result

 n--- 2

ST_NumInteriorRings function

Returns the number of interior rings (holes) of a polygon.

Query

SELECT ST_NumInteriorRings('POLYGON((0 0,10 0,10 10,0 10,0 0),(2 2,4 2,4 4,2 4,2 2))'::GEOMETRY) AS n;

Result

 n--- 1

ST_ExteriorRing function

Returns the exterior ring (the shell) of a polygon as a line.

Query

SELECT ST_AsText(ST_ExteriorRing('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY)) AS geom;

Result

 geom-------------------------------------- LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0)

ST_InteriorRingN function

Returns the nth interior ring of a polygon as a line. Rings are numbered from 1.

Query

SELECT ST_AsText(ST_InteriorRingN('POLYGON((0 0,10 0,10 10,0 10,0 0),(2 2,4 2,4 4,2 4,2 2))'::GEOMETRY, 1)) AS geom;

Result

 geom-------------------------------------- LINESTRING (2 2, 4 2, 4 4, 2 4, 2 2)

ST_PointN function

Returns the nth vertex of a line. Vertices are numbered from 1.

Query

SELECT ST_AsText(ST_PointN('LINESTRING(0 0,1 1,2 2)'::GEOMETRY, 2)) AS geom;

Result

 geom------------- POINT (1 1)

ST_StartPoint function

Returns the first vertex of a line; ST_EndPoint returns the last.

Query

SELECT ST_AsText(ST_StartPoint('LINESTRING(0 0,1 1,2 2)'::GEOMETRY)) AS start, ST_AsText(ST_EndPoint('LINESTRING(0 0,1 1,2 2)'::GEOMETRY)) AS "end";

Result

 start       | end-------------+------------- POINT (0 0) | POINT (2 2)

ST_Dump function

Expands a collection into a list of its parts, each with the path that locates it. There is no ST_GeometryN in SereneDB, so this is how an individual part is reached.

Query

SELECT g.path AS path, ST_AsText(g.geom) AS geom FROM unnest(ST_Dump('MULTIPOINT(0 0,1 1)'::GEOMETRY)) AS g;

Result

 path | geom------+------------- {1}  | POINT (0 0) {2}  | POINT (1 1)

ST_CollectionExtract function

Extracts the elements of one dimension from a collection, as a multi-geometry: 1 for points, 2 for lines, 3 for polygons. Without the second argument, the highest dimension present is used.

Query

SELECT ST_AsText(ST_CollectionExtract('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,2 2))'::GEOMETRY, 1)) AS geom;

Result

 geom------------------ MULTIPOINT (1 1)

ST_HasZ function

Reports whether the geometry carries Z coordinates; ST_HasM does the same for M, and ST_ZMFlag returns a code for the combination (0 for XY, 1 for XYM, 2 for XYZ, 3 for XYZM).

Query

SELECT ST_HasZ('POINT Z(1 2 3)'::GEOMETRY) AS has_z, ST_HasM('POINT ZM(1 2 3 4)'::GEOMETRY) AS has_m, ST_ZMFlag('POINT Z(1 2 3)'::GEOMETRY) AS zm_flag;

Result

 has_z | has_m | zm_flag-------+-------+--------- t     | t     |       2

ST_Extent function

Returns the bounding box of the geometry.

Query

SELECT ST_Extent('LINESTRING(0 0,2 3)'::GEOMETRY)::text AS extent;

Result

 extent--------------- BOX(0 0, 2 3)

Measuring Geometries

These functions work in the geometry's own coordinate units. For measurements on the earth, see Spheroidal Measurements.

Name	Description
ST_Area	Returns the area of the geometry
ST_Length	Returns the length of the geometry
ST_Perimeter	Returns the perimeter of the geometry
ST_Distance	Returns the shortest distance between two geometries
ST_Azimuth	Returns the bearing from one point to another, in radians

ST_Area function

Returns the area of the geometry. Points and lines have zero area.

Query

SELECT ST_Area('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS area;

Result

 area------   16

ST_Length function

Returns the length of the geometry. Points and polygons have zero length; for a polygon's outline use ST_Perimeter.

Query

SELECT ST_Length('LINESTRING(0 0,3 4)'::GEOMETRY) AS length;

Result

 length--------      5

ST_Perimeter function

Returns the perimeter of the geometry, the total length of its rings.

Query

SELECT ST_Perimeter('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS perimeter;

Result

 perimeter-----------        16

ST_Distance function

Returns the shortest distance between two geometries. Geometries that intersect are zero apart.

Query

SELECT ST_Distance('POINT(0 0)'::GEOMETRY, 'POINT(3 4)'::GEOMETRY) AS distance;

Result

 distance----------        5

ST_Azimuth function

Returns the bearing from one point to another, in radians clockwise from north.

Query

SELECT (ST_Azimuth('POINT(0 0)'::GEOMETRY, 'POINT(1 1)'::GEOMETRY) * 1000000)::bigint AS azimuth_micro;

Result

 azimuth_micro---------------        785398

Testing Relationships

Name	Description
ST_Intersects	Returns true if the geometries share any point
ST_Disjoint	Returns true if the geometries share no point
ST_Contains	Returns true if the first geometry contains the second
ST_Covers	Like ST_Contains, but a boundary point counts as covered
ST_ContainsProperly	Returns true if the second geometry is in the first's interior
ST_Crosses	Returns true if the geometries cross
ST_Overlaps	Returns true if the geometries overlap at their own dimension
ST_Touches	Returns true if the geometries meet only at their boundaries
ST_Equals	Returns true if the geometries cover the same space
ST_DWithin	Returns true if the geometries are within a given distance
ST_Intersects_Extent	Returns true if the geometries bounding boxes intersect
ST_IsValid	Reports whether the geometry is valid. Also ST_IsEmpty
ST_IsClosed	Reports line properties. Also ST_IsRing, ST_IsSimple

ST_Intersects function

Returns true if the geometries share any point.

Query

SELECT ST_Intersects('POINT(1 1)'::GEOMETRY, 'POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS intersects;

Result

 intersects------------ t

ST_Disjoint function

Returns true if the geometries share no point. The exact inverse of ST_Intersects.

Query

SELECT ST_Disjoint('POINT(9 9)'::GEOMETRY, 'POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS disjoint;

Result

 disjoint---------- t

ST_Contains function

Returns true if the first geometry contains the second. ST_Within is the same test with the arguments swapped.

Query

SELECT ST_Contains('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POINT(1 1)'::GEOMETRY) AS contains, ST_Within('POINT(1 1)'::GEOMETRY, 'POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS within;

Result

 contains | within----------+-------- t        | t

ST_Covers function

Like ST_Contains, except that a point lying on the boundary counts as covered. ST_CoveredBy is the same test with the arguments swapped. The example shows the one case where the two disagree: the polygon's own corner.

Query

SELECT ST_Covers('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POINT(0 0)'::GEOMETRY) AS covers, ST_Contains('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POINT(0 0)'::GEOMETRY) AS contains;

Result

 covers | contains--------+---------- t      | f

ST_ContainsProperly function

Returns true if the second geometry lies in the interior of the first, touching neither its boundary nor its exterior. ST_WithinProperly is the same test with the arguments swapped.

Query

SELECT ST_ContainsProperly('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POINT(1 1)'::GEOMETRY) AS properly;

Result

 properly---------- t

ST_Crosses function

Returns true if the geometries cross, meaning they share some but not all interior points and the shared part has a lower dimension than at least one of them.

Query

SELECT ST_Crosses('LINESTRING(0 0,4 4)'::GEOMETRY, 'LINESTRING(0 4,4 0)'::GEOMETRY) AS crosses;

Result

 crosses--------- t

ST_Overlaps function

Returns true if the geometries have the same dimension and share some, but not all, of their interiors.

Query

SELECT ST_Overlaps('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POLYGON((2 2,6 2,6 6,2 6,2 2))'::GEOMETRY) AS overlaps;

Result

 overlaps---------- t

ST_Touches function

Returns true if the geometries meet only at their boundaries, with no shared interior.

Query

SELECT ST_Touches('LINESTRING(0 0,1 1)'::GEOMETRY, 'LINESTRING(1 1,2 2)'::GEOMETRY) AS touches;

Result

 touches--------- t

ST_Equals function

Returns true if the geometries cover the same space, regardless of vertex order or representation.

Query

SELECT ST_Equals('LINESTRING(0 0,2 2)'::GEOMETRY, 'LINESTRING(2 2,0 0)'::GEOMETRY) AS equals;

Result

 equals-------- t

ST_DWithin function

Returns true if the geometries are within the given distance of one another.

Query

SELECT ST_DWithin('POINT(0 0)'::GEOMETRY, 'POINT(3 4)'::GEOMETRY, 5.0) AS within_5;

Result

 within_5---------- t

ST_Intersects_Extent function

Returns true if the geometries bounding boxes intersect. Alias: &&.

Query

SELECT ST_Intersects_Extent('POINT(5 5)'::GEOMETRY, 'LINESTRING(0 0, 10 20)'::GEOMETRY) AS intersects;

Result

 intersects------------ t

ST_IsValid function

Reports whether the geometry is valid; ST_IsEmpty reports whether it holds no points.

Query

SELECT ST_IsValid('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY) AS valid, ST_IsEmpty('POLYGON EMPTY'::GEOMETRY) AS empty;

Result

 valid | empty-------+------- t     | t

ST_IsClosed function

Reports whether a line starts and ends at the same point. ST_IsRing additionally requires the line to be simple, and ST_IsSimple reports whether a geometry has no self-intersections.

Query

SELECT ST_IsClosed('LINESTRING(0 0,1 0,1 1,0 0)'::GEOMETRY) AS closed, ST_IsRing('LINESTRING(0 0,1 0,1 1,0 0)'::GEOMETRY) AS ring, ST_IsSimple('LINESTRING(0 0,1 1,2 2)'::GEOMETRY) AS simple;

Result

 closed | ring | simple--------+------+-------- t      | t    | t

Deriving New Geometries

Name	Description
ST_Intersection	Returns the part shared by both geometries
ST_Union	Returns the combination of both geometries
ST_Difference	Returns the part of the first geometry not in the second
ST_SymDifference	Returns the parts belonging to exactly one of the geometries
ST_Buffer	Returns the area within a given distance of the geometry
ST_ConvexHull	Returns the smallest convex geometry enclosing the input
ST_Simplify	Removes vertices that fall within a tolerance
ST_Centroid	Returns the centre of mass of the geometry
ST_Envelope	Returns the bounding box as a polygon
ST_Boundary	Returns the boundary of the geometry
ST_PointOnSurface	Returns a point guaranteed to lie on the geometry
ST_ClosestPoint	Returns the point of the first geometry closest to the second
ST_ShortestLine	Returns the shortest line between two geometries

ST_Intersection function

Returns the part shared by both geometries.

Query

SELECT ST_AsText(ST_Intersection('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY, 'POLYGON((2 2,6 2,6 6,2 6,2 2))'::GEOMETRY)) AS geom;

Result

 geom------------------------------------- POLYGON ((2 4, 4 4, 4 2, 2 2, 2 4))

ST_Union function

Returns the combination of both geometries. Both arguments must have the same dimension, because a mixed result would need a GEOMETRYCOLLECTION.

Query

SELECT ST_AsText(ST_Union('POINT(0 0)'::GEOMETRY, 'POINT(1 1)'::GEOMETRY)) AS geom;

Result

 geom----------------------- MULTIPOINT (0 0, 1 1)

ST_Difference function

Returns the part of the first geometry that is not in the second.

Query

SELECT ST_AsText(ST_Difference('LINESTRING(0 0,4 0)'::GEOMETRY, 'LINESTRING(1 0,2 0)'::GEOMETRY)) AS geom;

Result

 geom------------------------------------------ MULTILINESTRING ((0 0, 1 0), (2 0, 4 0))

ST_SymDifference function

Returns the parts that belong to exactly one of the geometries. Like ST_Union, both arguments must have the same dimension.

Query

SELECT ST_AsText(ST_SymDifference('LINESTRING(0 0,2 0)'::GEOMETRY, 'LINESTRING(1 0,3 0)'::GEOMETRY)) AS geom;

Result

 geom------------------------------------------ MULTILINESTRING ((0 0, 1 0), (2 0, 3 0))

ST_Buffer function

Returns the area within the given distance of the geometry. The optional third argument sets the number of triangles used per quarter circle, which controls how round the result is.

Query

SELECT ST_NPoints(ST_Buffer('POINT(0 0)'::GEOMETRY, 1, 2)) AS npoints;

Result

 npoints---------       9

ST_ConvexHull function

Returns the smallest convex geometry that encloses the input.

Query

SELECT ST_AsText(ST_ConvexHull('MULTIPOINT(0 0,2 0,2 2,0 2,1 1)'::GEOMETRY)) AS geom;

Result

 geom------------------------------------- POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))

ST_Simplify function

Removes vertices that fall within the given tolerance, using the Douglas-Peucker algorithm. The result may be invalid or may break topology shared with neighbouring geometries; there is no ST_SimplifyPreserveTopology.

Query

SELECT ST_AsText(ST_Simplify('LINESTRING(0 0,1 0.1,2 0)'::GEOMETRY, 0.5)) AS geom;

Result

 geom----------------------- LINESTRING (0 0, 2 0)

ST_Centroid function

Returns the centre of mass of the geometry, which is not necessarily on the geometry itself. For a point that is, use ST_PointOnSurface.

Query

SELECT ST_AsText(ST_Centroid('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY)) AS geom;

Result

 geom------------- POINT (2 2)

ST_Envelope function

Returns the bounding box of the geometry as a polygon. ST_Extent returns the same bounds as a box value instead.

Query

SELECT ST_AsText(ST_Envelope('LINESTRING(0 0,2 3)'::GEOMETRY)) AS geom;

Result

 geom------------------------------------- POLYGON ((0 0, 0 3, 2 3, 2 0, 0 0))

ST_Boundary function

Returns the boundary of the geometry: the rings of a polygon, the endpoints of a line.

Query

SELECT ST_AsText(ST_Boundary('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY)) AS geom;

Result

 geom-------------------------------------- LINESTRING (0 0, 0 4, 4 4, 4 0, 0 0)

ST_PointOnSurface function

Returns a point guaranteed to lie on the geometry.

Query

SELECT ST_AsText(ST_PointOnSurface('POLYGON((0 0,4 0,4 4,0 4,0 0))'::GEOMETRY)) AS geom;

Result

 geom------------- POINT (2 2)

ST_ClosestPoint function

Returns the point of the first geometry that lies closest to the second.

Query

SELECT ST_AsText(ST_ClosestPoint('LINESTRING(0 0,4 0)'::GEOMETRY, 'POINT(2 2)'::GEOMETRY)) AS geom;

Result

 geom------------- POINT (2 0)

ST_ShortestLine function

Returns the shortest line between two geometries.

Query

SELECT ST_AsText(ST_ShortestLine('LINESTRING(0 0,4 0)'::GEOMETRY, 'POINT(2 2)'::GEOMETRY)) AS geom;

Result

 geom----------------------- LINESTRING (2 0, 2 2)

Editing Geometries

Name	Description
ST_Affine	Applies an affine transformation to every vertex
ST_Expand	Returns the bounding box grown by a distance
ST_Reverse	Reverses the vertex order
ST_Normalize	Rewrites the geometry into a canonical form
ST_FlipCoordinates	Swaps the X and Y of every vertex
ST_Force2D	Drops the Z and M dimensions
ST_RemoveRepeatedPoints	Removes consecutive duplicate vertices

ST_Affine function

Applies an affine transformation to every vertex. The six-argument form takes a, b, d, e, xoff, yoff and maps a vertex to (a*x + b*y + xoff, d*x + e*y + yoff); a thirteen-argument form does the same in three dimensions. SereneDB has no ST_Translate, ST_Scale or ST_Rotate, so this is how those are expressed.

Query

SELECT ST_AsText(ST_Affine('POINT(1 1)'::GEOMETRY, 2, 0, 0, 2, 1, 1)) AS geom;

Result

 geom------------- POINT (3 3)

ST_Expand function

Returns the bounding box of the geometry grown by the given distance in every direction.

Query

SELECT ST_AsText(ST_Expand('POINT(1 1)'::GEOMETRY, 1)) AS geom;

Result

 geom------------------------------------- POLYGON ((0 0, 0 2, 2 2, 2 0, 0 0))

ST_Reverse function

Reverses the vertex order of the geometry.

Query

SELECT ST_AsText(ST_Reverse('LINESTRING(0 0,1 1,2 2)'::GEOMETRY)) AS geom;

Result

 geom---------------------------- LINESTRING (2 2, 1 1, 0 0)

ST_Normalize function

Rewrites the geometry into a canonical form, so that geometries covering the same space become identical.

Query

SELECT ST_AsText(ST_Normalize('LINESTRING(0 0,1 1)'::GEOMETRY)) AS geom;

Result

 geom----------------------- LINESTRING (0 0, 1 1)

ST_FlipCoordinates function

Swaps the X and Y of every vertex. Useful for data stored in latitude/longitude order.

Query

SELECT ST_AsText(ST_FlipCoordinates('POINT(1 2)'::GEOMETRY)) AS geom;

Result

 geom------------- POINT (2 1)

ST_Force2D function

Drops the Z and M dimensions from the geometry.

Query

SELECT ST_AsText(ST_Force2D('POINT Z(1 2 3)'::GEOMETRY)) AS geom;

Result

 geom------------- POINT (1 2)

ST_RemoveRepeatedPoints function

Removes consecutive duplicate vertices.

Query

SELECT ST_AsText(ST_RemoveRepeatedPoints('LINESTRING(0 0,0 0,1 1)'::GEOMETRY)) AS geom;

Result

 geom----------------------- LINESTRING (0 0, 1 1)

Linear Referencing

Name	Description
ST_LineInterpolatePoint	Returns the point at a fraction along a line
ST_LineSubstring	Returns the part of a line between two fractions
ST_LineLocatePoint	Returns the fraction along a line closest to a point
ST_LocateAlong	Returns the positions on a measured line at a measure
ST_LocateBetween	Returns the part of a measured line between two measures

ST_LineInterpolatePoint function

Returns the point at the given fraction along a line, where the fraction runs from 0 at the start to 1 at the end. ST_LineInterpolatePoints returns several such points at a repeating interval.

Query

SELECT ST_AsText(ST_LineInterpolatePoint('LINESTRING(0 0,4 0)'::GEOMETRY, 0.25)) AS geom;

Result

 geom------------- POINT (1 0)

ST_LineSubstring function

Returns the part of a line between two fractions of its length.

Query

SELECT ST_AsText(ST_LineSubstring('LINESTRING(0 0,4 0)'::GEOMETRY, 0.25, 0.75)) AS geom;

Result

 geom----------------------- LINESTRING (1 0, 3 0)

ST_LineLocatePoint function

Returns the fraction along a line at which it passes closest to the given point.

Query

SELECT (ST_LineLocatePoint('LINESTRING(0 0,4 0)'::GEOMETRY, 'POINT(1 0)'::GEOMETRY) * 100)::bigint AS pct;

Result

 pct-----  25

ST_LocateAlong function

Returns the positions on a line carrying M values where the measure equals the given value. ST_InterpolatePoint is the inverse: it returns the M value of a line at the position closest to a point.

Query

SELECT ST_AsText(ST_LocateAlong('LINESTRING M(0 0 0,4 0 4)'::GEOMETRY, 2)) AS geom;

Result

 geom----------------- POINT M (2 0 2)

ST_LocateBetween function

Returns the part of a line carrying M values that falls between two measures.

Query

SELECT ST_AsText(ST_LocateBetween('LINESTRING M(0 0 0,4 0 4)'::GEOMETRY, 1, 3)) AS geom;

Result

 geom----------------------------- LINESTRING M (1 0 1, 3 0 3)

Coordinate Reference Systems

SereneDB attaches the coordinate reference system to the GEOMETRY type itself rather than storing an SRID number alongside each value, so ST_SRID and ST_SetSRID do not exist. ST_CRS and ST_SetCRS take their place, and identifiers are strings such as OGC:CRS84.

A CRS is a label that SereneDB tracks and checks. It does not reproject: there is no ST_Transform, because reprojection needs a database of coordinate-system definitions that SereneDB does not ship. Convert coordinates before loading them, and use ST_SetCRS to record which system the result is in. Measuring on the WGS84 ellipsoid needs no such database and is supported, see Spheroidal Measurements.

Name	Description
ST_CRS	Returns the CRS identifier of the geometry
ST_SetCRS	Sets the CRS identifier of the geometry

ST_CRS function

Returns the Coordinate Reference System (CRS) identifier of the geometry.

Query

SELECT ST_CRS('POINT(1 2)'::GEOMETRY('OGC:CRS84')) AS crs;

Result

 crs----------- OGC:CRS84

ST_SetCRS function

Sets the Coordinate Reference System (CRS) identifier of the geometry. The coordinates are left as they are; only the label changes.

Query

SELECT typeof(ST_SetCRS('POINT(1 2)'::GEOMETRY, 'OGC:CRS84')) AS type;

Result

 type----------------------- GEOMETRY('OGC:CRS84')

Spheroidal Measurements

These functions measure on the earth rather than in the coordinate plane and return metres. They take X as the latitude and Y as the longitude, which is the opposite of the order used by the planar functions on this page.

ST_Distance_Sphere treats the earth as a sphere, which is fast and approximate. The *_Spheroid functions use the WGS84 ellipsoid, which is slower and accurate.

Name	Description
ST_Distance_Sphere	Returns the great-circle distance between two points
ST_Length_Spheroid	Returns the length of a line on the WGS84 spheroid
ST_Area_Spheroid	Returns the area of a polygon on the WGS84 spheroid
ST_Perimeter_Spheroid	Returns the perimeter of a polygon on the WGS84 spheroid

ST_Distance_Sphere function

Returns the great-circle distance between two points in metres, treating the earth as a sphere. The example measures one degree along the equator.

Query

SELECT ST_Distance_Sphere('POINT(0 0)'::GEOMETRY, 'POINT(1 0)'::GEOMETRY)::bigint AS meters;

Result

 meters-------- 111195

ST_Length_Spheroid function

Returns the length of a line in metres on the WGS84 spheroid. The example spans one degree of latitude, which is why the result is close to 110.6 km rather than the 111.3 km of a degree of longitude at the equator.

Query

SELECT ST_Length_Spheroid('LINESTRING(0 0,1 0)'::GEOMETRY)::bigint AS meters;

Result

 meters-------- 110574

ST_Area_Spheroid function

Returns the area of a polygon in square metres on the WGS84 spheroid.

Query

SELECT ST_Area_Spheroid('POLYGON((0 0,1 0,1 1,0 1,0 0))'::GEOMETRY)::bigint AS sq_meters;

Result

 sq_meters------------- 12308778361

ST_Perimeter_Spheroid function

Returns the perimeter of a polygon in metres on the WGS84 spheroid.

Query

SELECT ST_Perimeter_Spheroid('POLYGON((0 0,1 0,1 1,0 1,0 0))'::GEOMETRY)::bigint AS meters;

Result

 meters-------- 443771

Spatial Ordering and Tiling

Name	Description
ST_Hilbert	Returns the Hilbert curve index of the geometry's centre
ST_QuadKey	Returns the Bing Maps quadkey for a point at a zoom level
ST_TileEnvelope	Returns the Web Mercator extent of an XYZ tile

ST_Hilbert function

Returns the Hilbert curve index of the geometry's centre, which sorts nearby geometries close together.

Query

SELECT ST_Hilbert('POINT(1 2)'::GEOMETRY) AS hilbert;

Result

 hilbert------------ 3355443200

ST_QuadKey function

Returns the Bing Maps quadkey for a point at the given zoom level.

Query

SELECT ST_QuadKey('POINT(11 22)'::GEOMETRY, 5) AS quadkey;

Result

 quadkey---------   12202

ST_TileEnvelope function

Returns the extent of an XYZ tile as a polygon in Web Mercator coordinates.

Query

SELECT ST_Area(ST_TileEnvelope(1, 0, 0))::bigint AS area;

Result

 area----------------- 401501740587349

Differences from PostGIS

How geometries are displayed

A GEOMETRY sent to a client is rendered as upper-case hexadecimal WKB, byte for byte what PostGIS sends:

SELECT 'POINT(1 2)'::GEOMETRY;
-- 0101000000000000000000F03F0000000000000040

Wrap the value in ST_AsText for the readable spelling, as the examples on this page do.

The hex agrees with PostGIS for two-dimensional geometries. Geometries carrying Z or M do not: SereneDB writes the ISO WKB type codes (POINT Z is 01E9030000...) where PostGIS sets the EWKB high-bit flags (0101000080...).

Identifying the coordinate reference system

The CRS belongs to the type, not to the value, so there is no SRID integer. Use ST_CRS and ST_SetCRS with string identifiers in place of ST_SRID and ST_SetSRID.

PostGIS functions that are not available

Geometry operations run on Boost.Geometry. The following PostGIS functions have no equivalent there and are therefore not provided. Calling one reports that the function does not exist.

Not available	Closest thing that is
ST_MakeValid	ST_IsValid reports the problem, but nothing repairs it
ST_Node, ST_Polygonize, ST_BuildArea	—
ST_LineMerge	—
ST_SimplifyPreserveTopology	ST_Simplify, which may break topology
ST_ConcaveHull	ST_ConvexHull
ST_ReducePrecision	—
ST_MaximumInscribedCircle	ST_PointOnSurface for a point known to be inside
ST_VoronoiDiagram	—
ST_MinimumRotatedRectangle	ST_Envelope, which is axis-aligned
ST_GeometryN	ST_Dump, which expands every part at once
ST_SRID, ST_SetSRID	ST_CRS and ST_SetCRS, which use string identifiers
ST_Translate, ST_Scale, ST_Rotate	ST_Affine, which expresses all three
ST_Relate	the individual predicates
ST_Segmentize, ST_Split, ST_Snap, ST_OffsetCurve	—
ST_GeoHash	ST_QuadKey, ST_Hilbert

Four further differences apply to functions that do exist:

• ST_Union and ST_SymDifference require both arguments to have the same dimension. Combining a point with a polygon would produce a GEOMETRYCOLLECTION, which cannot be built. ST_Intersection and ST_Difference accept mixed dimensions.
• The Boost-backed operations drop Z and M from their results. Measurement and predicates are computed in two dimensions, as they are in PostGIS, but PostGIS carries the extra dimensions through to the result and these do not: ST_Envelope, ST_Boundary, ST_ConvexHull, ST_Simplify, ST_Intersection, ST_PointOnSurface, ST_Normalize and ST_RemoveRepeatedPoints. The functions that move vertices around rather than computing new ones keep every dimension: ST_Reverse, ST_Multi, ST_Points, ST_StartPoint, ST_EndPoint and ST_PointN, and so does ST_Centroid.
• Only some functions accept a GEOMETRYCOLLECTION. Those that can answer member by member do: ST_Reverse, ST_Centroid, ST_Envelope, ST_ConvexHull, ST_Area, ST_Length, ST_NumGeometries, ST_IsValid, ST_IsEmpty, and the ST_Intersects / ST_Disjoint predicates. ST_Boundary returns NULL for one. Everything else rejects it, because a collection's answer is not the combination of its parts' answers -- the other predicates, ST_Buffer and ST_Simplify among them.
• An empty geometry is valid, and empty input yields NULL where a geometry is expected. ST_IsValid('LINESTRING EMPTY') is true, and ST_ClosestPoint and ST_ShortestLine return NULL when either argument is empty rather than raising.