postgresql - 使用 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() window function它为具有特定顺序的组中的每个记录生成连续的数字。该组是您的 gid，我猜，作为命令，您可以简单地采用 random() 值。

我用一个更简单的表来演示:myroad 是你的 gid 而 mypoint 是你的 text_geom columnb :

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