How to select points within polygon in PostGIS using jOOQ?

Using jOOQ 3.16 out-of-the-box GIS support

Starting with jOOQ 3.16 (see #982), jOOQ will offer out-of-the-box support for the most popular GIS implementations, including PostGIS

As always with jOOQ, just translate your query to the equivalent jOOQ query:

ctx.select(SENSOR_LOCATION.SENSOR_ID, SENSOR_LOCATION.LOCATION_TIME)   .from(SENSOR_LOCATION)   .where(stWithin(       SENSOR_LOCATION.LOCATION_POINT,       // The ST_Polygon(...) wrapper isn't really needed       stGeomFromText("LINESTRING(...)", 0   ))   .fetch();

Historic answer, or when something is still missing

... then, using plain SQL will certainly do the trick. Here's one example, how to do that:

ctx.select(SENSOR_LOCATION.SENSOR_ID, SENSOR_LOCATION.LOCATION_TIME)   .from(SENSOR_LOCATION)   .where("ST_WITHIN({0}, ST_Polygon(ST_GeomFromText('...'), 0))",           SENSOR_LOCATION.LOCATION_POINT)   .fetch();

Note how you can still use some type safety by using the plain SQL templating mechanism as shown above

If you're running lots of GIS queries

In this case, you probably want to build your own API that encapsulates all the plain SQL usage. Here's an idea how to get started with that:

public static Condition stWithin(Field<?> left, Field<?> right) {    return DSL.condition("ST_WITHIN({0}, {1})", left, right);}public static Field<?> stPolygon(Field<?> geom, int value) {    return DSL.field("ST_Polygon({0}, {1})", Object.class, geom, DSL.val(value));}

If you also want to support binding GIS data types to the JDBC driver, then indeed, custom data type bindings will be the way to go:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings

You will then use your custom data types rather than the above Object.class, and you can then use Field<YourType> rather than Field<?> for additional type safety.

I found jooq-postgis-spatial spatial support: https://github.com/dmitry-zhuravlev/jooq-postgis-spatial

It allows working with geometries either using jts or postgis types.