How to write withinOP postgis extension in kotlin-exposed?

user_11010034 0
Viewed 319

this is piece of code make me able to run postgis && with geometry as input

private class WithinOp(val expr1: Expression<*>, val geom: PGgeometry) : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        expr1.toQueryBuilder(queryBuilder)
        queryBuilder.args
        queryBuilder.append(" && \'${geom.value}\'")
    }

how query looks like: SELECT * FROM table WHERE table."location" && 'SRID=4326;POLYGON((1 2,2 2,2 3,1 3,1 2))

For now it is became a problem since && is searching by minimum bounding rectangle. It is not accurate enough for my goals so I want to replace it with more accurate postgis method such as ST_Intersects

but when I updated my builder it is not working because exposed wrongly builded this query:

private class WithinOp(val expr1: Expression<*>, val geom: PGgeometry) : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        expr1.toQueryBuilder(queryBuilder)
        queryBuilder.args
        queryBuilder.append(" ST_Intersects($expr1, \'${geom.value}\'")

query: SELECT * FROM table WHERE table."location" ST_Intersects(waycare.sql.Table.location, 'SRID=4326;POLYGON((1 2,2 2,2 3,1 3,1 2)) - incorrect

correct syntax is :SELECT * FROM table WHERE ST_Intersects(table."location",'SRID=4326;POLYGON((1 2,2 2,2 3,1 3,1 2))

How to build nice extension in exposed it is not pretty documented. But I have to find some soulution. Any help are appreciated.

kotlin-exposed kotlin postgis
1 Answers

I think you need to use CustomFunction here. I didn't test code below, but it should be enough to catch the idea:

class ST_IntersectsFunction(val expr1: Expression<*>, val geom: String) 
   : CustomFunction<Boolean>("ST_Intersects", BooleanColumnType(), expr1, stringParam(geom.value))

And to use:

 table.select { ST_IntersectsFunction(table.id, geom) eq true }
user_8139810
Related