[mapserver-users] help with mapfile postgis query

[Andy Colson]

On 5/26/2010 1:49 PM, pcreso at pcreso.com wrote:
> 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
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

Do you need the random number returned?  Can you try removing it from 
the select list and change the order to:

"order by random()" instead to see if it makes a difference.


-Andy