Thursday, October 23, 2014

Extracting Image meta data using Oracle ORDSYS.Image

Let's say you have a table "PICTURE" has a blob column "PIC_BLOB" which contains some image and you want to extract the meta data of the image, here is the query,

Don't ask me any more questions. I spilt my hair to get it working.

PS: If you are asking me why did I do it. Because Photos taken with iPhone are rotated randomly and learned that, rotation angle is stored in the meta data


select VALUE(xml_type).getstringval() 
FROM table(
    select ORDSYS.ORDImage.getMetadata(
                ordsys.ordimage(ordsys.ordsource( PIC_BLOB, null, null, null, null, 1),null, null, null, null, null, null, null)
            )
      from PICTURE WHERE ID=1
)xml_type

Your Output will be something like this.

<ordImageAttributes xmlns="http://xmlns.oracle.com/ord/meta/ordimage" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/ordimage http://xmlns.oracle.com/ord/meta/ordimage"></ordImageAttributes>
<iptcMetadata xmlns="http://xmlns.oracle.com/ord/meta/iptc" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/iptc http://xmlns.oracle.com/ord/meta/iptc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>

<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" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <TiffIfd>
      <Orientation tag="274">top left</Orientation> -- This is what I am interested in
   </TiffIfd>
   <ExifIfd tag="34665">
      <ColorSpace tag="40961">sRGB</ColorSpace>
      <PixelXDimension tag="40962">3264</PixelXDimension>
      <PixelYDimension tag="40963">2448</PixelYDimension>
   </ExifIfd>
</exifMetadata>


Tip:If you are just interested in Orientation tag then apply Regular Expression like this
replace
VALUE(xml_type).getstringval() 
with
 max(REGEXP_SUBSTR(
               VALUE(xml_type).getStringval(),'<Orientation (.*)>(.*)</Orientation>',1,1,'i',2))

2 comments:

  1. Hi Balaji,

    Do you have any tips on how to extract the Date Taken EXIF attribute using PL/SQL when the JPEG is not a BLOB?

    In other words I have the server file path of the image and would like to extract the date.

    Thanks!

    ReplyDelete
    Replies
    1. Did you try
      select ORDSYS.ORDImage.getMetadata(
      ordsys.ordimage(ordsys.ordsource( PIC_BLOB, null, null, null, null, 1),null, null, null, null, null, null, null)
      )
      from dual

      First read the file into BLOB variable PIC_BLOB

      Delete