如何在BigQuery中捕获ST_MAKEPOLYGON错误 [英] How to Catch ST_MAKEPOLYGON Error in BigQuery

问题描述

我在BigQuery中使用ST_MAKEPOLYGON函数，如下所示:

I am using ST_MAKEPOLYGON function in BigQuery as follows:

  with data AS (
  SELECT
    61680 AS id, 139.74862575531006 AS lon,
    35.674973127377314 AS lat union all
  SELECT
    61680,
    139.75087881088257,
    35.673909836018375 union all
  SELECT
    61680,
    139.747037887573,
    35.6765767531247 union all
  SELECT
    61680,
    139.75308895111,
    35.6813525780394 union all
  SELECT
    61680,
    139.747509956359,
    35.6798884869144 union all
  SELECT
    61680,
    139.754590988159,
    35.6799930657428 union all
  SELECT
    61680,
    139.754977226257,
    35.6762281415729 union all
  SELECT
    61680,
    139.750170707702,
    35.6815268728124 union all
  SELECT
    61680,
    139.755363464355,
    35.6782500673754
    )
SELECT
  ST_makepolygon(ST_MAKELINE(ARRAY_AGG(st_geogpoint(lon,
          lat)))) AS valid
FROM
  `w_nagakawa.geo_test`
GROUP BY
  id

出现类似以下错误:

Error: ST_MakePolygon failed: Invalid polygon loop: Edge 3 has duplicate vertex with edge 10

ST_MAKEPOLYGON中的地理参数可以，而且所有纬度似乎都不同.

Geography argument inside ST_MAKEPOLYGON is okay, and all lat-lon seems to be different.

我想知道为什么会发生这种情况，并想知道一些解决办法.

I'd like to know why it happens and would like to know some ideas to solve this.

谢谢.

推荐答案

第一个问题首先出现……

Frist question first …

我想知道为什么会发生……

I'd like to know why it happens …

将ST_MAKEPOLYGON与线串输入一起使用(通过ST_MAKELINE)要求正确地组装线，这样就不会与数据发生交集(因为线是使用出现的[random]顺序的点构建的)

Using ST_MAKEPOLYGON with linestring input (via ST_MAKELINE) requires line to be properly assembled such that there is no intersections which happens with your data (as line is built using points in the [random] order of appearance)

相反，您需要在蓝色中使用如下所示的线-在其中所有地理点都进行了排序，以使它们形成非自交叉线

Instead, you would need line like below in blue - where all geo-points ordered such that they form non-self-crossed line

注意:必须关闭线串:也就是说，第一个和最后一个顶点必须相同.如果第一个和最后一个顶点不同，则该函数将构造从第一个顶点到最后一个顶点的最终边缘.

使用"proper_line"构建多边形会完美地工作并产生以下结果

Building Polygon using the "proper_line" will perfectly work and produce below result

第二个问题……

...，并且想知道一些解决此问题的方法

… and would like to know some ideas to solve this

因此，显然，我们需要以某种方式正确地订购地理点
可以手动完成(使用此选项很有趣)，也可以通过编程方式完成
下面是在BigQuery(Standard SQL)中如何执行此操作的想法以及实现的详细信息

So, obviously, we need somehow properly order geo-points
This can be done manually (have fun with this option) or can be done programmatically
Below is idea of how to do so within BigQuery (Standard SQL) along with details of implementation

因此，我们要通过以下步骤为每个点分配适当的序列号:

So, we want to assign proper sequence number to each point by following below steps:

步骤1 –让我们确定所有点(红色引脚)的质心(下图中的绿色引脚)

Step 1 – let’s identify centroid (green pin in below image) for all the points (red pins)

我们可以使用以下语句:

We can use below statement for this:

SELECT ST_CENTROID(ST_UNION_AGG(ST_GEOGPOINT(lon, lat))) centroid FROM `data`

