25. Juni 2013

"Image Processing" in der Datenbank - handlich und praktisch

"Image processing" in the database - handy and practical
Zum Thema "Bildbearbeitung" in der Datenbank hatte ich vor fast 6 Jahren schon mal das Blog Posting "Bilder in der Oracle-Datenbank: ORDIMAGE". Darin wird der Datentyp ORDIMAGE vorgestellt; und als Beispiel hatte ich die Extraktion von EXIF-Metadaten aus einem Bild exemplarisch dargestellt. Heute möchte ich auf das Thema nochmals eingehen, da es doch immer wieder bei der Anwendungsentwicklung auftaucht ...
  • Von hochgeladenen Bildern sollen Vorschaubilder (Thumbnails) erzeugt werden
  • Von mit dem Smartphone gemachten Bildern soll die GPS-Position extrahiert werden
  • Bilder müssen gedreht werden
Der Datentyp ORDIMAGE (welcher auch in Tabellen verwendet werden kann), sieht all diese Möglichkeiten vor. Nur, wer verwendet schon ORDIMAGE in seinen Tabellen - nahezu überall werden diese als BLOB gespeichert. Und wenn man sich ORDIMAGE mal in SQL*Plus mit einem describe ansieht, findet zwar jede Menge Funktionen darin, aber keine mit Namen "THUMBNAIL", "GET_PREVIEW" oder "ROTATE" ...
Die interessanten Funktionen in ORDIMAGE sind PROCESS und PROCESSCOPY . Diese führen eine Operation auf einem Bild durch. Während PROCESS das Originalbild überschreibt, schreibt PROCESSCOPY das Ergebnis in einen zweiten BLOB, der als Parameter übergeben wird. ORDIMAGE ist ein Objekttyp - beide Funktionen stehen als Member Functions, aber auch als Static Functions bereit. Und die letzten beiden sind interessant - denn diese arbeiten mit ganz normalen BLOBs - man muss keine Tabellenspalten vom Typ ORDIMAGE erzeugen.
METHOD
------
 STATIC PROCEDURE PROCESS
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 IMAGEBLOB                      BLOB                    IN/OUT NOCOPY
 COMMAND                        VARCHAR2                IN

METHOD
------
 STATIC PROCEDURE PROCESSCOPY
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 IMAGEBLOB                      BLOB                    IN
 COMMAND                        VARCHAR2                IN
 DEST                           BLOB                    IN/OUT NOCOPY
Bleibt die Frage, was als COMMAND übergeben werden kann. Das ist im Handbuch "Oracle Multimedia Reference" in der Dokumentation zu PROCESS näher erläutert. Demnach dreht man ein Bild mit dem Befehl rotate=90 um ein Viertel nach rechts. maxScale=200 200 skaliert das Bild so, dass die maximale Kantenlänge 200 Pixel beträgt. Damit könnte man schon direkt losarbeiten. Nachdem ein Bild in einer Tabelle TAB_IMAGES und dort in einer Spalte IMAGE gespeichert ist, könnte das Miniaturbild mit einer einfachen PL/SQL-Prozedur generiert und in die Spalte THUMBNAIL gespeichert werden.
declare
  v_image blob;
  v_thumb blob;
begin
  select image, empty_blob() into v_image, v_thumb 
  from tab_images 
  where id = 1
  for update;

  ordimage.processcopy(
    imageblob => v_image,
    command   => 'maxScale=200 200',
    dest      => v_thumb
  );

  update tab_images set thumbnail = v_thumb 
  where id = 1;
end;
/
Allerdings braucht man nun stets die Dokumentation zu ORDIMAGE.PROCESS - wenn ein ganzes Team die Funktionalität nutzen soll, muss erst ein aufwändiger Know-How-Transfer stattfinden; außerdem kann man nicht von Entwicklungswerkzeugen profitieren, die die in einem Package verfügbaren Funktionen direkt vorschlagen und bei den Parametern helfen. Die Lösung ist einfach: Wir bauen uns das Package einfach selbst - und zwar so, dass man es einfach und "natürlich" bedienen kann.
create or replace package pkg_ordimage is
  procedure rotate(
    p_image    in out nocopy blob,
    p_degree   in            number
  );

  function rotate(
    p_image    in blob,
    p_degree   in number
  ) return blob;

  procedure thumbnail(
    p_image    in out nocopy blob,
    p_width    in            number,
    p_height   in            number
  );

  function thumbnail(
    p_image    in blob,
    p_width    in number,
    p_height   in number
  ) return blob;

  procedure grayscale(
    p_image    in out nocopy blob
  );

  function grayscale(
    p_image    in blob
  ) return blob;

  procedure crop(
    p_image    in out nocopy blob,
    p_x        in            number,
    p_y        in            number,
    p_width    in            number,
    p_height   in            number
  );
 
  function crop(
    p_image    in blob,
    p_x        in number,
    p_y        in number,
    p_width    in number,
    p_height   in number
  ) return blob;

  function get_exif( 
    p_image    in blob
  ) return xmltype;

  function get_location(
    p_image    in blob
  ) return sdo_geometry;
