ST_MAKELINE丢弃重复的点，即使不是连续的点

问题描述

考虑以下简化示例:

SELECT ST_MAKELINE([
  ST_GEOGPOINT(5, 51),
  ST_GEOGPOINT(5, 52),
  ST_GEOGPOINT(5, 51)
])

我希望这会构成一条包含三个点的线，该线会自身加倍.相反，我得到的是:

I would expect this to construct a line with three points, that doubles back on itself. Instead, what I'm getting is:

LINESTRING(5 51, 5 52)

BigQuery似乎正在丢弃重复的点，即使它们不是连续的也是如此.这弄乱了我的距离计算，其中涉及到所得线串的ST_LENGTH.

It seems that BigQuery is discarding duplicate points, even if they are not consecutive. This is messing up my distance calculation, which involves taking the ST_LENGTH of the resulting linestring.

作为一种解决方法，我正在构造一系列的线串，每个连续的点都是两个，然后求和它们的距离.但这很尴尬和冗长.

As a workaround, I'm constructing a series of linestrings, each of two consecutive points, then summing the distance of those. But it's awkward and verbose.

有没有一种方法可以创建线串而不丢弃重复的点?

Is there a way to create the linestring without dropping duplicate points?

推荐答案

BigQuery GIS会丢弃重复的重叠边(而不是顶点).

What happens is BigQuery GIS discards duplicate overlapping edges (not vertices).

例如您可以使用行字符串A，B，C，A，并且如果A，B和C不在同一行上，则不会丢弃任何内容，并且A在该行中出现两次.但是，如果两个边缘完全重叠-重叠部分将被丢弃.

E.g. you can have line string A, B, C, A and if A, B, and C are not on a single line, nothing is discarded, A is present twice in the line. But if two edges exactly overlap - the overlapping section is discarded.

另一方面，这里没有重复的顶点，但是第二条边完全与第一个边重叠，因此将其忽略:

On the other hand, here there are no duplicate vertices, but second edge completely overlaps the first one, so it is ignored:

SELECT ST_MAKELINE([
  ST_GEOGPOINT(5, 51),
  ST_GEOGPOINT(5, 53),
  ST_GEOGPOINT(5, 52)
])

LINESTRING(5 51, 5 52, 5 53)

目前尚无办法避免这种行为-BigQuery地理表示一个点集，而且当集合两个相同的集合并集时，您只能得到该集合的一个副本.

There is currently no way to avoid this behavior - BigQuery Geography represents a point set, and as sets go, when you union two identical sets you get just one copy of this set.

这篇关于ST_MAKELINE丢弃重复的点，即使不是连续的点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！