[MapServer-users] filtering the data based on postgis attributes

[Seth G]

Hi Marcin,

In these cases previously I've used a SQL trick to implement this, by setting a default value for the parameter (of a non-existent ID) and adding an OR clause to the SQL. E.g;

VALIDATION
    "default_pid" "-1"
..

WHERE (pid  = %pid% OR %pid% = -1)

If no value is provided then the SQL will be WHERE (pid  = -1 OR -1 = -1) which will always return True so all records will return.

Seth

--
web:https://geographika.net & https://mapserverstudio.net
twitter: @geographika

On Tue, Nov 28, 2023, at 3:32 PM, Marcin Niemyjski via MapServer-users wrote:
> Hello, 
> I would like to define several dimensions for filtering my WMS (Web Map Service). They are based on a PostGIS table from which the service is exposed. Here lies my problem because among the required information to expose, I need to provide the following to the map server:
> 
>  • wms_dimensionlist: (Mandatory)
>  • wms_[dimensionname]_item: (Mandatory)
>  • wms_[dimensionname]_units: (Mandatory)
>  • wms_[dimensionname]_extent: (Mandatory)
>  • wms_[dimensionname]_default: (Optional)
> My issue arises because I want to filter my data based on the "product_id" column containing a string. The table is large, so it's not possible to provide all the ID values to "wms_productid_extent."
> 
> So, I thought about using https://mapserver.org/cgi/runsub.html#table-of-contents, but the problem arises with the implementation in the query. I would like to lead to a situation where if I don't provide any filtering parameters (product_id, cloud_cover), the entire data set is returned.
> 
> I do not want to use this:
> DATA 'geometry from (select * from mrc order by maxcc desc) where pid = '%pid%' as subquerry using unique unique_id using srid=3857'
> because if I do not provide pid, my query wiill not work.
> 
> 
> 
> So, to summarize I'm looking for something like this:
> 
> DATA 'geometry from (select * from mrc order by maxcc desc) as subquerry using unique unique_id using srid=3857'
> 
> VALIDATION
>     'maxCC' '^[0-9](1, 3)$'
>     'tile' '^.{6}$'
>     'pid' '^.{65}$'
> END
> 
> No default values (except time), if none is provided Mapserver does not filter the data (except time).
> 
> Best,
> Marcin
> 
 <https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature>
> 
> Book time to meet with me <https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature>
> 
> _______________________________________________
> MapServer-users mailing list
> MapServer-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20231128/8f709a61/attachment.htm>