Sounds good to me

Ian

On 7 March 2018 at 14:59, Nuno Oliveira <nuno.olive...@geo-solutions.it>
wrote:

> Hi all,
>
> Sorry for the cross porting but this touches the two projects ...
>
> I would like to extend the current support of SQL views (Virtual Tables)
> to
> allow us to add a placeholder for the where clause where GeoServer will
> add the needed filter.
>
> This comes handy (better performance) when we have extra operations that
> can  be done on top of the rows filtered with the GeoServer filter first.
>
> Consider the following use case: we have several meteorological stations
> that have several measurements (wind speed, temperature, humidity,
> etc ..) and for each one we are only interested in the last measurement for
> each available measurement type.
>
> We can create a query that returns exactly what we want:
>
> SELECT STATION_NAME,
>        MEASUREMENT,
>        MEASUREMENT_TYPE,
>        LOCATION
> FROM
>   (SELECT st.common_name AS STATION_NAME,
>           ob.value AS MEASUREMENT,
>           pr.param_name AS MEASUREMENT_TYPE,
>           st.position AS LOCATION,
>           *ROW_NUMBER() OVER(PARTITION BY st.id <http://st.id>,
> pr.param_name*
> *                            ORDER BY ob.time DESC) AS RANK*
>    FROM meteo.meteo_stations st
>    LEFT JOIN meteo.meteo_observations ob ON st.id = ob.station_id
>    LEFT JOIN meteo.meteo_parameters pr ON ob.parameter_id = pr.id) AS
> stations
> WHERE RANK = 1;
>
> The issue is that this query will NOT be as efficient as it can be when
> used with GeoServer,
> actually the performance will be quite bad. This happens because GeoServer
> will add
> is WHERE clause around the query above, which means that the ranking will
> happen
> for all the data set first and only after we will filter what we don't
> need.
>
> The following query that uses the :where_clause: placeholder will do the
> opposite, we will first
> filter what we don't want and then do the ranking:
>
> SELECT STATION_NAME,
>        MEASUREMENT,
>        MEASUREMENT_TYPE,
>        LOCATION
> FROM
>   (SELECT STATION_NAME,
>           MEASUREMENT,
>           MEASUREMENT_TYPE,
>           LOCATION,
>           *ROW_NUMBER() OVER(PARTITION BY STATION_ID, MEASUREMENT_TYPE*
> *                            ORDER BY TIME DESC) AS RANK*
>    FROM
>      (SELECT st.id AS STATION_ID,
>              st.common_name AS STATION_NAME,
>              ob.value AS MEASUREMENT,
>              pr.param_name AS MEASUREMENT_TYPE,
>              ob.time AS TIME,
>              st.position AS LOCATION
>       FROM meteo.meteo_stations st
>       LEFT JOIN meteo.meteo_observations ob ON st.id = ob.station_id
>       LEFT JOIN meteo.meteo_parameters pr ON ob.parameter_id = pr.id
>       WHERE 1 = 1 *:where_clause:*) AS stations_filtered) AS stations
> WHERE RANK = 1;
>
> This will make the usage of some SQL analytic functions affordable
> (performance wise) with GeoServer.
>
> The changes in the code are quite small (a few lines), and this is an
> additive change fully backward compatible.
>
> Any objection ? comments ?
>
> Regards,
>
> Nuno Oliveira
>
> --
> Regards,
> Nuno Oliveira
> ==
> GeoServer Professional Services from the experts! Visit http://goo.gl/it488V 
> for more information.
> ==
>
> Nuno Miguel Carvalho Oliveira
> @nmcoliveira
> Software Engineer
>
> GeoSolutions S.A.S.
> Via di Montramito 3/A
> 55054  Massarosa (LU)
> Italy
> phone: +39 0584 962313 <+39%200584%20962313>
> fax:      +39 0584 1660272 <+39%200584%20166%200272>
> http://www.geo-solutions.ithttp://twitter.com/geosolutions_it
>
> -------------------------------------------------------
> AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
> Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i 
> file/s allegato/i sono da considerarsi strettamente riservate. Il loro 
> utilizzo è consentito esclusivamente al destinatario del messaggio, per le 
> finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio 
> senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia 
> via e-mail e di procedere alla distruzione del messaggio stesso, 
> cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo 
> anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per 
> finalità diverse, costituisce comportamento contrario ai principi dettati dal 
> D.Lgs. 196/2003.
>
> The information in this message and/or attachments, is intended solely for 
> the attention and use of the named addressee(s) and may be confidential or 
> proprietary in nature or covered by the provisions of privacy act 
> (Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection 
> Code).Any use not in accord with its purpose, any disclosure, reproduction, 
> copying, distribution, or either dissemination, either whole or partial, is 
> strictly forbidden except previous formal approval of the named addressee(s). 
> If you are not the intended recipient, please contact immediately the sender 
> by telephone, fax or e-mail and delete the information in this message that 
> has been received in error. The sender does not give any warranty or accept 
> liability as the content, accuracy or completeness of sent messages and 
> accepts no responsibility  for changes made after they were sent or for other 
> risks which arise as a result of e-mail transmission, viruses, etc.
>
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> GeoTools-Devel mailing list
> geotools-de...@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geotools-devel
>
>


-- 
Ian Turton
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

Reply via email to