Picking random points using PostgreSQL

Question

I am trying to geocode some OSM roads and I have created a table with a sample of 500 roads. All of the roads are multilinestrings. I have created another table using ST_DumpPoints so now I have one more table with the roads info where each entry is a single point from a single road. The 500 records from the initial sample table have turned to 48000. I would like to create a third table in which there would be three random points from a single road.

The points table:

| 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) |

Note: some entries have 1800 points to pick from. This table is just to show what it looks like.

The table I would like to have:

| 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) |

Another thing worth mentioning is that all of the entries have more than three points to pick from.

S-Man · Accepted Answer · 2019-07-24 08:09:17Z

2

demo:db<>fiddle

You can use the row_number() window function which generates consecutive numbers for each record within a group with a specific order. The group is your gid, I guess, and as order you could simply take the random() value.

I demonstrated this with a more simplyfied table: myroad is your gid and mypoint is your text_geom columnb:

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

answered Jul 24, 2019 at 8:09

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Picking random points using PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related