Spatial functions

Spatial functions use PostGIS-style ST_ names and operate on planar (Cartesian) geometry—there is no geography type; coordinates are always interpreted as x = easting/longitude. Buffering and overlay operations (ST_Buffer, ST_Union, ST_Intersection, ST_MakeValid, ...) and coordinate reprojection (ST_Transform(geom, 'EPSG:4326', 'EPSG:3857')) are available. Geodesic measurements in metres use the _Spheroid family (ST_Area_Spheroid, ST_Distance_Spheroid, ...) or ST_DistanceSphere on lon/lat data. See the SQL reference for worked examples.

st_3dmakebox

Creates a box3d defined by two 3D Point geometries.

ST_3DMakeBox(ST_MakePoint(-989502.1875, 528439.5625, 10),
	ST_MakePoint(-987121.375 ,529933.1875, 10))

st_affine

Applies a 2D affine transform: x' = a·x + b·y + xoff, y' = d·x + e·y + yoff. Z/M ordinates are dropped.

ST_Affine(geom, a, b, d, e, xoff, yoff)

st_area

Returns the area of a polygonal geometry.

ST_Area(geom)

st_area_spheroid

Returns the area of a (multi)polygon in square meters on the WGS84 ellipsoid. Coordinates are interpreted as lon/lat degrees. Non-areal geometries return 0.

ST_Area_Spheroid(geom)

st_asbinary

Returns the OGC/ISO Well-Known Binary (WKB) representation of the geometry.

ST_AsBinary(geometry)

st_asgeojson

Returns the GeoJSON geometry object for the geometry (2D).

ST_AsGeoJSON(geom)

st_ashexwkb

Returns the uppercase hex-encoded little-endian WKB of the geometry (2D).

ST_AsHEXWKB(geom)

st_astext

Returns the OGC Well-Known Text (WKT) representation of the geometry/geography.

ST_AsText(geometry)

st_azimuth

Planar azimuth in radians (clockwise from north, in [0, 2π)) of the segment from the first point to the second. NULL if the points coincide or either is not a point.

ST_Azimuth(origin, target)

st_boundary

Returns the topological boundary of a geometry.

ST_Boundary(geom)

st_box2dfromgeohash

Return a BOX2D from a GeoHash string.

ST_Box2dFromGeoHash(geohash)

st_buffer

Returns a geometry covering all points within the given distance of the input (8 quadrant segments).

ST_Buffer(geom, distance)

st_centroid

Computes a point which is the geometric center of mass of a geometry.

ST_Centroid(geometry)

st_collectionextract

Extracts sub-geometries of a single dimension into a multi-geometry: 1 = points → MultiPoint, 2 = lines → MultiLineString, 3 = polygons → MultiPolygon. Multi- and collection geometries are flattened.

ST_CollectionExtract(geom, type)

st_contains

Returns TRUE if geometry A contains geometry B. A contains B if and only if all points of B lie inside (i.e. in the interior or boundary of) A (or equivalently, no points of B lie in the exterior of A), and the interiors of A and B have at least one point in common.

ST_Contains(geomA, geomB)

st_containsproperly

Returns true if every point of the second geometry is in the interior of the first.

ST_ContainsProperly(geomA, geomB)

st_convexhull

Computes the convex hull of a geometry. The convex hull is the smallest convex geometry that encloses all geometries in the input.

ST_ConvexHull(geometry)

st_coorddim

Return the coordinate dimension of the ST_Geometry value.

ST_CoordDim(geometry)

st_coveredby

Returns true if every point in Geometry/Geography A lies inside (i.e. intersects the interior or boundary of) Geometry/Geography B. Equivalently, tests that no point of A lies outside (in the exterior of) B.

ST_CoveredBy(geomA, geomB)

st_covers

Returns true if every point in Geometry/Geography B lies inside (i.e. intersects the interior or boundary of) Geometry/Geography A. Equivalently, tests that no point of B lies outside (in the exterior of) A.

ST_Covers(geomA, geomB)

st_crosses

