17

I maintain a layer in my project that indexes geotagged research photos. Right now, the layer (in a gpkg) has an attribute PhotoPath which points to the photos stored separately on disk. I then use a QML or HTML widget in the layer attribute form to display the photo. This widget (HTML version) has a document.write(expression.evaluate("\"PhotoPath\"")); embedded in a <img src="..."> tag.

For portability and version control, I'd like to store the actual photos inside the gpkg.

Since version 3.8, QGIS allows storing binary (blob) data inside a gpkg and accessing it with a binary widget. Unfortunately, so far it seems this widget only allows importing or exporting the blob data, not other manipulation. Any ideas how to display it as an image?

I've done a manual proof of concept of the following klugey solution, but I hope there's something better.

  1. Storing the photo in the gpkg not as a binary blob, but in a base64 encoded text representation.

  2. Replacing the <img src="..."> with a <img src="data:image/jpeg;base64,..."> to paste in the picture as a data URL.

To do this fully, I'd have to implement the base64 encoding as a PyQGIS action and/or processing algorithm; and muck around with various escaped quotes in the HTML widget. It's storage-space and computationally wasteful, and I'm not sure if there's an upper limit on the data URL length. Any better approach I'm missing?

3
  • 1
    Geopackage is an extended SQL lite 3 database so you should be able to use python pynative.com/…
    – Mapperz
    Feb 13, 2020 at 2:32
  • 3
    Have you seen this pigrecoinfinito.com/2017/06/18/…? It is about SpatiaLite but may give some ideas.
    – user30184
    Feb 13, 2020 at 9:46
  • @Mapperz and @user30184, thanks. I was hoping to now be able to access the blob through the data provider as a binary field, but going straight through to sqlite is an option too. May be needed since the binary field is just 4 characters of pseudo-text in field calculator and haven't seen how much more can do with PyQGIS. The QImage approach in the script linked to by user30184's link is worth exploring too.
    – Houska
    Feb 13, 2020 at 10:42

4 Answers 4

25

To display the photo stored as blob in GPKG or PostgreSQL, one simple solution is to use QGIS HTML Widget.

It is a widget to be used in forms. It can be used also for a table without geometries.

Add a HTML Widget to the form

  • Go to: Layer Properties → Attributes Form
  • Switch to Drag and Drop Designer
  • Add an HTML Widget to you Form Layout

HTML Widget

Edit the HTML Form Widget

Double click on the HTML Widget to configure it.

  • Change the default Title (or choose to hide it)
  • Select you blob field from the drop down and press the plus button
  • You will get the default HTML to display your field:
<script>document.write(expression.evaluate("\"photo\""));</script>

You just have to replace this default, with this one:

<script>document.write(expression.evaluate(" '<img src=' || '\"data:image/png;base64,' || to_base64(\"photo\") || '\">' "))</script>

Ensure that you replace photo with your own blob field name. If you write it properly, you should see one of your images in the preview. Configure HTML Widget

Additional tweaks (optional)

Take advantage of the Drag and Drop Designer and move your photo to another tab, to have more display area, if your pictures are large.

If you want to see the entire image adjusted to the widget size, add width=\"100%\" to the img tag, like:

<script>document.write(expression.evaluate(" '<img width=\"100%\" src=' || '\"data:image/png;base64,' || to_base64(\"photo\") || '\">' "))</script>

You can move the width attribute to a <style> section, and add other tweaks, like:

<style>
img {
  display: block;
  max-width:400px;
  max-height:400px;
  width: auto;
  height: auto;
  box-shadow: 0 0 5px 5px #993300;
}
</style>
<script>document.write(expression.evaluate(" '<img src=' || '\"data:image/png;base64,' || to_base64(\"photo\") || '\">' "))</script>

Final result

Blob image displayed

Rotating images

Images bobs can have the orientation stored in the Exif metadata. It is possible to extract the Exif metadata and rotate the image, if necessary. We can use an external JavaScript library called exif-js.

