[postgis-users] Generating new random points throughout an update

[Brent Wood]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231118/d6044ff1/attachment.htm>