https://issues.qgis.org/https://issues.qgis.org/favicon.ico2016-06-19T09:21:41ZQGIS Issue TrackingQGIS Application - Bug report #15082: SpatiaLite index issuehttps://issues.qgis.org/issues/15082?journal_id=710382016-06-19T09:21:41ZR. R.inactive@the-moment.org
<ul><li><strong>File</strong> <a href="/attachments/download/10046/15082.mp4">15082.mp4</a> added</li></ul> QGIS Application - Bug report #15082: SpatiaLite index issuehttps://issues.qgis.org/issues/15082?journal_id=710432016-06-20T00:49:54ZJukka Rahkonenjukka.rahkonen@mmmtike.fi
<ul></ul><p>The issue is real but how to improve the current situation needs some thinking because spatial index in SpatiaLite/GeoPackage is not an index but a table with some well known triggers.</p>
<p>The list of true indexes from SQLite can be obtained with a PRAGMA command</p>
<p><code>PRAGMA index_list('table_name')</code></p>
<p>I suppose that the Indexes tab in Layer properties makes exactly that request.</p>
<p>From SpatiaLite databases created with recent versions the existence of the spatial index is supposed to be checked from the geometry columns table which has a column "spatial_index_enabled".</p>
<p><code>SELECT ROWID, "f_table_name", "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled" <br />FROM "geometry_columns" <br />ORDER BY ROWID</code></p>
<p>If user is pressing "Create spatial index" button and spatial index already exists then the application should perhaps make a question:<br />"Table xxx has already a spatial index. Do you want to recover it?" If user says "Yes" then RecoverSpatialIndex function would be performed.</p>
<p>For the GeoPackage case I suppose that if spatial index exists or not should be checked with the test that is included in the standard, on page 75:</p>
<p>@A.3.1.3 Spatial Indexes<br />A.3.1.3.1 Data<br />A.3.1.3.1.1 Spatial Indexes Implementation</p>
<p>...snip...</p>
<p>3) For each row table_name, column_name from step 1<br />a. SELECT sql FROM sqlite_master WHERE tbl_name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name<br />b. Not testable if result set is empty<br />c. Fail if returned sql != ‘CREATE VIRTUAL TABLE rtree_’ ’ || result_set_table_name || ‘_’ || result_set_column_name || USING rtree(id, minx, maxx, miny, maxy)<br />d. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND tname = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_insert’<br />e. Fail if returned sql != result of populating insert triggers template in Annex L using result_set_table_name for <t> and result_set_column_name for <c><br />f. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND name LIKE ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_update%’<br />g. Fail if returned sql != result of populating 4 update triggers templates in Annex L using result_set_table_name for <t> and result_set_column_name for <c><br />h. SELECT sql FROM sqlite_master WHERE type=’trigger’ AND name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_delete’<br />i. Fail if returned sql != result of populating delete trigger template in Annex L using result_set_table_name for <t> and result_set_column_name for <c><br />j. Log pass otherwise<br />4) Pass if logged pass and no fails@</p> QGIS Application - Bug report #15082: SpatiaLite index issuehttps://issues.qgis.org/issues/15082?journal_id=777372017-04-30T23:05:19ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Easy fix?</strong> set to <i>No</i></li><li><strong>Regression?</strong> set to <i>No</i></li></ul> QGIS Application - Bug report #15082: SpatiaLite index issuehttps://issues.qgis.org/issues/15082?journal_id=1025962019-03-09T15:23:21ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Resolution</strong> set to <i>end of life</i></li><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li></ul><p><strong>End of life notice: QGIS 2.18 LTR</strong></p>
<p><strong>Source:</strong><br /><a class="external" href="http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/">http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/</a></p>