Let's see an example. Use it as a starting point and adapt it to your needs. There's a lot of debug information in the example that can be helpful for the first attempt. Just remove style="display: none;" from the first HTML elements created.

<style>
    img {
        display: block;
        max-width: 800px;
        max-height: 800px;
        width: auto;
        height: auto;
    }
</style>
<script src="https://cdn.jsdelivr.net/npm/exif-js"></script>
<script>
    x = expression.evaluate("to_base64(\"Elementos__ATTACH_DATA\")");
    document.write('<img style="display: none;" id="img1" src=' + '\"data:image/png;base64,' + x + '\">');
    document.write('<pre style="display: none;" id="orientationSpan"></pre> ');
    document.write('<pre style="display: none;" id="allMetaDataSpan"></pre> ');
    var image = document.getElementById("img1");
    EXIF.getData(image, function () {
        var allMetaData = EXIF.getAllTags(this);
        var allMetaDataSpan = document.getElementById("allMetaDataSpan");
        allMetaDataSpan.innerHTML = JSON.stringify(allMetaData, null, "\t");
        var orientationSpan = document.getElementById("orientationSpan");
        orientationSpan.innerHTML = JSON.stringify(allMetaData["Orientation"], null, "\t");
        orientation = allMetaData["Orientation"];
        let rotation = '';
        switch (orientation) {
            case 2:
                rotation = 'transform: scaleX(-1);';
                break;
            case 3:
                rotation = 'transform:rotate(180deg);';
                break;
            case 4:
                rotation = 'transform:rotate(180deg) scaleX(-1);';
                break;
            case 5:
                rotation = 'transform:rotate(-90deg) scaleX(-1);';
                break;
            case 6:
                rotation = 'transform:rotate(90deg);';
                break;
            case 7:
                rotation = 'transform:rotate(90deg) scaleX(-1);';
                break;
            case 8:
                rotation = 'transform:rotate(270deg);';
                break;
            default:
                rotation = '';
        }
        document.write('<img style="' + rotation + '" id="img2" src=' + '\"data:image/png;base64,' + x + '\">');
    });
</script>

enter image description here

Using QML Widget (another alternative way)

Alternatively, you can use one QML Widget. Instead of HTML, you have to use QML language. In this case, you have to use the Image QML Type.

Use almost the same steps as described for the HTML Widget. Use the following QML code:

import QtQuick 2.0

Image {
    source:   "data:image/png;base64," + expression.evaluate(" to_base64(\"photo\") ")
}

enter image description here

