如何使用st_centroid获取bigquery多边形的正确质心 [英] How to get the correct centroid of a bigquery polygon with st_centroid

问题描述

bigquery中的ST_CENTROID函数遇到一些麻烦.获取GEOGRAPHY列的质心和从该列的相同WKT版本之间是有区别的.该表是使用带有地理列的 bq load 和包含多边形作为wkt文本的newline_delimited_json文件生成的.

示例:

 选择st_centroid(polygon)loc，st_centroid(ST_GEOGFROMTEXT(st_astext(polygon)))loc2，来自table_with_polygon的多边形 

结果:

  POINT(-174.333247842246 -51.6549479435566)要点(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)要点(-174.367214581541 -51.645030856473)要点(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)要点(-174.37100400049 -51.6434992715399)要点(5.62899599950984 51.6434992715399)POLYGON((5.629063 51.643523、5.629084 51.643465、5.662988 51.643454、5.628957 51.643436、5.662815 51.643558、5.62903 51.64357、5.662921 51.643518、5.662963 51.643523)要点(-174.293340001044 -51.6424190026157)要点(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))要点(-174.306209997018 -51.6603530009923)要点(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))要点(-174.291766437718 -51.6499633041183)要点(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))要点(-174.369405698681 -51.653769846544)要点(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.630852653...等等 

这是错误还是我做错了什么?

更新使用Michael Entin的答案作为提示进行了进一步的挖掘.事实证明，默认情况下，使用WKT的 bq加载不会使用最小的多边形.而且 bq load 不能更改此行为.导入的json非常大(openstreetmap数据)，因此没有简单的选项可以将其更改为geoJson.

为了更深入地了解存储在列中的实际值，我做了

 从...选择st_asgeojson(polygon) 

导致

  {"type":多边形"，坐标":[[[[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]]]} 

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

解决方案

这些多边形中的一些或全部看起来可能已经颠倒了，这会产生对立的质心: POINT(-174.333247842246 -51.6549479435566)与 POINT(5.66675215775447 51.6549479435566)等相反

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

有两种可能的原因和解决方法(我敢打赌情况1):

1. 多边形应该很小，但是以不正确的方向加载，因此变成了倒置-现在它们与预期的形状互补，并且比半球大.由于您没有将 directional 参数传递给 ST_GEOGFROMTEXT ，因此此功能通过忽略方向来修复.

正确的解决方案通常是将它们作为GeoJson加载(这还避免了加载WKT字符串时出现的另一个问题-测地线与平面边缘).或者，如果所有边缘都很小，并且测地线与平面无关紧要-将表的地理位置替换为 ST_GEOGFROMTEXT(st_astext(polygon)).

2. 多边形应确实很大，并以正确的方向加载.然后，当您没有将 directional 参数传递给 ST_GEOGFROMTEXT 时，此函数会通过忽略方向而破坏它们.

在这种情况下，您应该将 TRUE 作为第二个参数传递给 ST_GEOGFROMTEXT .

I'm having some trouble with the ST_CENTROID function in bigquery. There is a difference between getting the centroid of a GEOGRAPHY column and from the same WKT version of the column. The table is generated using a bq load with a geography column and a newline_delimited_json file containing the polygon as wkt text.

Example:

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

Result:

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

Is this a bug or am I doing something wrong?

Update Did some further digging using Michael Entin's answer as a hint. It turns out that bq load with WKT does NOT use the smallest polygon by default. And there is no option with bq load to change this behaviour. The imported json is very large (openstreetmap data) so there is no easy option to change this to geoJson.

To dig deeper into the actual value stored in the column, I did a

select st_asgeojson(polygon) from ...

which resulted in

{ "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] ] ] } 

So here the wrong orientation can be seen.

解决方案

Looks like some or all of these polygons might have gotten inverted, and this produces antipodal centroids: POINT(-174.333247842246 -51.6549479435566) is antipodal to POINT(5.66675215775447 51.6549479435566) etc.

See BigQuery doc for details of what this means: https://cloud.google.com/bigquery/docs/gis-data#polygon_orientation

There are two possible reasons and ways to resolve this (my bet is case 1):

1. The polygons should be small, but were loaded with incorrect orientation, and thus became inverted - they are now complimentary to what was the intended shape, and are larger than hemisphere. Since you don't pass oriented parameter to ST_GEOGFROMTEXT, this function fixes them by ignoring the orientation.

The correct solution is usually to load them as GeoJson (this also avoids another issue with loading WKT strings - geodesic vs planar edges). Or if all the edges are small and geodesic vs planar does not matter - replace the table geography with ST_GEOGFROMTEXT(st_astext(polygon)).

2. The polygons should really be large, and were loaded with correct orientation. Then when you don't pass oriented parameter to ST_GEOGFROMTEXT, this function breaks them by ignoring the orientation.

If this is the case, you should pass TRUE as second parameter to ST_GEOGFROMTEXT.

这篇关于如何使用st_centroid获取bigquery多边形的正确质心的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！