If you want the answer different each time, you don’t want to feed a seed to ST_GeneratePoints.
The seed argument was added because some people wanted to generate the same answer for each run. https://postgis.net/docs/ST_GeneratePoints.html (note the sentence: The optional seed is used to regenerate a deterministic sequence of points, and must be greater than zero.) From: postgis-users <[email protected]> On Behalf Of Brent Wood via postgis-users Sent: Friday, November 17, 2023 11:53 PM To: PostGIS Users Discussion <[email protected]> Cc: Brent Wood <[email protected]> Subject: [postgis-users] Generating new random points throughout an update Hopefully someone can help with a problem I'm having. I have a table with simple linestrings that I need to create a randomly modified version of. The linestrings represent vessel tracks. I can identify a set of "similar" tracks & create a single "average" linestring that is somewhat representative. Many of the records don't have a linestring, but for statistical purposes I need to assign a linestring to each - by creating a jittered version of the average linestring so they are not all identical. The simplest approach I have tried is to use an update with ST_Project() given a random() distance & random() direction applied to each vertex in the average line. I use the first two vertices with ST_Makeline(), then append a vertex for the third point, as in the SQL below. My problem is that every new line is identical. From some Googled hints, I figure the optimiser has decided to run random() once & re-use the value instead of running the function for every iteration (but I could be wrong!). Any suggestions as to how I can force a different random result for each record that is updated? I also tried using ST_GeneratePoints() in a buffer around each point, but need to use something like (random()::int as the seed, and this seems to do exactly the same - valid linestrings are generated, but they are identical, so I'm assuming the seed is not being recalculated for each record. update events set jittered = ST_MakeLine( (select ST_Project( ST_POINTN(std_track,1), (random()*5000), radians(random()*360))::geometry from std_tow), (select ST_Project( ST_PointN(std_track,2), (random()*5000), radians(random()*360))::geometry from std_tow) ); Thanks, Brent Wood
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
