Parse and show bitmap images by using T-SQL

Introduction

Inspired by this Itzik Ben Gan’s post, I wrote T-SQL query which parses a bitmap image, converts it to SQL Server Geometry format, and shows results in Spatial Data viewer.

 

The query is designed for SQL Server 2008 and supports processing of monochrome uncompressed bitmap images.

 

Code



 BEGIN TRY
   -- 1. Read bitmap image from a disk file
   DECLARE
       @BitmapImage VARBINARY(MAX)

   SELECT
       @BitmapImage = BitmapFile.BulkColumn
   FROM
       OPENROWSET(BULK N'C:\logo.bmp', SINGLE_BLOB) AS BitmapFile

   -- 2. Parse header
   DECLARE @BitmapHeader TABLE (
       FieldId INT NOT NULL IDENTITY,
       FieldName VARCHAR(MAX) NOT NULL,
       FieldOffset INT NOT NULL,
       FieldLength INT NOT NULL,
       FieldValue VARBINARY(MAX) NULL,
       TargetValue VARBINARY(MAX) NULL
   )

   INSERT
       @BitmapHeader (FieldName, FieldOffset, FieldLength, TargetValue)
   VALUES
       ('ImageType', 1, 2, CAST('BM' AS BINARY(2))), -- Bitmap images are always started with these 2 chars
       ('ImageSize', 3, 4, CAST(REVERSE(CAST(DATALENGTH(@BitmapImage) AS BINARY(4))) AS BINARY(4))), -- cross check
       ('BitOffset', 11, 4, NULL),
       ('Width', 19, 4, NULL),
       ('Height', 23, 4, NULL),
       ('BitCount', 27, 2, 0x0100) -- 1-bit - this script works only with monchrome images

   UPDATE
       @BitmapHeader
   SET
       FieldValue = SUBSTRING(@BitmapImage, FieldOffset, FieldLength)

   -- 3. Make sure that header are correct and we can parse image
   DECLARE
       @ParseHeaderError NVARCHAR(4000)

   SELECT TOP 1
       @ParseHeaderError = 'Error during header parsing - invalid ' + FieldName
   FROM
       @BitmapHeader
   WHERE
       TargetValue IS NOT NULL
           AND
       (FieldValue <> TargetValue OR FieldValue IS NULL)
   ORDER BY
       FieldId

   IF @@ROWCOUNT > 0
       RAISERROR(@ParseHeaderError, 16, 0)

   -- 4. Now, let's extract characteristics required for further parsing
   DECLARE
       @Width INT = (SELECT CAST(REVERSE(FieldValue) AS BINARY(4)) FROM @BitmapHeader WHERE FieldName = 'Width'),
       @Height INT = (SELECT CAST(REVERSE(FieldValue) AS BINARY(4)) FROM @BitmapHeader WHERE FieldName = 'Height'),
       @BitOffset INT = (SELECT CAST(REVERSE(FieldValue) AS BINARY(4)) FROM @BitmapHeader WHERE FieldName = 'BitOffset')

   -- 5. In BMP format, actual image width are always padded to 4 bytes
   DECLARE
       @WidthWithPadding INT = @Width + CASE WHEN (@Width % 32) = 0 THEN 0 ELSE 32 - @Width % 32 END

   -- 6. Parse image pixels and covert them to geometry text
   DECLARE
       @BitmapPoints VARCHAR(MAX) = NULL

   ;WITH BitCounter AS (
       SELECT 0 AS BitNumber
       UNION ALL
       SELECT BitNumber + 1 FROM BitCounter WHERE BitNumber < (@WidthWithPadding * @Height - 1)
   ),
   Points AS (
       SELECT
           X = BitNumber % @WidthWithPadding,
           Y = BitNumber / @WidthWithPadding
       FROM
           BitCounter
       WHERE
           (BitNumber % @WidthWithPadding) < @Width
               AND
           (CAST(SUBSTRING(@BitmapImage, @BitOffset + 1 + (BitNumber / 8), 1) AS INT) &amp; POWER(2, 7 - BitNumber % 8)) = 0
   )
   SELECT
       @BitmapPoints = ISNULL(@BitmapPoints + ',', '') + 'POINT(' + CAST(X AS VARCHAR(30)) + ' ' + CAST(Y AS VARCHAR(30)) + ')'
   FROM
       Points
   OPTION
       (MAXRECURSION 0)

   -- 7. Conver text to GEOMETRY type, so it can be showed in Spatial Viewer
   SELECT
       GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION(' + @BitmapPoints + ')', 0)
END TRY

BEGIN CATCH
   DECLARE
       @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(),
       @ErrorSeverity INT = ERROR_SEVERITY(),
       @ErrorState INT = ERROR_STATE()

   RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

 

How It Works

 

• Read BMP image from a disk file as binary data by using OPENROWSET statement. Actually, it is not mandatory to read image from a local file. Image can be already stored in table column, or it can be specified directly as hexadecimal string.

• Process bitmap header. First of all, we need to make sure that binary data obtained on the previous step represents valid bitmap image. Then, we need to obtain characteristics of the bitmap image, such as width and height, which will be used for the further image parsing.

• Convert image pixels to OGC Well-Known Text representation. This is done in three steps. At first, by using recursive CTE generate sequence of bit numbers from 0 to (count of pixels in the bitmap image - 1). At second, read image bits and convert them in X / Y coordinates (0 bit is represented as point, 1 - as no point). At third, generate well-known text as list of POINT statements.

• Convert well-known text to SQL Server GEOMETRY Spatial Data Type and output results.

 

 

Examples

 

I use following two pictures for testing (please do not forget that they must be stored as monochrome bitmap images):

 

 

 

 

This is how these pictures are shown in Spatial Data viewer: