时间戳

首页 > 解决方案 > GEOGRAPHY 字段不在一个表中工作，而在另一个表中工作？

google-bigquery - GEOGRAPHY 字段不在一个表中工作，而在另一个表中工作？

问题描述

我有两个表，其中包含单个 GEOGRAPHY 列，其中包含 Polygon 的 WRT 字符串。一个表上的查询在其他表上运行良好，返回所有行而不是一个。

GEOGRAPHY 列中带有 POLYGON 的表。当使用 ST_CONTAINS 查询时，单点返回所有行而不是单行。如果我将字段内容转换为字符串，然后返回 GEO，它可以工作但非常慢。我有另一个类似的表，它在更多记录上运行得非常快。

返回所有记录的查询：

SELECT count(1) FROM pur.sections WHERE ST_CONTAINS((POLYGON), ST_GEOGPOINT(-121.253763, 38.354874))

查询哪个有效但速度慢 - 23 秒！：

SELECT polygon FROM pur.sections WHERE ST_CONTAINS(ST_GEOGFROMTEXT(ST_ASTEXT(POLYGON)), ST_GEOGPOINT(-121.253763, 38.354874))

回报：

[
  {
    "polygon": "POLYGON((-121.241934066695 38.3649672043987, -121.241834795711 38.350891363934, -121.260351761597 38.3508607773966, -121.260367162138 38.3649633106378, -121.241934066695 38.3649672043987))"
  }
]

表有 164K 行。

第一个查询返回单行非常快。

标签： google-bigquery

解决方案

我有一个合理的解释这是如何发生的。问题可能是多边形方向：

https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

如果数据是从文件加载到表中的，BigQuery 会假定多边形的方向是根据此链接中描述的规则：

如果按照输入顶点的顺序遍历多边形的边界，多边形的内部在左边

可能多边形的方向错误，因此 BigQuery 将它们解释为巨大的互补多边形，因此现在 ST_Contains 大多数时间都返回 true。

在第二个查询中，您打印多边形，然后ST_GEOGFROMTEXT不带oriented参数调用，这里 BigQuery 将 WKT 解释为描述面积较小的多边形，因此您会得到预期的结果。

您可以通过运行来修复表格（假设所有多边形都小于半球，这可能是这种情况）：

CREATE OR REPLACE pur.sections AS 
SELECT * EXCEPT(POLYGON), ST_GEOGFROMTEXT(ST_ASTEXT(POLYGON)) AS POLYGON
FROM pur.sections

之后，您应该从更简单的查询中获得预期的结果。

推荐阅读