1

I'd like to apply the PostGIS ST_Union operation on all Geometries in a column. The equivalent SQL would look something like this:

select ST_Union(region) from region_geometries
    where ST_Contains(point);

However, kOOQs implementation of stUnion takes two arguments, so I'm not sure how to utilize it. https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/spatial-functions/st-union-function/

Sample code that doesn't compile but looks like what I want to do:

val result = context
    .select(stUnion(
        region_geometries.REGION.cast(Geometry::class.java)
    ))
    .from(region_geometries)
    .where(
        stContains(
            region_geometries.REGION.cast(Geometry::class.java),
            geometryPointConverter.toGeometry(point),
        ),
    )
    .fetch()

Is my best option here to construct the raw SQL for ST_Union instead of utilizing jOOQs stUnion?

PunDefeated
  • 566
  • 5
  • 18

1 Answers1

1

As of jOOQ 3.18, there is yet little support for spatial aggregate functions, see #12736. The stUnion() function taking two arguments is not an aggregate function.

You can use the generic DSL.aggregate() function as a workaround:

aggregate("ST_Union", SQLDataType.GEOMETRY, region_geometries.REGION)

Or just use plain SQL templating, which is the raw SQL escape hatch for arbitrary expressions:

field("ST_Union({0})", SQLDataType.GEOMETRY, region_geometries.REGION)
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509