[QGIS-Developer] SpatiaLite: open blob images with Qgis3

[a.furieri at lqt.it]

On Mon, 12 Jun 2017 18:04:31 +0000 (UTC), silvio grosso wrote:
> Hello everyone,
>
> At present, QGIS doesn't currently have *any* support for blob 
> fields.
>
> I am wondering how long would it take, in terms of *working days*, to
> develop a feature for Qgis 3 related to SpatiaLite ?
>
> In short :
> - You open a SpatiaLite database which contains some jpeg images
> (stored as blob) ;
> - As soon as you connect this database, you are able to visualize 
> your
> images (jpeg) with Qgis 3.
>
> Something similar to what it is possible with many commercial Sqlite
> editors (e.g. SqliteMaestro).
> As regards open source softwares, I currently work with SqliteStudio
> but this feature is not available yet.
>

Ciao Silvio,

also SpatialiteGUI has the capability to show a preview of
digital images stored as BLOBs fields within the database.

it's surely worth noting that libspatialite supports several
useful SQL functions specifically intended to quickly "sniff"
the actual payload stored within an arbitrary BLOB value:
- IsGifBlob()
- IsPngBlob()
- IsTiffBlob()
- IsJpegBlob()
- IsExifBlob()
- IsExifGpsBlob()
- IsWebpBlob()
- IsJP2Blob()
and a more generic "GetMimeType()" will return "image/png"
or "image/jpeg" and so on.

all these SQL functions just examine the internal signature
of the binary payload based on well known "magic numbers",
so they are lightweight and very fast to be computed.

an eventual practical implementation just requires few
lines of code more or less like this:

1. SELECT my_blob_column, GetMimeType(my_blob_colummn)
    FROM my_table
    WHERE GetMimeType(my_blob_column) IN (
       'image/gif', 'image/png', 'image'jpeg', 'image/tiff');

2. then unwind the returned resulset, and for each row
    directly dispatch the binary payload to the appropriate
    image decoder (libpng, libjpeg, libtiff or whatever else)
    depending on the value of "mime_type".

bye Sandro