<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.Shkpostityyli17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I wonder if it could be possible to do something like “DROP CASCADING” in GeoPackage and delete the spatial index by the same. It will for sure be unusable once the table is dropped. I feel that SQLite does not support
that because cascading drop seems to require that parent and child tables have foreign key relation, but RTree index which is created through the Virtual RTRee does not offer any way for defining the foreign key.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I know I can delete the index with another SQL request with ogrinfo<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo –sql “drop table bar” foo.gpkg<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo –sql “drop table rtree_bar_geom” foo.gpkg<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Metadata tables are not cleaned automatically and two more commands would be needed:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo –sql “delete from gpkg_contents where table_name=’bar’ and identifier=’bar’” foo.gpkg<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo –sql “delete from gpkg_geometry_columns where table_name=’bar’ and column_name=’geom’” foo.gpkg<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">However, it is impossible to run the latter one because GPKG database is at this moment in a bad shape<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo -sql "delete from gpkg_geometry_columns where table_name=bar' and column_name='geom'" foo.gpkg<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ERROR 1: pragma foreign_key_check on 'foo.gpkg' failed<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ERROR 1: bad result for PRAGMA foreign_key_check, got 1 rows, expected 0<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ERROR 1: pragma foreign_key_check on 'foo.gpkg' failed<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">FAILURE:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Unable to open datasource `foo.gpkg' with the following drivers.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">…<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I can delete that row from gpkg_geometry_columns with spatialite_gui.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">So what is the right procedure and order for dropping a table from GeoPackage with ogrinfo?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
</div>
</body>
</html>