<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div class=""><b class=""><font class="">Background (a bit of the business case for non </font>his<font class=""> users..ignore if required ;-)</font></b></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 17px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><font face="sans-serif" size="2" class="">Statistical Agencies traditionally publish statistical data related to statistical areas separately from the statistical boundaries that defines these areas. This creates a significant barrier to the use of these data in general mapping applications and more specialised spatial analysis systems. </font></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 17px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><font face="sans-serif" size="2" class="">To remove this barrier the Australian Bureau of Statistics (ABS) is evaluating the use of GeoPackage as an output format for facilitating the publication of both geospatial and statistical data. GeoPackage has a number of advantages one of the most significant is the ability to define database views. A database view is SQL statement that results in the creation of a “virtual” table that can be The use of </font><font face="sans-serif" size="2" class=""><b class="">database views</b></font><font face="sans-serif" size="2" class=""> within the GeoPackage database container provides a efficient mechanism to maintain the relationship (the relational join) between the statistical boundaries (“geometries”) and the statistical (“attribute") data. </font></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">By maintaining this relationship in a single published product this reduces the barrier to their use in mapping applications, reduces the probability of introduced error, increases the ease of use of statistical data in the community.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family: UICTFontTextStyleTallBody;font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class=""><b class="">The Problem...</b></font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">QGIS is not seem to fully support database views in GeoPackage. In some respects QGIS works as expected, but spatial and attribute selections and the display of the attribute table produce incorrect results.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class="">Example GeoPackage File:</span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class="">SA1_BCP_B01_05.gpkg (134 MB)</span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><a href="https://drive.google.com/open?id=0Bz44nz9iptC5NkF5WlpEZjBoSVk" class=""></a><a href="https://drive.google.com/open?id=0Bz44nz9iptC5NkF5WlpEZjBoSVk" class="">https://drive.google.com/open?id=0Bz44nz9iptC5NkF5WlpEZjBoSVk</a></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class="">Australian Bureau of Statistics 2011 Census Data</span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class="">Product: Basic Community Profile. Statistical Tables B01 to B05</span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">Images illustrating the issues</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><a href="https://drive.google.com/open?id=0Bz44nz9iptC5YjlrLV9vRFhmVE0" class=""></a><a href="https://drive.google.com/open?id=0Bz44nz9iptC5YjlrLV9vRFhmVE0" class="">https://drive.google.com/open?id=0Bz44nz9iptC5YjlrLV9vRFhmVE0</a></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><br class=""></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class="">Things that work...</span></span></div><div style="font-family: UICTFontTextStyleTallBody;font-size:14px;" class=""><span style="font-size: 12px;" class=""><br class=""></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">1. Views are identified as layers and can be loaded into the Layers Panel</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">2. Polygons are drawn as expected.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">3. The Identify Feature tool can be used to highlight and individual polygon and display the correct associated attribute data.</font></span></div><div style="font-family: UICTFontTextStyleTallBody;font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">4. Styling and classification algorithms can be applied to compose correct choropleth maps.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">Things that do not work...</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: UICTFontTextStyleTallBody;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">1. QGIS is not displaying the Attribute Table of a GeoPackage view correctly. Attribute values are repeated for a single row.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">2. Any Select operation that is applied either to the attributes or the geometries results in the whole layer being selected.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">In summary it looks like whilst the geometries of a GeoPackage view are being interpreted correctly the link to the underlying attributes in a view is not. I</font><font face="sans-serif" class="">ncidentally, neither the latest version of ArcGIS or MapInfo Profession can interpret views in GeoPackage either and this has been identified as a bug by both organisations.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><font face="sans-serif" class="">Any help from some one who knows their way around how GeoPackage is interpreting views would be gratefully received.</font></span></div><div style="font-family:'Helvetica Neue';font-size:14px;" class=""><span style="font-size: 12px;" class=""><span style="font-family: sans-serif;" class=""><br class=""></span></span></div><div style="font-family: sans-serif;" class="">Marcus.</div></body></html>