| Function | Summary |
|---|---|
| ST_Area | Returns 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_AsText | Returns the geometry as a WKT string |
| ST_AsWKB | Returns the geometry as a WKB blob |
| ST_Boundary | Returns the "boundary" of a geometry |
| ST_Buffer | Returns a buffer around the input geometry at the target distance |
| ST_Centroid | Calculates the centroid of a geometry |
| ST_Collect | Collects geometries into a collection geometry |
| ST_CollectionExtract | Extracts a sub-geometry from a collection geometry |
| ST_Contains | Returns true if geom1 contains geom2. |
| ST_ContainsProperly | Returns true if geom1 "properly contains" geom2 |
| ST_ConvexHull | Returns the convex hull enclosing the geometry |
| ST_CoveredBy | Returns true if geom1 is "covered" by geom2 |
| ST_Covers | Returns if 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 dimension of a geometry. |
| ST_Disjoint | Returns if two geometries are disjoint |
| ST_Distance | Returns the distance between two geometries. |
| ST_Distance_Sphere | Returns the haversine distance between two geometries. |
| ST_Distance_Spheroid | Returns the distance between two geometries in meters using a ellipsoidal model of the earths surface |
| ST_Dump | Dumps a geometry into a set of sub-geometries |
| ST_EndPoint | Returns the end point of a line. |
| ST_Envelope | Returns the minimum bounding box for the input geometry as a polygon geometry. |
| ST_Equals | Compares two geometries for equality |
| ST_Extent | Returns the minimal bounding box enclosing the input geometry |
| 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 HEXEWKB encoded string |
| ST_GeomFromHEXWKB | Creates a GEOMETRY from a HEXWKB string |
| ST_GeomFromText | Deserializes a GEOMETRY from a WKT string, optionally ignoring invalid geometries |
| ST_GeomFromWKB | Deserializes a GEOMETRY from a WKB encoded blob |
| ST_GeometryType | Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type. |
| 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 geom1 and geom2 |
| ST_Intersects | Returns true if two geometries intersects |
| ST_Intersects_Extent | Returns true if the extent of two geometries intersects |
| ST_IsClosed | Returns true if a geometry is "closed" |
| ST_IsEmpty | Returns true if the geometry is "empty" |
| ST_IsRing | Returns true if the input line geometry is a ring (both ST_IsClosed and ST_IsSimple). |
| ST_IsSimple | Returns true if the input geometry is "simple" |
| ST_IsValid | Returns true if the geometry is topologically "valid" |
| ST_Length | Returns the length of the input line geometry |
| ST_Length_Spheroid | Returns the length of the input geometry in meters, using a ellipsoidal model of the earth |
| ST_LineMerge | "Merges" the input line geometry, optionally taking direction into account. |
| ST_M | Returns the M value of a point geometry, or NULL if not a point or empty |
| ST_MMax | Returns the maximum M value of a geometry |
| ST_MMin | Returns the minimum M value of a geometry |
| ST_MakeEnvelope | Returns a minimal bounding box polygon enclosing the input geometry |
| ST_MakeLine | Creates a LINESTRING geometry from a pair or list of input points |
| ST_MakePolygon | Creates a polygon from a shell geometry and an optional set of holes |
| ST_MakeValid | Attempts to make an invalid geometry valid without removing any vertices |
| 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_Normalize | Returns a "normalized" version of the input 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 geom1 "overlaps" geom2 |
| 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_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 that is guaranteed to be on the surface of the input geometry. Sometimes a useful alternative to ST_Centroid. |
| ST_QuadKey | Computes a quadkey from a given lon/lat point. |
| ST_ReducePrecision | Returns the geometry with all vertices reduced to the target precision |
| ST_RemoveRepeatedPoints | Returns a new geometry with repeated points removed, optionally within a target distance of eachother. |
| ST_Reverse | Returns a new version of the input geometry with the order of its vertices reversed |
| ST_ShortestLine | Returns the line between the two closest points between geom1 and geom2 |
| ST_Simplify | Simplifies the input geometry by collapsing edges smaller than 'distance' |
| ST_SimplifyPreserveTopology | Returns a simplified geometry but avoids creating invalid topologies |
| ST_StartPoint | Returns the first point of a line geometry |
| ST_Touches | Returns true if geom1 "touches" geom2 |
| ST_Transform | Transforms a geometry between two coordinate systems |
| ST_Union | Returns the union of two geometries. |
| ST_Within | Returns true if geom1 is "within" geom2 |
| ST_X | Returns the X value of a point geometry, or NULL if not a point or empty |
| ST_XMax | Returns the maximum X value of a geometry |
| ST_XMin | Returns the minimum X value of a geometry |
| ST_Y | Returns the Y value of a point geometry, or NULL if not a point or empty |
| ST_YMax | Returns the maximum Y value of a geometry |
| ST_YMin | Returns the minimum Y value of a geometry |
| ST_Z | Returns the Z value of a point geometry, or NULL if not a point or empty |
| ST_ZMax | Returns the maximum Z value of a geometry |
| ST_ZMin | Returns the minimum Z value of a geometry |
| Aggregate Functions |
| Function | Summary |
|---|---|
| ST_Envelope_Agg | Computes a minimal-bounding-box polygon 'enveloping' 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_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 and the metadata from a variety of geospatial file formats using the GDAL library. |
Returns the area of a geometry.
DOUBLE ST_Area (col0 POINT_2D)
DOUBLE ST_Area (col0 LINESTRING_2D)
DOUBLE ST_Area (col0 POLYGON_2D)
DOUBLE ST_Area (col0 GEOMETRY)
DOUBLE ST_Area (col0 BOX_2D)Returns the area of a geometry.
Compute the area of a geometry.
Returns 0.0 for any geometry that is not a POLYGON, MULTIPOLYGON or GEOMETRYCOLLECTION containing polygon geometries.
The POINT_2D and LINESTRING_2D variants of this function always return 0.0 but are included for completeness.
select ST_Area('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);Returns the area of a geometry in meters, using an ellipsoidal model of the earth
DOUBLE ST_Area_Spheroid (col0 POLYGON_2D)
DOUBLE ST_Area_Spheroid (col0 GEOMETRY)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.
Returns the geometry as a GeoJSON fragment
JSON ST_AsGeoJSON (col0 GEOMETRY)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.
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]]]}Returns the geometry as a HEXWKB string
VARCHAR ST_AsHEXWKB (col0 GEOMETRY)Returns the geometry as a HEXWKB string
SELECT ST_AsHexWKB('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);Returns the geometry as a WKT string
VARCHAR ST_AsText (col0 POINT_2D)
VARCHAR ST_AsText (col0 LINESTRING_2D)
VARCHAR ST_AsText (col0 POLYGON_2D)
VARCHAR ST_AsText (col0 BOX_2D)
VARCHAR ST_AsText (col0 GEOMETRY)Returns the geometry as a WKT string
SELECT ST_AsText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);Returns the geometry as a WKB blob
WKB_BLOB ST_AsWKB (col0 GEOMETRY)Returns the geometry as a WKB blob
SELECT ST_AsWKB('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);Returns the "boundary" of a geometry
GEOMETRY ST_Boundary (col0 GEOMETRY)Returns the "boundary" of a geometry
Returns a buffer around the input geometry at the target distance
GEOMETRY ST_Buffer (col0 GEOMETRY, col1 DOUBLE)
GEOMETRY ST_Buffer (col0 GEOMETRY, col1 DOUBLE, col2 INTEGER)
GEOMETRY ST_Buffer (col0 GEOMETRY, col1 DOUBLE, col2 INTEGER, col3 VARCHAR, col4 VARCHAR, col5 DOUBLE)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.
join_style must be one of "JOIN_ROUND", "JOIN_MITRE", "JOIN_BEVEL". This parameter is case-insensitive.
cap_style must be one of "CAP_ROUND", "CAP_FLAT", "CAP_SQUARE". This parameter is case-insensitive.
mite_limit only applies when join_style is "JOIN_MITRE". It is the ratio of the distance from the corner to the miter 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.
Calculates the centroid of a geometry
POINT_2D ST_Centroid (col0 POINT_2D)
POINT_2D ST_Centroid (col0 LINESTRING_2D)
POINT_2D ST_Centroid (col0 POLYGON_2D)
POINT_2D ST_Centroid (col0 BOX_2D)
GEOMETRY ST_Centroid (col0 GEOMETRY)Calculates the centroid of a geometry
select st_centroid('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);
----
POINT(0.5 0.5)Collects geometries into a collection geometry
GEOMETRY ST_Collect (col0 GEOMETRY[])Collects geometries into a collection geometry
Collects a list of geometries into a collection geometry.
- If all geometries are
POINT's, aMULTIPOINTis returned. - If all geometries are
LINESTRING's, aMULTILINESTRINGis returned. - If all geometries are
POLYGON's, aMULTIPOLYGONis returned. - Otherwise if the input collection contains a mix of geometry types, a
GEOMETRYCOLLECTIONis returned.
Empty and NULL geometries are ignored. If all geometries are empty or NULL, a GEOMETRYCOLLECTION EMPTY is returned.
-- 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)Extracts a sub-geometry from a collection geometry
GEOMETRY ST_CollectionExtract (col0 GEOMETRY)
GEOMETRY ST_CollectionExtract (col0 GEOMETRY, col1 INTEGER)Extracts a sub-geometry from a collection geometry
select st_collectionextract('MULTIPOINT(1 2,3 4)'::geometry, 1);
-- POINT(1 2)Returns true if geom1 contains geom2.
BOOLEAN ST_Contains (col0 POLYGON_2D, col1 POINT_2D)
BOOLEAN ST_Contains (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 contains geom2.
select st_contains('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry, 'POINT(0.5 0.5)'::geometry);
----
trueReturns true if geom1 "properly contains" geom2
BOOLEAN ST_ContainsProperly (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 "properly contains" geom2
Returns the convex hull enclosing the geometry
GEOMETRY ST_ConvexHull (col0 GEOMETRY)Returns the convex hull enclosing the geometry
Returns true if geom1 is "covered" by geom2
BOOLEAN ST_CoveredBy (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 is "covered" by geom2
Returns if geom1 "covers" geom2
BOOLEAN ST_Covers (col0 GEOMETRY, col1 GEOMETRY)Returns if geom1 "covers" geom2
Returns true if geom1 "crosses" geom2
BOOLEAN ST_Crosses (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 "crosses" geom2
Returns if two geometries are within a target distance of each-other
BOOLEAN ST_DWithin (col0 GEOMETRY, col1 GEOMETRY, col2 DOUBLE)Returns if two geometries are within a target distance of each-other
Returns if two POINT_2D's are within a target distance in meters, using an ellipsoidal model of the earths surface
DOUBLE ST_DWithin_Spheroid (col0 POINT_2D, col1 POINT_2D, col2 DOUBLE)Returns if two POINT_2D's are within a target distance in meters, using an ellipsoidal model of the earths surface
Returns the "difference" between two geometries
GEOMETRY ST_Difference (col0 GEOMETRY, col1 GEOMETRY)Returns the "difference" between two geometries
Returns the dimension of a geometry.
INTEGER ST_Dimension (col0 GEOMETRY)Returns the dimension of a geometry.
select st_dimension('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'::geometry);
----
2Returns if two geometries are disjoint
BOOLEAN ST_Disjoint (col0 GEOMETRY, col1 GEOMETRY)Returns if two geometries are disjoint
Returns the distance between two geometries.
DOUBLE ST_Distance (col0 POINT_2D, col1 POINT_2D)
DOUBLE ST_Distance (col0 POINT_2D, col1 LINESTRING_2D)
DOUBLE ST_Distance (col0 LINESTRING_2D, col1 POINT_2D)
DOUBLE ST_Distance (col0 GEOMETRY, col1 GEOMETRY)Returns the distance between two geometries.
select st_distance('POINT(0 0)'::geometry, 'POINT(1 1)'::geometry);
----
1.4142135623731Returns the haversine distance between two geometries.
DOUBLE ST_Distance_Sphere (col0 POINT_2D, col1 POINT_2D)
DOUBLE ST_Distance_Sphere (col0 GEOMETRY, col1 GEOMETRY)Returns the haversine 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.
Returns the distance between two geometries in meters using a ellipsoidal model of the earths surface
DOUBLE ST_Distance_Spheroid (col0 POINT_2D, col1 POINT_2D)Returns the distance between two geometries in meters using a 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.
-- 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)
);
----
5243187.666873225
-- Roughly 5243km!Dumps a geometry into a set of sub-geometries
STRUCT(geom GEOMETRY, path INTEGER[])[] ST_Dump (col0 GEOMETRY)Dumps a geometry into a set of sub-geometries
Dumps a geometry into a set of sub-geometries and their "path" in the original geometry.
select st_dump('MULTIPOINT(1 2,3 4)'::geometry);
----
[{'geom': 'POINT(1 2)', 'path': [0]}, {'geom': 'POINT(3 4)', 'path': [1]}]Returns the end point of a line.
GEOMETRY ST_EndPoint (col0 GEOMETRY)
POINT_2D ST_EndPoint (col0 LINESTRING_2D)Returns the end point of a line.
select st_endpoint('LINESTRING(0 0, 1 1)'::geometry);
-- POINT(1 1)Returns the minimum bounding box for the input geometry as a polygon geometry.
GEOMETRY ST_Envelope (col0 GEOMETRY)Returns the minimum bounding box for the input geometry as a polygon geometry.
Compares two geometries for equality
BOOLEAN ST_Equals (col0 GEOMETRY, col1 GEOMETRY)Compares two geometries for equality
Returns the minimal bounding box enclosing the input geometry
BOX_2D ST_Extent (col0 GEOMETRY)Returns the minimal bounding box enclosing the input geometry
Returns the exterior ring (shell) of a polygon geometry.
LINESTRING_2D ST_ExteriorRing (col0 POLYGON_2D)
GEOMETRY ST_ExteriorRing (col0 GEOMETRY)Returns the exterior ring (shell) of a polygon geometry.
Returns a new geometry with the coordinates of the input geometry "flipped" so that x = y and y = x.
POINT_2D ST_FlipCoordinates (col0 POINT_2D)
LINESTRING_2D ST_FlipCoordinates (col0 LINESTRING_2D)
POLYGON_2D ST_FlipCoordinates (col0 POLYGON_2D)
BOX_2D ST_FlipCoordinates (col0 BOX_2D)
GEOMETRY ST_FlipCoordinates (col0 GEOMETRY)Returns a new geometry with the coordinates of the input geometry "flipped" so that x = y and y = x.
Forces the vertices of a geometry to have X and Y components
GEOMETRY ST_Force2D (col0 GEOMETRY)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.
Forces the vertices of a geometry to have X, Y and M components
GEOMETRY ST_Force3DM (col0 GEOMETRY, col1 DOUBLE)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.
Forces the vertices of a geometry to have X, Y and Z components
GEOMETRY ST_Force3DZ (col0 GEOMETRY, col1 DOUBLE)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.
Forces the vertices of a geometry to have X, Y, Z and M components
GEOMETRY ST_Force4D (col0 GEOMETRY, col1 DOUBLE, col2 DOUBLE)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.
Deserializes a GEOMETRY from a GeoJSON fragment.
GEOMETRY ST_GeomFromGeoJSON (col0 VARCHAR)
GEOMETRY ST_GeomFromGeoJSON (col0 JSON)Deserializes a GEOMETRY from a GeoJSON fragment.
Deserialize a GEOMETRY from a HEXEWKB encoded string
GEOMETRY ST_GeomFromHEXEWKB (col0 VARCHAR)Deserialize a GEOMETRY from a HEXEWKB encoded string
Creates a GEOMETRY from a HEXWKB string
GEOMETRY ST_GeomFromHEXWKB (col0 VARCHAR)Creates a GEOMETRY from a HEXWKB string
Deserializes a GEOMETRY from a WKT string, optionally ignoring invalid geometries
GEOMETRY ST_GeomFromText (col0 VARCHAR)
GEOMETRY ST_GeomFromText (col0 VARCHAR, col1 BOOLEAN)Deserializes a GEOMETRY from a WKT string, optionally ignoring invalid geometries
Deserializes a GEOMETRY from a WKB encoded blob
GEOMETRY ST_GeomFromWKB (col0 WKB_BLOB)
GEOMETRY ST_GeomFromWKB (col0 BLOB)Deserializes a GEOMETRY from a WKB encoded blob
Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type.
ANY ST_GeometryType (col0 POINT_2D)
ANY ST_GeometryType (col0 LINESTRING_2D)
ANY ST_GeometryType (col0 POLYGON_2D)
ANY ST_GeometryType (col0 GEOMETRY)Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type.
Encodes the X and Y values as the hilbert curve index for a curve covering the given bounding box
UINTEGER ST_Hilbert (col0 DOUBLE, col1 DOUBLE, col2 BOX_2D)Encodes the X and Y values as the hilbert curve index for a curve covering the given bounding box
Returns the "intersection" of geom1 and geom2
GEOMETRY ST_Intersection (col0 GEOMETRY, col1 GEOMETRY)Returns the "intersection" of geom1 and geom2
Returns true if two geometries intersects
BOOLEAN ST_Intersects (col0 BOX_2D, col1 BOX_2D)
BOOLEAN ST_Intersects (col0 GEOMETRY, col1 GEOMETRY)Returns true if two geometries intersects
Returns true if the extent of two geometries intersects
BOOLEAN ST_Intersects_Extent (col0 GEOMETRY, col1 GEOMETRY)Returns true if the extent of two geometries intersects
Returns true if a geometry is "closed"
BOOLEAN ST_IsClosed (col0 GEOMETRY)Returns true if a geometry is "closed"
Returns true if the geometry is "empty"
BOOLEAN ST_IsEmpty (col0 LINESTRING_2D)
BOOLEAN ST_IsEmpty (col0 POLYGON_2D)
BOOLEAN ST_IsEmpty (col0 GEOMETRY)Returns true if the geometry is "empty"
Returns true if the input line geometry is a ring (both ST_IsClosed and ST_IsSimple).
BOOLEAN ST_IsRing (col0 GEOMETRY)Returns true if the input line geometry is a ring (both ST_IsClosed and ST_IsSimple).
Returns true if the input geometry is "simple"
BOOLEAN ST_IsSimple (col0 GEOMETRY)Returns true if the input geometry is "simple"
Returns true if the geometry is topologically "valid"
BOOLEAN ST_IsValid (col0 GEOMETRY)Returns true if the geometry is topologically "valid"
Returns the length of the input line geometry
DOUBLE ST_Length (col0 LINESTRING_2D)
DOUBLE ST_Length (col0 GEOMETRY)Returns the length of the input line geometry
Returns the length of the input geometry in meters, using a ellipsoidal model of the earth
DOUBLE ST_Length_Spheroid (col0 LINESTRING_2D)
DOUBLE ST_Length_Spheroid (col0 GEOMETRY)Returns the length of the input geometry in meters, using a 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 square 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.
"Merges" the input line geometry, optionally taking direction into account.
GEOMETRY ST_LineMerge (col0 GEOMETRY)
GEOMETRY ST_LineMerge (col0 GEOMETRY, col1 BOOLEAN)"Merges" the input line geometry, optionally taking direction into account.
Returns the M value of a point geometry, or NULL if not a point or empty
DOUBLE ST_M (col0 GEOMETRY)Returns the M value of a point geometry, or NULL if not a point or empty
Returns the maximum M value of a geometry
DOUBLE ST_MMax (col0 GEOMETRY)Returns the maximum M value of a geometry
Returns the minimum M value of a geometry
DOUBLE ST_MMin (col0 GEOMETRY)Returns the minimum M value of a geometry
Returns a minimal bounding box polygon enclosing the input geometry
GEOMETRY ST_MakeEnvelope (col0 DOUBLE, col1 DOUBLE, col2 DOUBLE, col3 DOUBLE)Returns a minimal bounding box polygon enclosing the input geometry
Creates a LINESTRING geometry from a pair or list of input points
GEOMETRY ST_MakeLine (col0 GEOMETRY[])
GEOMETRY ST_MakeLine (col0 GEOMETRY, col1 GEOMETRY)Creates a LINESTRING geometry from a pair or list of input points
Creates a polygon from a shell geometry and an optional set of holes
GEOMETRY ST_MakePolygon (col0 GEOMETRY, col1 GEOMETRY[])
GEOMETRY ST_MakePolygon (col0 GEOMETRY)Creates a polygon from a shell geometry and an optional set of holes
Attempts to make an invalid geometry valid without removing any vertices
GEOMETRY ST_MakeValid (col0 GEOMETRY)Attempts to make an invalid geometry valid without removing any vertices
Returns the number of component geometries in a collection geometry
INTEGER ST_NGeometries (col0 GEOMETRY)Returns the number of component geometries in a collection geometry If the input geometry is not a collection, returns 1 if the geometry is not empty, otherwise 0
Returns the number if interior rings of a polygon
INTEGER ST_NInteriorRings (col0 POLYGON_2D)
INTEGER ST_NInteriorRings (col0 GEOMETRY)Returns the number if interior rings of a polygon
Returns the number of vertices within a geometry
UBIGINT ST_NPoints (col0 POINT_2D)
UBIGINT ST_NPoints (col0 LINESTRING_2D)
UBIGINT ST_NPoints (col0 POLYGON_2D)
UBIGINT ST_NPoints (col0 BOX_2D)
UINTEGER ST_NPoints (col0 GEOMETRY)Returns the number of vertices within a geometry
Returns a "normalized" version of the input geometry.
GEOMETRY ST_Normalize (col0 GEOMETRY)Returns a "normalized" version of the input geometry.
Returns the number of component geometries in a collection geometry
INTEGER ST_NumGeometries (col0 GEOMETRY)Returns the number of component geometries in a collection geometry If the input geometry is not a collection, returns 1 if the geometry is not empty, otherwise 0
Returns the number if interior rings of a polygon
INTEGER ST_NumInteriorRings (col0 POLYGON_2D)
INTEGER ST_NumInteriorRings (col0 GEOMETRY)Returns the number if interior rings of a polygon
Returns the number of vertices within a geometry
UBIGINT ST_NumPoints (col0 POINT_2D)
UBIGINT ST_NumPoints (col0 LINESTRING_2D)
UBIGINT ST_NumPoints (col0 POLYGON_2D)
UBIGINT ST_NumPoints (col0 BOX_2D)
UINTEGER ST_NumPoints (col0 GEOMETRY)Returns the number of vertices within a geometry
Returns true if geom1 "overlaps" geom2
BOOLEAN ST_Overlaps (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 "overlaps" geom2
Returns the length of the perimeter of the geometry
DOUBLE ST_Perimeter (col0 BOX_2D)
DOUBLE ST_Perimeter (col0 POLYGON_2D)
DOUBLE ST_Perimeter (col0 GEOMETRY)Returns the length of the perimeter of the geometry
Returns the length of the perimeter in meters using an ellipsoidal model of the earths surface
DOUBLE ST_Perimeter_Spheroid (col0 POLYGON_2D)
DOUBLE ST_Perimeter_Spheroid (col0 GEOMETRY)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.
Creates a GEOMETRY point
GEOMETRY ST_Point (col0 DOUBLE, col1 DOUBLE)Creates a GEOMETRY point
Creates a POINT_2D
POINT_2D ST_Point2D (col0 DOUBLE, col1 DOUBLE)Creates a POINT_2D
Creates a POINT_3D
POINT_3D ST_Point3D (col0 DOUBLE, col1 DOUBLE, col2 DOUBLE)Creates a POINT_3D
Creates a POINT_4D
POINT_4D ST_Point4D (col0 DOUBLE, col1 DOUBLE, col2 DOUBLE, col3 DOUBLE)Creates a POINT_4D
Returns the n'th vertex from the input geometry as a point geometry
GEOMETRY ST_PointN (col0 GEOMETRY, col1 INTEGER)
POINT_2D ST_PointN (col0 LINESTRING_2D, col1 INTEGER)Returns the n'th vertex from the input geometry as a point geometry
Returns a point that is guaranteed to be on the surface of the input geometry. Sometimes a useful alternative to ST_Centroid.
GEOMETRY ST_PointOnSurface (col0 GEOMETRY)Returns a point that is guaranteed to be on the surface of the input geometry. Sometimes a useful alternative to ST_Centroid.
Computes a quadkey from a given lon/lat point.
VARCHAR ST_QuadKey (col0 DOUBLE, col1 DOUBLE, col2 INTEGER)
VARCHAR ST_QuadKey (col0 GEOMETRY, col1 INTEGER)Computes a quadkey from a given lon/lat point.
Compute the quadkey for a given lon/lat point at a given level. Note that the 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).
Throws for any geometry that is not a POINT
SELECT ST_QuadKey(st_point(11.08, 49.45), 10);
----
1333203202Returns the geometry with all vertices reduced to the target precision
GEOMETRY ST_ReducePrecision (col0 GEOMETRY, col1 DOUBLE)Returns the geometry with all vertices reduced to the target precision
Returns a new geometry with repeated points removed, optionally within a target distance of eachother.
LINESTRING_2D ST_RemoveRepeatedPoints (col0 LINESTRING_2D)
LINESTRING_2D ST_RemoveRepeatedPoints (col0 LINESTRING_2D, col1 DOUBLE)
GEOMETRY ST_RemoveRepeatedPoints (col0 GEOMETRY)
GEOMETRY ST_RemoveRepeatedPoints (col0 GEOMETRY, col1 DOUBLE)Returns a new geometry with repeated points removed, optionally within a target distance of eachother.
Returns a new version of the input geometry with the order of its vertices reversed
GEOMETRY ST_Reverse (col0 GEOMETRY)Returns a new version of the input geometry with the order of its vertices reversed
Returns the line between the two closest points between geom1 and geom2
GEOMETRY ST_ShortestLine (col0 GEOMETRY, col1 GEOMETRY)Returns the line between the two closest points between geom1 and geom2
Simplifies the input geometry by collapsing edges smaller than 'distance'
GEOMETRY ST_Simplify (col0 GEOMETRY, col1 DOUBLE)Simplifies the input geometry by collapsing edges smaller than 'distance'
Returns a simplified geometry but avoids creating invalid topologies
GEOMETRY ST_SimplifyPreserveTopology (col0 GEOMETRY, col1 DOUBLE)Returns a simplified geometry but avoids creating invalid topologies
Returns the first point of a line geometry
GEOMETRY ST_StartPoint (col0 GEOMETRY)
POINT_2D ST_StartPoint (col0 LINESTRING_2D)Returns the first point of a line geometry
Returns true if geom1 "touches" geom2
BOOLEAN ST_Touches (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 "touches" geom2
Transforms a geometry between two coordinate systems
BOX_2D ST_Transform (col0 BOX_2D, col1 VARCHAR, col2 VARCHAR)
BOX_2D ST_Transform (col0 BOX_2D, col1 VARCHAR, col2 VARCHAR, col3 BOOLEAN)
POINT_2D ST_Transform (col0 POINT_2D, col1 VARCHAR, col2 VARCHAR)
POINT_2D ST_Transform (col0 POINT_2D, col1 VARCHAR, col2 VARCHAR, col3 BOOLEAN)
GEOMETRY ST_Transform (col0 GEOMETRY, col1 VARCHAR, col2 VARCHAR)
GEOMETRY ST_Transform (col0 GEOMETRY, col1 VARCHAR, col2 VARCHAR, col3 BOOLEAN)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 optional always_xy parameter can be used to force the input and output geometries to be interpreted as having a [northing, easting] 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.
-- 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 [northing, easting] 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
-- system definition 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)Returns the union of two geometries.
GEOMETRY ST_Union (col0 GEOMETRY, col1 GEOMETRY)Returns the union of two geometries.
SELECT ST_AsText(
ST_Union(
ST_GeomFromText('POINT(1 2)'),
ST_GeomFromText('POINT(3 4)')
)
);
----
MULTIPOINT (1 2, 3 4)Returns true if geom1 is "within" geom2
BOOLEAN ST_Within (col0 POINT_2D, col1 POLYGON_2D)
BOOLEAN ST_Within (col0 GEOMETRY, col1 GEOMETRY)Returns true if geom1 is "within" geom2
Returns the X value of a point geometry, or NULL if not a point or empty
DOUBLE ST_X (col0 POINT_2D)
DOUBLE ST_X (col0 GEOMETRY)Returns the X value of a point geometry, or NULL if not a point or empty
Returns the maximum X value of a geometry
DOUBLE ST_XMax (col0 BOX_2D)
DOUBLE ST_XMax (col0 POINT_2D)
DOUBLE ST_XMax (col0 LINESTRING_2D)
DOUBLE ST_XMax (col0 POLYGON_2D)
DOUBLE ST_XMax (col0 GEOMETRY)Returns the maximum X value of a geometry
Returns the minimum X value of a geometry
DOUBLE ST_XMin (col0 BOX_2D)
DOUBLE ST_XMin (col0 POINT_2D)
DOUBLE ST_XMin (col0 LINESTRING_2D)
DOUBLE ST_XMin (col0 POLYGON_2D)
DOUBLE ST_XMin (col0 GEOMETRY)Returns the minimum X value of a geometry
Returns the Y value of a point geometry, or NULL if not a point or empty
DOUBLE ST_Y (col0 POINT_2D)
DOUBLE ST_Y (col0 GEOMETRY)Returns the Y value of a point geometry, or NULL if not a point or empty
Returns the maximum Y value of a geometry
DOUBLE ST_YMax (col0 BOX_2D)
DOUBLE ST_YMax (col0 POINT_2D)
DOUBLE ST_YMax (col0 LINESTRING_2D)
DOUBLE ST_YMax (col0 POLYGON_2D)
DOUBLE ST_YMax (col0 GEOMETRY)Returns the maximum Y value of a geometry
Returns the minimum Y value of a geometry
DOUBLE ST_YMin (col0 BOX_2D)
DOUBLE ST_YMin (col0 POINT_2D)
DOUBLE ST_YMin (col0 LINESTRING_2D)
DOUBLE ST_YMin (col0 POLYGON_2D)
DOUBLE ST_YMin (col0 GEOMETRY)Returns the minimum Y value of a geometry
Returns the Z value of a point geometry, or NULL if not a point or empty
DOUBLE ST_Z (col0 GEOMETRY)Returns the Z value of a point geometry, or NULL if not a point or empty
Returns the maximum Z value of a geometry
DOUBLE ST_ZMax (col0 GEOMETRY)Returns the maximum Z value of a geometry
Returns the minimum Z value of a geometry
DOUBLE ST_ZMin (col0 GEOMETRY)Returns the minimum Z value of a geometry
Computes a minimal-bounding-box polygon 'enveloping' the set of input geometries
GEOMETRY ST_Envelope_Agg (col0 GEOMETRY)Computes a minimal-bounding-box polygon 'enveloping' the set of input geometries
Computes the intersection of a set of geometries
GEOMETRY ST_Intersection_Agg (col0 GEOMETRY)Computes the intersection of a set of geometries
Computes the union of a set of input geometries
GEOMETRY ST_Union_Agg (col0 GEOMETRY)Computes the union of a set of input geometries
Returns the list of supported GDAL drivers and file formats
ST_Drivers ()Returns the list of supported GDAL drivers and file formats
Note that far from all of these drivers have been tested properly, and 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.
SELECT * FROM ST_Drivers();Read and import a variety of geospatial file formats using the GDAL library.
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[])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 |
-- Read a Shapefile
SELECT * FROM ST_Read('some/file/path/filename.shp');
-- Read a GeoJSON file
CREATE TABLE my_geojson_table AS SELECT * FROM ST_Read('some/file/path/filename.json');The ST_ReadOsm() table function enables reading compressed OpenStreetMap data directly from a .osm.pbf file.
ST_ReadOSM (col0 VARCHAR)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;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 │ │ │
└──────────────────────┴────────┴──────────────────────┴─────────┴────────────────────┴────────────┴───────────┴────────────────────────┘Read and the metadata from a variety of geospatial file formats using the GDAL library.
ST_Read_Meta (col0 VARCHAR)
ST_Read_Meta (col0 VARCHAR[])Read and 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.
-- 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');