end pkg_ordimage;
/
sho err

create or replace package body pkg_ordimage is
  procedure rotate(
    p_image    in out nocopy blob,
    p_degree   in            number
  ) is
  begin
    ordimage.process(imageblob => p_image, command => 'rotate='||p_degree);
  end rotate;

  function rotate(
    p_image    in blob,
    p_degree   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(imageblob => p_image, command=>'rotate='||p_degree, dest =>l_target);
    return l_target;
  end rotate;

  procedure thumbnail(
    p_image    in out nocopy blob,
    p_width    in            number,
    p_height   in            number
  ) is
  begin
    ordimage.process(p_image, 'maxScale='||p_width||' '||p_height);
  end thumbnail;

  function thumbnail(
    p_image    in blob,
    p_width    in number,
    p_height   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'maxScale='||p_width||' '||p_height, l_target);
    return l_target;
  end thumbnail;

  procedure grayscale(
    p_image    in out nocopy blob
  ) is
  begin
    ordimage.process(p_image, 'contentFormat=8BITGRAY');
  end grayscale;

  function grayscale(
    p_image    in blob
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'contentFormat=8BITGRAY', l_target);
    return l_target;
  end grayscale;

  procedure crop(
    p_image    in out nocopy blob,
    p_x        in            number,
    p_y        in            number,
    p_width    in            number,
    p_height   in            number
  ) is
  begin
    ordimage.process(p_image, 'cut='||p_x||' '||p_y||' '||p_width||' '||p_height);
  end crop;

  function crop(
    p_image    in blob,
    p_x        in number,
    p_y        in number,
    p_width    in number,
    p_height   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'cut='||p_x||' '||p_y||' '||p_width||' '||p_height, l_target);
    return l_target;
  end crop;

  function get_exif( 
    p_image    blob
  ) return xmltype is
    l_exif xmltype := null;
    l_exiflist xmlsequencetype;
  begin
   l_exiflist := ordimage.getmetadata(p_image, 'EXIF');
    if l_exiflist.count > 0 then 
      l_exif := l_exiflist(1);
    end if;
    return l_exif;
  end get_exif;

  function get_location(
    p_image    in blob
  ) return sdo_geometry is
    l_geom sdo_geometry := null;
    l_exif xmltype      := null;

    l_geomlat     number;
    l_geomlatr    varchar2(100);
    l_geomlon     number;
    l_geomlonr    varchar2(100);
  begin
    l_exif := get_exif(p_image); 
    if l_exif is not null then 
      select 
       to_char(lon, '9999D999999999999999', 'nls_numeric_characters=''.,'''), 
       lontype, 
       to_char(lat, '9999D999999999999999', 'nls_numeric_characters=''.,'''), 
       lattype 
      into l_geomlon, l_geomlonr, l_geomlat, l_geomlatr
      from
       xmltable(
         xmlnamespaces(DEFAULT 'http://xmlns.oracle.com/ord/meta/exif' ),
         '/exifMetadata/GpsIfd'
         passing l_exif
         columns 
           lat varchar2(30) path '/GpsIfd/GPSLatitude',
           lon varchar2(30) path '/GpsIfd/GPSLongitude',
           lattype varchar2(30) path '/GpsIfd/GPSLatitudeRef',
           lontype varchar2(30) path '/GpsIfd/GPSLongitudeRef'
       ) gps;
      if lower(l_geomlonr) = 'west longitude' then l_geomlon := -(abs(l_geomlon)); end if;
      if lower(l_geomlatr) = 'south latitude' then l_geomlat := -(abs(l_geomlat)); end if;
      if l_geomlon is not null and l_geomlat is not null then 
        l_geom := sdo_geometry(2001, 8307, sdo_point_type(l_geomlon, l_geomlat, null), null, null);
      end if;
    end if;
    return l_geom;
  end get_location;
end;
/
sho err
Dieses Package sieht nun so aus ...
SQL> desc pkg_ordimage
FUNCTION GET_EXIF RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN
PROCEDURE ROTATE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN/OUT
 P_DEGREE                       NUMBER                  IN
FUNCTION ROTATE RETURNS BLOB
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN
 P_DEGREE                       NUMBER                  IN
PROCEDURE THUMBNAIL
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN/OUT
 P_WIDTH                        NUMBER                  IN
 P_HEIGHT                       NUMBER                  IN
:
Auch im SQL Developer profitiert man vom neuen Package ...
Das Generieren eines Thumbnails sieht nun so aus ...
SQL> select pkg_ordimage.thumbnail(image,200,200) thumbnail
  2  from tab_images where id = 1;

THUMBNAIL
--------------------------------------------------------------------------------
FFD8FFE000104A46494600010100000100010000FFDB004300080606070605080707070909080A0C
140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27
:
Möchte man die EXIF-Metadaten haben, so macht man dies ...
SQL> select pkg_ordimage.get_exif(image).getclobval() exif_xml
  2  from tab_images where id = 1;

EXIF_XML
--------------------------------------------------------------------------------
<exifMetadata xmlns="http://xmlns.oracle.com/ord/meta/exif" xsi:schemaLocation="
http://xmlns.oracle.com/ord/meta/exif http://xmlns.oracle.com/ord/meta/exif" xml
ns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <TiffIfd>
      <Make tag="271">Apple</Make>
      <Model tag="272">iPhone 4S</Model>
Daraus könnte man nun die GPS-Daten extrahieren (XML-Tags GPSLatitude und GPSLongitude); aber schöner ist es natürlich, wenn unser Package diesen Dienst direkt anbietet und die Daten "Oracle-fertig" zurückgibt.+
SQL> select pkg_ordimage.get_location(image).getclobval() location
  2  from tab_images where id = 1;

LOCATION
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(11.83, 48.175667, NULL), NULL, NULL)
Viel Spaß beim Ausprobieren. Und wenn Ihr das Package erweitern wollt: Die Übersicht über die Kommandos, die man dem ORDIMAGE.PROCESS übergeben kann, findet Ihr im Handbuch. Außerdem interessant dürfte die Funktion APPLYWATERMARK sein, die "im Orginal" auch etwas sperrig ist ...
About 6 years ago, I already published a (german only) Blog Posting on "Image Processing" within the database: "Bilder in der Oracle-Datenbank: ORDIMAGE". This posting was about the ORDIMAGE type - and as an example, I showed how EXIF metadata can be extracted from an image. Today I'd like to elaborate again on this topic, since it comes up in discussions again and again.
  • After storing in image into a table, a smaller preview image (thumbnail) should be generated automatically
  • Extract the GPS location from an image taken with a smartphone
  • Rotate an image with wrong orientation
