Re: [postgis-users] Query with LIMIT but as random result set? - Mailing list pgsql-general

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

Note that even for an even probability distribution,you have to be careful.

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