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
  • [x] ST_AsText | Alias for ST_AsWKT
  • [x] ST_AsWKT | Geometry → Well Known Text
  • [ ] ST_Force2D | 3D Geometry → 2D Geometry
  • [ ] ST_GeomFromGML | GML → Geometry
  • [x] 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

  • [x] 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

  • [x] 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 https://github.com/elastic/elasticsearch/pull/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)