Rajendra Gupta

Working with images in SQL Machine Learning using R scripts

November 27, 2020 by

In the article, An overview of SQL Machine Learning with R scripts, we learned the R services integration with SQL Server 2019. We also explored a few useful external packages.

There is a famous adage in English: “A picture is worth a thousand words”. You can represent your information using the image in various formats such as JPEG, PNG, GIF. Usually, we use various client tools such as MS Paint, Photo, photoshop or other client applications for working with the images. You can convert image format, modify the size, applying various effects, multiple animated images.

SQL Machine Learning language – R makes us capable of working with the images directly with the SQL Server. In this article, we will use SQL Machine Learning using R scripts for image processing.

Pre-requisites

You should follow the article, External packages in R SQL Server and configure the following:

  • Install Machine learning language R for SQL Server 2019
  • Use the sp_configure command to enable the execution of the external script
  • Install the magick external package to work with the images

Once you have installed the magick package, you can run the following R scripts to verify it is working fine or not.

R Service package

Work with images using SQL Machine Learning with R scripts

For this article, I will use the Mickey Mouse image. You can use any image for demo purposes.

SQL Machine Learning for images

Image Source: Pinterest

By default, R services install in the C:\Program Files\Microsoft SQL Server\MSSQL15.INST1\R_SERVICES\bin directory. Here, you get an application R for running the scripts directory using R services.

For this article, I will show the script in using the R application and its equivalent T-SQL using the sp_execute_external_script stored procedure.

sp_execute_external_script stored procedure

Launch the R application as an administrator, and you get the following Microsoft R client.

Microsoft R client

Similar to figure 1, we can run the following command to check the image formats supported by the Magick module.

>str(magick::magick_config())

check the image formats

Load the magick library in your R client.

>library(magick)

Load the magick library

Read the image properties using R scripts

To read the image, we use the image_read() function in the SQL machine learning language R.

>Mickey <- image_read(“./mickey.JPEG”)
>print(Mickey)

Read the image properties

To use the same script in the SQL Server 2019, we can run the following T-SQL.

@language: Specify R language.

@script: In the script, we import the Magick library and specify the image_read() function for retrieving image properties. In the script, you can specify either URL or image complete path. In my case, I stored the image in the default bin directory of R services.

You get the same output that we received from the R client. It returns the image format, height, width, color space, file size and density.

Output received from the R client

Export an image in another format to a file on disk

Many times, we require the image in a specific format. For example, if your application requires an image in the PNG format, you need to convert the existing image to the PNG format.

To convert the image format, R uses the image_convert() function. In my demo, the existing file format is JPEG. Let’s convert it into PNG format and save it on the disk. To save the file in the required format, we use the image_write() function.

In the below command, we check the image properties after converting them into the PNG format.

>Mickey_PNG <- image_convert(Mickey,”PNG”)
>print(Mickey_PNG)

In the output, verify the image format as PNG. In the below image, we see [filesize] as zero because we have not stored the file on the disk yet.

> Mickey_PNG <- image_convert(Mickey,”PNG”)
>print (Mickey_PNG)

Export an image in another format to a file on disk

Now, save this file using the image_write() function. Here, we specify the format as PNG for the new file.

> image_write(Mickey_PNG, path = “./Mickey_png.png”, format = “png”)

save this file

Now, again read the PNG file and verify its properties. Now, it shows you the file size as well.

>Mickey1 <- image_read(“./mickey_png.png”)
>print(Mickey1)

read the PNG file

In the below T-SQL script, we combined the above-specified R scripts, and you get a similar output in SSMS as well.

specified R scripts

Change the Image width using R

Suppose you require changing the image width. To edit using the R, we use the function image_scale() and specify the width in px. As per the previous image, our image width is 564 px.

In the below R script, we first read the image properties using the image_read() and later use the image_scale() function to modify its width. We save this file with a new name in the JPEG format.

> Mickey1 <- image_read(“./mickey_png.png”)
> target <- image_scale(Mickey1,”200″)
> image_write(target, path = “./Mickey_10.JPEG”, format = “JPEG”)

Change the Image width using R

