<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<font face="Times New Roman, Times, serif">Just a hunch, Ronnie,
would it hel to use PostGIS inheritance: creating master and child
tables and using the ones you need for particular zoom levels?<br>
<br>
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/9.1/static/tutorial-inheritance.html">http://www.postgresql.org/docs/9.1/static/tutorial-inheritance.html</a><br>
<br>
Jan<br>
<br>
</font>
<div class="moz-cite-prefix">On 04/17/2013 10:41 AM, Lassche, M.R.
wrote:<br>
</div>
<blockquote
cite="mid:9ABA1C68F57D824A92DE4EA3D23AABB677E6FA9A@PEXMB002A.vu.local"
type="cite">
<pre wrap="">Bernard,
Yes there are spatial indexes in place. The get extent is slow because building the result set for the whole table (9 million rows with point geometry) is slow.
What I do now is a multi step approach
1 - alter the query in such a way that no rows are returned - simple by adding a where condition that is never true e.g where 1=2
2- opening the layer in QGis, gives me no results
4 - zoom in to the desired region
5 - alter the view so the query does return rows
6 - refresh the data layer.
This does work, but is kind of a hassle.
Ronnie
-----Oorspronkelijk bericht-----
Van: Bernhard Ströbl [<a class="moz-txt-link-freetext" href="mailto:bernhard.stroebl@jena.de">mailto:bernhard.stroebl@jena.de</a>]
Verzonden: woensdag 17 april 2013 10:29
Aan: <a class="moz-txt-link-abbreviated" href="mailto:qgis-user@lists.osgeo.org">qgis-user@lists.osgeo.org</a>
Onderwerp: Re: [Qgis-user] opening multitable postgis view is slow
Hi Ronnie,
do you have a spatial index on the geometry columns of the tables contained in the view?
(just guessing)
Bernhard
Am 17.04.2013 10:26, schrieb Lassche, M.R.:
</pre>
<blockquote type="cite">
<pre wrap="">Hi all,
When loading multi table views in QGis it always starts with determining the extent of the result set.
With pg_stat_activity you can see the following query running for a long time: SELECT st_extent("geom") FROM "bag"."vw_bag_pand_functie"
Does anyone has ideas of how to speed up the loading this dataset in QGis. The view is as efficient as can be, it will normally returns something like 9 million rows for the whole of the Netherlands, but normally I zoom in to a region of interest and then there is no need of first establishing the extent of the whole data set.
All suggestions are welcome
Ronnie
</pre>
</blockquote>
<pre wrap="">
__________ Information from ESET Mail Security, version of virus signature database 8236 (20130417) __________
The message was checked by ESET Mail Security.
<a class="moz-txt-link-freetext" href="http://www.eset.com">http://www.eset.com</a>
_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
</pre>
</blockquote>
<br>
</body>
</html>