Jooq and postGIS

user_2713740 0
Viewed 139

I am trying to get Jooq working with GIS queries and found some samples on SO. I have a question regarding one of the samples. I found the following function which builds a Polygon (How to select points within polygon in PostGIS using jOOQ?). Something like:

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

I am trying to figure out how I can use this to build a polygon field from GIS coordinates (specified as doubles as a list of [latitude, longitude])

After that, if I want to select records where some column (point) lies within this polygon, how can I create my comparison operator. Would the field.in function work fine for such needs?

jooq kotlin postgis
1 Answers

You cannot use the IN predicate to check if a geometry is "in" another geometry. You ahve to use the spatial predicates for that, instead. For example:

select st_contains(
  st_makepolygon(st_makeline(array[
    st_point(0, 0), 
    st_point(0, 1), 
    st_point(1, 1), 
    st_point(1, 0), 
    st_point(0, 0)
  ])),
  st_point(0.5, 0.5)
)

Inspiration taken from this question. If you have a table containing geometries for your points, you can also aggregate the points to an array like this:

select st_contains(
  st_makepolygon(st_makeline(array_agg(points.point))), 
  st_point(0.5, 0.5)
)
from points

Built in support starting from jOOQ 3.16

Starting from jOOQ 3.16, there's built-in GIS support in the commercial editions of jOOQ. Some natively supported functions can be seen in the manual:

user_5217990
Related