[Geoserver-devel] Feedback on GeoPackage plugin

GeoServer GeoServer Developer
,
1

Hi,

I've recently added read/write support in GDAL for GeoPackage rasters and did
try it a bit with the GeoPackage plugin of GeoServer.

I found several issues in the GeoServer plugin:

- it refuses to add some geopackages as raster datasources. For example
http://download.osgeo.org/gdal/data/geopackage/utm_jpeg_one_band.gpkg or
http://download.osgeo.org/gdal/data/geopackage/small_world_googlemapscompatible.gpkg.
An exception is generated when trying to publish the datasource ( see (1) at
bottom of this email). After a bit of trying, it seems that the issue is
encountered when the SRS of the GeoPackage is not EPSG:4326 as I could import
all my GeoPackage datasets with SRS=EPSG:4326 and none in other SRS.

- when previewing the
http://download.osgeo.org/gdal/data/geopackage/small_world_png.gpkg in the
OpenLayers previewer, I get strange artifacts on the tile of the eastern
hemisphere that go beyon longitude +180 (the actual data stops at longitude
+180 but the extent of the tile goes beyond with padding).

- a bit similar issue with
http://download.osgeo.org/gdal/data/geopackage/small_world_googlecrs84quad.gpkg,
still using OpenLayers previewer, only northern hemisphere can be displayed.
This is likely due to the WMTS googlecrs84quad tiling scheme used where the
extent of latitudes at zoom level 0 is [-180,180].

- when downloading that layer with format=geopackage in WMS, with
http://localhost:8080/geoserver/test/wms?service=WMS&version=1.1.0&request=GetMap&layers=test:small_world_png&styles=&bbox=-180.0,-90.0,180.0,90.0&width=660&height=330&srs=EPSG:4326&format=geopackage
I found the following issues:
   * typo in the gpkg_metadata table. The column "md_standard_uri" mandated by
the specification (see OGC® GeoPackage Encoding Standard) is
labelled "metadata_standard_URI" in the geopackage generated by GeoServer. In
the changelog of the specification, one can see :
"2013-11-21 R10 Paul Daisey Annex C, G Change metadata_standard_uri column
name to md_standard_uri"
   * more annoying: it seems that GeoServer uses the bottom left corner of the
tiling scheme as the origin for tile_row numbering, instead of the top left
corner.
   * not really an issue, but generated that output file is extremely slow,
like 30 seconds, and lot of noise done by harddisk :wink:

* actually when trying to display
http://download.osgeo.org/gdal/data/geopackage/small_world_googlecrs84quad_bigger.gpkg
with OpenLayers previewer, I get also the issue with the inverted tile_row
numbering. So the issue seems to exist on both reading and writing side.

Best regards,

Even

(1) :
java.lang.RuntimeException: Error occurred while building the resources for
the configuration page at
org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:330)
at org.geoserver.web.data.layer.NewLayerPage$7.onClick(NewLayerPage.java:261)
at org.geoserver.web.wicket.SimpleAjaxLink$1.onClick(SimpleAjaxLink.java:46)
at org.apache.wicket.ajax.markup.html.AjaxLink$1.onEvent(AjaxLink.java:68) at
org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
at
org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:300)
at
org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
at
org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
at
org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329) at
org.apache.wicket.RequestCycle.steps(RequestCycle.java:1436) at
org.apache.wicket.RequestCycle.request(RequestCycle.java:545) at
org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:484) at
org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:159)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
at
org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:28)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:75)
at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:71)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:71)
at
org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:46)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:50)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:54)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.geoserver.security.filter.GeoServerUserNamePasswordAuthenticationFilter.doFilter(GeoServerUserNamePasswordAuthenticationFilter.java:116)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at
org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:53)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
at
org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:135)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:76) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:42) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:48)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:44) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360) at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at
org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at
org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
at
org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at
org.mortbay.jetty.Server.handle(Server.java:324) at
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at
org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:828)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at
org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at
org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
Caused by: java.lang.IllegalArgumentException: L'enveloppe doit avoir au moins
deux dimensions et ne pas être vide. at
org.geotools.coverage.grid.GridCoverage2D.<init>(GridCoverage2D.java:280) at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:592)
at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:516)
at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:457)
at org.geotools.geopkg.mosaic.GeoPackageReader.read(GeoPackageReader.java:257)
at
org.geoserver.catalog.CoverageDimensionCustomizerReader.read(CoverageDimensionCustomizerReader.java:227)
at
org.geoserver.catalog.SingleGridCoverage2DReader.read(SingleGridCoverage2DReader.java:141)
at org.geoserver.catalog.CatalogBuilder.buildCoverage(CatalogBuilder.java:968)
at org.geoserver.catalog.CatalogBuilder.buildCoverage(CatalogBuilder.java:856)
at
org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:320)
... 97 more

