如何用st_centroid获取bigquery多边形的正确质心

Question

我在bigquery中的ST_CENTROID函数有一些问题。在获取地理列的质心和从相同的WKT版本的列中得到质心之间有一个区别。该表使用一个带有地理列的bq load和一个包含多边形的newline_delimited_json文件作为wkt文本生成。

示例：

select st_centroid(polygon) loc, st_centroid(ST_GEOGFROMTEXT(st_astext(polygon))) loc2,polygon from table_with_polygon

结果：

POINT(-174.333247842246 -51.6549479435566)
POINT(5.66675215775447 51.6549479435566)
POLYGON((5.666771 51.654721, 5.666679 51.655027, 5.666597 51.655017, 5.666556 51.655154, 5.666702 51.655171, 5.666742 51.655037, 5.666824 51.655046, 5.666917 51.654737, 5.666771 51.654721))

POINT(-174.367214581541 -51.645030856473)
POINT(5.63278541845948 51.645030856473)
POLYGON((5.632691 51.644997, 5.63269 51.644999, 5.63273 51.645003, 5.632718 51.645049, 5.632843 51.645061, 5.632855 51.645014, 5.632691 51.644997))

POINT(-174.37100400049 -51.6434992715399)
POINT(5.62899599950984 51.6434992715399)
POLYGON((5.629063 51.643523, 5.629084 51.643465, 5.629088 51.643454, 5.628957 51.643436, 5.628915 51.643558, 5.629003 51.64357, 5.629021 51.643518, 5.629063 51.643523))

POINT(-174.293340001044 -51.6424190026157)
POINT(5.70665999895557 51.6424190026157)
POLYGON((5.706608 51.642414, 5.706624 51.642443, 5.706712 51.642424, 5.706696 51.642395, 5.706608 51.642414))

POINT(-174.306209997018 -51.6603530009923)
POINT(5.69379000298176 51.6603530009923)
POLYGON((5.693801 51.660361, 5.693802 51.660346, 5.693779 51.660345, 5.693778 51.66036, 5.693801 51.660361))

POINT(-174.291766437718 -51.6499633041183)
POINT(5.70823356228228 51.6499633041183)
POLYGON((5.708187 51.649858, 5.708091 51.650027, 5.70828 51.650069, 5.708376 51.649899, 5.708187 51.649858))

POINT(-174.369405698681 -51.653769846544)
POINT(5.63059430131924 51.653769846544)
POLYGON((5.630653 51.653531, 5.630462 51.653605, 5.630579 51.653722, 5.630574 51.65373, 5.630566 51.653729, 5.630551 51.653759, 5.630559 51.65376, 5.630555 51.653769, 5.630273 51.653846, 5.630364 51.653974, 5.630787 51.653858, 5.630852 51.653728, 5.630653 51.653531))

...etc

这是个窃听器还是我做错什么了？

Update使用Michael的答案作了进一步的挖掘。结果表明，在默认情况下，WKT的bq load不使用最小多边形。bq load没有改变这种行为的选择。导入的json非常大(openstreetmap数据)，因此很难将其更改为geoJson。

为了深入挖掘存储在列中的实际值，我做了一个

select st_asgeojson(polygon) from ...

这导致

{ "type": "Polygon", "coordinates": [ [ [5.598659, 51.65927], [5.598651, 51.659295], [5.598638, 51.659293], [5.598626, 51.65933], [5.598788, 51.659353], [5.598799, 51.659319], [5.598855, 51.659139], [5.598692, 51.65912], [5.598643, 51.659268], [5.598659, 51.65927] ], [ [180, 90], [180, 0], [180, -90], [-180, -90], [-180, 0], [-180, 90], [180, 90] ] ] } 

所以在这里可以看到错误的方向。

Answer 1

看起来，这些多边形中的一部分或全部被倒置，这就产生了对足质心：POINT(-174.333247842246 -51.6549479435566)是POINT(5.66675215775447 51.6549479435566)的对立面，等等。

有关此内容的详细信息，请参阅BigQuery文档：https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

解决这一问题有两个可能的原因和方法(我打赌是案例1)：

1. --多边形应该是小的，但是装上了错误的方向，因此变成了倒置--它们现在与原来的形状是互补的，比半球还要大。由于您没有将oriented参数传递给ST_GEOGFROMTEXT，因此该函数通过忽略方向来修复。

正确的解决方案通常是将它们加载为GeoJson (这也避免了加载WKT字符串的另一个问题--测地线对平面边缘)。或者，如果所有的边都是小的，而测地线相对于平面并不重要-用ST_GEOGFROMTEXT(st_astext(polygon))代替表格地理。

1. 多边形应该真的很大，并且加载正确的方向。然后，当您不将oriented参数传递给ST_GEOGFROMTEXT时，这个函数忽略了方向，从而破坏了。

如果是这样的话，您应该将TRUE作为第二个参数传递给ST_GEOGFROMTEXT。