bug with Postgis query when "order by random() limit n;"

[pcreso]

I want a random subset of features returned each call from a postgis table via a wfs layer. I thought a simple SQL could do this pretty easily, but it doesn't work from mapserver.

The mapfile data statement:

DATA "geom_ll from (select gid, geom_ll, name, random() as rand

from coast_poly order by rand limit 1) as foo using unique gid using srid=4326"

This returns 0 features.

If I change the query to order by gid it works fine & returns 1 feature. If I retain order by rand but with no limit it works fine & returns all the features.

If I paste the queries into a psql terminal, all instances work fine: eg:

select gid, name, random() as rand from coast_poly order by rand limit 1; gid | name | rand

---

1 | NORTH ISLAND | 0.409694513771683

(1 row)

But I cannot get mapserver WFS output to work with both a random order & a limit:

Source for this layer: ​http://localhost/cgi-bin/mapserv?map=/srv/www/htdocs/map_nz/nz_wms_server.map&SERVICE=WFS&VERSION=1.0.0&REQUEST=GetFeature&TYPENAME=nz_land The number of features in this layer: 0

More confusing is that the WFS extent is correct for the feature that is not there:

Extents: In layer spatial reference system units : xMin,yMin 172.643,-41.6138 : xMax,yMax 178.551,-34.3934

& worse, a data statement of: DATA "geom_ll from (select gid, geom_ll, name, random() as rand

from coast_poly where gid = 3 or gid = 1 order by rand limit 2) as foo using unique gid using srid=4326"

sometimes returns 1 & sometimes 0 features (but should return 2) as a wfs layer, but as an sql in psql always returns the correct two features, but varies the order, as you'd expect:

corax=# select gid, name, random() as rand

from coast_poly where gid = 3 or gid=1 order by rand limit 2;

gid | name | rand

---

3 | STEWART ISLAND | 0.102461547125131 1 | NORTH ISLAND | 0.609191857278347

(2 rows)

corax=# select gid, name, random() as rand

from coast_poly where gid = 3 or gid=1 order by rand limit 2;

gid | name | rand

---

1 | NORTH ISLAND | 0.531711789779365 3 | STEWART ISLAND | 0.784405956044793

(2 rows)

[pramsey]

If I take a random feature from your whole table, what are the odds it is within your view extent? Try using the !BOX! token.

DATA "geom_ll from (
select gid, geom_ll, name, random() as rand from coast_poly where geom_ll && !BOX! order by rand limit 1
) as foo using unique gid using srid=4326"

[pcreso]

Replying to pramsey:

If I take a random feature from your whole table, what are the odds it is within your view extent? Try using the !BOX! token.

Hi Paul,

In this use case all the features have the same position. All are in the Box. Ordering by rand without "limit 5;" I get all 250 records fine & all showing at the same point on the map. With "limit 5;" I get zero records/features. If I order by gid, "limit 5;" it works correctly, but I obviously get the same 5 every time. It works fine from the psql command line, only failing when called via mapserver, so I'm assuming more a mapserver than Postgis problem.

Note I have only tested this with WFS, as I was hoping to use the web service to return the info required for the web app to then retrieve the appropriate photos (ie: the atlas_id value) using WFS. I have two fall back situations: just order by gid & take the first 5 photos or snd all 250 records - order by rand limit 5; - and have the web application just grab the first 5 in the list.

The purpose is to allow a user to click on a point on a map (point locations where we deployed an underwater camera) and retrieve 5 random photos out of the 250 that were taken at that site.

Thanks,

Brent

[pcreso]

I can give you ssh login to a box where this is evident if it would help?

[pramsey]

A minimal repeatable setup so that I can exercise this effect with 15 second set-up would be the most useful thing, Brent. Table dump, map file, and test both to ensure there's no dependencies left (font references, symbol references). And then a test URL to turn things over.

[pcreso]

script to creat db & mapfile