[mapserver-users] help with mapfile postgis query

[pcreso at pcreso.com]

I'm confused, can anyone help?

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)


Thanks in advance,

  Brent Wood