10
  • Thanks, you've helped me with my first QGIS forms experience. Please explain, in your HTML Forms Widget section, how you achieved the image under Final Result; what did you click, or what tool did you use, or....??? I don't know how to display my blob photos!
    – Stu Smith
    May 10, 2021 at 3:36
  • Soon as you replace the default expression with the recommend one (using your own blob field), you will see the image preview.
    – jgrocha
    May 10, 2021 at 7:06
  • Thanks. Weirdly, the image preview only works if my blob field name is photo! It was originally data_image, which I edited your example to that field name. However, that generated a small green "broken-link" icon in the preview. Confused and in desperation, I added the field photo to my .gpkg table, and presto, it worked.
    – Stu Smith
    May 10, 2021 at 14:33
  • Another comment: The last line in your Additional tweaks (optional) section contains the text ...to_base64(\"data\")..., which points to the field data. I note this because your earlier statements used ...to_base64(\"photo\")...
    – Stu Smith
    Aug 5, 2021 at 17:50
  • 1
    Tested and works also for MSSQL
    – Albine Pro
    Jun 15, 2022 at 8:21
10

Well, I've gotten my kluge to work, though I hope someone still comes up with a better answer!

I've created custom functions for manipulating blobs via expressions (see definitions below.) I can now use the Field calculator to select all features where "PhotoBlob" is Null and then update PhotoBlob to file_to_blob("PhotoPath"). And I use expressions like blobjpg_to_html("PhotoBlob",'max-height:128;max-width:128;') to display it in HTML map tips and (with 2nd parameter Null) in an HTML form widget.

The custom functions do indeed base64 encode the blob and then push it out as an <img src="data:image...">, but this way the wasteful encoding is done only on demand for the image currently being displayed.

Here's the custom function definition:

from qgis.core import *
from qgis.gui import *
from PyQt5.QtCore import QByteArray

@qgsfunction(args='auto', group='Blob')
def file_to_blob(filename,feature,parent):
    """
    Loads and returns the contents of file 'filename' as a binary blob.
    <br>This can be used to e.g load in jpegs to a gpkg database, for instance,
    by filtering for the blob field being null in Field Calculator, if the filename
    is in a different attribute.
    """
    with open(filename, 'rb') as file:
        blobData = QByteArray(file.read())
    return blobData

@qgsfunction(args='auto', group='Blob', handlesnull=True)
def blobjpg_to_html(blob,style,feature,parent):
    """
    Returns the blob (which must be a jpeg) converted a HTML img data url for display in an HTML widget or map tip.
    <br>A 2nd parameter, style, is obligatory. Style is '' for no styling (size etc as stored) or can be a string passed as a CSS style parameter to the img tag,
    <br>If Style is passed as Null (not ''), a default of 'style="max-width:100%; max-height:100%;' is used to fill image container during display.
    """
    blob64 = blob.toBase64().data().decode()
    # not sure why need the decode(). Have also tried str(...data()) but doesn't work
    if style is None:
        stylestring = 'style="max-width:100%; max-height:100%;"'
    elif not(style):
        stylestring = 'style=""'
    else:
        stylestring = 'style="' + style + '"'
    fullstring = '<img src="data:image/jpeg;base64,' + blob64 + '" ' + stylestring + ' alt="Invalid jpeg">'
    return fullstring
1
  • Coming back to note that unfortunately, HTML img display in QGIS does not respect a jpeg's EXIF Orientation tag, so portrait photos appear rotated. However, to my surprise a QML (rather than HTML) widget does respect it (provided you include autoTransform: true in the image {...} code), and accepts data in base64 as a source. Styles can't be included in the source, but a function with fullstring = 'data:image/jpeg;base64') + blob64 passed to the QML image { source: } works just fine. You have to specify maximum height and width in pixels and setthe fill mode to fit rather than crop.
    – Houska
    Feb 25, 2020 at 13:23
0

Another updated compact solution:

  1. Layer Properties → Attributes Form
  2. Choose the field that contains the path where the photos are saved
  3. Change the widget type to attachment
  4. Change in the Attachment settings the Integrated Document Viewer Type to Image
2
  • Thank you. However, my understanding is that this displays attachments, i.e. photos stored in the filesystem pointed to by a field, but not photos stored inside the gpkg as blobs. Right? As such, it would be a (more recent and easier) alternative to using a custom-coded HTML/QML widget with the path in the 1st para of the question, but wouldn't allow embedding this photos. I might be missing something...
    – Houska
    Oct 7, 2021 at 14:26
  • When I try to change the widget type, attachment is greyed out. Thoughts?
    – Stu Smith
    Nov 28, 2021 at 4:44
0

I answered a different question with an approach I found easier.

Link to other question: How to get image pop ups in QGIS using oracle blob field with image?

Link to same answer there: https://gis.stackexchange.com/a/477771/171677

If you have a blob/bytea variable you can make a virtual field where you convert the blob to 64 bit using to_base64( "photo_blob" )

snip of blob photo

Then in the display tab you can add your HTML referring to it <img width='500' src='data:image/png;base64,[% "photo_blob_64bit" %]'>:

snip of setting display value in layer properties

Then you can make these appear with the hover over using "show map tips":

show map tips

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.