No Possibility of Effectively Limiting Data Query Results When Using Time Dimensions about mapserver HOT 21 OPEN

I think that creating new class called LIMIT within Layer would be a good idea. Then users will be able to define maximal number of rendered features within layer based on data after filtrations.

from mapserver.

Does setting MAXFEATURES to 10 give you the result you want?

from mapserver.

No, I believe that MAXFEATURES defines the number of images to be drawn, so if I will have my data i descending cloud cover order then on top of my service I will have the Tenth worst image. If I will have my data i ascending cloud cover order then the one on top would be the Tenth best image.

t. In my case, as you can see:

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

I'm selecting all of the images, but I only need to render the top ten, meaning the last ten images in the order, with the one on top being the best.

from mapserver.

It seems similar to an issue I had in #5008. To resolve it an ORDER BY was added to the outer query to guarantee order of the filtered data. This in combination with MAXFEATURES might work. The fix was only for the SQL Server driver, AFAIK this does not exist in the Postgres one: e653b9b

from mapserver.

As I wrote, the "order by" in its current form returns the column in the following order:

maxcc
90%
90%
90%
90%
90%
90%
90%
85%
75%
65%
50%
10%
0%

Let's assume I need the last 5, which are:

75%
65%
50%
10%
0%

And they must be in the order so that the image with 0% cloud cover is at the top.

In the example you mentioned, the column is sorted by the column name. Let's assume that my column will be ordered in the following way:

Descending, just like above. In this case, MAXFEATURES 5 will return:

90%
90%
90%
90%
90%

Ascending. In this case, MAXFEATURES 5 will return:

0%
10%
50%
65%
75%

So in both situations, the best image will not be returned at the very top.
Am I right?

from mapserver.

Is your use case somewhat similar than the one in #5899?

Would that serve also your needs? Start filling pixels of the output image starting from the best source image within the time window until all the pixels are filled. Then stop.

from mapserver.

You currently have your ORDER BY within the subquery. The SQL Server driver allows this to be moved to the outer query, so records are ordered after the time filter has been applied and not before. In this case you'd have filtered out the 90% values and then records would be ordered as follows:

75%
65%
50%
10%
0%

from mapserver.

@geographika
Okay but how to add ORDER BY to outer query? From what I understand config keyword SORTBY mentioned here #5041 (comment) is not implemented?

I guess for now this is how it should be done? #5008 (comment)

from mapserver.

@jratike80
Yup saw this one before.
But from what I understand it is based on timestamp parameter passed to DATA query, but this parameter is not TIME dimension, so in fact I will not be able to use WMS-T full potential?
So how to let Mapserver know which image is the best one, and where is NoData to be filled by second best? -- Really don't get this part, please explain if possible :)

from mapserver.

It is a feature request, not implemented. If it was implemented it would work with any sorted tileindex features. User decides what is the best just like you want to do by sorting the images with ORDER BY maxcc DESC. The difference would be that there would be no need to use LIMIT for shortening the list. Mapserver would take all pixels which are not nodata from 0% and then automatically try to fill the holes from 10%, 50% and so on.

Because this is not implemented we are not using the native TIME support of Mapserver. We have additional time attributes in the tile index. The WMS GetMaps from standard WMS-T clients contain the &TIME= parameter but we modify the request on-the-fly with a frontal service. &TIME= is dropped and new query parameters &START= and &END= are created. In the mapfile the DATA is something like this

DATA "SELECT path||location as location, crs, resolution, time, GEOMETRY FROM ortokuva where (end_year is null or end_year>=%END%) and (start_year>=%START% and start_year<=%END%) order by time,resolution desc"

Order by is still needed because there may be several images from the same time and then we want to render the one with smallest pixel size last. Isn't that the same use case that you have? We just prefer small pixels but you would like to prefer low cloud coverage.

from mapserver.

This is practically the same situation.

Okay, now I fully understand the process of selecting images for rendering. You work around the lack of implementation of this functionality by using a time parameter that is not the TIME dimension. But from my experience with MapServer, the last rendered image is the one on top. I understand how to make MapServer recognize the best image, but how do you make it recognize NoData and use the second best image to fill in the gaps and also do not overlap actual data from the first/best image? Or Is it also an unimplemented feature, and just an idea?

from mapserver.

@geographika Okay but how to add ORDER BY to outer query? From what I understand config keyword SORTBY mentioned here #5041 (comment) is not implemented?

I guess for now this is how it should be done? #5008 (comment)

Correct - the ORDER BY isn't as yet implemented in the PostGIS driver, It would need similar changes to e653b9b added.

from mapserver.

But from my experience with MapServer, the last rendered image is the one on top.

