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

[Marcin Niemyjski]

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/2f71d199/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/2f71d199/attachment-0001.png>