英文:
STUnion an entire column in jOOQ
问题
我想对列中的所有几何图形应用PostGIS ST_Union操作。等效的SQL可能如下所示：
select ST_Union(region) from region_geometries
where ST_Contains(point);
然而，kOOQs的stUnion实现需要两个参数，所以我不确定如何使用它。您可以尝试构建ST_Union的原始SQL，而不是使用jOOQ的stUnion。
英文:
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?
答案1
得分: 1
jOOQ 3.18 版本之后，对于空间聚合函数的支持仍然有限，参见 #12736。stUnion() 函数接受两个参数 并不是一个聚合函数。
您可以使用通用的 DSL.aggregate() 函数作为解决方法：
aggregate("ST_Union", SQLDataType.GEOMETRY, region_geometries.REGION)
或者只是使用纯SQL模板，这是用于任意表达式的原始SQL转义通道：
field("ST_Union({0})", SQLDataType.GEOMETRY, region_geometries.REGION)
英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论