Postgis查询ST_Intersects与GeoJSON（jsonb）

Question

假设有一个PostgreSQL 9.4 jsonb列'location'，其内容如下：

{"type": "FeatureCollection","features": [
  {
    "properties": {},
    "geom": "01030000000100000004000000000000000000244000000000000049400000000000002840000000000000494000000000000026400000000000004A4000000000000024400000000000004940",
    "type": "Feature",
    "geometry": {"type": "Polygon", "coordinates": [[[10,50],[12,50],[11,52],[10,50]]]}             
  },
  {..},
  {..}
]}

为了检索特征键“geom”，我使用以下查询：

SELECT geom
FROM (
  SELECT jsonb_array_elements(t.location -> 'features') -> 'geom' AS geom
  FROM my_object t) AS g
WHERE geom IS NOT NULL;

可以工作。但现在我想执行一个Postgis的ST_Intersects查询，如下：

SELECT ST_Intersects(g.geom, ST_GeomFromText('POINT(11 51)'))
FROM (
  SELECT t.iid, (jsonb_array_elements(t.location -> 'features') -> 'geom') AS geom
  FROM my_object t) AS g;

无法工作，因为g.geom以jsonb形式传递：function st_intersects(jsonb, geometry) does not exist。我试着将其转换为text，然后出现错误：function st_intersects(text, geometry) is not unique。我该如何处理将jsonb结果作为Postgis函数输入的情况？

- Rainer

Answer 1

首先，函数jsonb_array_elements()返回一个集合，因此您应该将其作为FROM子句中的表函数使用。 其次，您应该使用->>运算符获取jsonb字段的文本表示，然后将其转换为geometry，然后在PostGIS函数中使用它。

要检索不是NULL的几何图形：

SELECT f.value ->> 'geom' AS geom
FROM my_object t
JOIN LATERAL jsonb_array_elements(t.location -> 'features') f ON true
WHERE f.value -> 'geom' IS NOT NULL;

执行交集操作：

SELECT t.iid, ST_Intersects((f.value->>'geom')::geometry, ST_GeomFromText('POINT(11 51)'))
FROM my_object t
JOIN LATERAL jsonb_array_elements(t.location -> 'features') f ON true;

您可能希望向选择列表中添加一些属性，以便区分来自表中每行的多个几何图形。

- Patrick

---

完美，谢谢！一个小问题：似乎没有LATERAL关键字也可以正常工作，是吗？ - Rainer

使用表函数时，关键字“LATERAL”确实是可选的，正如我所参考的文档中所描述的那样。我在这里包含它是为了“教育目的”。 - Patrick

Answer 2

要从GeoJSON创建一个PostGIS几何类型，您可以使用函数ST_GeomFromGeoJSON。

例如：

SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}');

- Francisco Puga

---

是的，我知道这个选项。但我想使用预先计算好的“geom”作为输入，而不是GeoJSON对象。我希望避免这种额外的转换（geomfromgeojson）以加快查询速度。 - Rainer

然后你可以尝试使用ST_GeomFromWKB。但是，如果你想避免额外的转换，我认为最好在你的表中创建一个新的几何列。 - Francisco Puga

很遗憾，它不起作用：函数st_geomfromwkb（jsonb）不存在。 - Rainer