SQL: Implement GEO functions

[matriv]

List of functions comes from: http://www.h2gis.org/docs/1.5.0/geom2D/

• Affine transformations

  • ST_Rotate | Rotate a Geometry counter-clockwise by an angle (in radians) about a point
  • ST_Scale | Scale a Geometry by multiplying the ordinates by the indicated scale factors
  • ST_Translate | Translate a Geometry

• Convert geometries

  • ST_AsBinary | Geometry → Well Known Binary
  • ST_AsGML | Geometry → GML
  • ST_AsText | Alias for ST_AsWKT
  • ST_AsWKT | Geometry → Well Known Text
  • ST_Force2D | 3D Geometry → 2D Geometry
  • ST_GeomFromGML | GML → Geometry
  • ST_GeomFromText | Well Known Text → Geometry
  • ST_GeomFromWKB | Well Known Binary → Geometry
  • ST_GeometryTypeCode | Return the OGC SFS type code of a Geometry
  • ST_GoogleMapLink | Geometry → Google map link
  • ST_Holes | Return a Geometry's holes
  • ST_LineFromText | Well Known Text → LINESTRING
  • ST_LineFromWKB | Well Known Binary → LINESTRING
  • ST_MLineFromText | Well Known Text → MULTILINESTRING
  • ST_MPointFromText | Well Known Text → MULTIPOINT
  • ST_MPolyFromText | Well Known Text → MULTIPOLYGON
  • ST_OSMMapLink | Geometry → OSM map link
  • ST_PointFromText | Well Known Text → POINT
  • ST_PointFromWKB | Well Known Binary → POINT
  • ST_PolyFromText | Well Known Text → POLYGON
  • ST_PolyFromWKB | Well Known Binary → POLYGON
  • ST_ToMultiLine | Geometry's coordinates → MULTILINESTRING
  • ST_ToMultiPoint | Geometry's coordinates → MULTIPOINT
  • ST_ToMultiSegments | GEOMETRY(COLLECTION) → MULTILINESTRING

• Create geometries

  • ST_Accum Construct an array of Geometries
  • ST_BoundingCircle Return the minimum bounding circle of a Geometry
  • ST_Collect Construct an array of Geometries
  • ST_Expand Expand a Geometry's envelope
  • ST_MakeEllipse Construct an ellipse
  • ST_MakeEnvelope Create a rectangular Polygon
  • ST_MakeGrid Calculate a regular grid of POLYGONs based on a Geometry or a table of Geometries
  • ST_MakeGridPoints Calculate a regular grid of POINTs based on a Geometry or a table of Geometries
  • ST_MakeLine (MULTI)POINT → LINESTRING
  • ST_MakePoint Construct a POINT from two or three coordinates
  • ST_MakePolygon LINESTRING → POLYGON
  • ST_MinimumDiameter Return the minimum diameter of a Geometry
  • ST_MinimumRectangle Return the minimum rectangle enclosing a Geometry
  • ST_OctogonalEnvelope Return the octogonal envelope of a Geometry
  • ST_Point Construct a POINT from two or three coordinates
  • ST_RingBuffer Return a MULTIPOLYGON of buffers centered at a Geometry and of increasing buffer Size

• Edit geometries

  • ST_AddPoint Add a point to a Geometry
  • ST_CollectionExtract Filter a Geometry with its dimension
  • ST_Densify Insert extra vertices along the line segments of a Geometry
  • ST_FlipCoordinates Flip the X and Y coordinates of a Geometry
  • ST_Normalize Return a Geometry with a normal form
  • ST_RemoveDuplicatedCoordinates Remove duplicated coordinates from a Geometry
  • ST_RemoveHoles Remove a Geometry's holes
  • ST_RemovePoints Return a Geometry with vertices less
  • ST_RemoveRepeatedPoints Remove repeated points from a Geometry
  • ST_Reverse Reverse the vertex order of a Geometry

• Measures

  • ST_Area Return a Geometry's area
  • ST_ClosestCoordinate Return the coordinate(s) of a Geometry closest to a POINT
  • ST_ClosestPoint Return the point of Geometry A closest to Geometry B
  • ST_FurthestCoordinate Compute the furthest coordinate(s) contained in a Geometry starting from a POINT
  • ST_Length Return the length of a Linestring
  • ST_LocateAlong Return a MULTIPOINT containing points along the line segments of a Geometry at a given segment length fraction and offset distance
  • ST_LongestLine Returns the 2-dimensional longest LINESTRING between the points of two geometries
  • ST_MaxDistance Compute the maximum distance between two geometries
  • ST_Perimeter Return the perimeter of a (multi)polygon
  • ST_ProjectPoint Project a POINT on a (MULTI)LINESTRING
  • ST_ShortestLine Returns the 2-dimensional shortest LINESTRING between two geometries

• Operators

  • ST_Buffer Compute a buffer around a Geometry
  • ST_ConvexHull Compute the smallest convex POLYGON that contains all the points in the Geometry
  • ST_Difference Compute the difference between two geometries
  • ST_Intersection Compute the intersection of two Geometries
  • ST_SymDifference Compute the symmetric difference between two Geometries
  • ST_Union Compute the union of two or more Geometries

