⌘+k ctrl+k
1.2 (stable)
Search Shortcut cmd + k | ctrl + k
Spatial Functions

Function Index

Scalar Functions

Function Summary
ST_Area Compute the area of a geometry.
ST_Area_Spheroid Returns the area of a geometry in meters, using an ellipsoidal model of the earth
ST_AsGeoJSON Returns the geometry as a GeoJSON fragment
ST_AsHEXWKB Returns the geometry as a HEXWKB string
ST_AsSVG Convert the geometry into a SVG fragment or path
ST_AsText Returns the geometry as a WKT string
ST_AsWKB Returns the geometry as a WKB (Well-Known-Binary) blob
ST_Boundary Returns the "boundary" of a geometry
ST_Buffer Returns a buffer around the input geometry at the target distance
ST_BuildArea Creates a polygonal geometry by attemtping to "fill in" the input geometry.
ST_Centroid Returns the centroid of a geometry
ST_Collect Collects a list of geometries into a collection geometry.
ST_CollectionExtract Extracts geometries from a GeometryCollection into a typed multi geometry.
ST_ConcaveHull Returns the 'concave' hull of the input geometry, containing all of the source input's points, and which can be used to create polygons from points. The ratio parameter dictates the level of concavity; 1.0 returns the convex hull; and 0 indicates to return the most concave hull possible. Set allowHoles to a non-zero value to allow output containing holes.
ST_Contains Returns true if the first geometry contains the second geometry
ST_ContainsProperly Returns true if the first geometry "properly" contains the second geometry
ST_ConvexHull Returns the convex hull enclosing the geometry
ST_CoverageInvalidEdges Returns the invalid edges in a polygonal coverage, which are edges that are not shared by two polygons.
ST_CoverageSimplify Simplify the edges in a polygonal coverage, preserving the coverange by ensuring that the there are no seams between the resulting simplified polygons.
ST_CoverageUnion Union all geometries in a polygonal coverage into a single geometry.
ST_CoveredBy Returns true if geom1 is "covered by" geom2
ST_Covers Returns true if the geom1 "covers" geom2
ST_Crosses Returns true if geom1 "crosses" geom2
ST_DWithin Returns if two geometries are within a target distance of each-other
ST_DWithin_Spheroid Returns if two POINT_2D's are within a target distance in meters, using an ellipsoidal model of the earths surface.
ST_Difference Returns the "difference" between two geometries
ST_Dimension Returns the "topological dimension" of a geometry.
ST_Disjoint Returns true if the geometries are disjoint
ST_Distance Returns the planar distance between two geometries
ST_Distance_Sphere Returns the haversine (great circle) distance between two geometries.
ST_Distance_Spheroid Returns the distance between two geometries in meters using an ellipsoidal model of the earths surface.
ST_Dump Dumps a geometry into a list of sub-geometries and their "path" in the original geometry.
ST_EndPoint Returns the end point of a LINESTRING.
ST_Envelope Returns the minimum bounding rectangle of a geometry as a polygon geometry
ST_Equals Returns true if the geometries are "equal"
ST_Extent Returns the minimal bounding box enclosing the input geometry
ST_Extent_Approx Returns the approximate bounding box of a geometry, if available.
ST_ExteriorRing Returns the exterior ring (shell) of a polygon geometry.
ST_FlipCoordinates Returns a new geometry with the coordinates of the input geometry "flipped" so that x = y and y = x
ST_Force2D Forces the vertices of a geometry to have X and Y components
ST_Force3DM Forces the vertices of a geometry to have X, Y and M components
ST_Force3DZ Forces the vertices of a geometry to have X, Y and Z components
ST_Force4D Forces the vertices of a geometry to have X, Y, Z and M components
ST_GeomFromGeoJSON Deserializes a GEOMETRY from a GeoJSON fragment.
ST_GeomFromHEXEWKB Deserialize a GEOMETRY from a HEX(E)WKB encoded string
ST_GeomFromHEXWKB Deserialize a GEOMETRY from a HEX(E)WKB encoded string
ST_GeomFromText Deserialize a GEOMETRY from a WKT encoded string
ST_GeomFromWKB Deserializes a GEOMETRY from a WKB encoded blob
ST_GeometryType Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type. Possible enum return types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.
ST_HasM Check if the input geometry has M values.
ST_HasZ Check if the input geometry has Z values.
ST_Hilbert Encodes the X and Y values as the hilbert curve index for a curve covering the given bounding box.
ST_Intersection Returns the intersection of two geometries
ST_Intersects Returns true if the geometries intersect
ST_Intersects_Extent Returns true if the extent of two geometries intersects
ST_IsClosed Check if a geometry is 'closed'
ST_IsEmpty Returns true if the geometry is "empty".
ST_IsRing Returns true if the geometry is a ring (both ST_IsClosed and ST_IsSimple).
ST_IsSimple Returns true if the geometry is simple
ST_IsValid Returns true if the geometry is valid
ST_Length Returns the length of the input line geometry
ST_Length_Spheroid Returns the length of the input geometry in meters, using an ellipsoidal model of the earth
ST_LineInterpolatePoint Returns a point interpolated along a line at a fraction of total 2D length.
ST_LineInterpolatePoints Returns a multi-point interpolated along a line at a fraction of total 2D length.
ST_LineMerge "Merges" the input line geometry, optionally taking direction into account.
ST_LineString2DFromWKB Deserialize a LINESTRING_2D from a WKB encoded blob
ST_LineSubstring Returns a substring of a line between two fractions of total 2D length.
ST_M Returns the M coordinate of a point geometry
ST_MMax Returns the maximum M coordinate of a geometry
ST_MMin Returns the minimum M coordinate of a geometry
ST_MakeEnvelope Create a rectangular polygon from min/max coordinates
ST_MakeLine Create a LINESTRING from a list of POINT geometries
ST_MakePolygon Create a POLYGON from a LINESTRING shell
ST_MakeValid Returns a valid representation of the geometry
ST_MaximumInscribedCircle Returns the maximum inscribed circle of the input geometry, optionally with a tolerance.
ST_MinimumRotatedRectangle Returns the minimum rotated rectangle that bounds the input geometry, finding the surrounding box that has the lowest area by using a rotated rectangle, rather than taking the lowest and highest coordinate values as per ST_Envelope().
ST_Multi Turns a single geometry into a multi geometry.
ST_NGeometries Returns the number of component geometries in a collection geometry.
ST_NInteriorRings Returns the number if interior rings of a polygon
ST_NPoints Returns the number of vertices within a geometry
ST_Node Returns a "noded" MultiLinestring, produced by combining a collection of input linestrings and adding additional vertices where they intersect.
ST_Normalize Returns the "normalized" representation of the geometry
ST_NumGeometries Returns the number of component geometries in a collection geometry.
ST_NumInteriorRings Returns the number if interior rings of a polygon
ST_NumPoints Returns the number of vertices within a geometry
ST_Overlaps Returns true if the geometries overlap
ST_Perimeter Returns the length of the perimeter of the geometry
ST_Perimeter_Spheroid Returns the length of the perimeter in meters using an ellipsoidal model of the earths surface.
ST_Point Creates a GEOMETRY point
ST_Point2D Creates a POINT_2D
ST_Point2DFromWKB Deserialize a POINT_2D from a WKB encoded blob
ST_Point3D Creates a POINT_3D
ST_Point4D Creates a POINT_4D
ST_PointN Returns the n'th vertex from the input geometry as a point geometry
ST_PointOnSurface Returns a point guaranteed to lie on the surface of the geometry
ST_Points Collects all the vertices in the geometry into a MULTIPOINT
ST_Polygon2DFromWKB Deserialize a POLYGON_2D from a WKB encoded blob
ST_Polygonize Returns a polygonized representation of the input geometries
ST_QuadKey Compute the quadkey for a given lon/lat point at a given level.
ST_ReducePrecision Returns the geometry with all vertices reduced to the given precision
ST_RemoveRepeatedPoints Remove repeated points from a LINESTRING.
ST_Reverse Returns the geometry with the order of its vertices reversed
ST_ShortestLine Returns the shortest line between two geometries
ST_Simplify Returns a simplified version of the geometry
ST_SimplifyPreserveTopology Returns a simplified version of the geometry that preserves topology
ST_StartPoint Returns the start point of a LINESTRING.
ST_Touches Returns true if the geometries touch
ST_Transform Transforms a geometry between two coordinate systems
ST_Union Returns the union of two geometries
ST_VoronoiDiagram Returns the Voronoi diagram of the supplied MultiPoint geometry
ST_Within Returns true if the first geometry is within the second
ST_X Returns the X coordinate of a point geometry
ST_XMax Returns the maximum X coordinate of a geometry
ST_XMin Returns the minimum X coordinate of a geometry
ST_Y Returns the Y coordinate of a point geometry
ST_YMax Returns the maximum Y coordinate of a geometry
ST_YMin Returns the minimum Y coordinate of a geometry
ST_Z Returns the Z coordinate of a point geometry
ST_ZMFlag Returns a flag indicating the presence of Z and M values in the input geometry.
ST_ZMax Returns the maximum Z coordinate of a geometry
ST_ZMin Returns the minimum Z coordinate of a geometry

