Hi folks,
This is primarily a question for the developers.
I've been experimenting with setting up an SQL view layer backed by a very large database table (hundreds of millions of rows), specifically with a view to configuring the TIME dimension. It turns out this is infeasible because:
1. For every service request (capabilities documents, WMS GetMap, etc.) a query like 'SELECT ... FROM (<SQL view definition>) AS vtable' is executed which in the case of large tables brings GeoServer (and possibly the database server) to its knees due to the number of rows encompassed by <SQL view definition>.
2. There is no way of configuring a limit to the length of interval for which data may be requested in a data request (e.g. WMS GetMap).
Firstly, is it possible for the queries to be constructed in a way that more directly reflects the view definition specified in the web admin interface (i.e. that doesn't attempt to extract the entire view)? This would potentially allow SQL view layers to be backed by large tables, assuming the admin specifies the view in such a way as to make use of relevant indexes.
For capabilities requests where the aim was to establish the bounding dates of availability, the query would be the same as <SQL view definition> except that the field list would be 'SELECT MIN(<column>), MAX(<column>)' where <column> is the name of the attribute the admin has configured the TIME dimension against.
For data requests, the query would be the same as <SQL view definition> but it would add necessary constraints on the above <column> to the WHERE clause, based on the TIME parameter included with the request (or the default value if omitted).
Secondly, could the web admin interface provide some controls to allow optional specification of a maximum interval length for which data may be requested? Do the OGC standards allow for reporting this in capabilities documents if applicable?
Regards,
Andrew