Compares two geometry objects and returns true if their intersection "spatially crosses"; that is, the geometries have some, but not all interior points in common. The intersection of the interiors of the geometries must be non-empty and must have dimension less than the maximum dimension of the two input geometries, and the intersection of the two geometries must not equal either geometry. Otherwise, it returns false. The crosses relation is symmetric and irreflexive.

ST_Crosses(geomA, geomB)

st_difference

Returns the part of the first geometry that does not intersect the second.

ST_Difference(geomA, geomB)

st_dimension

Returns the topological dimension of a geometry: 0 for points, 1 for curves, 2 for surfaces. For a collection, returns the maximum dimension of its members (0 if empty). ST_Dimension(NULL) is NULL.

ST_Dimension(geom)

st_disjoint

Returns true if two geometries are disjoint. Geometries are disjoint if they have no point in common.

ST_Disjoint(geomA, geomB)

st_distance

For geometry types returns the minimum 2D Cartesian (planar) distance between two geometries, in projected units (spatial ref units).

ST_Distance(geomA, geomB)

st_distance_geos

Returns the Cartesian distance between two geometries.

ST_Distance_GEOS(geomA, geomB)

st_distance_spheroid

Geodesic (WGS84 ellipsoid) distance in meters between two points given as lon/lat degrees. Returns NULL if either argument is not a point.

ST_Distance_Spheroid(pointA, pointB)

st_distancesphere

Great-circle (haversine) distance in meters between two points given as lon/lat degrees. Returns NULL if either argument is not a point. (Alias: st_distance_sphere.)

ST_DistanceSphere(pointA, pointB)

st_dump

Decomposes a geometry into its atomic components (Point, LineString, Polygon). Multi-geometries and GeometryCollections are split (recursively) into their parts. Atomic geometries are returned unchanged. This includes Polygons, which are returned whole. Returns, per input row, a list of (path, geom) structs where path is the 1-based navigation path to the component (empty for an atomic input). Use unnest if you want to expand into a row per component instead of row per input geom. Empty inputs (recursively, per the same definition as ST_Empty) produce zero components.

ST_Dump(geom)

st_dwithin

Returns true if the two geometries are within the given planar (Cartesian) distance of each other.

ST_DWithin(geomA, geomB, distance)

st_dwithin_geos

Returns true if the two geometries are within the given Cartesian distance of each other.

ST_DWithin_GEOS(geomA, geomB, distance)

st_dwithin_spheroid

Returns true if two points are within the given geodesic (WGS84 ellipsoid) distance in meters of each other. Coordinates are interpreted as lon/lat degrees (x = longitude), matching the other *_Spheroid functions. NULL if either argument is not a point.

ST_DWithin_Spheroid(pointA, pointB, distance)

st_endpoint

Returns the last point of a LINESTRING geometry as a POINT. Returns NULL if the input is not a LINESTRING.

ST_EndPoint(line_string)

st_equals

Returns true if the given geometries are "topologically equal". Use this for a 'better' answer than '='. Topological equality means that the geometries have the same dimension, and their point-sets occupy the same space. This means that the order of vertices may be different in topologically equal geometries.

ST_Equals(geomA, geomB)

st_exteriorring

Returns the exterior ring of a polygon as a LineString. NULL for non-polygons.

ST_ExteriorRing(geom)

st_flipcoordinates

Returns a version of the geometry with X and Y axes swapped. Useful for fixing lat/lon vs lon/lat ordering.

ST_FlipCoordinates(geom)

st_force2d

Returns the geometry with any Z and M ordinates removed (forced to XY).

ST_Force2D(geom)

st_geohash

Computes a GeoHash representation of a geometry. A GeoHash encodes a geographic Point into a text form that is sortable and searchable based on prefixing. A shorter GeoHash is a less precise representation of a point. It can be thought of as a box that contains the point.

ST_GeoHash(point)

st_geometrytype

Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.

ST_GeometryType(geometry)

st_geomfromgeojson

Parses a geometry from a GeoJSON geometry object.

ST_GeomFromGeoJSON(geojson)

st_geomfromhexwkb

Parses a geometry from a hex-encoded WKB string.

ST_GeomFromHEXWKB(hex)

st_geomfromtext

Constructs a geometry object from the OGC Well-Known text representation. (Aliases: st_geometryfromtext, st_wkttosql.)

ST_GeomFromText(text)

st_geomfromwkb

Takes a well-known binary representation of a geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type (Alias: st_wkbtosql.)

ST_GeomFromWKB(buffer)

st_hasm

Returns true if the geometry has an M coordinate. ST_HasM(NULL) is NULL.

ST_HasM(geom)

st_hasz

Returns true if the geometry has a Z coordinate. ST_HasZ(NULL) is NULL.

ST_HasZ(geom)

st_intersection

Returns the geometric intersection of two geometries.

ST_Intersection(geomA, geomB)

st_intersects

Returns true if two geometries intersect. Geometries intersect if they have any point in common.

ST_Intersects(geomA, geomB)

st_intersects_extent

Returns true if the 2D bounding boxes (extents) of the two geometries intersect, including when they merely touch. This is a cheap extent-only test and does not consider the actual geometry shape. Returns NULL if either argument is NULL or has no extent.

ST_Intersects_Extent(geomA, geomB)

st_isclosed

Tests if a LineStrings's start and end points are coincident.

ST_IsClosed(geom)

st_isempty

