google-bigquery - 当点在多边形之外时，ST_Within 错误地返回 true

Question

此查询用于确定点是否在多边形内...

SELECT ST_Within
(
    ST_GeogPoint(69.6, 21.1),
    ST_GeogFromText('POLYGON((67.8 23.4, 69 17, 72.8 16.9, 67.8 23.4))')
)

...在 BigQuery 中返回 TRUE。这是一个错误的结果。

同样的查询...

SELECT ST_Within
(
    ST_MakePoint(69.6, 21.1),
    'POLYGON((67.8 23.4, 69 17, 72.8 16.9, 67.8 23.4))'
)

...在 PostGIS 中返回 FALSE。这是一个正确的结果。

Answer 1

此查询为您提供了您正在寻找的 false 回复:

SELECT ST_WITHIN(
  ST_GeogPoint(69.6, 21.1)
  , ST_GeogFromGeoJSON(
    '{"type": "Polygon", "coordinates": [[[67.8, 23.4], [69, 17], [72.8, 16.9], [67.8, 23.4]]]}'
))

有什么不同？

让我们检查文档:

https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromgeojson

A BigQuery GEOGRAPHY has spherical geodesic edges, whereas a GeoJSON Geometry object explicitly has planar edges. To convert between these two types of edges, BigQuery adds additional points to the line where necessary so that the resulting sequence of edges remains within 10 meters of the original edge.

事实上，在两个系统之间进行转换时发生了一个非常有趣的曲面分割:

 SELECT ST_GeogFromGeoJSON(
   '{"type": "Polygon", "coordinates": [[[67.8, 23.4], [69, 17], [72.8, 16.9], [67.8, 23.4]]]}'
 )

 POLYGON((67.8 23.4, 67.875 23, 67.95 22.6, 68.025 22.2, 68.1 21.8, 68.175 21.4, 68.25 21, 68.325 20.6, 68.4 20.2, 68.475 19.8, 68.55 19.4, 68.625 19, 68.7 18.6, 68.775 18.2, 68.85 17.8, 68.925 17.4, 69 17, 69.2375 16.99375, 69.475 16.9875, 69.7125 16.98125, 69.95 16.975, 70.1875 16.96875, 70.425 16.9625, 70.6625 16.95625, 70.9 16.95, 71.1375 16.94375, 71.375 16.9375, 71.6125 16.93125, 71.85 16.925, 72.0875 16.91875, 72.325 16.9125, 72.5625 16.90625, 72.8 16.9, 72.64375 17.103125, 72.4875 17.30625, 72.33125 17.509375, 72.175 17.7125, 72.01875 17.915625, 71.8625 18.11875, 71.70625 18.321875, 71.55 18.525, 71.39375 18.728125, 71.2375 18.93125, 71.08125 19.134375, 70.925 19.3375, 70.76875 19.540625, 70.6125 19.74375, 70.45625 19.946875, 70.3 20.15, 70.14375 20.353125, 69.9875 20.55625, 69.83125 20.759375, 69.675 20.9625, 69.51875 21.165625, 69.3625 21.36875, 69.20625 21.571875, 69.05 21.775, 68.89375 21.978125, 68.7375 22.18125, 68.58125 22.384375, 68.425 22.5875, 68.26875 22.790625, 68.1125 22.99375, 67.95625 23.196875, 67.8 23.4))   

顺便说一句，来自地球引擎文档:

https://developers.google.com/earth-engine/geometries_planar_geodesic

A geometry created in Earth Engine is either geodesic (i.e. edges are the shortest path on the surface of a sphere) or planar (i.e. edges are the shortest path in a 2-D Cartesian plane). No one planar coordinate system is suitable for global collections of features, so Earth Engine's geometry constructors build geodesic geometries by default.

现在让我们检查 PostGIS - 它可以与几何和地理一起使用。

SELECT ST_CoveredBy (
    ST_MakePoint(69.6, 21.1),
    'POLYGON((67.8 23.4, 69 17, 72.8 16.9, 67.8 23.4))'
)    
false

SELECT ST_CoveredBy(
    ST_MakePoint(69.6, 21.1)::geography,
    'POLYGON((67.8 23.4, 69 17, 72.8 16.9, 67.8 23.4))'
)
true

(在这个多边形的大陆尺度上:你真的想在几何上使用地理 - 即使 PostGIS 中的性能会变慢)

回到 BigQuery:巴黎到洛杉矶的距离是多少？

SELECT ROUND(ST_Length(line)/1000,2) km
FROM (
  SELECT [
    ST_GeogFromGeoJSON('{"type": "LineString", "coordinates": [[-118.4079, 33.9434], [2.5559, 49.0083]]}')
    , ST_GEOGFROMTEXT('LINESTRING(-118.4079 33.9434, 2.5559 49.0083)')
  ] lines
), UNNEST(lines) line


km
10187.92     
9103.09

我刚刚使用测地线为您节省了 1084 公里的行程。