--
Spatialys - Geospatial professional services
http://www.spatialys.com

2

Hi,

I didn¹t try GeoPackage plugin in GeoServer, I¹ve played with Geopackage
in GDAL and creating a geopackage db via ogr2ogr was slow as well.

In my opinion it would be save for both GDAL as well as the GeoPackage
plugin to change the SQLite journaling mode to WAL and turn file
synchronization off (at least when creating databases). C Example:

sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
    sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, NULL);

This must be done right after opening the database. The the insert /
delete performance difference is enorm (several orders of magnitude
compared to non-transactional inserts and also twice or three times faster
for transactions).

A speedup between 100x - 1000x is not unlikely. This comes of course at
the slightly increased cost of db corruption (see also [1,2]). But even
then, it¹s debatable whether for the db utilization of both GeoServer and
GDAL this can be configuration enabled globally for all operations: rare
updates, still journaling enabled though with backup point from last disk
write time compared to constant disk sync journaling for each op.

Cheers
Ben

[1] https://www.sqlite.org/wal.html
[2] http://www.sqlite.org/pragma.html#pragma_journal_mode

Am 10.12.14 23:41 schrieb "Even Rouault" unter
<even.rouault@anonymised.com>:

  * not really an issue, but generated that output file is extremely
slow,
like 30 seconds, and lot of noise done by harddisk :wink:

3

Hi Benjamin,

Your estimate about 100x - 1000x speedup makes me suspicious but I will be happily surprised if it happens to be true.

I would like to get some sample data and exact timings from your test for repeating them. I mean from what you wrote " I¹ve played with Geopackage in GDAL and creating a geopackage db via ogr2ogr was slow as well." Did you compare the speed with creating a SpatiaLite db with ogr2ogr instead?

-Jukka Rahkonen-

________________________________________
Stadin, Benjamin wrote:

Hi,

I didn¹t try GeoPackage plugin in GeoServer, I¹ve played with Geopackage
in GDAL and creating a geopackage db via ogr2ogr was slow as well.

In my opinion it would be save for both GDAL as well as the GeoPackage
plugin to change the SQLite journaling mode to WAL and turn file
synchronization off (at least when creating databases). C Example:

sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
    sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, NULL);

This must be done right after opening the database. The the insert /
delete performance difference is enorm (several orders of magnitude
compared to non-transactional inserts and also twice or three times faster
for transactions).

A speedup between 100x - 1000x is not unlikely. This comes of course at
the slightly increased cost of db corruption (see also [1,2]). But even
then, it¹s debatable whether for the db utilization of both GeoServer and
GDAL this can be configuration enabled globally for all operations: rare
updates, still journaling enabled though with backup point from last disk
write time compared to constant disk sync journaling for each op.

Cheers
Ben

[1] https://www.sqlite.org/wal.html
[2] http://www.sqlite.org/pragma.html#pragma_journal_mode

Am 10.12.14 23:41 schrieb "Even Rouault" unter
<even.rouault@anonymised.com>:

  * not really an issue, but generated that output file is extremely
slow,
like 30 seconds, and lot of noise done by harddisk :wink:

------------------------------------------------------------------------------
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

4

Le jeudi 11 décembre 2014 00:40:56, Stadin, Benjamin a écrit :

Hi,

I didn¹t try GeoPackage plugin in GeoServer, I¹ve played with Geopackage
in GDAL and creating a geopackage db via ogr2ogr was slow as well.

In my opinion it would be save for both GDAL as well as the GeoPackage
plugin to change the SQLite journaling mode to WAL and turn file
synchronization off (at least when creating databases). C Example:

sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);

This can actually be done by adding "--config OGR_SQLITE_SYNCHRONOUS OFF" to
the gdal_translate/ogr2ogr command line (see "Performance hints" section of
SQLite / Spatialite RDBMS — GDAL documentation), and is what I practice regularly, and yes it
gives a huge performance boost.

    sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, NULL);

