Skip to content

Instantly share code, notes, and snippets.

@ncclementi
Created December 1, 2023 17:58
Show Gist options
  • Save ncclementi/fbc5564af709e2d7f8882821e3a8649f to your computer and use it in GitHub Desktop.
Save ncclementi/fbc5564af709e2d7f8882821e3a8649f to your computer and use it in GitHub Desktop.
duckdb ST ibis ops ibis + duckdb support Summary
ST_Area GeoArea 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 GeoAsText Returns the geometry as a WKT string
ST_AsWKB GeoAsBinary TODO next Returns the geometry as a WKB blob
ST_Boundary Returns the "boundary" of a geometry
ST_Buffer GeoBuffer Returns a buffer around the input geometry at the target distance
ST_Centroid GeoCentroid Returns 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 GeoContains Returns true if geom1 contains geom2.
ST_ContainsProperly GeoContainsProperly TODO next Returns true if geom1 "properly contains" geom2
ST_ConvexHul Returns the convex hull enclosing the geometry
ST_CoveredBy GeoCoveredBy Returns true if geom1 is "covered" by geom2
ST_Covers GeoCovers Returns if geom1 "covers" geom2
ST_Crosses GeoCrosses Returns true if geom1 "crosses" geom2
ST_Difference GeoDifference Returns the "difference" between two geometries
ST_Dimension Returns the dimension of a geometry.
ST_Disjoint GeoDisjoint Returns if two geometries are disjoint
ST_Distance GeoDistance Returns the 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_DWithin GeoDWithin Returns if two geometries are within a target distance of eachother
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_EndPoint GeoEndPoint Returns the end point of a line.
ST_Envelope GeoEnvelope Returns the minimum bounding box for the input geometry as a polygon geometry.
ST_Envelope_Agg Computes a minimal-bounding-box polygon 'enveloping' the set of input geometries
ST_Equals GeoEquals 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_GeometryType GeoGeometryType ❔ problem with dtype Returns a 'GEOMETRY_TYPE' enum identifying the input geometry type.
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_Intersection GeoIntersection Returns the "intersection" of geom1 and geom2
ST_Intersection_Agg Computes the intersection of a set of geometries
ST_Intersects GeoIntersects 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 GeoIsValid Returns true if the geometry is topologically "valid"
ST_Length GeoLength 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 ? GeoLineMerge UNCLEAR "Merges" the input line geometry, optionally taking direction into account.
ST_LineString2DFromWKB Deserialize a LINESTRING_2D from a WKB encoded geometry blob
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_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 GeoNPoints (sa.func.ST_NPoints) Returns the number of points within a geometry
ST_Overlaps GeoOverlaps Returns true if geom1 "overlaps" geom2
ST_Perimeter GeoPerimeter TODO next 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 GeoPoint Creates a GEOMETRY point
ST_Point2D Creates a POINT_2D
ST_Point2DFromWKB Deserialize a POINT_2D from a WKB encoded geometry blob
ST_Point3D Creates a POINT_3D
ST_Point4D Creates a POINT_4D
ST_PointN GeoPointN TODO next 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_Polygon2DFromWKB Deserialize a POLYGON_2D from a WKB encoded blob
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_Simplify GeoSimplify TODO next Simplifies the input geometry by collapsing edges smaller than 'distance'
ST_SimplifyPreserveTopology Returns a simplified geometry but avoids creating invalid topologies
ST_StartPoint GeoStartPoint Returns the first point of a line geometry
ST_Touches GeoTouches Returns true if geom1 "touches" geom2
ST_Transform ? GeoTransform ? TODO next Transforms a geometry between two coordinate systems
ST_Union GeoUnion Returns the union of two geometries.
ST_Union_Agg GeoUnaryUnion Computes the union of a set of input geometries
ST_Within GeoWithin Returns true if geom1 is "within" geom2
ST_X GeoX Returns the X coordinate of a point geometry, or NULL if not a point or empty
ST_XMax GeoXMax TODO next Returns the maximum x coordinate of a geometry
ST_XMin GeoXMin TODO next Returns the minimum x coordinate of a geometry
ST_Y GeoY Returns the y coordinate of a point geometry, or NULL if not a point or empty.
ST_YMax GeoYMax TODO next Returns the maximum y coordinate of a geometry
ST_YMin GeoYMin TODO next Returns the minimum y coordinate of a geometry
ST_Read read_geo duckdb docs link
@cboettig
Copy link

Awesome stuff. Consider st_quadkey please?

@ncclementi
Copy link
Author

@cboettig will do, I noticed that this was recently added to duckdb. Is this something you are needing or looking to have at some point?

I'd encourage you opening an issue on the repo requesting this, if this is something somehow time sensitive.

@cboettig
Copy link

thanks, will do. (Learning where best to comment, the docs point to this gist as the road map. Also noticed that not everything on this list appears on what I think are the official docs? https://ibis-project.org/reference/expression-geospatial e.g. read_geo seems pretty fundamental! but maybe I'm not completely following the process here.

@ncclementi
Copy link
Author

@cboettig That's fair, and you are right, things are a bit disorganized docs wise. We are working on a better "How-to"/Docs for the DuckDB geospatial support. The docs you are pointing to have the expressions supported but it doesn't specify which are ones are DuckDB, or Postgres, or BigQuery.

We appreciate the feedback, but as a rule of thumb any suggestions, requests, bug reports will get better attention in the Ibis repo.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment