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

Your Output will be something like this.

<ordImageAttributes xmlns="" xmlns:xsi="" xsi:schemaLocation=""></ordImageAttributes>
<iptcMetadata xmlns="" xsi:schemaLocation="" xmlns:xsi=""/>

<exifMetadata xmlns="" xsi:schemaLocation="" xmlns:xsi="">
      <Orientation tag="274">top left</Orientation> -- This is what I am interested in
   <ExifIfd tag="34665">
      <ColorSpace tag="40961">sRGB</ColorSpace>
      <PixelXDimension tag="40962">3264</PixelXDimension>
      <PixelYDimension tag="40963">2448</PixelYDimension>

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