Hi, Conditional +1 for making GeoPackage into core. However, before giving a more official status for it I suggest to make sure that geopackage data store supports the use of spatial index in BBOX queries. Otherwise users who try to use big geopackage as datastore for WMS will see suboptimal performance and believe that GeoPackage itself is slow because the service is slow.
I do see code about adding the spatial index support in https://github.com/geotools/geotools/commit/171bfb8931ca2866bd55aa2309770f88c3b37cf4#diff-0adb69381cfbeb460b956e5c794f7051. However, I made a test with GS 2.11 snapshot today and by use experience I feel that spatial index is not really used. I am not sure if logging at Geotools developer level is logging all the SQL because the log shows me just this 2017-08-18 10:48:31,215 DEBUG [org.geotools.rendering] - Querying layer http://www.openplans.org/topp:Jarvi with bbox: ReferencedEnvelope[520694.10973698314 : 529214.8293650263, 7152639.246868316 : 7167382.654399831] 2017-08-18 10:48:31,215 DEBUG [org.geotools.jdbc] - CREATE CONNECTION 2017-08-18 10:48:31,216 DEBUG [org.geotools.jdbc] - SELECT "fid","geom" as "geom" FROM "Jarvi" I suggest developers to have a look at the spatial index and also read http://erouault.blogspot.fi/2017/03/dealing-with-huge-vector-geopackage.html and analyze if suggestion for using JOIN instead of subquery makes sense. I have made some tests with tables which have less than 1 million features and I did not feel a big difference in speed but SQLite does make different query plans for those two. This is the plan with JOIN SCAN TABLE rtree_ranta10jarvi_geom AS r VIRTUAL TABLE INDEX 2:C0E1C2E3 SEARCH TABLE ranta10jarvi AS t USING INTEGER PRIMARY KEY (rowid=?) and this is the plan with subquery SEARCH TABLE ranta10jarvi USING INTEGER PRIMARY KEY (rowid=?) EXECUTE LIST SUBQUERY 1 SCAN TABLE rtree_ranta10jarvi_geom VIRTUAL TABLE INDEX 2:C0E1C2E3 Geoserver should require that spatial tables have R-Tree indexes and create them if they are missing. It will be harder to handle views, see https://github.com/opengeospatial/geopackage/issues/207. In simple cases views can be handled and QGIS knows how to do it http://osgeo-org.1560.x6.nabble.com/gdal-dev-GeoPackege-views-and-spatial-index-td5312752.html. -Jukka Rahkonen- ________________________________________ Lähettäjä: Brad Hards <br...@frogmouth.net> Lähetetty: 18. elokuuta 2017 1:14 Vastaanottaja: 'Jody Garnett' Kopio: 'Geoserver-devel'; 'Andrea Aime' Aihe: Re: [Geoserver-devel] Crazy idea... what about GeoPackage straight in core for 2.12? > I like the idea of making geopackage core - because it is a good idea. +1 > Brad when someone does have a chance to work on 1.2 is implemented it can > start off life as a community module, and move into core when ready. We > would need to support both versions right? We would, but it probably won't be as a community module - its probably a variation on the geopackage implementation we already have. My thinking is that we don't want two implementations - users shouldn't need to know what kind of geopackage they have, they just want to throw it against the geoserver wall and have it stick in the right place. Brad ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Geoserver-devel mailing list Geoserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-devel ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Geoserver-devel mailing list Geoserver-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/geoserver-devel
