Обсуждение: Re: [postgis-users] Query with LIMIT but as random result set?
Stefan Keller wrote: > "... ORDER BY random() LIMIT 10;" works ok. > > But with the following option it gets more tricky assume: >> And as an option the (limited) resultset should be spatially >> distributed (not clustered). > > I'm thinking about some radial spatial distribution function. So, you explicitly *don't* want a random selection? By "spatially distributed" you mean that if you have already chosen one particular location, other locations which are close to it should be less probable (or impossible) to include in the limited result set? How would you define the desired result? The one with the highest best solution to the "traveling salesman" problem? -Kevin
Hi Kevin No; I'm thinking about some query (or function) that selects random points (POIs) with certain characteristics like decreasing density. I didn't find much theory about how to *create* such random points. There seems to be more literature and implementation about measuring geographic distribution (like in ArcGIS http://bit.ly/13lTFj9 ). Under "radial distribution function" I understand a function which describes how density varies depending on the distance from a reference point (= the user). Yours, Stefan 2013/1/10 Kevin Grittner <kgrittn@mail.com>: > Stefan Keller wrote: > >> "... ORDER BY random() LIMIT 10;" works ok. >> >> But with the following option it gets more tricky assume: >>> And as an option the (limited) resultset should be spatially >>> distributed (not clustered). >> >> I'm thinking about some radial spatial distribution function. > > So, you explicitly *don't* want a random selection? By "spatially > distributed" you mean that if you have already chosen one > particular location, other locations which are close to it should > be less probable (or impossible) to include in the limited result > set? How would you define the desired result? The one with the > highest best solution to the "traveling salesman" problem? > > -Kevin
Hi Stefan,
Please do not to post! :-)
Here, we post replies at the end, so people can see the context before the new stuff.
On 11/01/13 11:36, Stefan Keller wrote:
Please do not to post! :-)
Here, we post replies at the end, so people can see the context before the new stuff.
On 11/01/13 11:36, Stefan Keller wrote:
Note that even for an even probability distribution,you have to be careful.Hi Kevin No; I'm thinking about some query (or function) that selects random points (POIs) with certain characteristics like decreasing density. I didn't find much theory about how to *create* such random points. There seems to be more literature and implementation about measuring geographic distribution (like in ArcGIS http://bit.ly/13lTFj9 ). Under "radial distribution function" I understand a function which describes how density varies depending on the distance from a reference point (= the user). Yours, Stefan 2013/1/10 Kevin Grittner <kgrittn@mail.com>:Stefan Keller wrote:"... ORDER BY random() LIMIT 10;" works ok. But with the following option it gets more tricky assume:And as an option the (limited) resultset should be spatially distributed (not clustered).I'm thinking about some radial spatial distribution function.So, you explicitly *don't* want a random selection? By "spatially distributed" you mean that if you have already chosen one particular location, other locations which are close to it should be less probable (or impossible) to include in the limited result set? How would you define the desired result? The one with the highest best solution to the "traveling salesman" problem? -Kevin
There are at least 2 ways to distribute points in a circle, both are valid in different contexts.
(1) Select (x,y) in a square containing the circle, through away any points outside the circle. This has an even probability density per unit area of the circle.
(2) Select (theta, r) at random. This has a greater density of points at the centre reducing lineraly towards the circumference.
I think something like the second fits your situation.
DROP TABLE IF EXISTS ranpoint;
CREATE TABLE ranpoint
(
id int,
theta float,
r float
);
-- theta in radians
-- for radius = 100
INSERT INTO ranpoint
(id, theta, r)
VALUES
(generate_series(1, 10), pi() * random(), 100 * random());
TABLE ranpoint;
Cheers,
Gavin
On 01/11/13 09:31, Gavin Flower wrote: > -- theta in radians > -- for radius = 100 > > INSERT INTO ranpoint > (id, theta, r) > VALUES > (generate_series(1, 10), pi() * random(), 100 * random()); Shouldn't the value for theta be: 2 * pi() * random() Bosco.
On 12/01/13 06:45, Bosco Rama wrote:
Very definitely! :-)On 01/11/13 09:31, Gavin Flower wrote:-- theta in radians -- for radius = 100 INSERT INTO ranpoint (id, theta, r) VALUES (generate_series(1, 10), pi() * random(), 100 * random());Shouldn't the value for theta be: 2 * pi() * random() Bosco.
Me bad, as the saying goes...
Cheers,
Gavin
On Saturday, January 12, 2013 at 04:49, Gavin Flower wrote: >On 12/01/13 06:45, Bosco Rama wrote: >>Shouldn't the value for theta be: >>2 * pi() * random() >> >>Bosco. >> >> >Very definitely! :-) > One could also ask if the value for theta shouldn't be: tau() * random() <http://tauday.com/> :-) Regards Gavan
On 12/01/13 10:44, Gavan Schneider wrote:
On Saturday, January 12, 2013 at 04:49, Gavin Flower wrote:Well Gavan,On 12/01/13 06:45, Bosco Rama wrote:One could also ask if the value for theta shouldn't be:Shouldn't the value for theta be:Very definitely! :-)
2 * pi() * random()
Bosco.
tau() * random()
<http://tauday.com/> :-)
Regards
Gavan
I must bow down before your greater wisdom, as I am forced to agree with you!
Especially as your name sorts before mine, yet our names are almost exactly the same. :-)
Cheers,
Gavin
P.S. Is tau() a standard part of pg core - if not, when will it be?