ORDIMAGE provides this functionaliy. It's a data type, not a package, so it can be used in tables instead of BLOB. But, in practice - virtually everybody stores images in BLOB columns - and nobody really wants to change the tables of an existing application. Beyond that, ORDIMAGE does not seem to contain any useful function - looking into it with SQL*Plus reveals many functions, but the names THUMBNAIL, ROTATE or similar ones are not present ...
The latter point first: The interesting functions within ORDIMAGE are PROCESS and PROCESSCOPY . Both perform an operation on an image. PROCESS manipulates the input image, whereas PROCESSCOPY generates the operation result as a new image. Both functions are available as Member and Static Functions - and we are interested in the static functions: These can be used with plain BLOBs - no ORDIMAGE instance or ORDIMAGE table column is required.
METHOD
------
 STATIC PROCEDURE PROCESS
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 IMAGEBLOB                      BLOB                    IN/OUT NOCOPY
 COMMAND                        VARCHAR2                IN

METHOD
------
 STATIC PROCEDURE PROCESSCOPY
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 IMAGEBLOB                      BLOB                    IN
 COMMAND                        VARCHAR2                IN
 DEST                           BLOB                    IN/OUT NOCOPY
The COMMAND parameter determines, which operation is to be performed on the image. The "Oracle Multimedia Reference" contains a complete list in the section about ORDIMAGE.PROCESS. An image can therefore be rotated with the rotate=90 command. maxScale=200 200 scales the image, so that the resulting image is not larger than 200 pixels width or height. Knowing this, we could start using ORDIMAGE immediately: After storing an image into the IMAGE column (BLOB) of the TAB_IMAGES table, we can generate the preview image as follows ...
declare
  v_image blob;
  v_thumb blob;
