jOOQ 3.16 has started adding support for spatial extensions in various SQL dialects (#982, continued as #12736). This task continues on previous work:

This task collects all related subtasks to add support for SQL spatial extensions of various vendors. This includes:

• Better support for WKT (String) and WKB (byte[]) representations (see also org.jooq.Geometry improvements #13041)
• Better Geography support (a lot of tests don't cover Geography types, yet)
• Bind variable support
  • Usage in stored procedures as IN / OUT parameters
  • Usage in Oracle UDTs via SQLInput / SQLOutput
• Function support (currently taken from PostGIS, see http://postgis.net/workshops/postgis-intro/geometries.html, http://postgis.net/workshops/postgis-intro/postgis-functions.html, check what others exist, and what good names to standardise on)
  • ST_AsEWKB
  • ST_AsEWKT
  • ST_AsGeoJSON
  • ST_AsGML
  • ST_AsKML
  • ST_AsSVG
  • ST_Buffer
  • ST_ConvexHull
  • ST_Dimension
  • ST_DistanceToPoint
  • ST_DWithin
  • ST_Envelope
  • ST_EnvelopeAsPts
  • ST_GeographyFromText
  • ST_GeogFromWKB
  • ST_GeomFromGML
  • ST_GeomFromKML
  • ST_Hexagon
  • ST_HexagonGrid
  • ST_Is3D
  • ST_M
  • ST_MakePoint
  • ST_NDims
  • ST_NRings
  • ST_NumRings
  • ST_PointAtDistance
  • ST_Points
  • ST_SRID
  • ST_Square
  • ST_SquareGrid
  • Many more, to be researched.
• Aggregate function support (to be investigated)
  • ST_ClusterIntersecting
  • ST_ClusterWithin
  • ST_Collect
  • ST_Extent
  • ST_MakeLine
  • ST_Polygonize
  • ST_Union
• Operators (see https://www.postgresql.org/docs/current/functions-geometry.html)
  • Translation (+, -)
  • Concatenation (+)
  • Scaling (*, /)
  • Length (@-@)
  • Center point (@@)
  • Number of points (#)
  • Intersection point, box (#)
  • Many more, to be researched
• Code generation support
• Dialects improvements
  • Oracle
    https://docs.oracle.com/database/121/SPATL/st_geometry-and-sdo_geometry-interoperability.htm#SPATL563
    • Improve parsing Oracle spatial operators (e.g. SDO_OVERLAPS(a, b) = 'TRUE')
  • PostgreSQL
    • Support the various built-in geometric types: https://www.postgresql.org/docs/current/datatype-geometric.html
    • Auto-recognise the PostGIS GEOMETRY type (it's a user defined type with arbitrary schema qualification, so we can't just assume it's PUBLIC.GEOMETRY or something similar). See: Support generating the PostGIS GEOMETRY type as SQLDataType.GEOMETRY #12780
• New dialects support
  • BigQuery (only geography, not geometry): https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions
  • Db2 (not installed by default)
  • EXASOL
  • H2
    • http://www.h2gis.org
  • HANA: https://help.sap.com/docs/SAP_HANA_PLATFORM/cbbbfc20871e4559abfd45a78ad58c02/7a1f0883787c101495ac9074d9bf3923.html
  • SingleStore: https://docs.singlestore.com/cloud/developer-resources/functional-extensions/working-with-geospatial-features/
  • Sybase SQL Anywhere
  • Teradata
  • Trino (doesn't work OOTB): https://trino.io/docs/current/functions/geospatial.html
  • Others (?)
• R2DBC integration
• Documentation
  • Manual
    • Aggregate functions
    • Data types (in references)
• Offer OOTB data type bindings for JTS: Offer out of the box data type bindings that bind org.jooq.Geometry to JTS Geometry #12738
• Support SVG graphics in PDF manual #12634