<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<p style="margin-top: 0px; margin-bottom: 0px;"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof">Actually, no. Now I know why it's not working.</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;" class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);" class="elementToProof"><br>
</span></p>
<p><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">As you can see in my mapfile, I've activated the time dimension, so it's a WMS-T.</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">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:</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">DATA 'geometry from (select * from mrc order by maxcc desc LIMIT 10) as subquery using
unique unique_id'</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">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.</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">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.</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"><br>
</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">Best regards and thanks,</span></p>
<p class="elementToProof"><span style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);"> Marcin</span></p>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> MapServer-users <mapserver-users-bounces@lists.osgeo.org> on behalf of Marcin Niemyjski via MapServer-users <mapserver-users@lists.osgeo.org><br>
<b>Sent:</b> Thursday, October 19, 2023 12:56 PM<br>
<b>To:</b> mapserver-users@lists.osgeo.org <mapserver-users@lists.osgeo.org>; Jörg Thomsen (WhereGroup) <joerg.thomsen@wheregroup.com><br>
<b>Subject:</b> Re: [MapServer-users] Postgres DATA query with LIMIT 10 does not work</font>
<div> </div>
</div>
<style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style>
<div dir="ltr">
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">hello Jörg,</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">so, If there's fewer than 10 results, none will be rendered? I taught that LIMIT sets only upper
border of results count.</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
and yup, I've just checked it:</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">SELECT *</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">FROM (</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> SELECT *</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> FROM mrc</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> WHERE mrc.timestamp >= '2023-08-01' AND mrc.timestamp <= '2023-08-01'</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> 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))</span></div>
<div><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"> ORDER BY maxcc DESC</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">) AS subquery;</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">query results in only 2 records.</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Is there any way to get only 10 or less results using postgis query in mapserver?</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Best,</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">Marcin</span></div>
<div class="x_elementToProof"><span style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)"><br>
</span></div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div id="x_Signature">
<table id="x_tableSelected0" style="margin-top:30px; margin-bottom:30px; width:95%">
</table>
<table id="x_pbpsiglinktable">
<tbody>
<tr>
<td><a href="https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature" id="OWA1a215076-1ea1-5284-d10c-135e1970e1dc" class="x_OWAAutoLink"><img data-outlook-trace="F:2|T:2" src="cid:3c4ade68-ed3d-4e2c-a9eb-ad74db8b6632"></a></td>
<td></td>
<td><span style="color:rgb(0,120,212)"><a href="https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature" id="OWA66b36991-106d-a51d-f0ed-d81757dc0c27" class="x_OWAAutoLink" style="text-decoration:none; color:rgb(0,120,212)">Book
time to meet with me</a></span></td>
<td></td>
</tr>
</tbody>
</table>
</div>
<div id="x_appendonsend"></div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="x_divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> MapServer-users <mapserver-users-bounces@lists.osgeo.org> on behalf of Jörg Thomsen (WhereGroup) via MapServer-users <mapserver-users@lists.osgeo.org><br>
<b>Sent:</b> Thursday, October 19, 2023 12:18 PM<br>
<b>To:</b> mapserver-users@lists.osgeo.org <mapserver-users@lists.osgeo.org><br>
<b>Subject:</b> Re: [MapServer-users] Postgres DATA query with LIMIT 10 does not work</font>
<div> </div>
</div>
<div class="x_BodyFragment"><font size="2"><span style="font-size:11pt">
<div class="x_PlainText">Hello Marcin,<br>
<br>
sounds stupid, but are you sure the 10 datasets have geometries within <br>
the requested bbox? I don't see any other problem/mistakes.<br>
<br>
Jörg<br>
<br>
<br>
<br>
Am 19.10.23 um 11:39 schrieb Marcin Niemyjski via MapServer-users:<br>
> Hello,<br>
> <br>
> <br>
> I encountered a problem, specifically:<br>
> <br>
> This query works:<br>
> <br>
> |DATA 'geometry from (select * from mrc order by maxcc desc) as subquery <br>
> using unique unique_id' |<br>
> <br>
> However, this query doesn't work:<br>
> <br>
> |DATA 'geometry from (select * from mrc order by maxcc desc limit 10) as <br>
> subquery using unique unique_id' |<br>
> <br>
> Here's the full tileindex definition:<br>
> <br>
> |LAYER DEBUG 5 STATUS OFF NAME "time_idx" TYPE POLYGON <br>
> CONNECTIONTYPE postgis CONNECTION "***" DATA 'geometry from (select <br>
> * from mrc order by maxcc desc limit 10) as subquery using unique <br>
> unique_id' PROJECTION "init=epsg:3857" END VALIDATION 'maxCC' <br>
> '^[0-9]{1,3}$' 'default_maxCC' '100' END METADATA "wms_title" <br>
> "tile-index-cloud" "wms_timeextent" "2022-02-01/2023-10-10/P1D" <br>
> "wms_timeitem" "timestamp" "wms_timedefault" "2023-10-10" <br>
> "wms_enable_request" "!*" END END |<br>
> <br>
> My MapServer version is 7.6.4.<br>
> <br>
> The query results (but returns data in Postgres<br>
> ) in an empty window. I'm following the guidelines provided at <br>
> <a href="https://mapserver.org/input/vector/postgis.html#data-access-connection-method">
https://mapserver.org/input/vector/postgis.html#data-access-connection-method</a> <<a href="https://mapserver.org/input/vector/postgis.html#data-access-connection-method">https://mapserver.org/input/vector/postgis.html#data-access-connection-method</a>>.<br>
> <br>
> <br>
> Best,<br>
> marcin<br>
> <br>
> <<a href="https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature">https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature</a>>
Book time to meet with me <<a href="https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature">https://outlook.office.com/bookwithme/user/6347c7def05a478ba013ae948648789d@cloudferro.com?anonymous&ep=signature</a>>
<br>
> <br>
> <br>
> _______________________________________________<br>
> MapServer-users mailing list<br>
> MapServer-users@lists.osgeo.org<br>
> <a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
<br>
<br>
-- <br>
Viele Grüße,<br>
Jörg Thomsen<br>
---------------------------------------------<br>
Aufwind durch Wissen!<br>
Web-Seminare und Online-Schulungen<br>
bei der <a href="http://www.foss-academy.com">www.foss-academy.com</a><br>
---------------------------------------------<br>
<br>
<br>
Jörg Thomsen<br>
WhereGroup GmbH<br>
Bundesallee 23<br>
10717 Berlin<br>
Germany<br>
<br>
Tel: +49 (0)30 / 5130 278 74<br>
Fax: +49 (0)30 / 5130 278 11 <br>
<br>
joerg.thomsen@wheregroup.com<br>
<a href="http://www.wheregroup.com">www.wheregroup.com</a><br>
<br>
Geschäftsführer:<br>
Olaf Knopp, Peter Stamm<br>
Amtsgericht Bonn, HRB 9885<br>
<br>
-------------------------------<br>
Folgen Sie der WhereGroup auf twitter: <a href="http://twitter.com/WhereGroup_com">
http://twitter.com/WhereGroup_com</a><br>
<br>
_______________________________________________<br>
MapServer-users mailing list<br>
MapServer-users@lists.osgeo.org<br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</div>
</span></font></div>
</div>
</body>
</html>