AFAIR using WAL uses a slightly different storage that makes it incompatible
with ancient sqlite3 versions.
In my tests synchronous = off gives enough performance boost.

This must be done right after opening the database. The the insert /
delete performance difference is enorm (several orders of magnitude
compared to non-transactional inserts and also twice or three times faster
for transactions).

A speedup between 100x - 1000x is not unlikely. This comes of course at
the slightly increased cost of db corruption (see also [1,2]). But even
then, it¹s debatable whether for the db utilization of both GeoServer and
GDAL this can be configuration enabled globally for all operations: rare
updates, still journaling enabled though with backup point from last disk
write time compared to constant disk sync journaling for each op.

Yes perhaps I should consider turning synchronous = off by default.

Cheers
Ben

[1] Write-Ahead Logging
[2] Pragma statements supported by SQLite

Am 10.12.14 23:41 schrieb "Even Rouault" unter

<even.rouault@anonymised.com>:
> * not really an issue, but generated that output file is extremely
>
>slow,
>like 30 seconds, and lot of noise done by harddisk :wink:

--
Spatialys - Geospatial professional services
http://www.spatialys.com

5

On Wed, Dec 10, 2014 at 11:41 PM, Even Rouault <even.rouault@anonymised.com>
wrote:

Hi,

I've recently added read/write support in GDAL for GeoPackage rasters and
did
try it a bit with the GeoPackage plugin of GeoServer.

Thanks for the feedback.
I do hope that the people that worked on it will follow up, but mind, the
module
is in unsupported land, so there is no guarantee it will happen, the modules
in the "community" section are either new or without an official maintaner,
sometimes a bit
of both at the same time.

- when previewing the
http://download.osgeo.org/gdal/data/geopackage/small_world_png.gpkg in the
OpenLayers previewer, I get strange artifacts on the tile of the eastern
hemisphere that go beyon longitude +180 (the actual data stops at longitude
+180 but the extent of the tile goes beyond with padding).

In version 1.6.0 we added map wrapping to raster data as well, to follow
suit with
the already available vector one, so it's normal that the data gets
replicated on
the other side of the dateline.

However, by your description it seems that it's not working as it should in
your case.
When I use one of natural earth tiffs and zoom out, I get the following,
which is the
expected behavior:

Cheers
Andrea
​

--

GeoServer Professional Services from the experts! Visit
http://goo.gl/NWWaa2 for more information.

Please, notice that GeoSolutions will be closed for seasonal holidays
from December the 24th to January the 6th

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it

*AVVERTENZE AI SENSI DEL D.Lgs. 196/2003*

Le informazioni contenute in questo messaggio di posta elettronica e/o
nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il
loro utilizzo è consentito esclusivamente al destinatario del messaggio,
per le finalità indicate nel messaggio stesso. Qualora riceviate questo
messaggio senza esserne il destinatario, Vi preghiamo cortesemente di
darcene notizia via e-mail e di procedere alla distruzione del messaggio
stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso,
divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od
utilizzarlo per finalità diverse, costituisce comportamento contrario ai
principi dettati dal D.Lgs. 196/2003.

The information in this message and/or attachments, is intended solely for
the attention and use of the named addressee(s) and may be confidential or
proprietary in nature or covered by the provisions of privacy act
(Legislative Decree June, 30 2003, no.196 - Italy's New Data Protection
Code).Any use not in accord with its purpose, any disclosure, reproduction,
copying, distribution, or either dissemination, either whole or partial, is
strictly forbidden except previous formal approval of the named
addressee(s). If you are not the intended recipient, please contact
immediately the sender by telephone, fax or e-mail and delete the
information in this message that has been received in error. The sender
does not give any warranty or accept liability as the content, accuracy or
completeness of sent messages and accepts no responsibility for changes
made after they were sent or for other risks which arise as a result of
e-mail transmission, viruses, etc.

-------------------------------------------------------

(attachments)

ne_wrapped.jpg

6

Those must be very ancient (> 5 years or so). But if so, it¹s post
performance speedup comes indeed from synchronous = off.

Am 11.12.14 09:40 schrieb "Even Rouault" unter
<even.rouault@anonymised.com>:

    sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, NULL);