Tests if a geometry is topologically empty. Multi-geometries and GeometryCollections where every leaf is empty (e.g. GEOMETRYCOLLECTION(POINT EMPTY, POLYGON EMPTY) are reported empty. ST_IsEmpty(NULL) is NULL.

ST_IsEmpty(geom)

st_isring

Returns true if the linestring is closed and simple (a ring).

ST_IsRing(geom)

st_issimple

Returns true if the geometry has no anomalous points such as self-intersections.

ST_IsSimple(geom)

st_isvalid

Tests if an ST_Geometry value is well-formed and valid in 2D according to the OGC rules

ST_IsValid(geomA)

st_isvalidreason

Returns text stating if a geometry is valid, or if invalid a reason why.

ST_IsValidReason(geomA)

st_length

Returns the 2D Cartesian length of the geometry if it is a LineString or MultiLineString. For areal geometries 0 is returned; use ST_Perimeter instead. (Alias: st_length2d.)

ST_Length(geom)

st_length_spheroid

Returns the length of a (multi)linestring in meters on the WGS84 ellipsoid. Coordinates are interpreted as lon/lat degrees. Non-lineal geometries return 0.

ST_Length_Spheroid(geom)

st_lineinterpolatepoint

Returns the point at the given fraction (0..1) along a linestring.

ST_LineInterpolatePoint(line, fraction)

st_linelocatepoint

Returns the fraction (0..1) along a linestring that is closest to the given point. NULL if the first argument is not a line or the second is not a point.

ST_LineLocatePoint(line, point)

st_linemerge

Returns a (set of) LineString(s) formed by sewing together the constituent line work of a MultiLineString. Lines are joined at endpoints where exactly two lines meet; lines are not merged across intersections of three or more lines. When directed is true, lines are only merged when their directions agree. Non-linear inputs yield an empty GeometryCollection. This function strips the M dimension.

ST_LineMerge(geometry, directed)

st_linesubstring

Returns the portion of a linestring between the start and end fractions (each 0..1).

ST_LineSubstring(line, start, end)

st_m

Return the M coordinate of the point, or NULL if not available. Input must be a point.

ST_M(geometry)

st_makebox2d

Creates a box2d defined by two Point geometries. This is useful for doing range queries.

ST_MakeBox2D(ST_Point(-989502.1875, 528439.5625), ST_Point(-987121.375, 529933.1875))

st_makeenvelope

Constructs a rectangular polygon from the bounds (xmin, ymin, xmax, ymax).

ST_MakeEnvelope(xmin, ymin, xmax, ymax)

st_makeline

Creates a LineString from the concatenated vertices of two geometries (e.g. two points).

ST_MakeLine(geomA, geomB)

st_makepoint

Creates a 2D XY or 3D XYZ or 4D XYZM Point geometry. Use ST_MakePointM to make points with XYM coordinates

ST_MakePoint(-71.104, 42.315)

st_makepointm

Creates a point with X, Y and M (measure) ordinates. Use ST_MakePoint to make points with XY, XYZ, or XYZM coordinates.

ST_MakePointM(-71.104, 42.315, 10)

st_makepolygon

Builds a Polygon whose exterior ring is the given closed LineString. Errors if the shell is not a closed LineString.

ST_MakePolygon(linestring)

st_makevalid

Returns a valid version of the (possibly invalid) input geometry.

ST_MakeValid(geom)

st_minimumrotatedrectangle

Returns the minimum-area rotated rectangle enclosing the geometry, as a Polygon. NULL when no rectangle can be formed (e.g. a single point).

ST_MinimumRotatedRectangle(geom)

st_mmax

Returns the maximum M coordinate of a geometry, or NULL if it has no M ordinate. ST_MMax(NULL) is NULL.

ST_MMax(geom)

st_mmin

Returns the minimum M coordinate of a geometry, or NULL if it has no M ordinate. ST_MMin(NULL) is NULL.

ST_MMin(geom)

st_multi

Wraps a single Point/LineString/Polygon into the corresponding multi-geometry; multi-geometries and collections are returned unchanged.

ST_Multi(geom)

st_ndims

Return the coordinate dimension of the geometry.

ST_NDims(geometry)

st_normalize

Returns the geometry in canonical (normalized) form.

ST_Normalize(geom)

st_npoints

Return the number of points in a geometry. Works for all geometries. (Alias: st_numpoints.)

ST_NPoints(geometry)

st_numgeometries

Returns the number of sub-geometries: the member count for multi-geometries and geometry collections, otherwise 1. ST_NumGeometries(NULL) is NULL. (Alias: st_ngeometries.)

ST_NumGeometries(geom)

st_numinteriorrings

Returns the number of interior rings (holes) of a Polygon. NULL for non-polygon inputs. (Alias: st_numinteriorring.)

ST_NumInteriorRings(polygon)

st_orientedenvelope

Returns the minimum-area rotated rectangle enclosing a geometry. Note that more than one such rectangle may exist. May return a Point or LineString in the case of degenerate inputs.

ST_OrientedEnvelope(geometry)

st_overlaps

Returns TRUE if geometry A and B "spatially overlap". Two geometries overlap if they have the same dimension, their interiors intersect in that dimension. and each has at least one point inside the other (or equivalently, neither one covers the other). The overlaps relation is symmetric and irreflexive.

ST_Overlaps(geomA, geomB)

st_perimeter

Returns the planar (Cartesian) perimeter of a geometry: the total boundary length of its polygonal parts, including hole boundaries, in the input's own coordinate units. Non-areal geometries return 0.

ST_Perimeter(geom)

st_perimeter_spheroid

Returns the perimeter of a (multi)polygon in meters on the WGS84 ellipsoid. Coordinates are interpreted as lon/lat degrees. Non-areal geometries return 0.

ST_Perimeter_Spheroid(geom)

st_point

Returns a Point with the given X and Y coordinate values.

ST_Point(-71.104, 42.315) or ST_Point(-71.104, 42.315, 4326)

st_point2d

Constructs a 2D point from X and Y coordinates.

ST_Point2D(x, y)

st_pointfromgeohash

Return a point from a GeoHash string. The point represents the center point of the GeoHash.

ST_PointFromGeoHash(geohash)

st_pointm

Returns an Point with the given X, Y and M coordinate values, and optionally an SRID number.

ST_PointM(-71.104, 42.315, 3.4) or ST_PointM(-71.104, 42.315, 3.4, 4326)

st_pointonsurface

Returns a POINT which is guaranteed to lie in the interior of a surface.

ST_PointOnSurface(geometry)

st_points

Collects every vertex of a geometry into a MultiPoint.

ST_Points(geom)

st_pointz

Returns an Point with the given X, Y and Z coordinate values, and optionally an SRID number.

ST_Point(-71.104, 42.315) or ST_Point(-71.104, 42.315, 4326)

st_pointzm

Returns an Point with the given X, Y, Z and M coordinate values, and optionally an SRID number.

ST_Point(-71.104, 42.315) or ST_Point(-71.104, 42.315, 4326)

st_quadkey

Returns the Bing Maps quadkey string for a longitude/latitude at the given zoom level.

ST_QuadKey(longitude, latitude, level)

st_reduceprecision

Snaps the geometry's coordinates to the given grid size.

ST_ReducePrecision(geom, gridsize)

st_removerepeatedpoints

Removes consecutive duplicate vertices from a geometry (exact equality; the optional tolerance argument is not supported).

ST_RemoveRepeatedPoints(geom)

st_reverse

Returns the geometry with the vertex order of each component reversed.

ST_Reverse(geom)

st_rotate

Rotates the geometry counter-clockwise about the origin by the given angle in radians. (Alias: st_rotatez.)

ST_Rotate(geom, radians)

st_scale

Scales the geometry about the origin by the given X and Y factors.

ST_Scale(geom, xfactor, yfactor)

st_shortestline

Returns the shortest 2-point line between two geometries (nearest points).

ST_ShortestLine(geomA, geomB)

st_simplify

Computes a simplified representation of a geometry using the Douglas-Peucker algorithm. The simplification tolerance is a distance value, in the units of the input SRS. Simplification removes vertices which are within the tolerance distance of the simplified linework. The result may not be valid even if the input is.

ST_Simplify(geometry, epsilon)

st_simplifypreservetopology

Computes a simplified representation of a geometry using a variant of the Douglas-Peucker algorithm which limits simplification to ensure the result has the same topology as the input. The simplification tolerance is a distance value, in the units of the input SRS. Simplification removes vertices which are within the tolerance distance of the simplified linework, as long as topology is preserved. The result will be valid and simple if the input is.

ST_SimplifyPreserveTopology(geometry, epsilon)

st_simplifyvw

Returns a simplified representation of a geometry using the Visvalingam-Whyatt algorithm. The simplification tolerance is an area value, in the units of the input SRS. Simplification removes vertices which form "corners" with area less than the tolerance. The result may not be valid even if the input is.

ST_SimplifyVW(geometry, epsilon)

st_startpoint

Returns the first point of a LINESTRING geometry as a POINT. Returns NULL if the input is not a LINESTRING

ST_StartPoint(line_string)

st_tileenvelope

Returns the Web Mercator (EPSG:3857) bounds of an XYZ map tile as a polygon.

ST_TileEnvelope(zoom, x, y)

st_touches

Returns TRUE if A and B intersect, but their interiors do not intersect. Equivalently, A and B have at least one point in common, and the common points lie in at least one boundary. For Point/Point inputs the relationship is always FALSE, since points do not have a boundary.

ST_Touches(geomA, geomB)

st_transform

Reprojects a geometry between coordinate reference systems, e.g. 'EPSG:4326' -> 'EPSG:3857'. Coordinates are always interpreted as x = easting/longitude (always-XY); the optional 4th argument is accepted for dialect compatibility but must be true.

ST_Transform(geom, source_crs, target_crs [, always_xy])

st_translate

Translates the geometry by the given X and Y offsets.

ST_Translate(geom, dx, dy)

st_transscale

Translates the geometry by (dx, dy) then scales it by (xfactor, yfactor).

ST_TransScale(geom, dx, dy, xfactor, yfactor)

st_union

Returns the geometric union of two geometries.

ST_Union(geomA, geomB)

st_within

Returns TRUE if geometry A is within geometry B. A is within B if and only if all points of A lie inside (i.e. in the interior or boundary of) B (or equivalently, no points of A lie in the exterior of B), and the interiors of A and B have at least one point in common.