如何在jOOQ中对整列执行PostGIS ST_Union操作？

问题 #

我想要对某列中的所有Geometry执行PostGIS的ST_Union操作，对应的SQL语句如下：

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

但jOOQ的stUnion实现需要传入两个参数，不清楚该如何使用它。以下是一段我期望实现功能但无法编译的Kotlin示例代码：

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()

请问我是否只能通过构造原生SQL来实现ST_Union，而非使用jOOQ内置的stUnion方法？

解决方案 #

不需要用原生SQL，jOOQ支持聚合版本的ST_Union，你可以通过以下两种方式实现：

方式1：构造聚合函数字段 #

直接用DSL.field方法生成单参数的ST_Union聚合调用，适配你的需求：

val result = context
    .select(field("ST_Union({0})", Geometry::class.java, region_geometries.REGION.cast(Geometry::class.java)))
    .from(region_geometries)
    .where(
        stContains(
            region_geometries.REGION.cast(Geometry::class.java),
            geometryPointConverter.toGeometry(point)
        )
    )
    .fetch()

方式2：使用字段的aggregate方法（新版jOOQ支持） #

如果你的jOOQ版本较新，可以直接调用字段的aggregate方法指定聚合函数名：

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

额外说明 #

jOOQ内置的双参数stUnion对应PostGIS中合并两个独立几何对象的场景，而你需要的是聚合查询结果集中所有几何对象的重载版本，这是ST_Union的两种不同用法，所以直接传单参数调用内置方法会编译失败，用上述两种方式即可在不写全量原生SQL的前提下实现需求。

内容的提问来源于stack exchange，提问作者PunDefeated