# Spatial functions Source: https://www.hotdata.dev/docs/sql-functions-spatial Site index: https://www.hotdata.dev/llms.txt 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](/docs/sql#geospatial-functions) 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. ``` ST_Within(geomA, geomB) ``` ### st_x Return the X coordinate of the point, or NULL if not available. Input must be a point. ``` ST_X(geometry) ``` ### st_xmax Returns X maxima of a bounding box 2d or 3d or a geometry ``` ST_XMax(geometry) ``` ### st_xmin Returns X minima of a bounding box 2d or 3d or a geometry ``` ST_XMin(geometry) ``` ### st_y Return the Y coordinate of the point, or NULL if not available. Input must be a point. ``` ST_Y(geometry) ``` ### st_ymax Returns Y maxima of a bounding box 2d or 3d or a geometry ``` ST_YMax(geometry) ``` ### st_ymin Returns Y minima of a bounding box 2d or 3d or a geometry ``` ST_YMin(geometry) ``` ### st_z Return the Z coordinate of the point, or NULL if not available. Input must be a point. ``` ST_Z(geometry) ``` ### st_zmax Returns Z maxima of a bounding box 2d or 3d or a geometry ``` ST_ZMax(geometry) ``` ### st_zmflag Returns a flag describing the geometry's dimensionality: 0 = XY, 1 = XYM, 2 = XYZ, 3 = XYZM. ST_ZMFlag(NULL) is NULL. ``` ST_ZMFlag(geom) ``` ### st_zmin Returns the Z minima of a 2D or 3D bounding box or a geometry ``` ST_ZMin(geometry) ```