[MapServer-users] Postgres DATA query with LIMIT 10 does not work

[Marcin Niemyjski]

Actually, no. Now I know why it's not working.


As you can see in my mapfile, I've activated the time dimension, so it's a WMS-T.

The issue looks like this: In the URL, I define the time range I'm interested in, and then I request data from DATA block, which in my case looks like this:


DATA 'geometry from (select * from mrc order by maxcc desc LIMIT 10) as subquery using unique unique_id'


In my opinion (which I just checked by querying it directly from postgres), the key is the subquery, which selects 10 records not from the query containing information about TIME and BBOX but simply from the entire table. This returns records from the beginning of the table that just don't overlap with the requested time period.

So, the problem solver is to implement the TIME parameter in the subquery. Does anyone have an idea of how to do that? The only thing that comes to mind is breaking it down into two new dimensions: start and stop.


Best regards and thanks,

 Marcin

________________________________
From: MapServer-users <mapserver-users-bounces at lists.osgeo.org> on behalf of Marcin Niemyjski via MapServer-users <mapserver-users at lists.osgeo.org>
Sent: Thursday, October 19, 2023 12:56 PM
To: mapserver-users at lists.osgeo.org <mapserver-users at lists.osgeo.org>; Jörg Thomsen (WhereGroup) <joerg.thomsen at wheregroup.com>
Subject: Re: [MapServer-users] Postgres DATA query with LIMIT 10 does not work

hello Jörg,

so, If there's fewer than 10 results, none will be rendered? I taught that LIMIT sets only upper border of results count.

and yup, I've just checked it:

SELECT *
FROM (
    SELECT *
    FROM mrc
    WHERE mrc.timestamp >= '2023-08-01' AND mrc.timestamp <= '2023-08-01'
        AND ST_Intersects(mrc.geometry, ST_GeomFromText('POLYGON((2791286.85068837 5622573.79066471,2791286.85068837 5638166.03910615,2805874.3941497 5638166.03910615,2805874.3941497 5622573.79066471,2791286.85068837 5622573.79066471))', 3857))
    ORDER BY maxcc DESC
) AS subquery;

query results in only 2 records.

Is there any way to get only 10 or less results using postgis query in mapserver?

Best,
Marcin


[cid:3c4ade68-ed3d-4e2c-a9eb-ad74db8b6632]<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>
________________________________
From: MapServer-users <mapserver-users-bounces at lists.osgeo.org> on behalf of Jörg Thomsen (WhereGroup) via MapServer-users <mapserver-users at lists.osgeo.org>
Sent: Thursday, October 19, 2023 12:18 PM
To: mapserver-users at lists.osgeo.org <mapserver-users at lists.osgeo.org>
Subject: Re: [MapServer-users] Postgres DATA query with LIMIT 10 does not work

Hello Marcin,

sounds stupid, but are you sure the 10 datasets have geometries within
the requested bbox? I don't see any other problem/mistakes.

Jörg



Am 19.10.23 um 11:39 schrieb Marcin Niemyjski via MapServer-users:
> Hello,
>
>
> I encountered a problem, specifically:
>
> This query works:
>
> |DATA 'geometry from (select * from mrc order by maxcc desc) as subquery
> using unique unique_id' |
>
> However, this query doesn't work:
>
> |DATA 'geometry from (select * from mrc order by maxcc desc limit 10) as
> subquery using unique unique_id' |
>
> Here's the full tileindex definition:
>
> |LAYER  DEBUG 5  STATUS OFF  NAME "time_idx"  TYPE POLYGON
>   CONNECTIONTYPE postgis  CONNECTION "***"  DATA 'geometry from (select
> * from mrc order by maxcc desc limit 10) as subquery using unique
> unique_id'  PROJECTION    "init=epsg:3857"  END  VALIDATION     'maxCC'
> '^[0-9]{1,3}$'     'default_maxCC' '100'  END  METADATA    "wms_title"
> "tile-index-cloud"    "wms_timeextent" "2022-02-01/2023-10-10/P1D"
>   "wms_timeitem" "timestamp"    "wms_timedefault" "2023-10-10"
>   "wms_enable_request" "!*"  END END |
>
> My MapServer version is 7.6.4.
>
> The query results (but returns data in Postgres
> ) in an empty window. I'm following the guidelines provided at
> https://mapserver.org/input/vector/postgis.html#data-access-connection-method <https://mapserver.org/input/vector/postgis.html#data-access-connection-method>.
>
>
> 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


--
Viele Grüße,
Jörg Thomsen
---------------------------------------------
Aufwind durch Wissen!
Web-Seminare und Online-Schulungen
bei der www.foss-academy.com<http://www.foss-academy.com>
---------------------------------------------


Jörg Thomsen
WhereGroup GmbH
Bundesallee 23
10717 Berlin
Germany

Tel: +49 (0)30 / 5130 278 74
Fax: +49 (0)30 / 5130 278 11

joerg.thomsen at wheregroup.com
www.wheregroup.com<http://www.wheregroup.com>

Geschäftsführer:
Olaf Knopp, Peter Stamm
Amtsgericht Bonn, HRB 9885

-------------------------------
Folgen Sie der WhereGroup auf twitter: http://twitter.com/WhereGroup_com

_______________________________________________
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/20231019/aceecfb7/attachment-0001.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Outlook-xjeacwcn.png
Type: image/png
Size: 528 bytes
Desc: Outlook-xjeacwcn.png
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20231019/aceecfb7/attachment-0001.png>