Aggregate Functions

Function Summary
ST_CoverageInvalidEdges_Agg Returns the invalid edges of a coverage geometry
ST_CoverageSimplify_Agg Simplifies a set of geometries while maintaining coverage
ST_CoverageUnion_Agg Unions a set of geometries while maintaining coverage
ST_Envelope_Agg Alias for ST_Extent_Agg.
ST_Extent_Agg Computes the minimal-bounding-box polygon containing the set of input geometries
ST_Intersection_Agg Computes the intersection of a set of geometries
ST_Union_Agg Computes the union of a set of input geometries

Table Functions

Function Summary
ST_Drivers Returns the list of supported GDAL drivers and file formats
ST_GeneratePoints Generates a set of random points within the specified bounding box.
ST_Read Read and import a variety of geospatial file formats using the GDAL library.
ST_ReadOSM The ST_ReadOsm() table function enables reading compressed OpenStreetMap data directly from a .osm.pbf file.
ST_Read_Meta Read the metadata from a variety of geospatial file formats using the GDAL library.

Scalar Functions

ST_Area

Signatures 

DOUBLE ST_Area (geom GEOMETRY)
DOUBLE ST_Area (polygon POLYGON_2D)
DOUBLE ST_Area (linestring LINESTRING_2D)
DOUBLE ST_Area (point POINT_2D)
DOUBLE ST_Area (box BOX_2D)

Description

Compute the area of a geometry.

Returns 0.0 for any geometry that is not a POLYGON, MULTIPOLYGON or GEOMETRYCOLLECTION containing polygon geometries.

The area is in the same units as the spatial reference system of the geometry.

The POINT_2D and LINESTRING_2D overloads of this function always return 0.0 but are included for completeness.

Example 

SELECT ST_Area('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY);
-- 1.0

ST_Area_Spheroid

Signatures 

DOUBLE ST_Area_Spheroid (geom GEOMETRY)
DOUBLE ST_Area_Spheroid (poly POLYGON_2D)

Description

Returns the area of a geometry in meters, using an ellipsoidal model of the earth

The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84), with [latitude, longitude] axis order and the area is returned in square meters. This function uses the GeographicLib library, calculating the area using an ellipsoidal model of the earth. This is a highly accurate method for calculating the area of a polygon taking the curvature of the earth into account, but is also the slowest.

Returns 0.0 for any geometry that is not a POLYGON, MULTIPOLYGON or GEOMETRYCOLLECTION containing polygon geometries.

ST_AsGeoJSON

Signature 

JSON ST_AsGeoJSON (geom GEOMETRY)

Description

Returns the geometry as a GeoJSON fragment

This does not return a complete GeoJSON document, only the geometry fragment. To construct a complete GeoJSON document or feature, look into using the DuckDB JSON extension in conjunction with this function. This function supports geometries with Z values, but not M values. M values are ignored.

Example 