第2步-然后，对于每个点，我们应该计算质心点线与水平线相交质心之间的角度
我们正在使用锚点(图像上的蓝色圆圈)

Step 2 - Then, for each point we should calculate angle between centroid-to-point line and horizontal line crossing centroid
We are using anchors (blue circles on the image)

WITH stats AS (
  SELECT ST_CENTROID(ST_UNION_AGG(ST_GEOGPOINT(lon, lat))) centroid FROM `data`
)
SELECT point, centroid, anchor,
  ACOS(ST_DISTANCE(centroid, anchor) / ST_DISTANCE(centroid, point)) angle
FROM (
  SELECT centroid, 
    ST_GEOGPOINT(lon, lat) point, 
    ST_GEOGPOINT(lon, ST_Y(centroid)) anchor
  FROM `data`, stats
)

第3步-现在，我们要将这些角度转换为反映各个点的象限的适当顺序

Step 3 - Now we want to convert those angles into proper sequence reflecting respective points’ quadrants

SELECT point, centroid, anchor,
  CASE 
    WHEN ST_X(point) > ST_X(centroid) AND ST_Y(point) > ST_Y(centroid) THEN 3.14 - angle
    WHEN ST_X(point) > ST_X(centroid) AND ST_Y(point) < ST_Y(centroid) THEN 3.14 + angle
    WHEN ST_X(point) < ST_X(centroid) AND ST_Y(point) < ST_Y(centroid) THEN 6.28 - angle
    ELSE angle
  END sequence
FROM (.. previous subquery here …)

第4步-现在，最后，我们可以使用序列列对点进行正确排序 最终查询如下:

Step 4 - So now, finally we can use sequence column to properly order points Final query below:

WITH `data` AS (
    SELECT 61680 AS id, 139.74862575531006 AS lon, 35.674973127377314 AS lat UNION ALL SELECT 61680, 139.75087881088257, 35.673909836018375 UNION ALL SELECT 61680, 139.747037887573, 35.6765767531247 UNION ALL SELECT 61680, 139.75308895111, 35.6813525780394 UNION ALL SELECT 61680, 139.747509956359, 35.6798884869144 UNION ALL SELECT 61680, 139.754590988159, 35.6799930657428 UNION ALL SELECT 61680, 139.754977226257, 35.6762281415729 UNION ALL SELECT 61680, 139.750170707702, 35.6815268728124 UNION ALL SELECT 61680, 139.755363464355, 35.6782500673754
), stats AS (
  SELECT ST_CENTROID(ST_UNION_AGG(ST_GEOGPOINT(lon, lat))) centroid FROM `data`
) 
SELECT ST_MAKEPOLYGON(ST_MAKELINE(ARRAY_AGG(point ORDER BY sequence))) AS polygon
FROM (
  SELECT point, 
    CASE 
      WHEN ST_X(point) > ST_X(centroid) AND ST_Y(point) > ST_Y(centroid) THEN 3.14 - angle
      WHEN ST_X(point) > ST_X(centroid) AND ST_Y(point) < ST_Y(centroid) THEN 3.14 + angle
      WHEN ST_X(point) < ST_X(centroid) AND ST_Y(point) < ST_Y(centroid) THEN 6.28 - angle
      ELSE angle
    END sequence
  FROM (
    SELECT point, centroid, 
      ACOS(ST_DISTANCE(centroid, anchor) / ST_DISTANCE(centroid, point)) angle
    FROM (
      SELECT centroid, 
        ST_GEOGPOINT(lon, lat) point, 
        ST_GEOGPOINT(lon, ST_Y(centroid)) anchor
      FROM `data`, stats
    )
  )
)

最终结果是:

注意:这种想法/解决方案-仍然只能局限于像您这样的明显案例-我没有机会针对一般案例进行探索和/或测试

Note: this idea/solution - still can be limited to just some obvious cases like yours - i did not have chance to explore and/or test it for generic cases

这篇关于如何在BigQuery中捕获ST_MAKEPOLYGON错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！