AFAIR using WAL uses a slightly different storage that makes it
incompatible
with ancient sqlite3 versions.
In my tests synchronous = off gives enough performance boost.

7

Hi Jukka,

I didn’t do any concrete timings this time, but I use these settings for a
mobile project since years where I once did concrete timings (differential
database updates on Android and iOS). It of course varies with the
particular database, indices etc.

See also some performance measurements of different journal and
synchronization modes here:
http://stackoverflow.com/questions/1711631/improve-insert-per-second-perfor
mance-of-sqlite

Cheers
Ben

Am 11.12.14 07:04 schrieb "Rahkonen Jukka (Tike)" unter
<jukka.rahkonen@...1245...>:

Hi Benjamin,

Your estimate about 100x - 1000x speedup makes me suspicious but I will
be happily surprised if it happens to be true.

I would like to get some sample data and exact timings from your test for
repeating them. I mean from what you wrote " I¹ve played with Geopackage
in GDAL and creating a geopackage db via ogr2ogr was slow as well." Did
you compare the speed with creating a SpatiaLite db with ogr2ogr instead?

-Jukka Rahkonen-

________________________________________
Stadin, Benjamin wrote:

Hi,

I didn¹t try GeoPackage plugin in GeoServer, I¹ve played with Geopackage
in GDAL and creating a geopackage db via ogr2ogr was slow as well.

In my opinion it would be save for both GDAL as well as the GeoPackage
plugin to change the SQLite journaling mode to WAL and turn file
synchronization off (at least when creating databases). C Example:

sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, NULL);
   sqlite3_exec(db, "PRAGMA journal_mode = WAL", NULL, NULL, NULL);

This must be done right after opening the database. The the insert /
delete performance difference is enorm (several orders of magnitude
compared to non-transactional inserts and also twice or three times faster
for transactions).

A speedup between 100x - 1000x is not unlikely. This comes of course at
the slightly increased cost of db corruption (see also [1,2]). But even
then, it¹s debatable whether for the db utilization of both GeoServer and
GDAL this can be configuration enabled globally for all operations: rare
updates, still journaling enabled though with backup point from last disk
write time compared to constant disk sync journaling for each op.

Cheers
Ben

[1] https://www.sqlite.org/wal.html
[2] http://www.sqlite.org/pragma.html#pragma_journal_mode

Am 10.12.14 23:41 schrieb "Even Rouault" unter
<even.rouault@...4157...>:

  * not really an issue, but generated that output file is extremely
slow,
like 30 seconds, and lot of noise done by harddisk :wink:

--------------------------------------------------------------------------
----
Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clkt
rk
_______________________________________________
Geoserver-devel mailing list
Geoserver-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

8

Thanks for the great feedback Evan. Raster support in the GeoTools/GeoServer geopackage driver was done with much less certainty than the vector stuff. I am not even 100% sure it is compliant with the latest spec as it was implemented before the spec got to final 1.0. Going to wade through this list and start filing bug reports in our JIRA.

···

On Wed, Dec 10, 2014 at 2:41 PM, Even Rouault <even.rouault@anonymised.com> wrote:

Hi,

I’ve recently added read/write support in GDAL for GeoPackage rasters and did
try it a bit with the GeoPackage plugin of GeoServer.