SELECT ST_AsGeoJSON('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY);
----
{"type":"Polygon","coordinates":[[[0.0,0.0],[0.0,1.0],[1.0,1.0],[1.0,0.0],[0.0,0.0]]]}

-- Convert a geometry into a full GeoJSON feature (requires the JSON extension to be loaded)
SELECT CAST({
    type: 'Feature',
    geometry: ST_AsGeoJSON(ST_Point(1,2)),
    properties: {
        name: 'my_point'
    }
} AS JSON);
----
{"type":"Feature","geometry":{"type":"Point","coordinates":[1.0,2.0]},"properties":{"name":"my_point"}}

ST_AsHEXWKB

Signature 

VARCHAR ST_AsHEXWKB (geom GEOMETRY)

Description

Returns the geometry as a HEXWKB string

Example 

SELECT ST_AsHexWKB('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY);
----
01030000000100000005000000000000000000000000000...

ST_AsSVG

Signature 

VARCHAR ST_AsSVG (geom GEOMETRY, relative BOOLEAN, precision INTEGER)

Description

Convert the geometry into a SVG fragment or path

The SVG fragment is returned as a string. The fragment is a path element that can be used in an SVG document. The second Boolean argument specifies whether the path should be relative or absolute. The third argument specifies the maximum number of digits to use for the coordinates.

Points are formatted as cx/cy using absolute coordinates or x/y using relative coordinates.

Example 

SELECT ST_AsSVG('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY, false, 15);
----
M 0 0 L 0 -1 1 -1 1 0 Z

ST_AsText

Signatures 

VARCHAR ST_AsText (geom GEOMETRY)
VARCHAR ST_AsText (point POINT_2D)
VARCHAR ST_AsText (linestring LINESTRING_2D)
VARCHAR ST_AsText (polygon POLYGON_2D)
VARCHAR ST_AsText (box BOX_2D)

Description

Returns the geometry as a WKT string

Example 

SELECT ST_AsText(ST_MakeEnvelope(0,0,1,1));
----
POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))

ST_AsWKB

Signature 

WKB_BLOB ST_AsWKB (geom GEOMETRY)

Description

Returns the geometry as a WKB (Well-Known-Binary) blob

Example 

SELECT ST_AsWKB('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY)::BLOB;
----
\x01\x03\x00\x00\x00\x01\x00\x00\x00\x05...

ST_Boundary

Signature 

GEOMETRY ST_Boundary (geom GEOMETRY)

Description

Returns the "boundary" of a geometry

ST_Buffer

Signatures 

GEOMETRY ST_Buffer (geom GEOMETRY, distance DOUBLE)
GEOMETRY ST_Buffer (geom GEOMETRY, distance DOUBLE, num_triangles INTEGER)
GEOMETRY ST_Buffer (geom GEOMETRY, distance DOUBLE, num_triangles INTEGER, cap_style VARCHAR, join_style VARCHAR, mitre_limit DOUBLE)

Description

Returns a buffer around the input geometry at the target distance

geom is the input geometry.

distance is the target distance for the buffer, using the same units as the input geometry.

num_triangles represents how many triangles that will be produced to approximate a quarter circle. The larger the number, the smoother the resulting geometry. The default value is 8.

cap_style must be one of "CAP_ROUND", "CAP_FLAT", "CAP_SQUARE". This parameter is case-insensitive.

join_style must be one of "JOIN_ROUND", "JOIN_MITRE", "JOIN_BEVEL". This parameter is case-insensitive.

mitre_limit only applies when join_style is "JOIN_MITRE". It is the ratio of the distance from the corner to the mitre point to the corner radius. The default value is 1.0.

This is a planar operation and will not take into account the curvature of the earth.

ST_BuildArea

Signature 

GEOMETRY ST_BuildArea (geom GEOMETRY)

Description

Creates a polygonal geometry by attemtping to "fill in" the input geometry.

Unlike ST_Polygonize, this function does not fill in holes.

ST_Centroid

Signatures 

POINT_2D ST_Centroid (point POINT_2D)
POINT_2D ST_Centroid (linestring LINESTRING_2D)
POINT_2D ST_Centroid (polygon POLYGON_2D)
POINT_2D ST_Centroid (box BOX_2D)
POINT_2D ST_Centroid (box BOX_2DF)
GEOMETRY ST_Centroid (geom GEOMETRY)

Description

Returns the centroid of a geometry

ST_Collect

Signature 

GEOMETRY ST_Collect (geoms GEOMETRY[])

Description

Collects a list of geometries into a collection geometry.

  • If all geometries are POINT's, a MULTIPOINT is returned.
  • If all geometries are LINESTRING's, a MULTILINESTRING is returned.
  • If all geometries are POLYGON's, a MULTIPOLYGON is returned.
  • Otherwise if the input collection contains a mix of geometry types, a GEOMETRYCOLLECTION is returned.

Empty and NULL geometries are ignored. If all geometries are empty or NULL, a GEOMETRYCOLLECTION EMPTY is returned.

Example 

-- With all POINT's, a MULTIPOINT is returned
SELECT ST_Collect([ST_Point(1, 2), ST_Point(3, 4)]);
----
MULTIPOINT (1 2, 3 4)

-- With mixed geometry types, a GEOMETRYCOLLECTION is returned
SELECT ST_Collect([ST_Point(1, 2), ST_GeomFromText('LINESTRING(3 4, 5 6)')]);
----
GEOMETRYCOLLECTION (POINT (1 2), LINESTRING (3 4, 5 6))

-- Note that the empty geometry is ignored, so the result is a MULTIPOINT
SELECT ST_Collect([ST_Point(1, 2), NULL, ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')]);
----
MULTIPOINT (1 2)

-- If all geometries are empty or NULL, a GEOMETRYCOLLECTION EMPTY is returned
SELECT ST_Collect([NULL, ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')]);
----
GEOMETRYCOLLECTION EMPTY

-- Tip: You can use the `ST_Collect` function together with the `list()` aggregate function to collect multiple rows of geometries into a single geometry collection:

CREATE TABLE points (geom GEOMETRY);

INSERT INTO points VALUES (ST_Point(1, 2)), (ST_Point(3, 4));

SELECT ST_Collect(list(geom)) FROM points;
----
MULTIPOINT (1 2, 3 4)

ST_CollectionExtract

Signatures 

GEOMETRY ST_CollectionExtract (geom GEOMETRY, type INTEGER)
GEOMETRY ST_CollectionExtract (geom GEOMETRY)

Description

Extracts geometries from a GeometryCollection into a typed multi geometry.

If the input geometry is a GeometryCollection, the function will return a multi geometry, determined by the type parameter.

  • if type = 1, returns a MultiPoint containg all the Points in the collection
  • if type = 2, returns a MultiLineString containg all the LineStrings in the collection
  • if type = 3, returns a MultiPolygon containg all the Polygons in the collection

If no type parameters is provided, the function will return a multi geometry matching the highest "surface dimension" of the contained geometries. E.g., if the collection contains only Points, a MultiPoint will be returned. But if the collection contains both Points and LineStrings, a MultiLineString will be returned. Similarly, if the collection contains Polygons, a MultiPolygon will be returned. Contained geometries of a lower surface dimension will be ignored.

If the input geometry contains nested GeometryCollections, their geometries will be extracted recursively and included into the final multi geometry as well.

If the input geometry is not a GeometryCollection, the function will return the input geometry as is.

Example 

SELECT st_collectionextract('MULTIPOINT(1 2,3 4)'::GEOMETRY, 1);
-- MULTIPOINT (1 2, 3 4)

ST_ConcaveHull

Signature 

GEOMETRY ST_ConcaveHull (geom GEOMETRY, ratio DOUBLE, allowHoles BOOLEAN)

Description

Returns the 'concave' hull of the input geometry, containing all of the source input's points, and which can be used to create polygons from points. The ratio parameter dictates the level of concavity; 1.0 returns the convex hull; and 0 indicates to return the most concave hull possible. Set allowHoles to a non-zero value to allow output containing holes.

ST_Contains

Signatures 

BOOLEAN ST_Contains (geom1 POLYGON_2D, geom2 POINT_2D)
BOOLEAN ST_Contains (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the first geometry contains the second geometry

In contrast to ST_ContainsProperly, this function will also return true if geom2 is contained strictly on the boundary of geom1. A geometry always ST_Contains itself, but does not ST_ContainsProperly itself.

ST_ContainsProperly

Signature 

BOOLEAN ST_ContainsProperly (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the first geometry "properly" contains the second geometry

In contrast to ST_Contains, this function does not return true if geom2 is contained strictly on the boundary of geom1. A geometry always ST_Contains itself, but does not ST_ContainsProperly itself.

ST_ConvexHull

Signature 

GEOMETRY ST_ConvexHull (geom GEOMETRY)

Description

Returns the convex hull enclosing the geometry

ST_CoverageInvalidEdges

Signatures 

GEOMETRY ST_CoverageInvalidEdges (geoms GEOMETRY[], tolerance DOUBLE)
GEOMETRY ST_CoverageInvalidEdges (geoms GEOMETRY[])

Description

Returns the invalid edges in a polygonal coverage, which are edges that are not shared by two polygons. Returns NULL if the input is not a polygonal coverage, or if the input is valid. Tolerance is 0 by default.

ST_CoverageSimplify

Signatures 

GEOMETRY ST_CoverageSimplify (geoms GEOMETRY[], tolerance DOUBLE, simplify_boundary BOOLEAN)
GEOMETRY ST_CoverageSimplify (geoms GEOMETRY[], tolerance DOUBLE)

Description

Simplify the edges in a polygonal coverage, preserving the coverange by ensuring that the there are no seams between the resulting simplified polygons.

By default, the boundary of the coverage is also simplified, but this can be controlled with the optional third 'simplify_boundary' parameter.

ST_CoverageUnion

Signature 

GEOMETRY ST_CoverageUnion (geoms GEOMETRY[])

Description

Union all geometries in a polygonal coverage into a single geometry. This may be faster than using ST_Union, but may use more memory.

ST_CoveredBy

Signature 

BOOLEAN ST_CoveredBy (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if geom1 is "covered by" geom2

ST_Covers

Signature 

BOOLEAN ST_Covers (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geom1 "covers" geom2

ST_Crosses

Signature 

BOOLEAN ST_Crosses (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if geom1 "crosses" geom2

ST_DWithin

Signature 

BOOLEAN ST_DWithin (geom1 GEOMETRY, geom2 GEOMETRY, distance DOUBLE)

Description

Returns if two geometries are within a target distance of each-other

ST_DWithin_Spheroid

Signature 

BOOLEAN ST_DWithin_Spheroid (p1 POINT_2D, p2 POINT_2D, distance DOUBLE)

Description

Returns if two POINT_2D's are within a target distance in meters, using an ellipsoidal model of the earths surface.

The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84), with [latitude, longitude] axis order and the distance is returned in meters. This function uses the GeographicLib library to solve the inverse geodesic problem, calculating the distance between two points using an ellipsoidal model of the earth. This is a highly accurate method for calculating the distance between two arbitrary points taking the curvature of the earths surface into account, but is also the slowest.

ST_Difference

Signature 

GEOMETRY ST_Difference (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns the "difference" between two geometries

ST_Dimension

Signature 

INTEGER ST_Dimension (geom GEOMETRY)

Description

Returns the "topological dimension" of a geometry.

  • For POINT and MULTIPOINT geometries, returns 0
  • For LINESTRING and MULTILINESTRING, returns 1
  • For POLYGON and MULTIPOLYGON, returns 2
  • For GEOMETRYCOLLECTION, returns the maximum dimension of the contained geometries, or 0 if the collection is empty

Example 

SELECT st_dimension('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::GEOMETRY);
----
2

ST_Disjoint

Signature 

BOOLEAN ST_Disjoint (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geometries are disjoint

ST_Distance

Signatures 

DOUBLE ST_Distance (point1 POINT_2D, point2 POINT_2D)
DOUBLE ST_Distance (point POINT_2D, linestring LINESTRING_2D)
DOUBLE ST_Distance (linestring LINESTRING_2D, point POINT_2D)
DOUBLE ST_Distance (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns the planar distance between two geometries

ST_Distance_Sphere

Signatures 

DOUBLE ST_Distance_Sphere (geom1 GEOMETRY, geom2 GEOMETRY)
DOUBLE ST_Distance_Sphere (point1 POINT_2D, point2 POINT_2D)

Description

Returns the haversine (great circle) distance between two geometries.

  • Only supports POINT geometries.
  • Returns the distance in meters.
  • The input is expected to be in WGS84 (EPSG:4326) coordinates, using a [latitude, longitude] axis order.

ST_Distance_Spheroid

Signature 

DOUBLE ST_Distance_Spheroid (p1 POINT_2D, p2 POINT_2D)

Description

Returns the distance between two geometries in meters using an ellipsoidal model of the earths surface.

The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84), with [latitude, longitude] axis order and the distance limit is expected to be in meters. This function uses the GeographicLib library to solve the inverse geodesic problem, calculating the distance between two points using an ellipsoidal model of the earth. This is a highly accurate method for calculating the distance between two arbitrary points taking the curvature of the earths surface into account, but is also the slowest.

Example 

-- Note: the coordinates are in WGS84 and [latitude, longitude] axis order
-- Whats the distance between New York and Amsterdam (JFK and AMS airport)?
SELECT st_distance_spheroid(
st_point(40.6446, -73.7797),
st_point(52.3130, 4.7725)
);
----
5863418.7459356235
-- Roughly 5863km!

ST_Dump

Signature 

STRUCT(geom GEOMETRY, path INTEGER[])[] ST_Dump (geom GEOMETRY)

Description

Dumps a geometry into a list of sub-geometries and their "path" in the original geometry.

You can use the UNNEST(res, recursive := true) function to explode resulting list of structs into multiple rows.

Example 

SELECT st_dump('MULTIPOINT(1 2,3 4)'::GEOMETRY);
----
[{'geom': 'POINT(1 2)', 'path': [0]}, {'geom': 'POINT(3 4)', 'path': [1]}]

SELECT unnest(st_dump('MULTIPOINT(1 2,3 4)'::GEOMETRY), recursive := true);
-- ┌─────────────┬─────────┐
-- │    geom     │  path   │
-- │  geometry   │ int32[] │
-- ├─────────────┼─────────┤
-- │ POINT (1 2) │ [1]     │
-- │ POINT (3 4) │ [2]     │
-- └─────────────┴─────────┘

ST_EndPoint

Signatures 

GEOMETRY ST_EndPoint (geom GEOMETRY)
POINT_2D ST_EndPoint (line LINESTRING_2D)

Description

Returns the end point of a LINESTRING.

ST_Envelope

Signature 

GEOMETRY ST_Envelope (geom GEOMETRY)

Description

Returns the minimum bounding rectangle of a geometry as a polygon geometry

ST_Equals

Signature 

BOOLEAN ST_Equals (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geometries are "equal"

ST_Extent

Signatures 

BOX_2D ST_Extent (geom GEOMETRY)
BOX_2D ST_Extent (wkb WKB_BLOB)

Description

Returns the minimal bounding box enclosing the input geometry

ST_Extent_Approx

Signature 

BOX_2DF ST_Extent_Approx (geom GEOMETRY)

Description

Returns the approximate bounding box of a geometry, if available.

This function is only really used internally, and returns the cached bounding box of the geometry if it exists. This function may be removed or renamed in the future.

ST_ExteriorRing

Signatures 

GEOMETRY ST_ExteriorRing (geom GEOMETRY)
LINESTRING_2D ST_ExteriorRing (polygon POLYGON_2D)

Description

Returns the exterior ring (shell) of a polygon geometry.

ST_FlipCoordinates

Signatures 

GEOMETRY ST_FlipCoordinates (geom GEOMETRY)
POINT_2D ST_FlipCoordinates (point POINT_2D)
LINESTRING_2D ST_FlipCoordinates (linestring LINESTRING_2D)
POLYGON_2D ST_FlipCoordinates (polygon POLYGON_2D)
BOX_2D ST_FlipCoordinates (box BOX_2D)

Description

Returns a new geometry with the coordinates of the input geometry "flipped" so that x = y and y = x

ST_Force2D

Signature 

GEOMETRY ST_Force2D (geom GEOMETRY)

Description

Forces the vertices of a geometry to have X and Y components

This function will drop any Z and M values from the input geometry, if present. If the input geometry is already 2D, it will be returned as is.

ST_Force3DM

Signature 

GEOMETRY ST_Force3DM (geom GEOMETRY, m DOUBLE)

Description

Forces the vertices of a geometry to have X, Y and M components

The following cases apply:

  • If the input geometry has a Z component but no M component, the Z component will be replaced with the new M value.
  • If the input geometry has a M component but no Z component, it will be returned as is.
  • If the input geometry has both a Z component and a M component, the Z component will be removed.
  • Otherwise, if the input geometry has neither a Z or M component, the new M value will be added to the vertices of the input geometry.

ST_Force3DZ

Signature 

GEOMETRY ST_Force3DZ (geom GEOMETRY, z DOUBLE)

Description

Forces the vertices of a geometry to have X, Y and Z components

The following cases apply:

  • If the input geometry has a M component but no Z component, the M component will be replaced with the new Z value.
  • If the input geometry has a Z component but no M component, it will be returned as is.
  • If the input geometry has both a Z component and a M component, the M component will be removed.
  • Otherwise, if the input geometry has neither a Z or M component, the new Z value will be added to the vertices of the input geometry.

ST_Force4D

Signature 

GEOMETRY ST_Force4D (geom GEOMETRY, z DOUBLE, m DOUBLE)

Description

Forces the vertices of a geometry to have X, Y, Z and M components

The following cases apply:

  • If the input geometry has a Z component but no M component, the new M value will be added to the vertices of the input geometry.
  • If the input geometry has a M component but no Z component, the new Z value will be added to the vertices of the input geometry.
  • If the input geometry has both a Z component and a M component, the geometry will be returned as is.
  • Otherwise, if the input geometry has neither a Z or M component, the new Z and M values will be added to the vertices of the input geometry.

ST_GeomFromGeoJSON

Signatures 

GEOMETRY ST_GeomFromGeoJSON (geojson JSON)
GEOMETRY ST_GeomFromGeoJSON (geojson VARCHAR)

Description

Deserializes a GEOMETRY from a GeoJSON fragment.

Example 

SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[1.0,2.0]}');
----
POINT (1 2)

ST_GeomFromHEXEWKB

Signature 

GEOMETRY ST_GeomFromHEXEWKB (hexwkb VARCHAR)

Description

Deserialize a GEOMETRY from a HEX(E)WKB encoded string

DuckDB spatial doesnt currently differentiate between WKB and EWKB, so ST_GeomFromHEXWKB and `ST_GeomFromHEXEWKB" are just aliases of each other.

ST_GeomFromHEXWKB

Signature 

GEOMETRY ST_GeomFromHEXWKB (hexwkb VARCHAR)

Description

Deserialize a GEOMETRY from a HEX(E)WKB encoded string

DuckDB spatial doesnt currently differentiate between WKB and EWKB, so ST_GeomFromHEXWKB and `ST_GeomFromHEXEWKB" are just aliases of each other.

ST_GeomFromText

Signatures 

GEOMETRY ST_GeomFromText (wkt VARCHAR)
GEOMETRY ST_GeomFromText (wkt VARCHAR, ignore_invalid BOOLEAN)

Description

Deserialize a GEOMETRY from a WKT encoded string

ST_GeomFromWKB

Signatures 

GEOMETRY ST_GeomFromWKB (wkb WKB_BLOB)
GEOMETRY ST_GeomFromWKB (blob BLOB)

Description

Deserializes a GEOMETRY from a WKB encoded blob

ST_GeometryType

Signatures 

ANY ST_GeometryType (geom GEOMETRY)
ANY ST_GeometryType (point POINT_2D)
ANY ST_GeometryType (linestring LINESTRING_2D)
ANY ST_GeometryType (polygon POLYGON_2D)
ANY ST_GeometryType (wkb WKB_BLOB)

Description

Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type. Possible enum return types are: POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, and GEOMETRYCOLLECTION.

Example 

SELECT DISTINCT ST_GeometryType(ST_GeomFromText('POINT(1 1)'));
----
POINT

ST_HasM

Signatures 

BOOLEAN ST_HasM (geom GEOMETRY)
BOOLEAN ST_HasM (wkb WKB_BLOB)

Description

Check if the input geometry has M values.

Example 

-- HasM for a 2D geometry
SELECT ST_HasM(ST_GeomFromText('POINT(1 1)'));
----
false

-- HasM for a 3DZ geometry
SELECT ST_HasM(ST_GeomFromText('POINT Z(1 1 1)'));
----
false

-- HasM for a 3DM geometry
SELECT ST_HasM(ST_GeomFromText('POINT M(1 1 1)'));
----
true

-- HasM for a 4D geometry
SELECT ST_HasM(ST_GeomFromText('POINT ZM(1 1 1 1)'));
----
true

ST_HasZ

Signatures 

BOOLEAN ST_HasZ (geom GEOMETRY)
BOOLEAN ST_HasZ (wkb WKB_BLOB)

Description

Check if the input geometry has Z values.

Example 

-- HasZ for a 2D geometry
SELECT ST_HasZ(ST_GeomFromText('POINT(1 1)'));
----
false

-- HasZ for a 3DZ geometry
SELECT ST_HasZ(ST_GeomFromText('POINT Z(1 1 1)'));
----
true

-- HasZ for a 3DM geometry
SELECT ST_HasZ(ST_GeomFromText('POINT M(1 1 1)'));
----
false

-- HasZ for a 4D geometry
SELECT ST_HasZ(ST_GeomFromText('POINT ZM(1 1 1 1)'));
----
true

ST_Hilbert

Signatures 

UINTEGER ST_Hilbert (x DOUBLE, y DOUBLE, bounds BOX_2D)
UINTEGER ST_Hilbert (geom GEOMETRY, bounds BOX_2D)
UINTEGER ST_Hilbert (geom GEOMETRY)
UINTEGER ST_Hilbert (box BOX_2D, bounds BOX_2D)
UINTEGER ST_Hilbert (box BOX_2DF, bounds BOX_2DF)

Description

Encodes the X and Y values as the hilbert curve index for a curve covering the given bounding box. If a geometry is provided, the center of the approximate bounding box is used as the point to encode. If no bounding box is provided, the hilbert curve index is mapped to the full range of a single-presicion float. For the BOX_2D and BOX_2DF variants, the center of the box is used as the point to encode.

ST_Intersection

Signature 

GEOMETRY ST_Intersection (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns the intersection of two geometries

ST_Intersects

Signatures 

BOOLEAN ST_Intersects (box1 BOX_2D, box2 BOX_2D)
BOOLEAN ST_Intersects (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geometries intersect

ST_Intersects_Extent

Signature 

BOOLEAN ST_Intersects_Extent (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the extent of two geometries intersects

ST_IsClosed

Signature 

BOOLEAN ST_IsClosed (geom GEOMETRY)

Description

Check if a geometry is 'closed'

ST_IsEmpty

Signatures 

BOOLEAN ST_IsEmpty (geom GEOMETRY)
BOOLEAN ST_IsEmpty (linestring LINESTRING_2D)
BOOLEAN ST_IsEmpty (polygon POLYGON_2D)

Description

Returns true if the geometry is "empty".

ST_IsRing

Signature 

BOOLEAN ST_IsRing (geom GEOMETRY)

Description

Returns true if the geometry is a ring (both ST_IsClosed and ST_IsSimple).

ST_IsSimple

Signature 

BOOLEAN ST_IsSimple (geom GEOMETRY)

Description

Returns true if the geometry is simple

ST_IsValid

Signature 

BOOLEAN ST_IsValid (geom GEOMETRY)

Description

Returns true if the geometry is valid

ST_Length

Signatures 

DOUBLE ST_Length (geom GEOMETRY)
DOUBLE ST_Length (linestring LINESTRING_2D)

Description

Returns the length of the input line geometry

ST_Length_Spheroid

Signatures 

DOUBLE ST_Length_Spheroid (geom GEOMETRY)
DOUBLE ST_Length_Spheroid (line LINESTRING_2D)

Description

Returns the length of the input geometry in meters, using an ellipsoidal model of the earth

The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84), with [latitude, longitude] axis order and the length is returned in meters. This function uses the GeographicLib library, calculating the length using an ellipsoidal model of the earth. This is a highly accurate method for calculating the length of a line geometry taking the curvature of the earth into account, but is also the slowest.

Returns 0.0 for any geometry that is not a LINESTRING, MULTILINESTRING or GEOMETRYCOLLECTION containing line geometries.

ST_LineInterpolatePoint

Signature 

GEOMETRY ST_LineInterpolatePoint (line GEOMETRY, fraction DOUBLE)

Description

Returns a point interpolated along a line at a fraction of total 2D length.

ST_LineInterpolatePoints

Signature 

GEOMETRY ST_LineInterpolatePoints (line GEOMETRY, fraction DOUBLE, repeat BOOLEAN)

Description

Returns a multi-point interpolated along a line at a fraction of total 2D length.

if repeat is false, the result is a single point, (and equivalent to ST_LineInterpolatePoint), otherwise, the result is a multi-point with points repeated at the fraction interval.

ST_LineMerge

Signatures 

GEOMETRY ST_LineMerge (geom GEOMETRY)
GEOMETRY ST_LineMerge (geom GEOMETRY, preserve_direction BOOLEAN)

Description

"Merges" the input line geometry, optionally taking direction into account.

ST_LineString2DFromWKB

Signature 

GEOMETRY ST_LineString2DFromWKB (linestring LINESTRING_2D)

Description

Deserialize a LINESTRING_2D from a WKB encoded blob

ST_LineSubstring

Signature 

GEOMETRY ST_LineSubstring (line GEOMETRY, start_fraction DOUBLE, end_fraction DOUBLE)

Description

Returns a substring of a line between two fractions of total 2D length.

ST_M

Signature 

DOUBLE ST_M (geom GEOMETRY)

Description

Returns the M coordinate of a point geometry

Example 

SELECT ST_M(ST_Point(1, 2, 3, 4))

ST_MMax

Signature 

DOUBLE ST_MMax (geom GEOMETRY)

Description

Returns the maximum M coordinate of a geometry

Example 

SELECT ST_MMax(ST_Point(1, 2, 3, 4))

ST_MMin

Signature 

DOUBLE ST_MMin (geom GEOMETRY)

Description

Returns the minimum M coordinate of a geometry

Example 

SELECT ST_MMin(ST_Point(1, 2, 3, 4))

ST_MakeEnvelope

Signature 

GEOMETRY ST_MakeEnvelope (min_x DOUBLE, min_y DOUBLE, max_x DOUBLE, max_y DOUBLE)

Description

Create a rectangular polygon from min/max coordinates

ST_MakeLine

Signatures 

GEOMETRY ST_MakeLine (geoms GEOMETRY[])
GEOMETRY ST_MakeLine (start GEOMETRY, end GEOMETRY)

Description

Create a LINESTRING from a list of POINT geometries

Example 

SELECT ST_MakeLine([ST_Point(0, 0), ST_Point(1, 1)]);
----
LINESTRING(0 0, 1 1)

ST_MakePolygon

Signatures 

GEOMETRY ST_MakePolygon (shell GEOMETRY)
GEOMETRY ST_MakePolygon (shell GEOMETRY, holes GEOMETRY[])

Description

Create a POLYGON from a LINESTRING shell

Example 

SELECT ST_MakePolygon(ST_LineString([ST_Point(0, 0), ST_Point(1, 0), ST_Point(1, 1), ST_Point(0, 0)]));

ST_MakeValid

Signature 

GEOMETRY ST_MakeValid (geom GEOMETRY)

Description

Returns a valid representation of the geometry

ST_MaximumInscribedCircle

Signatures 

STRUCT(center GEOMETRY, nearest GEOMETRY, radius DOUBLE) ST_MaximumInscribedCircle (geom GEOMETRY)
STRUCT(center GEOMETRY, nearest GEOMETRY, radius DOUBLE) ST_MaximumInscribedCircle (geom GEOMETRY, tolerance DOUBLE)

Description

Returns the maximum inscribed circle of the input geometry, optionally with a tolerance.

By default, the tolerance is computed as max(width, height) / 1000. The return value is a struct with the center of the circle, the nearest point to the center on the boundary of the geometry, and the radius of the circle.

ST_MinimumRotatedRectangle

Signature 

GEOMETRY ST_MinimumRotatedRectangle (geom GEOMETRY)

Description

Returns the minimum rotated rectangle that bounds the input geometry, finding the surrounding box that has the lowest area by using a rotated rectangle, rather than taking the lowest and highest coordinate values as per ST_Envelope().

ST_Multi

Signature 

GEOMETRY ST_Multi (geom GEOMETRY)

Description

Turns a single geometry into a multi geometry.

If the geometry is already a multi geometry, it is returned as is.

Example 

SELECT ST_Multi(ST_GeomFromText('POINT(1 2)'));
----
MULTIPOINT (1 2)

SELECT ST_Multi(ST_GeomFromText('LINESTRING(1 1, 2 2)'));
----
MULTILINESTRING ((1 1, 2 2))

SELECT ST_Multi(ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'));
----
MULTIPOLYGON (((0 0, 0 1, 1 1, 1 0, 0 0)))

ST_NGeometries

Signature 

INTEGER ST_NGeometries (geom GEOMETRY)

Description

Returns the number of component geometries in a collection geometry. If the input geometry is not a collection, this function returns 0 or 1 depending on if the geometry is empty or not.

ST_NInteriorRings

Signatures 

INTEGER ST_NInteriorRings (geom GEOMETRY)
INTEGER ST_NInteriorRings (polygon POLYGON_2D)

Description

Returns the number if interior rings of a polygon

ST_NPoints

Signatures 

UINTEGER ST_NPoints (geom GEOMETRY)
UBIGINT ST_NPoints (point POINT_2D)
UBIGINT ST_NPoints (linestring LINESTRING_2D)
UBIGINT ST_NPoints (polygon POLYGON_2D)
UBIGINT ST_NPoints (box BOX_2D)

Description

Returns the number of vertices within a geometry

ST_Node

Signature 

GEOMETRY ST_Node (geom GEOMETRY)

Description

Returns a "noded" MultiLinestring, produced by combining a collection of input linestrings and adding additional vertices where they intersect.

ST_Normalize

Signature 

GEOMETRY ST_Normalize (geom GEOMETRY)

Description

Returns the "normalized" representation of the geometry

ST_NumGeometries

Signature 

INTEGER ST_NumGeometries (geom GEOMETRY)

Description

Returns the number of component geometries in a collection geometry. If the input geometry is not a collection, this function returns 0 or 1 depending on if the geometry is empty or not.

ST_NumInteriorRings

Signatures 

INTEGER ST_NumInteriorRings (geom GEOMETRY)
INTEGER ST_NumInteriorRings (polygon POLYGON_2D)

Description

Returns the number if interior rings of a polygon

ST_NumPoints

Signatures 

UINTEGER ST_NumPoints (geom GEOMETRY)
UBIGINT ST_NumPoints (point POINT_2D)
UBIGINT ST_NumPoints (linestring LINESTRING_2D)
UBIGINT ST_NumPoints (polygon POLYGON_2D)
UBIGINT ST_NumPoints (box BOX_2D)

Description

Returns the number of vertices within a geometry

ST_Overlaps

Signature 

BOOLEAN ST_Overlaps (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geometries overlap

ST_Perimeter

Signatures 

DOUBLE ST_Perimeter (geom GEOMETRY)
DOUBLE ST_Perimeter (polygon POLYGON_2D)
DOUBLE ST_Perimeter (box BOX_2D)

Description

Returns the length of the perimeter of the geometry

ST_Perimeter_Spheroid

Signatures 

DOUBLE ST_Perimeter_Spheroid (geom GEOMETRY)
DOUBLE ST_Perimeter_Spheroid (poly POLYGON_2D)

Description

Returns the length of the perimeter in meters using an ellipsoidal model of the earths surface.

The input geometry is assumed to be in the EPSG:4326 coordinate system (WGS84), with [latitude, longitude] axis order and the length is returned in meters. This function uses the GeographicLib library, calculating the perimeter using an ellipsoidal model of the earth. This is a highly accurate method for calculating the perimeter of a polygon taking the curvature of the earth into account, but is also the slowest.

Returns 0.0 for any geometry that is not a POLYGON, MULTIPOLYGON or GEOMETRYCOLLECTION containing polygon geometries.

ST_Point

Signature 

GEOMETRY ST_Point (x DOUBLE, y DOUBLE)

Description

Creates a GEOMETRY point

ST_Point2D

Signature 

POINT_2D ST_Point2D (x DOUBLE, y DOUBLE)

Description

Creates a POINT_2D

ST_Point2DFromWKB

Signature 

GEOMETRY ST_Point2DFromWKB (point POINT_2D)

Description

Deserialize a POINT_2D from a WKB encoded blob

ST_Point3D

Signature 

POINT_3D ST_Point3D (x DOUBLE, y DOUBLE, z DOUBLE)

Description

Creates a POINT_3D

ST_Point4D

Signature 

POINT_4D ST_Point4D (x DOUBLE, y DOUBLE, z DOUBLE, m DOUBLE)

Description

Creates a POINT_4D

ST_PointN

Signatures 

GEOMETRY ST_PointN (geom GEOMETRY, index INTEGER)
POINT_2D ST_PointN (linestring LINESTRING_2D, index INTEGER)

Description

Returns the n'th vertex from the input geometry as a point geometry

ST_PointOnSurface

Signature 

GEOMETRY ST_PointOnSurface (geom GEOMETRY)

Description

Returns a point guaranteed to lie on the surface of the geometry

ST_Points

Signature 

GEOMETRY ST_Points (geom GEOMETRY)

Description

Collects all the vertices in the geometry into a MULTIPOINT

Example 

SELECT st_points('LINESTRING(1 1, 2 2)'::GEOMETRY);
----
MULTIPOINT (1 1, 2 2)

SELECT st_points('MULTIPOLYGON Z EMPTY'::GEOMETRY);
----
MULTIPOINT Z EMPTY

ST_Polygon2DFromWKB

Signature 

GEOMETRY ST_Polygon2DFromWKB (polygon POLYGON_2D)

Description

Deserialize a POLYGON_2D from a WKB encoded blob

ST_Polygonize

Signature 

GEOMETRY ST_Polygonize (geometries GEOMETRY[])

Description

Returns a polygonized representation of the input geometries

ST_QuadKey

Signatures 

VARCHAR ST_QuadKey (longitude DOUBLE, latitude DOUBLE, level INTEGER)
VARCHAR ST_QuadKey (point GEOMETRY, level INTEGER)

Description

Compute the quadkey for a given lon/lat point at a given level. Note that the parameter order is longitude, latitude.

level has to be between 1 and 23, inclusive.

The input coordinates will be clamped to the lon/lat bounds of the earth (longitude between -180 and 180, latitude between -85.05112878 and 85.05112878).

The geometry overload throws an error if the input geometry is not a POINT

Example 

SELECT ST_QuadKey(st_point(11.08, 49.45), 10);
----
1333203202

ST_ReducePrecision

Signature 

GEOMETRY ST_ReducePrecision (geom GEOMETRY, precision DOUBLE)

Description

Returns the geometry with all vertices reduced to the given precision

ST_RemoveRepeatedPoints

Signatures 

LINESTRING_2D ST_RemoveRepeatedPoints (line LINESTRING_2D)
LINESTRING_2D ST_RemoveRepeatedPoints (line LINESTRING_2D, tolerance DOUBLE)
GEOMETRY ST_RemoveRepeatedPoints (geom GEOMETRY)
GEOMETRY ST_RemoveRepeatedPoints (geom GEOMETRY, tolerance DOUBLE)

Description

Remove repeated points from a LINESTRING.

ST_Reverse

Signature 

GEOMETRY ST_Reverse (geom GEOMETRY)

Description

Returns the geometry with the order of its vertices reversed

ST_ShortestLine

Signature 

GEOMETRY ST_ShortestLine (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns the shortest line between two geometries

ST_Simplify

Signature 

GEOMETRY ST_Simplify (geom GEOMETRY, tolerance DOUBLE)

Description

Returns a simplified version of the geometry

ST_SimplifyPreserveTopology

Signature 

GEOMETRY ST_SimplifyPreserveTopology (geom GEOMETRY, tolerance DOUBLE)

Description

Returns a simplified version of the geometry that preserves topology

ST_StartPoint

Signatures 

GEOMETRY ST_StartPoint (geom GEOMETRY)
POINT_2D ST_StartPoint (line LINESTRING_2D)

Description

Returns the start point of a LINESTRING.

ST_Touches

Signature 

BOOLEAN ST_Touches (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the geometries touch

ST_Transform

Signatures 

BOX_2D ST_Transform (box BOX_2D, source_crs VARCHAR, target_crs VARCHAR)
BOX_2D ST_Transform (box BOX_2D, source_crs VARCHAR, target_crs VARCHAR, always_xy BOOLEAN)
POINT_2D ST_Transform (point POINT_2D, source_crs VARCHAR, target_crs VARCHAR)
POINT_2D ST_Transform (point POINT_2D, source_crs VARCHAR, target_crs VARCHAR, always_xy BOOLEAN)
GEOMETRY ST_Transform (geom GEOMETRY, source_crs VARCHAR, target_crs VARCHAR)
GEOMETRY ST_Transform (geom GEOMETRY, source_crs VARCHAR, target_crs VARCHAR, always_xy BOOLEAN)

Description

Transforms a geometry between two coordinate systems

The source and target coordinate systems can be specified using any format that the PROJ library supports.

The third optional always_xy parameter can be used to force the input and output geometries to be interpreted as having a [easting, northing] coordinate axis order regardless of what the source and target coordinate system definition says. This is particularly useful when transforming to/from the WGS84/EPSG:4326 coordinate system (what most people think of when they hear "longitude"/"latitude" or "GPS coordinates"), which is defined as having a [latitude, longitude] axis order even though [longitude, latitude] is commonly used in practice (e.g., in GeoJSON). More details available in the PROJ documentation.

DuckDB spatial vendors its own static copy of the PROJ database of coordinate systems, so if you have your own installation of PROJ on your system the available coordinate systems may differ to what's available in other GIS software.

Example 

-- Transform a geometry from EPSG:4326 to EPSG:3857 (WGS84 to WebMercator)
-- Note that since WGS84 is defined as having a [latitude, longitude] axis order
-- we follow the standard and provide the input geometry using that axis order,
-- but the output will be [easting, northing] because that is what's defined by
-- WebMercator.

SELECT ST_AsText(
    ST_Transform(
        st_point(52.373123, 4.892360),
        'EPSG:4326',
        'EPSG:3857'
    )
);
----
POINT (544615.0239773799 6867874.103539125)

-- Alternatively, let's say we got our input point from e.g., a GeoJSON file,
-- which uses WGS84 but with [longitude, latitude] axis order. We can use the
-- `always_xy` parameter to force the input geometry to be interpreted as having
-- a [northing, easting] axis order instead, even though the source coordinate
-- reference system definition (WGS84) says otherwise.

SELECT ST_AsText(
    ST_Transform(
        -- note the axis order is reversed here
        st_point(4.892360, 52.373123),
        'EPSG:4326',
        'EPSG:3857',
        always_xy := true
    )
);
----
POINT (544615.0239773799 6867874.103539125)

-- Transform a geometry from OSG36 British National Grid EPSG:27700 to EPSG:4326 WGS84
-- Standard transform is often fine for the first few decimal places before being wrong
-- which could result in an error starting at about 10m and possibly much more
SELECT ST_Transform(bng, 'EPSG:27700', 'EPSG:4326', xy := true) AS without_grid_file
FROM (SELECT ST_GeomFromText('POINT( 170370.718 11572.405 )') AS bng);
----
POINT (-5.202992651563592 49.96007490162923)

-- By using an official NTv2 grid file, we can reduce the error down around the 9th decimal place
-- which in theory is below a millimetre, and in practise unlikely that your coordinates are that precise
-- British National Grid "NTv2 format files" download available here:
-- https://www.ordnancesurvey.co.uk/products/os-net/for-developers
SELECT ST_Transform(bng
    , '+proj=tmerc +lat_0=49 +lon_0=-2 +k=0.9996012717 +x_0=400000 +y_0=-100000 +ellps=airy +units=m +no_defs +nadgrids=/full/path/to/OSTN15-NTv2/OSTN15_NTv2_OSGBtoETRS.gsb +type=crs'
    , 'EPSG:4326', xy := true) AS with_grid_file
FROM (SELECT ST_GeomFromText('POINT( 170370.718 11572.405 )') AS bng) t;
----
POINT (-5.203046090608746 49.96006137018598)

ST_Union

Signature 

GEOMETRY ST_Union (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns the union of two geometries

ST_VoronoiDiagram

Signature 

GEOMETRY ST_VoronoiDiagram (geom GEOMETRY)

Description

Returns the Voronoi diagram of the supplied MultiPoint geometry

ST_Within

Signatures 

BOOLEAN ST_Within (geom1 POINT_2D, geom2 POLYGON_2D)
BOOLEAN ST_Within (geom1 GEOMETRY, geom2 GEOMETRY)

Description

Returns true if the first geometry is within the second

ST_X

Signatures 

DOUBLE ST_X (geom GEOMETRY)
DOUBLE ST_X (point POINT_2D)

Description

Returns the X coordinate of a point geometry

Example 

SELECT ST_X(ST_Point(1, 2))

ST_XMax

Signatures 

DOUBLE ST_XMax (geom GEOMETRY)
DOUBLE ST_XMax (point POINT_2D)
DOUBLE ST_XMax (line LINESTRING_2D)
DOUBLE ST_XMax (polygon POLYGON_2D)
DOUBLE ST_XMax (box BOX_2D)
FLOAT ST_XMax (box BOX_2DF)

Description

Returns the maximum X coordinate of a geometry

Example 

SELECT ST_XMax(ST_Point(1, 2))

ST_XMin

Signatures 

DOUBLE ST_XMin (geom GEOMETRY)
DOUBLE ST_XMin (point POINT_2D)
DOUBLE ST_XMin (line LINESTRING_2D)
DOUBLE ST_XMin (polygon POLYGON_2D)
DOUBLE ST_XMin (box BOX_2D)
FLOAT ST_XMin (box BOX_2DF)

Description

Returns the minimum X coordinate of a geometry

Example 

SELECT ST_XMin(ST_Point(1, 2))

ST_Y

Signatures 

DOUBLE ST_Y (geom GEOMETRY)
DOUBLE ST_Y (point POINT_2D)

Description

Returns the Y coordinate of a point geometry

Example 

SELECT ST_Y(ST_Point(1, 2))

ST_YMax

Signatures 

DOUBLE ST_YMax (geom GEOMETRY)
DOUBLE ST_YMax (point POINT_2D)
DOUBLE ST_YMax (line LINESTRING_2D)
DOUBLE ST_YMax (polygon POLYGON_2D)
DOUBLE ST_YMax (box BOX_2D)
FLOAT ST_YMax (box BOX_2DF)

Description

Returns the maximum Y coordinate of a geometry

Example 

SELECT ST_YMax(ST_Point(1, 2))

ST_YMin

Signatures 

DOUBLE ST_YMin (geom GEOMETRY)
DOUBLE ST_YMin (point POINT_2D)
DOUBLE ST_YMin (line LINESTRING_2D)
DOUBLE ST_YMin (polygon POLYGON_2D)
DOUBLE ST_YMin (box BOX_2D)
FLOAT ST_YMin (box BOX_2DF)

Description

Returns the minimum Y coordinate of a geometry

Example 

SELECT ST_YMin(ST_Point(1, 2))

ST_Z

Signature 

DOUBLE ST_Z (geom GEOMETRY)

Description

Returns the Z coordinate of a point geometry

Example 

SELECT ST_Z(ST_Point(1, 2, 3))

ST_ZMFlag

Signatures 

UTINYINT ST_ZMFlag (geom GEOMETRY)
UTINYINT ST_ZMFlag (wkb WKB_BLOB)

Description

Returns a flag indicating the presence of Z and M values in the input geometry. 0 = No Z or M values 1 = M values only 2 = Z values only 3 = Z and M values

Example 

-- ZMFlag for a 2D geometry
SELECT ST_ZMFlag(ST_GeomFromText('POINT(1 1)'));
----
0

-- ZMFlag for a 3DZ geometry
SELECT ST_ZMFlag(ST_GeomFromText('POINT Z(1 1 1)'));
----
2

-- ZMFlag for a 3DM geometry
SELECT ST_ZMFlag(ST_GeomFromText('POINT M(1 1 1)'));
----
1

-- ZMFlag for a 4D geometry
SELECT ST_ZMFlag(ST_GeomFromText('POINT ZM(1 1 1 1)'));
----
3

ST_ZMax

Signature 

DOUBLE ST_ZMax (geom GEOMETRY)

Description

Returns the maximum Z coordinate of a geometry

Example 

SELECT ST_ZMax(ST_Point(1, 2, 3))

ST_ZMin

Signature 

DOUBLE ST_ZMin (geom GEOMETRY)

Description

Returns the minimum Z coordinate of a geometry

Example 

SELECT ST_ZMin(ST_Point(1, 2, 3))

Aggregate Functions

ST_CoverageInvalidEdges_Agg

Signatures 

GEOMETRY ST_CoverageInvalidEdges_Agg (col0 GEOMETRY)
GEOMETRY ST_CoverageInvalidEdges_Agg (col0 GEOMETRY, col1 DOUBLE)

Description

Returns the invalid edges of a coverage geometry

ST_CoverageSimplify_Agg

Signatures 

GEOMETRY ST_CoverageSimplify_Agg (col0 GEOMETRY, col1 DOUBLE)
GEOMETRY ST_CoverageSimplify_Agg (col0 GEOMETRY, col1 DOUBLE, col2 BOOLEAN)

Description

Simplifies a set of geometries while maintaining coverage

ST_CoverageUnion_Agg

Signature 

GEOMETRY ST_CoverageUnion_Agg (col0 GEOMETRY)

Description

Unions a set of geometries while maintaining coverage

ST_Envelope_Agg

Signature 

GEOMETRY ST_Envelope_Agg (col0 GEOMETRY)

Description

Alias for ST_Extent_Agg.

Computes the minimal-bounding-box polygon containing the set of input geometries.

Example 

SELECT ST_Extent_Agg(geom) FROM UNNEST([ST_Point(1,1), ST_Point(5,5)]) AS _(geom);
-- POLYGON ((1 1, 1 5, 5 5, 5 1, 1 1))

ST_Extent_Agg

Signature 

GEOMETRY ST_Extent_Agg (col0 GEOMETRY)

Description

Computes the minimal-bounding-box polygon containing the set of input geometries

Example 

SELECT ST_Extent_Agg(geom) FROM UNNEST([ST_Point(1,1), ST_Point(5,5)]) AS _(geom);
-- POLYGON ((1 1, 1 5, 5 5, 5 1, 1 1))

ST_Intersection_Agg

Signature 

GEOMETRY ST_Intersection_Agg (col0 GEOMETRY)

Description

Computes the intersection of a set of geometries

ST_Union_Agg

Signature 

GEOMETRY ST_Union_Agg (col0 GEOMETRY)

Description

Computes the union of a set of input geometries

Table Functions

ST_Drivers

Signature 

ST_Drivers ()

Description

Returns the list of supported GDAL drivers and file formats

Note that far from all of these drivers have been tested properly. Some may require additional options to be passed to work as expected. If you run into any issues please first consult the consult the GDAL docs.

Example 

SELECT * FROM ST_Drivers();

ST_GeneratePoints

Signature 

ST_GeneratePoints (col0 BOX_2D, col1 BIGINT)
ST_GeneratePoints (col0 BOX_2D, col1 BIGINT, col2 BIGINT)

Description

Generates a set of random points within the specified bounding box.

Takes a bounding box (min_x, min_y, max_x, max_y), a count of points to generate, and optionally a seed for the random number generator.

Example 

SELECT * FROM ST_GeneratePoints({min_x: 0, min_y:0, max_x:10, max_y:10}::BOX_2D, 5, 42);

ST_Read

Signature 

ST_Read (col0 VARCHAR, keep_wkb BOOLEAN, max_batch_size INTEGER, sequential_layer_scan BOOLEAN, layer VARCHAR, sibling_files VARCHAR[], spatial_filter WKB_BLOB, spatial_filter_box BOX_2D, allowed_drivers VARCHAR[], open_options VARCHAR[])

Description

Read and import a variety of geospatial file formats using the GDAL library.

The ST_Read table function is based on the GDAL translator library and enables reading spatial data from a variety of geospatial vector file formats as if they were DuckDB tables.

See ST_Drivers for a list of supported file formats and drivers.

Except for the path parameter, all parameters are optional.

Parameter Type Description
path VARCHAR The path to the file to read. Mandatory
sequential_layer_scan BOOLEAN If set to true, the table function will scan through all layers sequentially and return the first layer that matches the given layer name. This is required for some drivers to work properly, e.g., the OSM driver.
spatial_filter WKB_BLOB If set to a WKB blob, the table function will only return rows that intersect with the given WKB geometry. Some drivers may support efficient spatial filtering natively, in which case it will be pushed down. Otherwise the filtering is done by GDAL which may be much slower.
open_options VARCHAR[] A list of key-value pairs that are passed to the GDAL driver to control the opening of the file. E.g., the GeoJSON driver supports a FLATTEN_NESTED_ATTRIBUTES=YES option to flatten nested attributes.
layer VARCHAR The name of the layer to read from the file. If NULL, the first layer is returned. Can also be a layer index (starting at 0).
allowed_drivers VARCHAR[] A list of GDAL driver names that are allowed to be used to open the file. If empty, all drivers are allowed.
sibling_files VARCHAR[] A list of sibling files that are required to open the file. E.g., the ESRI Shapefile driver requires a .shx file to be present. Although most of the time these can be discovered automatically.
spatial_filter_box BOX_2D If set to a BOX_2D, the table function will only return rows that intersect with the given bounding box. Similar to spatial_filter.
keep_wkb BOOLEAN If set, the table function will return geometries in a wkb_geometry column with the type WKB_BLOB (which can be cast to BLOB) instead of GEOMETRY. This is useful if you want to use DuckDB with more exotic geometry subtypes that DuckDB spatial doesnt support representing in the GEOMETRY type yet.

Note that GDAL is single-threaded, so this table function will not be able to make full use of parallelism.

By using ST_Read, the spatial extension also provides “replacement scans” for common geospatial file formats, allowing you to query files of these formats as if they were tables directly.

SELECT * FROM './path/to/some/shapefile/dataset.shp';

In practice this is just syntax-sugar for calling ST_Read, so there is no difference in performance. If you want to pass additional options, you should use the ST_Read table function directly.

The following formats are currently recognized by their file extension:

Format Extension
ESRI ShapeFile .shp
GeoPackage .gpkg
FlatGeoBuf .fgb

Example 

-- Read a Shapefile
ELECT * FROM ST_Read('some/file/path/filename.shp');

- Read a GeoJSON file
REATE TABLE my_geojson_table AS SELECT * FROM ST_Read('some/file/path/filename.json');

ST_ReadOSM

Signature 

ST_ReadOSM (col0 VARCHAR)

Description

The ST_ReadOsm() table function enables reading compressed OpenStreetMap data directly from a .osm.pbf file.

This function uses multithreading and zero-copy protobuf parsing which makes it a lot faster than using the ST_Read() OSM driver, however it only outputs the raw OSM data (Nodes, Ways, Relations), without constructing any geometries. For simple node entities (like PoI's) you can trivially construct POINT geometries, but it is also possible to construct LINESTRING and POLYGON geometries by manually joining refs and nodes together in SQL, although with available memory usually being a limiting factor. The ST_ReadOSM() function also provides a "replacement scan" to enable reading from a file directly as if it were a table. This is just syntax sugar for calling ST_ReadOSM() though. Example:

SELECT * FROM 'tmp/data/germany.osm.pbf' LIMIT 5;

Example 

SELECT *
FROM ST_ReadOSM('tmp/data/germany.osm.pbf')
WHERE tags['highway'] != []
LIMIT 5;
----
┌──────────────────────┬────────┬──────────────────────┬─────────┬────────────────────┬────────────┬───────────┬────────────────────────┐
│         kind         │   id   │         tags         │  refs   │        lat         │    lon     │ ref_roles │       ref_types        │
│ enum('node', 'way'…  │ int64  │ map(varchar, varch…  │ int64[] │       double       │   double   │ varchar[] │ enum('node', 'way', …  │
├──────────────────────┼────────┼──────────────────────┼─────────┼────────────────────┼────────────┼───────────┼────────────────────────┤
│ node                 │ 122351 │ {bicycle=yes, butt…  │         │         53.5492951 │   9.977553 │           │                        │
│ node                 │ 122397 │ {crossing=no, high…  │         │ 53.520990100000006 │ 10.0156924 │           │                        │
│ node                 │ 122493 │ {TMC:cid_58:tabcd_…  │         │ 53.129614600000004 │  8.1970173 │           │                        │
│ node                 │ 123566 │ {highway=traffic_s…  │         │ 54.617268200000005 │  8.9718171 │           │                        │
│ node                 │ 125801 │ {TMC:cid_58:tabcd_…  │         │ 53.070685000000005 │  8.7819939 │           │                        │
└──────────────────────┴────────┴──────────────────────┴─────────┴────────────────────┴────────────┴───────────┴────────────────────────┘

ST_Read_Meta

Signature 

ST_Read_Meta (col0 VARCHAR)
ST_Read_Meta (col0 VARCHAR[])

Description

Read the metadata from a variety of geospatial file formats using the GDAL library.

The ST_Read_Meta table function accompanies the ST_Read table function, but instead of reading the contents of a file, this function scans the metadata instead. Since the data model of the underlying GDAL library is quite flexible, most of the interesting metadata is within the returned layers column, which is a somewhat complex nested structure of DuckDB STRUCT and LIST types.

Example 

-- Find the coordinate reference system authority name and code for the first layers first geometry column in the file
SELECT
    layers[1].geometry_fields[1].crs.auth_name as name,
    layers[1].geometry_fields[1].crs.auth_code as code
FROM st_read_meta('../../tmp/data/amsterdam_roads.fgb');
About this page