• Predicates

  • ST_Contains Return true if Geometry A contains Geometry B
  • ST_Covers Return true if no point in Geometry B is outside Geometry A
  • ST_Crosses Return true if Geometry A crosses Geometry B
  • ST_DWithin Return true if the Geometries are within the specified distance of one another
  • ST_Disjoint Return true Geometries A and B are disjoint
  • ST_EnvelopesIntersect Return true if the envelope of Geometry A intersects the envelope of Geometry B
  • ST_Equals Return true if Geometry A equals Geometry B
  • ST_Intersects Return true if Geometry A intersects Geometry B
  • ST_OrderingEquals Returns TRUE if Geometry A equals Geometry B and their coordinates and component Geometries are listed in the same order
  • ST_Overlaps Return true if Geometry A overlaps Geometry B
  • ST_Relate Return the DE-9IM intersection matrix of the two Geometries or true if they are related by the given intersection matrix
  • ST_Touches Return true if Geometry A touches Geometry B
  • ST_Within Return true if Geometry A is within Geometry B

• Process geometries

  • ST_ISOVist Compute the visibility from a point
  • ST_LineIntersector Split an input LINESTRING with another geometry
  • ST_LineMerge Merges a collection of linear components to form maximal-length LINESTRING
  • ST_MakeValid Make a Geometry valid
  • ST_Node Add nodes on a geometry for each intersection
  • ST_Polygonize Create a MULTIPOLYGON from edges of Geometries
  • ST_PrecisionReducer Reduce a Geometry's precision
  • ST_RingSideBuffer Compute a ring buffer on one side
  • ST_SideBuffer Compute a single buffer on one side
  • ST_Simplify Simplify a Geometry
  • ST_SimplifyPreserveTopology Simplify a Geometry, preserving its topology
  • ST_Snap Snap two Geometries together
  • ST_Split Split Geometry A by Geometry B

• Projections

  • ST_SetSRID Return a copy of a Geometry with a new SRID
  • ST_Transform Transform a Geometry from one CRS to another

• Properties

  • ST_Boundary Return a Geometry's boundary
  • ST_Centroid Return the centroid of a Geometry
  • ST_CompactnessRatio Return the square root of a POLYGON's area divided by the area of the circle with circumference equal to its perimeter
  • ST_CoordDim Return the dimension of the coordinates of a Geometry
  • ST_Dimension Return the dimension of a Geometry
  • ST_Distance Return the distance between two Geometries
  • ST_EndPoint Return the last coordinate of a LINESTRING
  • ST_Envelope Return a Geometry's envelope as a Geometry
  • ST_Explode Explode GEOMETRYCOLLECTIONs into multiple Geometries
  • ST_Extent Return the minimum bounding box of a GEOMETRYCOLLECTION
  • ST_ExteriorRing Return the exterior ring of a POLYGON
  • ST_GeometryN Return the nth Geometry of a GEOMETRYCOLLECTION
  • ST_GeometryType Return the type of a Geometry
  • ST_InteriorRingN Return the nth interior ring of a POLYGON
  • ST_IsClosed Return true if a Geometry is a closed LINESTRING or MULTILINESTRING
  • ST_IsEmpty Return true if a Geometry is empty
  • ST_IsRectangle Return true if the Geometry is a rectangle
  • ST_IsRing Return true if a LINESTRING is ring
  • ST_IsSimple Return true if a Geometry is simple
  • ST_IsValid Return true if the Geometry is valid
  • ST_IsValidDetail Return a valid detail as an array of objects
  • ST_IsValidReason Return text stating if a geometry is valid or not and if not valid, a reason why
  • ST_NPoints Return the number of points in a Geometry
  • ST_NumGeometries Return the number of Geometries in a GEOMETRYCOLLECTION
  • ST_NumInteriorRing Alias for ST_NumInteriorRings
  • ST_NumInteriorRings Return the number of interior rings of a Geometry
  • ST_NumPoints Return the number of points in a Linestring
  • ST_PointN Return the nth point of a LINESTRING
  • ST_PointOnSurface Return an interior or boundary point of a Geometry
  • ST_SRID Return a SRID value
  • ST_StartPoint Return the first coordinate of a LINESTRING
  • ST_X Return the x-value of the first coordinate of a Geometry
  • ST_XMax Return the maximum x-value of a Geometry
  • ST_XMin Return the minimum x-value of a Geometry
  • ST_Y Return the y-value of the first coordinate of a Geometry
  • ST_YMax Return the maximum y-value of a Geometry
  • ST_YMin Return the minimum y-value of a Geometry

• Trigonometry

  • ST_Azimuth Return the azimuth of the segment from point A to point B

[elasticmachine]

Pinging @elastic/es-search (:Search/SQL)

[polyfractal]

We should probably have a discussion about which of the above functions are a blocker to making GeoSQL GA, and which we can work on after GA. E.g. it seems unlikely that we will implement all of them (potentially ever?) so need to draw a line in the sand somewhere :)

[iverase]

In #72886 we have added painless support for geo_shape field, which should unlock.a few functions. In particular you can access the centroid and bonding box of a geo field.

[timonmasberg]

Especially the Predicates would be awesome since most of them have equivalent functions in the DSL.

[Kiriakos1998]

Hello @rjernst @iverase @polyfractal @matriv, is this issue still in progress? I would like to work on implementing some geo functions in SQL.

[costin]

No, the issue is on hold at the moment.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)