如何在BigQuery中使用空间连接进行外连接，以便能够在没有交集的情况下获得计数值0？

Question

我正在做一个相当传统的点/多边形空间连接，以确定落入每个多边形中的点数。结果(来自左连接)包括所有多边形+存在交叉点的计数。但它不会在没有交集的地方返回多边形+0或null。我仍然希望保留多边形几何图形，因为它在地图上看起来更好(而不是缺少的区域)。

 WITH
  geoidandcount AS(
  SELECT
    a.geo_id,
    COUNT(b.latitude) AS count
  FROM
    `polygon_dataset` a
  LEFT OUTER JOIN -- ERROR
    `point_dataset` b
  ON
    st_contains(a.the_geom,
      ST_GEOGPOINT(b.longitude,
        b.latitude))
  GROUP BY
    a.geo_id )
SELECT
  a.*,
  b.the_geom,
  a.count,
FROM
  geoidandcount a
JOIN
  `polygon_dataset` b
USING
  (geo_id)

我不得不做CTE，因为你不能按地理位置分组。

​

​

Answer 1

是的，BigQuery还不能优化地理空间外连接，这就是为什么会出现这个错误。解决方法是对外部表中的某个唯一ID执行地理空间内连接，然后进行外连接。

为了在更简单的示例中演示，让我们以查询的这一部分为例：

  SELECT
    a.geo_id,
    b.latitude
  FROM
    `polygon_dataset` a
  LEFT OUTER JOIN -- ERROR
    `point_dataset` b
  ON
    st_contains(a.the_geom, ST_GEOGPOINT(b.longitude, b.latitude))

假设geo_id在左表中是唯一的，它可以替换为

  SELECT
    a.geo_id,
    c.latitude
  FROM
    `polygon_dataset` a
  LEFT OUTER JOIN
  (
    SELECT 
      a.geo_id
      b.latitude
    FROM
      `polygon_dataset` a
    JOIN
      `point_dataset` b
    ON
      st_contains(a.the_geom, ST_GEOGPOINT(b.longitude, b.latitude))
  ) c
  ON a.geo_id = c.geo_id

在这里，当右边没有任何匹配时，计数将为NULL，所以让我们使用COALESCE(count, 0)来获取0。

完整的查询如下所示

  SELECT
    a.geo_id,
    a.the_geom,
    COALESCE(c.count, 0) AS count
  FROM
    `polygon_dataset` a
  LEFT OUTER JOIN
  (
    SELECT 
      a.geo_id
      COUNT(b.latitude) as count
    FROM
      `polygon_dataset` a
    JOIN
      `point_dataset` b
    ON
      st_contains(a.the_geom, ST_GEOGPOINT(b.longitude, b.latitude))
    GROUP BY
      geo_id
  ) c
  ON a.geo_id = c.geo_id