begin
  select image, empty_blob() into v_image, v_thumb 
  from tab_images 
  where id = 1
  for update;

  ordimage.processcopy(
    imageblob => v_image,
    command   => 'maxScale=200 200',
    dest      => v_thumb
  );
  -- finally store the thumbnail into the column THUMBNAIL
  update tab_images set thumbnail = v_thumb 
  where id = 1;
end;
/
But we would now always need the documentation for ORDIMAGE.PROCESS - in a development team, we'd need to educate all team members on ORDIMAGE usage. The suggestion features within development tools cannot help, since there is only one function: PROCESS. The solution would be a package with easy-to-use functions having meaningful names and parameters. So let's create that package.
create or replace package pkg_ordimage is
  procedure rotate(
    p_image    in out nocopy blob,
    p_degree   in            number
  );

  function rotate(
    p_image    in blob,
    p_degree   in number
  ) return blob;

  procedure thumbnail(
    p_image    in out nocopy blob,
    p_width    in            number,
    p_height   in            number
  );

  function thumbnail(
    p_image    in blob,
    p_width    in number,
    p_height   in number
  ) return blob;

  procedure grayscale(
    p_image    in out nocopy blob
  );

  function grayscale(
    p_image    in blob
  ) return blob;

  procedure crop(
    p_image    in out nocopy blob,
    p_x        in            number,
    p_y        in            number,
    p_width    in            number,
    p_height   in            number
  );
 
  function crop(
    p_image    in blob,
    p_x        in number,
    p_y        in number,
    p_width    in number,
    p_height   in number
  ) return blob;

  function get_exif( 
    p_image    in blob
  ) return xmltype;

  function get_location(
    p_image    in blob
  ) return sdo_geometry;
end pkg_ordimage;
/
sho err

