OSGeo mailing list statistics random data generator

gistfile1.txt

with
    numbers as 
        (select 10 as _top, 50 as num_of_subscriptions, 28404 as unique_subscribers, 290 as number_of_lists),
    people as
        (select st_point(random() * 99 + 1, random() * 99 + 1) as g, 'special subscribers'::text as ty 
        from numbers n, generate_series(1,n._top)),
    unique_people as 
        (select st_point(random() * 99 + 1, random() * 99 + 1) as g, 'unique subscribers'::text as ty 
        from numbers n, generate_series(1,n.unique_subscribers-n._top)),
    subs as
        (select st_exteriorring(st_buffer(st_extent(p.g), 25)) as g from unique_people p),
    len as (select generate_series(1, n.number_of_lists) as s, st_length(subs.g)/n.number_of_lists as fractlength, 
        st_length(subs.g) as totallength from subs, numbers n),
    rando as (
        select (random()*(n.number_of_lists-1) +1)::int as r  from generate_series(1, 1000), numbers n
        group by r
        limit 50),
    lists as
        (select s, st_line_interpolate_point(subs.g, s*(len.fractlength/len.totallength) - 0.000001) as g, 
            case when r.r is null then 'all subscriptions'::text else 'special subscriptions' end as ty
            from subs, len left join rando r on r.r = len.s),
    d as (
        select p.g, p.ty from people p
        union all 
        select u.g, u.ty from unique_people u
        union all
        select l.g, l.ty from lists l)
select row_number()  over ()::int as id, d.* from d