I found several issues in the GeoServer plugin:

  • typo in the gpkg_metadata table. The column “md_standard_uri” mandated by
    the specification (see http://www.geopackage.org/spec/#_metadata_table) is
    labelled “metadata_standard_URI” in the geopackage generated by GeoServer. In
    the changelog of the specification, one can see :
    “2013-11-21 R10 Paul Daisey Annex C, G Change metadata_standard_uri column
    name to md_standard_uri”

  • more annoying: it seems that GeoServer uses the bottom left corner of the
    tiling scheme as the origin for tile_row numbering, instead of the top left
    corner.

  • not really an issue, but generated that output file is extremely slow,
    like 30 seconds, and lot of noise done by harddisk :wink:

  • actually when trying to display
    http://download.osgeo.org/gdal/data/geopackage/small_world_googlecrs84quad_bigger.gpkg
    with OpenLayers previewer, I get also the issue with the inverted tile_row
    numbering. So the issue seems to exist on both reading and writing side.

Best regards,

Even

(1) :
java.lang.RuntimeException: Error occurred while building the resources for
the configuration page at
org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:330)
at org.geoserver.web.data.layer.NewLayerPage$7.onClick(NewLayerPage.java:261)
at org.geoserver.web.wicket.SimpleAjaxLink$1.onClick(SimpleAjaxLink.java:46)
at org.apache.wicket.ajax.markup.html.AjaxLink$1.onEvent(AjaxLink.java:68) at
org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
at
org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:300)
at
org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:119)
at
org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
at
org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1250)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1329) at
org.apache.wicket.RequestCycle.steps(RequestCycle.java:1436) at
org.apache.wicket.RequestCycle.request(RequestCycle.java:545) at
org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:484) at
org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.springframework.web.servlet.mvc.ServletWrappingController.handleRequestInternal(ServletWrappingController.java:159)
at
org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at
org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at
javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
at
org.geoserver.filters.ThreadLocalsCleanupFilter.doFilter(ThreadLocalsCleanupFilter.java:28)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:75)
at org.geoserver.wms.animate.AnimatorFilter.doFilter(AnimatorFilter.java:71)
at
org.geoserver.filters.SpringDelegatingFilter$Chain.doFilter(SpringDelegatingFilter.java:71)
at
org.geoserver.filters.SpringDelegatingFilter.doFilter(SpringDelegatingFilter.java:46)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.platform.AdvancedDispatchFilter.doFilter(AdvancedDispatchFilter.java:50)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:311)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
at
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerAnonymousAuthenticationFilter.doFilter(GeoServerAnonymousAuthenticationFilter.java:54)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.geoserver.security.filter.GeoServerUserNamePasswordAuthenticationFilter.doFilter(GeoServerUserNamePasswordAuthenticationFilter.java:116)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:146)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:69)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
at
org.geoserver.security.filter.GeoServerSecurityContextPersistenceFilter$1.doFilter(GeoServerSecurityContextPersistenceFilter.java:53)
at
org.geoserver.security.filter.GeoServerCompositeFilter$NestedFilterChain.doFilter(GeoServerCompositeFilter.java:73)
at
org.geoserver.security.filter.GeoServerCompositeFilter.doFilter(GeoServerCompositeFilter.java:92)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:323)
at
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:173)
at
org.geoserver.security.GeoServerSecurityFilterChainProxy.doFilter(GeoServerSecurityFilterChainProxy.java:135)
at
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:76) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:42) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.geoserver.filters.SessionDebugFilter.doFilter(SessionDebugFilter.java:48)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.geoserver.filters.FlushSafeFilter.doFilter(FlushSafeFilter.java:44) at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at
org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:109)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360) at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216) at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181) at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726) at
org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405) at
org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
at
org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152) at
org.mortbay.jetty.Server.handle(Server.java:324) at
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505) at
org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:828)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:514) at
org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211) at
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380) at
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
at
org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
Caused by: java.lang.IllegalArgumentException: L’enveloppe doit avoir au moins
deux dimensions et ne pas être vide. at
org.geotools.coverage.grid.GridCoverage2D.(GridCoverage2D.java:280) at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:592)
at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:516)
at
org.geotools.coverage.grid.GridCoverageFactory.create(GridCoverageFactory.java:457)
at org.geotools.geopkg.mosaic.GeoPackageReader.read(GeoPackageReader.java:257)
at
org.geoserver.catalog.CoverageDimensionCustomizerReader.read(CoverageDimensionCustomizerReader.java:227)
at
org.geoserver.catalog.SingleGridCoverage2DReader.read(SingleGridCoverage2DReader.java:141)
at org.geoserver.catalog.CatalogBuilder.buildCoverage(CatalogBuilder.java:968)
at org.geoserver.catalog.CatalogBuilder.buildCoverage(CatalogBuilder.java:856)
at
org.geoserver.web.data.layer.NewLayerPage.buildLayerInfo(NewLayerPage.java:320)
… 97 more

–
Spatialys - Geospatial professional services
http://www.spatialys.com

Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
from Actuate! Instantly Supercharge Your Business Reports and Dashboards
with Interactivity, Sharing, Native Excel Exports, App Integration & more
Get technology previously reserved for billion-dollar corporations, FREE
http://pubads.g.doubleclick.net/gampad/clk?id=164703151&iu=/4140/ostg.clktrk

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

–

Justin Deoliveira
VP Engineering | Boundless
jdeolive@anonymised.com
@boundlessgeo