My experience since this https://lists.osgeo.org/pipermail/mapserver-users/2008-April/055317.html is the opposite. Mapserver takes the first image that it gets from the tileindex (the one on top) renders it, takes the next image, renders that above the previous etc. What remain on the top of the final rendered image is the one that was on the bottom of the image list from the tileindex. order by time shows the newest image, not the oldest.

You can see Mapserver in action here https://kartta.paikkatietoikkuna.fi/?zoomLevel=8&coord=433861.1118164062_6947879.803305684&mapLayers=801+100+default,3400+100+ortokuva:indeksi&timeseries=1950&uuid=90246d84-3958-fd8c-cb2c-2510cccca1d3&noSavedState=true&showIntro=false

I can see from your debug info above that when time dimension is used with WMS-T Mapserver generates a query like this
...and (("timestamp" >= date_trunc('day',date '2023-08-01') and "timestamp" <= (date_trunc('day',date '2023-08-31') + interval '1 day' - interval '1 second')))

However, Mapserver does not sort the selected tileindex rows. I think it should do ORDER BY timestamp because now what gets rendered into the final image when the time range is large depends on the native order of entries in the data source.

from mapserver.

Yes I agree thank you for the clarification. The query I should aim for should contain information on two parameters: start and stop. This would select the rasters acquired in the period I'm interested in (so I don't know if ORDER BY timestamp is necessary for this), and then I would need an order BY cloud cover desc/asc so that the raster with the least cloud cover is at the top.

Right?

But there is one thing that still puzzles me. Is it possible for Mapserver to know when it can stop rendering tiles? Let's assume that I have such a raster:

How to make the mapserver generate data only where there is NoData in first raster, and when all the pixels are full (based on the rasters in the right order using order by) it will stop rendering, even if there were other rasters (because all of the pixels for requested extent are already associated with values)?

from mapserver.

There are two separate things: how Mapserver behave now and how it could behave for being more efficient. The current way is to use the painters algorithm.

so that the raster with the least cloud cover is at the top.

The pixels from this raster should go to the top of the resulting raster which means that by the painters method it must be rendered last, and that means that the image must appear last in the list that is returned by the "select" from the tileindex.

stop rendering, even if there were other rasters

It is not possible now. Painters algorithm keeps on rendering till the end of the image list. What Mapserver does is

• Render the first image from the tileindex stack; nodata remains nodata in the result
• Render the second image from the stack; pixels which are not nodata in image 2 are overwriting all the existing pixels in the rendered interim raster. Now the result contains pixels from image 2 where image 2 has valid data. In places which are nodata in image 2 the pixels originate from image 1. If the pixel was nodata in both image 1 and image 2 then the result has also nodata there.
• Render the 3rd image on top of the rendered image etc.
• Render the last image in the stack.

The painters algorithm is making a good end result: every pixel comes from the most valuable source image but it is heavy and slow if there are many overlapping images in the source image list that is selected from the tileindex.

from mapserver.

For comparison, Geoserver has an option named ExcessGranuleRemoval for avoiding unnecessary rendering. I found documentation from https://docs.geoserver.geo-solutions.it/edu/en/raster_data/mosaic_pyramid.html. The system filters out images (granules in Geoserver language) based on their footprints. Documentation about how Geoserver handles the footprints in https://docs.geoserver.geo-solutions.it/edu/en/raster_data/imagemosaic_footprint.html#geoserver-imagemosaic-footprint.

from mapserver.

@geographika Is there any possibility of implementing the LIMIT class in the near future?

from mapserver.

For comparison, Geoserver has an option named ExcessGranuleRemoval for avoiding unnecessary rendering. I found documentation from https://docs.geoserver.geo-solutions.it/edu/en/raster_data/mosaic_pyramid.html. The system filters out images (granules in Geoserver language) based on their footprints. Documentation about how Geoserver handles the footprints in https://docs.geoserver.geo-solutions.it/edu/en/raster_data/imagemosaic_footprint.html#geoserver-imagemosaic-footprint.

The possibility of stopping rendering data when every pixel is covered seems like an amazing performance improvement opportunity. I'm rooting for this to happen someday!

from mapserver.

@geographika Is there any possibility of implementing the LIMIT class in the near future?

@MathewNWSH - I'm not currently a user of the PostGIS drive so no plans on implementing this in the near future. You could try making the modifications and submitting a pull request, or sponsoring adding this feature to MapServer.

from mapserver.

Is the algorithm associated with the solution well known?

from mapserver.

Would it be possible to add MAXFEATURES_FIRST MAXFEATURES_LAST along with the MAXFEATURES keyword in order to select first/last N features? This would resolve the problem mentioned by @MathewNWSH

from mapserver.