create or replace package body pkg_ordimage is
  procedure rotate(
    p_image    in out nocopy blob,
    p_degree   in            number
  ) is
  begin
    ordimage.process(imageblob => p_image, command => 'rotate='||p_degree);
  end rotate;

  function rotate(
    p_image    in blob,
    p_degree   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(imageblob => p_image, command=>'rotate='||p_degree, dest =>l_target);
    return l_target;
  end rotate;

  procedure thumbnail(
    p_image    in out nocopy blob,
    p_width    in            number,
    p_height   in            number
  ) is
  begin
    ordimage.process(p_image, 'maxScale='||p_width||' '||p_height);
  end thumbnail;

  function thumbnail(
    p_image    in blob,
    p_width    in number,
    p_height   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'maxScale='||p_width||' '||p_height, l_target);
    return l_target;
  end thumbnail;

  procedure grayscale(
    p_image    in out nocopy blob
  ) is
  begin
    ordimage.process(p_image, 'contentFormat=8BITGRAY');
  end grayscale;

  function grayscale(
    p_image    in blob
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'contentFormat=8BITGRAY', l_target);
    return l_target;
  end grayscale;

  procedure crop(
    p_image    in out nocopy blob,
    p_x        in            number,
    p_y        in            number,
    p_width    in            number,
    p_height   in            number
  ) is
  begin
    ordimage.process(p_image, 'cut='||p_x||' '||p_y||' '||p_width||' '||p_height);
  end crop;

  function crop(
    p_image    in blob,
    p_x        in number,
    p_y        in number,
    p_width    in number,
    p_height   in number
  ) return blob is
    l_target blob;
  begin
    dbms_lob.createtemporary(l_target, true, dbms_lob.call);
    ordimage.processcopy(p_image, 'cut='||p_x||' '||p_y||' '||p_width||' '||p_height, l_target);
    return l_target;
  end crop;

  function get_exif( 
    p_image    blob
  ) return xmltype is
    l_exif xmltype := null;
    l_exiflist xmlsequencetype;
  begin
   l_exiflist := ordimage.getmetadata(p_image, 'EXIF');
    if l_exiflist.count > 0 then 
      l_exif := l_exiflist(1);
    end if;
    return l_exif;
  end get_exif;

  function get_location(
    p_image    in blob
  ) return sdo_geometry is
    l_geom sdo_geometry := null;
    l_exif xmltype      := null;

    l_geomlat     number;
    l_geomlatr    varchar2(100);
    l_geomlon     number;
    l_geomlonr    varchar2(100);
  begin
    l_exif := get_exif(p_image); 
    if l_exif is not null then 
      select 
       to_char(lon, '9999D999999999999999', 'nls_numeric_characters=''.,'''), 
       lontype, 
       to_char(lat, '9999D999999999999999', 'nls_numeric_characters=''.,'''), 
       lattype 
      into l_geomlon, l_geomlonr, l_geomlat, l_geomlatr
      from
       xmltable(
         xmlnamespaces(DEFAULT 'http://xmlns.oracle.com/ord/meta/exif' ),
         '/exifMetadata/GpsIfd'
         passing l_exif
         columns 
           lat varchar2(30) path '/GpsIfd/GPSLatitude',
           lon varchar2(30) path '/GpsIfd/GPSLongitude',
           lattype varchar2(30) path '/GpsIfd/GPSLatitudeRef',
           lontype varchar2(30) path '/GpsIfd/GPSLongitudeRef'
       ) gps;
      if lower(l_geomlonr) = 'west longitude' then l_geomlon := -(abs(l_geomlon)); end if;
      if lower(l_geomlatr) = 'south latitude' then l_geomlat := -(abs(l_geomlat)); end if;
      if l_geomlon is not null and l_geomlat is not null then 
        l_geom := sdo_geometry(2001, 8307, sdo_point_type(l_geomlon, l_geomlat, null), null, null);
      end if;
    end if;
    return l_geom;
  end get_location;
end;
/
sho err
In SQL*Plus the package looks like this ...
SQL> desc pkg_ordimage
FUNCTION GET_EXIF RETURNS XMLTYPE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN
PROCEDURE ROTATE
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN/OUT
 P_DEGREE                       NUMBER                  IN
FUNCTION ROTATE RETURNS BLOB
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN
 P_DEGREE                       NUMBER                  IN
PROCEDURE THUMBNAIL
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P_IMAGE                        BLOB                    IN/OUT
 P_WIDTH                        NUMBER                  IN
 P_HEIGHT                       NUMBER                  IN
:
In SQL Developer, we also get benefits from the package ...
A thumbnail can now be generated as follows (of course, SQL*Plus does not show too much) ...
SQL> select pkg_ordimage.thumbnail(image,200,200) thumbnail
  2  from tab_images where id = 1;

THUMBNAIL
--------------------------------------------------------------------------------
FFD8FFE000104A46494600010100000100010000FFDB004300080606070605080707070909080A0C
140D0C0B0B0C1912130F141D1A1F1E1D1A1C1C20242E2720222C231C1C2837292C30313434341F27
:
EXIF metadata can be extracted with the new GET_EXIF function.
SQL> select pkg_ordimage.get_exif(image).getclobval() exif_xml
  2  from tab_images where id = 1;

EXIF_XML
--------------------------------------------------------------------------------
<exifMetadata xmlns="http://xmlns.oracle.com/ord/meta/exif" xsi:schemaLocation="
http://xmlns.oracle.com/ord/meta/exif http://xmlns.oracle.com/ord/meta/exif" xml
ns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <TiffIfd>
      <Make tag="271">Apple</Make>
      <Model tag="272">iPhone 4S</Model>
If a geotagged image, the location is also contained in the EXIF metadata. We could extract the location using XML functions and the XML tags GPSLatitude and GPSLongitude), but having a package function returning an Oracle SDO_GEOMETRY (the "location data type) is of course much more elegant.
SQL> select pkg_ordimage.get_location(image).getclobval() location
  2  from tab_images where id = 1;

LOCATION
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(11.83, 48.175667, NULL), NULL, NULL)
With the new package, Oracle's built-in image processing capabilities become really accessible to PL/SQL applications. Using this in an APEX application is now very easy and every PL/SQL developer can do it. For those who like to extend the package: The complete list of commands available for ORDIMAGE.PROCESS is here and the function ORDIMAGE.APPLYWATERMARK is definitively worth having a look.

Kommentare:

fateh.cis hat gesagt…

Hello,
I suppose that I am lucky to find your post even though it is not in English.
Can you please help in Bulk processing images?
I posted this question last month, but no answer till now.
Regards,
Fateh

Carsten Czarski hat gesagt…

Hi Fateh,

I just answered your question ...
https://forums.oracle.com/message/11095432#11095432

Best regards

-Carsten

Beliebte Postings