How to select points within polygon in PostGIS using jOOQ?

Question

I have a table sensor_location:

CREATE TABLE public.sensor_location (
  sensor_id INTEGER NOT NULL,
  location_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  location_point public.geometry NOT NULL,
  CONSTRAINT sensor_location_sensor_id_fkey FOREIGN KEY (sensor_id)
    REFERENCES public.sensor(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) 

I want a query which will return sensor_ids of sensors and location_times within selected polygon.

The query should look something like:

SELECT 
  sensor_id,
  location_time,
FROM 
  public.sensor_location
WHERE
  ST_Within(location_point, ST_Polygon(ST_GeomFromText('LINESTRING(-71.050316 48.422044,-71.070316 48.422044,-71.070316 48.462044,-71.050316 48.462044,-71.050316 48.422044)'), 0));

How can I do that using jOOQ? Is it even possible to use jOOQ with PostGIS? Do I have to write my own sql query and just execute it with jOOQ?

I found this but I have no idea how to use it. I'm still a novice Java programmer.

Answer 1

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.