主页 / dba / 问题 / 210758
Accepted
Randomize
Asked: 2018-06-28 10:21:29 +0800 CST

如何使用 Postgis 验证至少有一个点落入多边形？

  • 772

我有两个表，其中表A有一个包含曲面 (~20M) 的几何 (4326) 列。第二张表B有 ~ 500K 点 (4326)。我想要做的是“标记”（添加一个布尔列）A其中至少包含一个点的每个表面B。

我这样做了：

CREATE TABLE foo_data AS
    SELECT
        A.*,
        (SELECT EXISTS (SELECT ST_Contains(A.geom, B.geom) FROM B)) as flagged
    FROM A;

但是我得到了所有“标记”值true（这是不可能的）。

对于连接/横向，我并没有走得太远。

任何想法？

postgresql spatial

2 个回答

  1. Best Answer

    你很接近。我不会使用EXISTS，这实际上只对减少集合有用。我会去，CROSS JOIN LATERAL然后coalesce结果。看看这表现如何。

    SELECT a.id, 
  coalesce(result, false) AS flag
FROM a
CROSS JOIN LATERAL (
  SELECT true
  FROM b 
  WHERE ST_Contains(a.geom, b.geom)
  LIMIT 1
) AS r(result);

    a.geom如果您在和上有空间索引b.geom（并且您应该有针对此工作负载的索引），这应该对索引有效。这看起来也像是您只是为了提高性能而缓存结果。我建议在正常情况下 a MATERIALIZED VIEW.

    或者你应该可以去，

    SELECT 
  a.id, 
  bool_or(b.geom IS NOT NULL) AS flag
FROM a 
LEFT OUTER JOIN b
  ON ST_Contains(a.geom, b.geom)
GROUP BY a.id;
    • 2

  2. 问题是 ST_Contains 在任何情况下都会返回一个值（即如果空间关系为真/假）。相反，我会按如下方式编写查询：

       SELECT 
     a.id, 
     count(b.geom)>0 as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id

    或者：

       SELECT 
     a.id, 
     CASE WHEN count(b.geom)>0 THEN 1 ELSE 0 END as flag
   FROM tbl a 
    LEFT JOIN tbl b 
   ON ST_Contains(a.geom, b.geom)
   GROUP BY a.id
    • 0

相关问题