Crunchy Bridge for Analytics now has support for Iceberg and other new features. Learn more in our Crunchy Bridge for Analytics announcement.

Postgres, PL/Python and SciPy/NumPy for Processing Images

Kat Batuigas

7 min read

I recently wrote about building a Django app that stores uploaded image files in bytea format in PostgreSQL. For the second post in this series, we're now going to take a look at applying a blur filter to the uploaded image using PL/Python.

Lately we've been wading into PL/Python waters, and want to learn how to process raw binary data using some popular Python libraries. Python has an almost dizzying array of options when it comes to data analysis and scientific computing. Pair that with PostgreSQL's capabilities (especially how easy it is to extend) and you get some pretty powerful processing right where your data is stored.

Since we've already learning some basic NumPy, let's see what NumPy/SciPy could do in terms of image processing. With this little exercise, we're learning even more about both Web frameworks as well as Python and Postgres for data science. So this could also be an example of how application developers might build on or extend data science work that's carried out on the database level.

We also use Pillow to help us do some intermediate processing. As long as Pillow or the Python library you want to use is on the same machine as the Python interpreter, you'll be able to load it in your PL/Python function.

A bit about images

There's a lot more to digital images and image processing than we can cover here, but there's a few basic things to keep in mind about what we're working with. Like almost every other person on the internet, I love kittens. So we'll be using the following image of a kitten as an example, downloaded as a 1920x1280 JPEG from this page:

Blue eyed kitten looking directly at camera

The JPEG format, like PNG or TIFF, uses pixels to compose the image (as opposed to vector graphics such as SVG which use math to draw the image).

If we take a look at the image file's properties, we see attributes like its dimensions (1920 px wide and 1280 px high), resolution (300 dpi), and bit depth (24):

Dialog box displaying the images properties

Bit depth tells us about the color information stored in this image. 24-bit means that the three color channels R, G, and B each uses 8 bits, which gives a total of more than 16 million possible colors.

We'll see some of the same information when we represent this image data in a NumPy array. (You can try this in your Python shell if you have Pillow installed):

>>> from PIL import Image
>>> import numpy as np

>>> img ='cat.jpg')
>>> print(img.format, img.size, img.mode)
JPEG (1920, 1280) RGB

>>> img_array = np.array(img)  # NumPy's array creation function can accept Pillow's Image object as input
>>> print(img_array)
[[[ 40  35  39]
  [ 40  35  39]
  [ 41  36  40]
  [ 68  47  54]
  [ 67  46  53]
  [ 69  48  55]]
  [[159 145 158]
  [159 145 158]
  [159 145 158]
  [189 176 193]
  [188 175 192]
  [186 173 190]]]

>>> print(img_array.shape)
(1280, 1920, 3)

Color images are represented as three-dimensional arrays (indicated by the number of elements in the shape tuple). Additionally, the value 3 within shape shows that there are 3 color channels. (You can try playing around with greyscale or RGBA images to see how the array data and shape value can differ.) Each inner list represents a pixel: for instance, the very last one [186 173 190] should tell us that it's a mauve-ish color, which seems right looking at our cute kitten photo again.

Apply a blur filter with PL/Python

What we now want to do is use a SciPy module scipy.ndimage to process the saved image in the database. We'll use the gaussian_filter function to give us a blurred version of the image. Check out the Wikipedia page on Gaussian blur to learn more. We'll be taking this basic implementation of the blur filter and repurposing it to expand our web application.

gaussian_filter takes in an input NumPy array and returns a new array with the same shape as the input. So in our PL/Python function, we'll have to:

  1. Extract the raw binary data from Postgres,
  2. Feed the binary data into gaussian_filter as a NumPy array, and then
  3. Return that processed data in binary format again.

When the PL/Python function is called, it should give us the modified binary and from there we can do something else with it, like display it in a Django template.

For simplicity's sake, our PL/Python function will apply a "static" blur (i.e. the same level of blurriness will be applied to any image it operates on). Here's the function that we ended up with:

CREATE OR REPLACE FUNCTION bytea_blur(data bytea)
    RETURNS bytea
AS $$
    from PIL import Image
    from scipy import ndimage
    import io
    import numpy as np

    img_array = np.array(
    blurred = ndimage.gaussian_filter(img_array, sigma=(5, 5, 0))
    blurred_img = Image.fromarray(blurred)

    return_buffer = io.BytesIO(), "JPEG")
    return return_buffer.getvalue()
$$ LANGUAGE 'plpython3u';

On Pillow

We first tried converting the incoming bytea directly into an array--for example, NumPy has a frombuffer function, but this doesn't work with 3D arrays. So our best option was to use Pillow to help load the binary data correctly from Postgres.

You may have also seen by now that Pillow itself does have an ImageFilter module. This or other libraries like scikit-image or imageio could be a better fit for your image processing needs. One of our goals was to learn more about NumPy and SciPy, but we wouldn't want you to have to use NumPy/SciPy if you don't need it!

Python's BytesIO and file objects

The function expects a filename, a pathlib.Path object or a "file object". To put it a little bit more simply, operates on an actual file, or something that implements the interface for a file object in Python. In our case, we don't have the file itself, but we do have the contents of the file, saved in raw bytes in Postgres. So, BytesIO allows us to take the binary contents and pack it up in an in-memory stream so that can execute, without having to first write back to a file on disk.

SciPy's gaussian_filter used with color images

The Gaussian filter performs a calculation on the NumPy array. The function takes in a sigma value: the greater the value, the more blurry the image. You'll notice that we're actually passing in a tuple instead of a single number. If we had passed in a single number, we do end up with a blurred image, but in greyscale. If we want to keep the color, we pass in a tuple where the third element is 0. That effectively allows the color channel to remain untouched.

The blurred image as a NumPy array should look something like the following: (You can use a plpy utility function within PL/Python, e.g."NumPy array: " + str(blurred)) to see it in your client app, since you can't debug like usual or print to the console from Python embedded in Postgres.)

[[[ 40  35  39]
  [ 40  35  39]
  [ 40  35  39]
  [ 70  48  56]
  [ 70  48  56]
  [ 70  48  56]]
 [[166 153 165]
  [166 153 165]
  [167 154 166]
  [186 172 189]
  [186 172 189]
  [186 172 189]]]

After the filter is applied, we kind of go through the earlier steps again, but in reverse:

  1. We take the modified NumPy array and create a new Image object blurred_img.
  2. Then we create another file-like object pointing to an in-memory stream, called return_buffer.
  3. We call the function on blurred_img and have it write to return_buffer.
  4. Finally, we use BytesIO.getvalue() to give us the bytes for this new blurred image, returned as a bytea value from PL/Python.

If we call this PL/Python function directly in Postgres, we should get a binary string that starts like this:

SELECT bytea_blur(image_data) FROM image_process_imagefile WHERE id = 1;

bytea_blur | \xffd8ffe000104a46494600010100000100010000...

The next and final post in this series will show a quick demo of how we access this PL/Python function from Django.

Avatar for Kat Batuigas

Written by

Kat Batuigas

December 21, 2020 More by this author