implement SQL over HTTP

[mmomtchev]

I am the author of sqlite-wasm-http - an HTTP VFS driver for SQLite WASM - the new official WASM distribution from the SQLite team.

I have used it to create an Openlayers plugin that can render maps in remote .mbtiles sources over HTTP - ol-mbtiles. It has an absolutely amazing performance - even when interacting with multi-gigabytes remote packages.

I have also created a DbAdapter backend for geopackage-js - this one is however unusable at the moment because of its abysmal performance. geopackage-js takes a much more wasteful approach when it comes to SQL:

• First, there are a number of sequential COUNT(*) statements that are issued when initializing a new connection which brings the startup time to almost 10s
• And there is the GeoPackageTileRetriever.getTile for raster tiles which will issue 5 SQL statements for each tile - where only one could be used - it uses the tileMatrixSet to get all potentially needed tiles - which will always be 4 tiles because of the edges - the needed one, the one to the right, the one to the bottom and the diagonal bottom-right - and it will even issue a COUNT(*) statement before retrieving them.

When used carefully, SQL over HTTP can be very fast - you can check my mbtiles demos - besides the two extra seconds at initialization - the rest is indistinguishable from serving raw tiles. It is a potentially game-changing technology.

I am willing to contribute my backend, which supports both Node.js and browsers - if you are willing to accept having a third SQL backend. It is based on the official SQLite WASM. Or - there is also another alternative implementation - @phiresky/sql.js-httpvfs - based on sql.js - which is the original implementation from which I drew inspiration.

However the real question is, can the SQL be fixed and will you accept sweeping changes in the tile requesting code? Obviously, this optimization won't support reprojection. I am willing to invest significant amounts of time if needed?

You can check my current (painfully slow) demo at https://mmomtchev.github.io/ol-gpkg/#StLouis and the DbAdapter at https://github.com/mmomtchev/ol-gpkg/tree/main/src

[danielbarela]

This library must support the entire GeoPackage spec which includes reprojection. If your optimizations do not support that, we would not be able to use it, but any other optimizations would be great as long as the library continues to support the GeoPackage spec.