On Mon, Mar 20, 2017 at 6:34 PM, Gabriel Vatin <gabriel.vatin@anonymised.com>
wrote:
Thanks Andrea for the quick answer. What I can't really guess for now, is
when and how is the SQL view executed : does the BBOX attribute of GetMap
queries affect this SQL view (when no parameter in the view definition) ?
Yes, just like any other table. With a plain table the query composed is:
select <fields> from <theTable> where <Filters>
where <filters> comes from the OGC request, that is, the bbox, time,
elevation, and the SLD itself.
When working off a sql view it becomes:
select <fields> from (<theSqlViewDefinition>) as _gt_table where <Filters>
In other words, GeoServer uses the SQL you provided verbatim (could be a
stored procedure call too, or whatever database specific mechanism returns
a set of rows) and then applies filters on it.
This should not be interpreted as running sql view definition first and
then filtering on top of it, it's up to the database to figure out an
optimal execution order, depending on the sql involved and database
abilities the bbox filter might be run first before doing the joins in your
sql, and so on. See also https://en.wikipedia.org/wiki/Query_plan
The type of query to create a new layer is done so : I have a whole "goi"
table with attributes such as name etc., and a table with all geometries
(stored in geo_data column, which is a Geometry,4326 with GIST index). Each
layers of my GeoServer changes with the "got_id" (layer theme ID), stored
on the goi table. Here is the whole view
SELECT id, id_ori, name, geo_data
FROM geometrie
LEFT JOIN goi USING (goi_id)
WHERE got_id = 105
See Jonathan's mail about getting the full query and running an explain.
It might just well be that you have index statistics that are out of date,
too, running a "analyze" might help:
https://www.postgresql.org/docs/9.1/static/sql-analyze.html
Cheers
Andrea
--
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via di Montramito 3/A
55054 Massarosa (LU)
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://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.
-------------------------------------------------------
