oraclespatialquery.java

用于GIS(全球地理系统)的分析和处理的代码。

/*
 * This file is part of the GeOxygene project source files. 
 * 
 * GeOxygene aims at providing an open framework which implements OGC/ISO specifications for 
 * the development and deployment of geographic (GIS) applications. It is a open source 
 * contribution of the COGIT laboratory at the Institut G閛graphique National (the French 
 * National Mapping Agency).
 * 
 * See: http://oxygene-project.sourceforge.net 
 *  
 * Copyright (C) 2005 Institut G閛graphique National
 *
 * This library is free software; you can redistribute it and/or modify it under the terms
 * of the GNU Lesser General Public License as published by the Free Software Foundation; 
 * either version 2.1 of the License, or any later version.
 *
 * This library is distributed in the hope that it will be useful, but WITHOUT ANY 
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A 
 * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License along with 
 * this library (see file LICENSE if present); if not, write to the Free Software 
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 *  
 */

package fr.ign.cogit.geoxygene.datatools.oracle;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oracle.sdoapi.OraSpatialManager;
import oracle.sdoapi.geom.Curve;
import oracle.sdoapi.geom.CurvePolygon;
import oracle.sdoapi.geom.CurveString;
import oracle.sdoapi.geom.Geometry;
import oracle.sdoapi.geom.GeometryFactory;
import oracle.sdoapi.geom.LineString;
import oracle.sdoapi.geom.MultiCurve;
import oracle.sdoapi.geom.MultiCurvePolygon;
import oracle.sdoapi.geom.MultiCurveString;
import oracle.sdoapi.geom.MultiLineString;
import oracle.sdoapi.geom.MultiPolygon;
import oracle.sdoapi.geom.MultiSurface;
import oracle.sdoapi.geom.Polygon;
import oracle.sdoapi.geom.Surface;
import oracle.sql.ARRAY;
import oracle.sql.STRUCT;
import fr.ign.cogit.geoxygene.datatools.Geodatabase;
import fr.ign.cogit.geoxygene.datatools.Metadata;
import fr.ign.cogit.geoxygene.spatial.coordgeom.DirectPosition;
import fr.ign.cogit.geoxygene.spatial.coordgeom.DirectPositionList;
import fr.ign.cogit.geoxygene.spatial.coordgeom.GM_Envelope;
import fr.ign.cogit.geoxygene.spatial.coordgeom.GM_Polygon;
import fr.ign.cogit.geoxygene.spatial.geomaggr.GM_Aggregate;
import fr.ign.cogit.geoxygene.spatial.geomprim.GM_Point;
import fr.ign.cogit.geoxygene.spatial.geomprim.GM_Surface;
import fr.ign.cogit.geoxygene.spatial.geomroot.GM_Object;


/**
 * Methode pour encapsuler l'appel d'une requete spatiale dans Oracle, ou d'une methode de la SDOAPI. 
 * Il peut s'agir de requetes geometriques, calcul d'index spatial, chargement d'objets, initialisation des metadonnees spatiales ...
 * Cette classe est appelee par les methodes de GM_Object, par des methodes de FT_Feature, par des methodes de Geodatabase.
 * Le but est d'assurer l'independance de ces classes vis a vis d'Oracle,
 * et de concentrer dans une classe tout ce qui depend d'Oracle.
 *
 * <P>On suppose l'existence d'une table TEMP_REQUETE avec une colonne GID(number) et GEOM(SDO_GEOMETRY).
 * On vide cette table, puis on recopie le(s) GM_Object passe(s) en parametre dans cette table,
 * et on execute sur lui(eux) la requete passee en parametre.
 *
 * INUTILE DEPUIS LE PASSAGE A JTS (sauf pour l'extraction par zone, et a l'initialisation des metadonnees) !
 *
 * @author Thierry Badard & Arnaud Braun
 * @version 1.1  
 *
 */



public class OracleSpatialQuery {
    
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    /// methodes generiques //////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    /** 1 seul objet geometrique passe en parametre - renvoie une geometrie */
    private static GM_Object executeGeometry(Geodatabase data, GM_Object isoGeom, String query) {
       
        // ceci sera le result
        GM_Object result = null;
        
        try {
            // initialise la connection a Oracle
            Connection conn = data.getConnection();
            
            // vide la table TEMP_REQUETE
            String update = "DELETE FROM TEMP_REQUETE";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.executeUpdate();
            ps.close();            
            
            // convertit isoGeom en type sdoapi, et l'ecrit dans la table TEMP_REQUETE            
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();
            
            // execute la requete
            ps = conn.prepareStatement(query);            
            ResultSet rs = (ResultSet)ps.executeQuery();
            while (rs.next()) 
                result = (GM_Object)(GeometryConvertor.Sdo2GM_Object (rs.getObject(1)));
            rs.close();
            ps.close();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
            
        return result;
    }
    
        
    /** 2 objets geometriques passes en parametre - renvoie une geometrie */
	private static GM_Object executeGeometry(Geodatabase data, GM_Object isoGeom1, GM_Object isoGeom2, String query)  {
        
        // ceci sera le result
        GM_Object result = null;
        
        try {       
            // initialise la connection a Oracle
            Connection conn = data.getConnection();
            
            // vide la table TEMP_REQUETE
            String update = "DELETE FROM TEMP_REQUETE";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom1, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();            

            // execute la requete
            ps = conn.prepareStatement(query);            
            ResultSet rs = (ResultSet)ps.executeQuery();
            while (rs.next()) 
                result = (GM_Object)(GeometryConvertor.Sdo2GM_Object (rs.getObject(1)));
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return result;
    }
    
        
    /** 2 objets geometriques passes en parametre - renvoie un string */
	private static String executeString(Geodatabase data, GM_Object isoGeom1, GM_Object isoGeom2, String query)  {
        
        // ceci sera le result
        String result = "";
        
        try {        
            // initialise la connection a Oracle
            Connection conn = data.getConnection();

            // vide la table TEMP_REQUETE
            String update = "DELETE FROM TEMP_REQUETE";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom1, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();            

            // execute la requete
            ps = conn.prepareStatement(query);            
            ResultSet rs = (ResultSet)ps.executeQuery();
            while (rs.next()) 
                result = rs.getString(1);
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
             
        // renvoi du resultat
        return result;
    }
    
        
    /** 2 objets geometriques passes en parametre - renvoie un double */
    private static double executeDouble(Geodatabase data, GM_Object isoGeom1, GM_Object isoGeom2, String query)  {
        
        // ceci sera le result
        double result = 0.0;
        
        try {
            // la requete renvoie un BigDecimal, qu'on convertira ensuite en double
            Object obj = null;
            BigDecimal theBig = null;

            // initialise la connection a Oracle
            Connection conn = data.getConnection();
            
            // vide la table TEMP_REQUETE
            String update = "DELETE FROM TEMP_REQUETE";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom1, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();
            
            // execute la requete
            ps = conn.prepareStatement(query);
            ResultSet rs = (ResultSet)ps.executeQuery();
            while (rs.next()) 
                obj = rs.getObject(1);
            rs.close();
            ps.close();

            // conversion du resultat en double
            theBig = (BigDecimal)obj;
            result = theBig.doubleValue();
            
        } catch (Exception e) {
            e.printStackTrace();
        }
 
        // renvoi du resultat
        return result;
    }
    

    /** 1 objet geometrique passe en parametre - renvoie un double */
	private static double executeDouble(Geodatabase data, GM_Object isoGeom1, String query)  {
        
        // ceci sera le result
        double result = 0.0;
        
        try {
            // la requete renvoie un BigDecimal, qu'on convertira ensuite en double
            Object obj = null;
            BigDecimal theBig = null;

            // initialise la connection a Oracle
            Connection conn = data.getConnection(); 

            // vide la table TEMP_REQUETE
            String update = "DELETE FROM TEMP_REQUETE";
            PreparedStatement ps = conn.prepareStatement(update);
            ps.executeUpdate();
            ps.close();            

            // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom1, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);