使用 PostgreSQL 选取随机点

Question

我正在尝试对一些OSM道路进行地理编码，并创建了一个包含500条道路样本的表。所有的道路都是多线串。我使用ST_DumpPoints创建了另一个表，所以现在我又多了一个包含道路信息的表，其中每个条目都是一条道路的单个点。初始样本表中的 500 条记录已变为 48000 条。我想创建第三个表，其中一条道路将有三个随机点。

积分表：

| gid   | number | path   | path1 | path2 | geom | text_geom  |
|-------|--------|--------|-------|-------|------|------------|
| 39559 | 49     | {1,1}  | 1     | 1     | ...  | POINT(x y) |
| 39559 | 49     | {1,2}  | 1     | 2     | ...  | POINT(x y) |
| 39559 | ...    | ...    | ...   | ...   | ...  | ...        |
| 39559 | 49     | {1,79} | 1     | 79    | ...  | POINT(x y) |
| 15168 | Null   | {1,1}  | 1     | 1     | ...  | POINT(x y) |
| 15168 | Null   | {1,2}  | 1     | 2     | ...  | POINT(x y) |
| 15168 | ...    | ...    | ...   | ...   | ...  | ...        |
| 15168 | Null   | {1,43} | 1     | 43    | ...  | POINT(x y) |

注意：有些条目有 1800 分可供选择。此表只是为了显示它的外观。

我想要的表格：

| gid   | number | path  | path1 | path2 | geom | text_geom  |
|-------|--------|-------|-------|-------|------|------------|
| 39559 | 49     | {1,1} | 1     | 1     | ...  | POINT(x y) |
| 39559 | 49     | {1,2} | 1     | 2     | ...  | POINT(x y) |
| 39559 | 49     | {1,3} | 1     | 3     | ...  | POINT(x y) |
| 15168 | Null   | {1,1} | 1     | 1     | ...  | POINT(x y) |
| 15168 | Null   | {1,2} | 1     | 2     | ...  | POINT(x y) |
| 14005 | 460    | {1,1} | 1     | 1     | ...  | POINT(x y) |
| 14005 | 460    | {1,2} | 1     | 2     | ...  | POINT(x y) |
| 14005 | 460    | {1,3} | 1     | 3     | ...  | POINT(x y) |

另一件值得一提的事情是，所有条目都有超过三点可供选择。

Answer 1

demo：db<>fiddle

您可以使用 row_number(( 窗口函数，该函数以特定顺序为组中的每条记录生成连续数字。我猜，该组是您gid，并且作为顺序，您可以简单地获取random()值。

我用一个更简单的表格演示了这一点：myroad是你的gid，mypoint是你的text_geom列b：

SELECT
    myroad,
    mypoint
FROM (
    SELECT
        *,
        row_number() OVER (PARTITION BY myroad ORDER BY random())
    FROM
        points
) s
WHERE row_number <= 3