Now, open the image file, and you get the new image size.

new image size

Similarly, if further modify the image size to 50px, it becomes small, as shown below.

> target <- image_scale(Mickey1,”50″)
> image_write(target, path = “./Mickey_10.JPEG”, format = “JPEG”)

Reduced image size

The equivalent T-SQL code for use with SQL Server Machine learning language – R is as below.

Image Borders using R scripts

We use image borders and backgrounds to make more impact using our images. You can also use a specific color border to showcase your organization branding.

>Mickey1 <- image_read(“./mickey_10.JPEG”)
>target <- image_border(Mickey1,”red”,”20×10″)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

Image Border

You can quickly convert from R script into the SQL Server Machine learning language format. The R script remains the same, and you need to embed it into the sp_execute_external_script stored procedure.

Image Border using SQL script

Flip and rotate an image using the R script

Suppose we want to rotate an image with a specific angle or flip the image (180 degrees). Usually, it can be done with only image editor tools. We have embedded functions in the R Maginc library.

To rotate an image with 45 degree, use the image_rotate() function.

>target <- image_rotate(Mickey1, 45)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

Now, open the image and you see the image rotated by 45 degree.

Flip and rotate an image

Similarly, if we want to flip the image (180 degrees), we can use the image_flip() function.

> target<- image_flip(Mickey1)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

The image looks as below:

flip image

To reverse the flip, we can use the image_flop() function and image returns to the original format.

> target<- image_flop(Mickey1)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

flop image

The T-SQL script to flip the image is below. You can modify the same script using the image_flop() function to flip it again and bring it in the original shape.

Special effects for the image using R scripts

In this part, we will apply a few special effects on the image using R scrips. You can apply various effects, but we will look at a few examples in this article.

The charcoal effect

The charcoal effect is popularly known as sketch images. Suppose, we want a sketch of Mickey Mouse. Instead of using 3rd party application, run the quick R script, and you get an image after applying the Charcoal effect as shown below.

> target<- image_charcoal(Mickey1)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

Charcoal effect

Blur an image

We can blur an image by applying the low-pass filter to an image. Nowadays, the latest mobile cameras have the functionality to blur an image as well.

In the R script, we can use a function image_blur() and get the image in blur format. It uses two parameters – radius and sigma as input values.

  • Radius: It shows the pixels for transformations
  • Sigma: It is the standard deviation for the Laplacian

Run the below code in R client, and you get following blur image.

> target<- image_blur(Mickey1,20,5) > image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

Blur an image

Oil paint image

It is getting interesting to see how good R in handling the images. If we want to see how my image will look in the oil paint, use the image_oilpaint() function.

> target<- image_oilpaint(Mickey1)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

The output image is as shown below.

Oil paint image

Negative of an image

Do you want to view the negatives of an image? If yes, R has a new function image_negate() to show the negatives of an image.

> target<- image_negate(Mickey1)
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

The output image is as shown below.

Negative of an image

You can convert these animation effects function into the T-SQL code and be familiar with the R language.

Text annotation for an image using R scripts

Usually, in an organization, we classify the information as confidential, private, public, internal. It is an attribute of the image that defines the image type.

To add the text annotation, we use the function image_annotate() function and specify the following values.

  • Text: Here, we wish to add the text CONFIDENTIAL for our image
  • Size: It is the font size
  • Color: Font color
  • Box color: Specify the color for the box in which you write the text
  • Degree: It is the angle at which the box is rotated
  • Location: It is the location of the text box

Once you run the below code, your image looks like below.

> > target<-image_annotate(Mickey1, “CONFIDENTIAL”, size=20, color=”blue”, boxcolor=”Yellow”, degree=45, location=”40+120”
> image_write(target, path = “./Mickey_11.JPEG”, format = “JPEG”)

Text annotation

The equivalent SQL Server Code in SQL Machine learning R is as below.

Conclusion

In this article, we explored the SQL Machine Learning Language R scripts for interacting with the images and modifying those images. I recommend you write the code in the R client first and then convert the code into the T-SQL stored procedure for easy troubleshooting.

Rajendra Gupta
Machine learning, R

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

287 Views