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?
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
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: