如何使用jOOQ在PostGIS中选择多边形内的点？

Question

我有一张表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
) 

我希望有一个查询可以返回在所选多边形内的传感器的sensor_id和location_time。

查询应类似于:

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));

如何使用jOOQ实现这个功能？是否能够与PostGIS一起使用jOOQ？我是否需要编写自己的SQL查询并使用jOOQ执行它？我在这里找到了这个，但我不知道如何使用。我还是一个初学者Java程序员。

- Defozo

Answer 1

使用 jOOQ 3.16 开箱即用的 GIS 支持

从 jOOQ 3.16 开始（参见 #982），jOOQ 将提供开箱即用的支持，包括 PostGIS 在内的最流行的 GIS 实现。

像往常一样，在 jOOQ 中，只需将查询转换为相应的 jOOQ 查询：

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();

历史性答案，或者当某些东西还缺失时

...那么，使用普通 SQL肯定会奏效。以下是一个示例，说明如何做到这一点：

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();

请注意，您仍然可以通过使用上述示例中显示的普通SQL模板机制来使用一些类型安全性。

如果您正在运行大量GIS查询

在这种情况下，您可能希望构建自己的API，封装所有普通SQL用法。以下是一个开始的想法：

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));
}

如果您也想支持将GIS数据类型绑定到JDBC驱动程序，则确实，自定义数据类型绑定是正确的方法：

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

你将使用自定义数据类型而不是上面的Object.class，然后你可以使用Field<YourType>而不是Field<?>来提供额外的类型安全性。

- Lukas Eder

---

在您的示例中，如果我想从一组双精度数对创建一个stPolygon，应该如何操作？ - Luca

@Luca：你能提出一个新问题吗？ - Lukas Eder

Ender https://dev59.com/gXANtIcB2Jgan1znvmYx - Luca

Answer 2

我发现了 jooq-postgis-spatial 空间支持：https://github.com/dmitry-zhuravlev/jooq-postgis-spatial 它允许使用 jts 或 postgis 类型处理几何图形。

- oshai