    001    //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.4_testing/src/org/deegree/io/oraclegeoraster/GeoRasterReader.java $
    002    /*----------------------------------------------------------------------------
    003     This file is part of deegree, http://deegree.org/
    004     Copyright (C) 2001-2009 by:
    005     Department of Geography, University of Bonn
    006     and
    007     lat/lon GmbH
    008     and
    009     grit GmbH
    010     http://www.grit.de
    011
    012     This library is free software; you can redistribute it and/or modify it under
    013     the terms of the GNU Lesser General Public License as published by the Free
    014     Software Foundation; either version 2.1 of the License, or (at your option)
    015     any later version.
    016     This library is distributed in the hope that it will be useful, but WITHOUT
    017     ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
    018     FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
    019     details.
    020     You should have received a copy of the GNU Lesser General Public License
    021     along with this library; if not, write to the Free Software Foundation, Inc.,
    022     59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
    023
    024     Contact information:
    025
    026     lat/lon GmbH
    027     Aennchenstr. 19, 53177 Bonn
    028     Germany
    029     http://lat-lon.de/
    030
    031     Department of Geography, University of Bonn
    032     Prof. Dr. Klaus Greve
    033     Postfach 1147, 53001 Bonn
    034     Germany
    035     http://www.geographie.uni-bonn.de/deegree/
    036
    037     e-mail: info@deegree.org
    038     ----------------------------------------------------------------------------*/
    039    package org.deegree.io.oraclegeoraster;
    040
    041    import java.awt.Graphics2D;
    042    import java.awt.Image;
    043    import java.awt.image.BufferedImage;
    044    import java.awt.image.RenderedImage;
    045    import java.io.IOException;
    046    import java.lang.reflect.Method;
    047    import java.sql.Connection;
    048    import java.sql.PreparedStatement;
    049    import java.sql.ResultSet;
    050    import java.sql.SQLException;
    051    import java.sql.Statement;
    052    import java.util.Properties;
    053
    054    import oracle.spatial.georaster.GeoRasterException;
    055    import oracle.spatial.georaster.JGeoRaster;
    056    import oracle.spatial.georaster.JGeoRasterMeta;
    057    import oracle.sql.STRUCT;
    058
    059    import org.deegree.framework.log.ILogger;
    060    import org.deegree.framework.log.LoggerFactory;
    061    import org.deegree.framework.util.StringTools;
    062    import org.deegree.graphics.transformation.GeoTransform;
    063    import org.deegree.graphics.transformation.WorldToScreenTransform;
    064    import org.deegree.io.DBConnectionPool;
    065    import org.deegree.io.JDBCConnection;
    066    import org.deegree.model.spatialschema.Envelope;
    067    import org.deegree.ogcwebservices.InvalidParameterValueException;
    068
    069    /**
    070     * @version $Revision: 21672 $
    071     * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
    072     * @author <a href="mailto:reichhelm@grit.de">Stephan Reichhelm</a>
    073     * @author <a href="mailto:lipski@grit.de">Eryk Lipski</a>
    074     * @author last edited by: $Author: apoth $
    075     *
    076     *         Currently only tested on Oracle 10gR2.
    077     *
    078     * @version 1.0. $Revision: 21672 $, $Date: 2009-12-29 09:44:20 +0100 (Di, 29. Dez 2009) $
    079     *
    080     * @since 2.0
    081     */
    082    public class GeoRasterReader {
    083
    084        private static final ILogger LOG = LoggerFactory.getLogger( GeoRasterReader.class );
    085
    086        /**
    087         *
    088         * @param grDesc
    089         * @param envelope
    090         *            requested envelope
    091         * @param width
    092         * @param height
    093         * @return rendered image
    094         * @throws SQLException
    095         * @throws IOException
    096         * @throws GeoRasterException
    097         * @throws Exception
    098         */
    099        public static RenderedImage exportRaster( GeoRasterDescription grDesc, Envelope envelope, float width, float height )
    100                                throws SQLException, IOException, GeoRasterException, Exception {
    101
    102            DBConnectionPool pool = DBConnectionPool.getInstance();
    103            JDBCConnection jdbc = grDesc.getJdbcConnection();
    104            Connection con = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    105
    106            RenderedImage ri = exportRaster( con, envelope, grDesc.getRdtTable(), grDesc.getTable(), grDesc.getColumn(),
    107                                             grDesc.getIdentification(), grDesc.getLevel(), width, height );
    108
    109            pool.releaseConnection( con, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
    110
    111            return ri;
    112        }
    113
    114        /**
    115         *
    116         * @param connection
    117         *            connection to Oracle database
    118         * @param envelope
    119         *            requested area
    120         * @param rasterRDT
    121         *            name of the RDT-table
    122         * @param rasterTable
    123         *            name of the table containing a geo raster column
    124         * @param geoRasterCol
    125         *            name of the geoRaster column
    126         * @param identification
    127         *            SQL where clause that identifies the raster of interest
    128         * @param level
    129         *            requested resolution level
    130         * @param width
    131         * @param height
    132         * @return rendered image
    133         * @throws SQLException
    134         * @throws IOException
    135         * @throws GeoRasterException
    136         * @throws Exception
    137         */
    138        public static RenderedImage exportRaster( Connection connection, Envelope envelope, String rasterRDT,
    139                                                  String rasterTable, String geoRasterCol, String identification,
    140                                                  int level, float width, float height )
    141                                throws Exception {
    142            RenderedImage img = null;
    143            try {
    144
    145                int rasterID = readRasterID( connection, identification, rasterTable, geoRasterCol );
    146
    147                STRUCT struct = readGeoRasterMetadata( connection, rasterRDT, rasterTable, geoRasterCol, rasterID );
    148
    149                int major = connection.getMetaData().getDriverMajorVersion();
    150                int minor = connection.getMetaData().getDriverMinorVersion();
    151
    152                JGeoRaster jGeoRaster = null;
    153                if ( major == 10 && minor == 1 ) {
    154                    // synthax for Oracle 10g R1
    155                    Class<?>[] clzz = new Class[] { STRUCT.class };
    156                    Method method = JGeoRaster.class.getMethod( "load", clzz );
    157                    jGeoRaster = (JGeoRaster) method.invoke( null, new Object[] { struct } );
    158                    jGeoRaster = JGeoRaster.load( struct );
    159                } else if ( major == 10 && minor == 2 ) {
    160                    // synthax for Oracle 10g R2
    161                    Class<?>[] clzz = new Class[] { STRUCT.class, Connection.class, boolean.class };
    162                    Method method = JGeoRaster.class.getMethod( "load", clzz );
    163                    Object[] params = new Object[] { struct, connection, false };
    164                    jGeoRaster = (JGeoRaster) method.invoke( null, params );
    165                } else {
    166                    String s = StringTools.concat( 250, "Oracle driver ", major, ".", minor,
    167                                                   " currently not supported for using Georaster functionality. (use 10.1 or 10.2)" );
    168                    throw new InvalidParameterValueException( s );
    169                }
    170                jGeoRaster.setViewerUse( true );
    171                Properties props = jGeoRaster.getProperties();
    172
    173                int maxWidth = Integer.parseInt( props.getProperty( "rasterInfo/dimensionSize_column" ) );
    174                int maxHeight = Integer.parseInt( props.getProperty( "rasterInfo/dimensionSize_row" ) );
    175
    176                JGeoRasterMeta metaObj = jGeoRaster.getMetadataObject();
    177
    178                // retieve meta-information how row/cells are referenced
    179                String metaTyp = "CC";
    180                String metaTxt = jGeoRaster.getMetadataString();
    181                if ( metaTxt != null
    182                     && metaTxt.indexOf( "<modelCoordinateLocation>UPPERLEFT</modelCoordinateLocation>" ) != -1 )
    183                    metaTyp = "UL";
    184
    185                double xMin = metaObj.getX( 0, 0 );
    186                double xMax = metaObj.getX( maxWidth - 1, maxHeight - 1 );
    187                double sc = Math.pow( 2, level );
    188                double yMin = metaObj.getY( 0, 0 );
    189                double yMax = metaObj.getY( maxWidth - 1, maxHeight - 1 );
    190
    191                double xDiffPx = Math.abs( ( metaObj.getX( 1, 1 ) - xMin ) / 2 );
    192                double yDiffPx = Math.abs( ( metaObj.getY( 1, 1 ) - yMin ) / 2 );
    193
    194                // LOG.logDebug(StringTools.concat(350, "georaster-extend ",
    195                // xMin, " ", yMin, " - ", xMax, " ", yMax, " typ: ", metaTyp ));
    196
    197                /*
    198                 * Difference between UL and CC handling
    199                 *
    200                 * CC: Extend BBOX from middle-middel-pixel values to outer extend (default) (A -> B)
    201                 *
    202                 * B-----* *-----B |\ | | /| | \ | . | / | | A | . | A | | | . | | | | | | *-----* *-----* ... ... *-----*
    203                 * *-----* | | | | | | . | | | A | . | A | | / | . | \ | |/ | | \| B-----* *-----B
    204                 *
    205                 * UL: Extend BBOX from upper-left-pixel values to outer extend (C=A=B; A->B)
    206                 *
    207                 * C-----* A->->-B | | | | | | . | | | | . | | | | . | | | | | | *-----* *-----* ... ... A-----* A-----* | |
    208                 * |\ | v | . | \ | | | . | \ | v | . | \ | | | | \| B-----A *-----B
    209                 */
    210                if ( "UL".equals( metaTyp ) ) {
    211                    // koordinatenreferenzierung oben-links
    212                    yMax -= ( yDiffPx + yDiffPx );
    213                    xMax += ( xDiffPx + xDiffPx );
    214                } else {
    215                    // koordinatenreferenzierung center-center
    216                    xMin -= xDiffPx;
    217                    yMin += yDiffPx;
    218                    xMax += xDiffPx;
    219                    yMax -= yDiffPx;
    220                }
    221
    222                WorldToScreenTransform wld2ora = new WorldToScreenTransform( xMin, yMin, xMax, yMax, 0, 0, maxWidth - 1,
    223                                                                             maxHeight - 1 );
    224
    225                int xMinCell = (int) Math.round( wld2ora.getDestX( envelope.getMin().getX() ) / sc );
    226                int xMaxCell = (int) Math.round( wld2ora.getDestX( envelope.getMax().getX() ) / sc );
    227                int yMaxCell = (int) Math.round( wld2ora.getDestY( envelope.getMin().getY() ) / sc );
    228                int yMinCell = (int) Math.round( wld2ora.getDestY( envelope.getMax().getY() ) / sc );
    229
    230                if ( LOG.isDebug() ) {
    231                    LOG.logDebug( StringTools.concat( 400, "req-env: ", envelope.getMin().getX(), " ",
    232                                                      envelope.getMin().getY(), " - ", envelope.getMax().getX(), " ",
    233                                                      envelope.getMax().getY(), " lvl: ", level, " typ: ", metaTyp,
    234                                                      " row/cell-env: ", xMinCell, " ", yMinCell, " - ", xMaxCell, " ",
    235                                                      yMaxCell, " dx/y: ", xDiffPx, "/", yDiffPx ) );
    236                }
    237
    238                // TODO: grit: testen und entfernen
    239                // if ( xMinCell < 0 )
    240                // xMinCell = 0;
    241                // if ( yMinCell < 0 )
    242                // yMinCell = 0;
    243                // if ( xMaxCell < 0 )
    244                // xMaxCell = 0;
    245                // if ( yMaxCell < 0 )
    246                // yMaxCell = 0;
    247
    248                img = jGeoRaster.getRasterImage( connection, level, xMinCell, yMinCell, xMaxCell, yMaxCell );
    249
    250                /*
    251                 * rearange returned image in new image with size of request
    252                 */
    253                if ( img != null ) {
    254                    /*
    255                     * 1) calculate result bbox in nativ/georaster-level resulution (m/px from oracle georaster) 2) convert
    256                     * to outer bbox 3) calculate result resulution (m/px from result) 4) convert from outer bbox to bbox in
    257                     * result resulution (middle px) 5) calculate position on result-image (px-box) 6) draw (maybe streched)
    258                     * returned image inside the px-box
    259                     */
    260
    261                    // 1a) returned cell values
    262                    int rcMinX = ( xMinCell < 0 ) ? 0 : xMinCell;
    263                    int rcMinY = ( yMinCell < 0 ) ? 0 : yMinCell;
    264                    int rcMaxX = rcMinX + ( img.getWidth() - 1 );
    265                    int rcMaxY = rcMinY + ( img.getHeight() - 1 );
    266
    267                    // 1b) cell values to world
    268                    double rwMinX = metaObj.getX( (int) ( rcMinX * sc ), (int) ( rcMinY * sc ) );
    269                    double rwMinY = metaObj.getY( (int) ( rcMinX * sc ), (int) ( rcMinY * sc ) );
    270                    double rwMaxX = metaObj.getX( (int) ( rcMaxX * sc ), (int) ( rcMaxY * sc ) );
    271                    double rwMaxY = metaObj.getY( (int) ( rcMaxX * sc ), (int) ( rcMaxY * sc ) );
    272
    273                    // 2) convert to edges (see UL/CC handling above)
    274                    if ( "UL".equals( metaTyp ) ) {
    275                        rwMaxY -= ( yDiffPx + yDiffPx );
    276                        rwMaxX += ( xDiffPx + xDiffPx );
    277                    } else {
    278                        rwMinX -= xDiffPx;
    279                        rwMinY += yDiffPx;
    280                        rwMaxX += xDiffPx;
    281                        rwMaxY -= yDiffPx;
    282                    }
    283
    284                    // 3) calculate result resulution (m/px)
    285                    double resDiffX = envelope.getWidth() / (double) width / 2.0d;
    286                    double resDiffY = envelope.getHeight() / (double) height / 2.0d;
    287
    288                    // 4) convert outer bbox to middle of pixel values
    289                    rwMinX -= resDiffX;
    290                    rwMinY += resDiffY;
    291                    rwMaxX += resDiffX;
    292                    rwMaxY -= resDiffY;
    293
    294                    GeoTransform wld2scr = new WorldToScreenTransform( envelope.getMin().getX(), envelope.getMin().getY(),
    295                                                                       envelope.getMax().getX(), envelope.getMax().getY(),
    296                                                                       0, 0, width - 1, height - 1 );
    297
    298                    // 5) calculate pixel position of returned fragment
    299                    int scMinX = (int) Math.round( wld2scr.getDestX( rwMinX ) );
    300                    int scMinY = (int) Math.round( wld2scr.getDestY( rwMinY ) );
    301                    int scMaxX = (int) Math.round( wld2scr.getDestX( rwMaxX ) );
    302                    int scMaxY = (int) Math.round( wld2scr.getDestY( rwMaxY ) );
    303
    304                    int scWidth = ( scMaxX - scMinX ) + 1;
    305                    int scHeight = ( scMaxY - scMinY ) + 1;
    306
    307                    // 6) draw returned image into result image
    308                    BufferedImage bimg = new BufferedImage( Math.round( width ), Math.round( height ),
    309                                                            BufferedImage.TYPE_INT_ARGB );
    310                    Graphics2D bg = bimg.createGraphics();
    311
    312                    bg.drawImage( (Image) img, scMinX, scMinY, scWidth, scHeight, null );
    313                    bg.dispose();
    314                    img = bimg;
    315                }
    316
    317            } catch ( SQLException e1 ) {
    318                String s = StringTools.concat( 1000, e1.getMessage(), " ", rasterTable, "; ", rasterRDT, "; ",
    319                                               geoRasterCol, "; ", identification, "; level: ", level );
    320                LOG.logError( s, e1 );
    321
    322                throw new RuntimeException( s );
    323            } catch ( Exception e ) {
    324                LOG.logError( "error reading georaster", e );
    325                throw new RuntimeException( e );
    326            }
    327            return img;
    328        }
    329
    330        /**
    331         *
    332         * @param connection
    333         * @param rasterRDT
    334         * @param rasterTable
    335         * @param geoRasterCol
    336         * @param rasterID
    337         * @return
    338         * @throws SQLException
    339         */
    340        private static STRUCT readGeoRasterMetadata( Connection connection, String rasterRDT, String rasterTable,
    341                                                     String geoRasterCol, int rasterID )
    342                                throws SQLException {
    343            PreparedStatement ps = connection.prepareStatement( "select " + geoRasterCol + " from " + rasterTable
    344                                                                + " a where a." + geoRasterCol + ".rasterid = " + rasterID
    345                                                                + " and a." + geoRasterCol + ".rasterdatatable = '"
    346                                                                + rasterRDT.toUpperCase() + "'" );
    347            ResultSet resultset = ps.executeQuery();
    348            if ( !resultset.next() ) {
    349                throw new SQLException( "No GeoRaster object exists at rasterid = " + rasterID + ", RDT = " + rasterRDT );
    350            }
    351
    352            STRUCT struct = (STRUCT) resultset.getObject( geoRasterCol.toUpperCase() );
    353            resultset.close();
    354            return struct;
    355        }
    356
    357        /**
    358         * returns the rasterID of the requested GeoRaster
    359         *
    360         * @param connection
    361         * @param identification
    362         * @param sql
    363         * @return
    364         * @throws SQLException
    365         * @throws GeoRasterException
    366         */
    367        private static int readRasterID( Connection connection, String identification, String rasterTable,
    368                                         String geoRasterCol )
    369                                throws SQLException, GeoRasterException {
    370            String sql = "SELECT  a." + geoRasterCol + ".rasterid FROM " + rasterTable + " a where " + identification;
    371            Statement stmt = connection.createStatement();
    372            ResultSet rs = stmt.executeQuery( sql );
    373            if ( !rs.next() ) {
    374                throw new GeoRasterException( "Georaster with identification = " + identification + " not found!" );
    375            }
    376            int rasterID = rs.getInt( 1 );
    377            stmt.close();
    378            rs.close();
    379            return rasterID;
    380        }
    381    }