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

[Regina Obe]

Well when I run random()  I do get a different answer for each run so random behaves as I would expect.  I didn’t look that closely at your query with random.

 

e.g.

 

SELECT random()

FROM generate_series(1,100);

 

Even if within the same row, the random numbers are different:

 

SELECT random(), random()

FROM generate_series(1,10);

 

If you were doing random()::integer as input into ST_GeneratePoints, I thought maybe that was a typo on your end.  Then your random number would only be 0 or 1, which is not that random.

 

So if you really were doing ST_GeneratePoints(geom, random()::integer) then that would explain why you got much less than random results with ST_GeneratePoints.

 

 

From: Brent Wood <pcreso at yahoo.com> 
Sent: Saturday, November 18, 2023 1:29 AM
To: Regina Obe <lr at pcorp.us>; PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Generating new random points throughout an update

 

Hi Regina,

 

The seed was an int generated from random(), so I'd expected to generate a different result every time. This didn't happen.

 

Do I understand that if I omit the seed, I'll get a different point each time by default?

 

 

Thanks,

 

   Brent 

 

On Saturday, November 18, 2023 at 06:01:37 PM GMT+13, Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote: 

 

 

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 <postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org> > On Behalf Of Brent Wood via postgis-users
Sent: Friday, November 17, 2023 11:53 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> >
Cc: Brent Wood <pcreso at yahoo.com <mailto:pcreso at yahoo.com> >
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

 

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