Images are a powerful way to convey information and make an impact. In SQL Server, you can use R scripts to work with images directly, allowing you to perform various image processing tasks. In this article, we will explore how to use SQL Machine Learning with R scripts to process and modify images in SQL Server.

Prerequisites

Before we begin, make sure you have followed the steps outlined in the article “External packages in R SQL Server” to install the necessary components:

• Install Machine learning language R for SQL Server 2019
• Enable the execution of external scripts using the sp_configure command
• Install the magick external package for working with images

Once you have completed these prerequisites, you can proceed with the following examples.

Reading Image Properties

To read the properties of an image, we can use the image_read() function in the SQL Machine Learning language R. Here is an example:

Mickey <- image_read("./mickey.JPEG")
print(Mickey)

In the above code, we read the image “mickey.JPEG” and print its properties. You can also run the equivalent T-SQL code using the sp_execute_external_script stored procedure:

EXECUTE sp_execute_external_script
  @language = N'R',
  @script = N'
    library(magick)
    Mickey <- image_read("C://Program Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//mickey.jpeg")
    print(Mickey)
  '

Converting Image Formats

Sometimes, you may need to convert an image to a different format. R provides the image_convert() function for this purpose. Here is an example of converting an image from JPEG to PNG format:

Mickey_PNG <- image_convert(Mickey, "PNG")
print(Mickey_PNG)

In the above code, we convert the image “Mickey” to the PNG format and print its properties. You can also save the converted image to a file using the image_write() function:

image_write(Mickey_PNG, path = "./Mickey_png.png", format = "png")

Modifying Image Size

If you need to change the size of an image, you can use the image_scale() function in R. Here is an example of resizing an image to a width of 200 pixels:

target <- image_scale(Mickey, "200")
image_write(target, path = "./Mickey_resized.JPEG", format = "JPEG")

In the above code, we resize the image “Mickey” to a width of 200 pixels and save the resized image to a file. You can also run the equivalent T-SQL code:

EXECUTE sp_execute_external_script
  @language = N'R',
  @script = N'
    library(magick)
    Mickey <- image_read("C://Program Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//mickey.jpeg")
    target <- image_scale(Mickey, "200")
    image_write(target, path = "./Mickey_resized.JPEG", format = "JPEG")
  '

Applying Special Effects

R provides various functions to apply special effects to images. Here are a few examples:

• Charcoal effect (sketch images): target <- image_charcoal(Mickey)
• Blur effect: target <- image_blur(Mickey, 20, 5)
• Oil paint effect: target <- image_oilpaint(Mickey)
• Negative effect: target <- image_negate(Mickey)

You can save the modified images to files using the image_write() function.

Adding Text Annotation

If you want to add text annotation to an image, you can use the image_annotate() function in R. Here is an example:

target <- image_annotate(Mickey, "CONFIDENTIAL", size = 20, color = "blue", boxcolor = "Yellow", degree = 45, location = "40+120")
image_write(target, path = "./Mickey_annotated.JPEG", format = "JPEG")

In the above code, we add the text “CONFIDENTIAL” to the image “Mickey” with specified font size, color, box color, rotation angle, and location. You can also run the equivalent T-SQL code:

EXECUTE sp_execute_external_script
  @language = N'R',
  @script = N'
    library(magick)
    Mickey <- image_read("C://Program Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//mickey.jpeg")
    target <- image_annotate(Mickey, "CONFIDENTIAL", size = 20, color = "blue", boxcolor = "Yellow", degree = 45, location = "40+120")
    image_write(target, path = "./Mickey_annotated.JPEG", format = "JPEG")
  '

Conclusion

In this article, we have explored how to work with images in SQL Server using R scripts. We have covered various tasks such as reading image properties, converting image formats, modifying image size, applying special effects, and adding text annotation. By leveraging the power of SQL Machine Learning with R scripts, you can easily perform image processing tasks directly in SQL Server.