oraclespatialquery.java

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

            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 classe de FT_Feature et 1 GM_Objet en parametre - renvoie une liste d'identifiants */ 
	private static List executeFeatureList(Geodatabase data, GM_Object geom, Class theClass, String theQuery) {
        
        // ceci sera le resultat
        List idList = new ArrayList();
        
        try {
            // recherche du tableName
            String tableName = data.getMetadata(theClass).getTableName();
            String pkColumn = data.getMetadata(theClass).getIdColumnName();
            // 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 geom en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
            STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(geom, conn);
            update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
            ps = conn.prepareStatement(update);
            ps.setObject(1, str);
            ps.executeUpdate();
            ps.close();

            // execute la requete
            String query = "SELECT t."+pkColumn+" FROM "+tableName+" t, TEMP_REQUETE tt ";
            query = query+theQuery;
            ps = conn.prepareStatement(query);
            ResultSet rs = (ResultSet)ps.executeQuery();
            while (rs.next()) 
                idList.add(rs.getObject(1));
            rs.close();
            ps.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

        // renvoi du resultat
        return idList;    
    }
  
    

    
    
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    /// methodes de la SDOAPI ////////////////////////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    public static boolean isSimple (GM_Object isoGeom)  {
        try {
            GeometryFactory gf = OraSpatialManager.getGeometryFactory();
            Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,isoGeom);
            return sdoGeom.isSimple();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }        
    }
    
    
    public static int getCoorDim (GM_Object isoGeom)  {
        try {
            GeometryFactory gf = OraSpatialManager.getGeometryFactory();
            Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,isoGeom);
            return sdoGeom.getCoordinateDimension();
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        }
    }
    
    
    public static double length (GM_Object isoGeom)  {
        try {
            GeometryFactory gf = OraSpatialManager.getGeometryFactory();
            Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,isoGeom);
            if (sdoGeom.getGeometryType() == Curve.class) return ((Curve)sdoGeom).length();
            else if (sdoGeom.getGeometryType() == LineString.class) return ((LineString)sdoGeom).length();
            else if (sdoGeom.getGeometryType() == CurveString.class) return ((CurveString)sdoGeom).length();
            else if (sdoGeom.getGeometryType() == MultiCurve.class) return ((MultiCurve)sdoGeom).length();
            else if (sdoGeom.getGeometryType() == MultiCurveString.class) return ((MultiCurveString)sdoGeom).length();
            else if (sdoGeom.getGeometryType() == MultiLineString.class) return ((MultiLineString)sdoGeom).length();
            else {
                System.out.println("### ATTENTION oracle.SpatialQuery.length() : mauvais type - renvoie -1 ###");
                return -1.0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return -1.0;
        }
    }
    
    
    public static double area (GM_Object isoGeom)  {
        try {
            GeometryFactory gf = OraSpatialManager.getGeometryFactory();
            Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,isoGeom);
            if (sdoGeom.getGeometryType() == Surface.class) return ((Surface)sdoGeom).area();
            else if (sdoGeom.getGeometryType() == CurvePolygon.class) return ((CurvePolygon)sdoGeom).area();
            else if (sdoGeom.getGeometryType() == Polygon.class) return ((Polygon)sdoGeom).area();
            else if (sdoGeom.getGeometryType() == MultiSurface.class) return ((MultiSurface)sdoGeom).area();
            else if (sdoGeom.getGeometryType() == MultiCurvePolygon.class) return ((MultiCurvePolygon)sdoGeom).area();
            else if (sdoGeom.getGeometryType() == MultiPolygon.class) return ((MultiPolygon)sdoGeom).area();
            else {
                System.out.println("### ATTENTION oracle.SpatialQuery.area() : mauvais type - renvoie -1 ###");
                return -1.0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return -1.0;
        }
    }

    
    public static double perimeter (GM_Object isoGeom)  {
        try {
            GeometryFactory gf = OraSpatialManager.getGeometryFactory();
            Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf,isoGeom);
            if (sdoGeom.getGeometryType() == Surface.class) return ((Surface)sdoGeom).perimeter();
            else if (sdoGeom.getGeometryType() == CurvePolygon.class) return ((CurvePolygon)sdoGeom).perimeter();
            else if (sdoGeom.getGeometryType() == Polygon.class) return ((Polygon)sdoGeom).perimeter();
            else if (sdoGeom.getGeometryType() == MultiSurface.class) return ((MultiSurface)sdoGeom).perimeter();
            else if (sdoGeom.getGeometryType() == MultiCurvePolygon.class) return ((MultiCurvePolygon)sdoGeom).perimeter();
            else if (sdoGeom.getGeometryType() == MultiPolygon.class) return ((MultiPolygon)sdoGeom).perimeter();
            else {
                System.out.println("### ATTENTION oracle.SpatialQuery.perimeter() : mauvais type - renvoie -1 ###");
                return -1.0;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return -1.0;
        }
    }
    

    
    
    
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    /// requetes geometriques Oracle sur des objets //////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////////////////////////////    
    public static double distance (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {
        String query = "SELECT SDO_GEOM.SDO_DISTANCE(t.geom,tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
        double result = OracleSpatialQuery.executeDouble(data,geom1,geom2,query);
        return result;
    }
    
    
    public static DirectPosition centroid (Geodatabase data, double tolerance, GM_Object geom1) {
        String query = "SELECT SDO_GEOM.SDO_CENTROID(t.geom,"+tolerance+") FROM TEMP_REQUETE t WHERE t.GID = 0";
        GM_Point thePoint = (GM_Point)OracleSpatialQuery.executeGeometry(data,geom1,query);
        return thePoint.getPosition();
    }
        
    
    public static DirectPosition representativePoint (Geodatabase data, double tolerance, GM_Object geom1) {
        String query = "SELECT SDO_GEOM.SDO_POINTONSURFACE(t.geom,"+tolerance+") FROM TEMP_REQUETE t WHERE t.GID = 0";
        GM_Point thePoint = (GM_Point)OracleSpatialQuery.executeGeometry(data,geom1,query);
        return thePoint.getPosition();
    }
    
    
    public static GM_Polygon convexHull (Geodatabase data, double tolerance, GM_Object geom1) {
        String query = "SELECT SDO_GEOM.SDO_CONVEXHULL(t.geom,"+tolerance+") FROM TEMP_REQUETE t WHERE t.GID = 0";
        return (GM_Polygon)OracleSpatialQuery.executeGeometry(data,geom1,query);
    }        
        
        
    public static GM_Object buffer (Geodatabase data, double tolerance, double radius, GM_Object geom1) {        
        String query = "SELECT SDO_GEOM.SDO_BUFFER(t.geom,"+radius+","+tolerance+") FROM TEMP_REQUETE t WHERE t.GID = 0";
        return OracleSpatialQuery.executeGeometry(data,geom1,query);
    }
                   
            
    public static boolean intersects (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {  
        String query = "SELECT SDO_GEOM.RELATE(t.geom,'disjoint',tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
        String result = OracleSpatialQuery.executeString(data,geom1,geom2,query);
        if (result.compareToIgnoreCase("FALSE") == 0) {
            query = "SELECT SDO_GEOM.RELATE(t.geom,'touch',tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
            result = OracleSpatialQuery.executeString(data,geom1,geom2,query);
            if (result.compareToIgnoreCase("FALSE") == 0)
                return true;
        }
        return false;
    }
    
    
    public static boolean equals (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {      
        String query = "SELECT SDO_GEOM.RELATE(t.geom,'equal',tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
        String result = OracleSpatialQuery.executeString(data,geom1,geom2,query);
        if (result.compareToIgnoreCase("TRUE") == 0) return true;
        else return false;
    }        
        
        
    public static GM_Object union (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {            
        String query = "SELECT SDO_GEOM.SDO_UNION(t.geom,tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
        return OracleSpatialQuery.executeGeometry(data,geom1,geom2,query);
    }

    
    public static GM_Object intersection (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {            
        String query = "SELECT SDO_GEOM.SDO_INTERSECTION(t.geom,tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
        return OracleSpatialQuery.executeGeometry(data,geom1,geom2,query);        
    }
     
    
    public static GM_Object difference (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {     
        String query = "SELECT SDO_GEOM.SDO_DIFFERENCE(t.geom,tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
        return OracleSpatialQuery.executeGeometry(data,geom1,geom2,query);
    }

    
    public static GM_Object symmetricDifference (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {  
        String query = "SELECT SDO_GEOM.SDO_XOR(t.geom,tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
        return OracleSpatialQuery.executeGeometry(data,geom1,geom2,query);
    }    
    
    
    public static boolean contains (Geodatabase data, double tolerance, GM_Object geom1, GM_Object geom2) {      
        String query = "SELECT SDO_GEOM.RELATE(t.geom,'contains',tt.geom,"+tolerance+") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
        String result = OracleSpatialQuery.executeString(data,geom1,geom2,query);
        if (result.compareToIgnoreCase("FALSE") == 0) return false;
        else return true;
    }    
    

    public static boolean contains (Geodatabase data, double tolerance, GM_Object geom1, DirectPosition P) {
        GM_Point g = new GM_Point(P);
        return OracleSpatialQuery.contains(data,tolerance,geom1,g);
    }
    

    public static GM_Envelope mbr (Geodatabase data, GM_Object geom1) {        
        String query = "SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.geom,1) FROM TEMP_REQUETE t WHERE t.GID = 0";
        double Xmin = OracleSpatialQuery.executeDouble(data,geom1,query);
        query = "SELECT SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.geom,1) FROM TEMP_REQUETE t WHERE t.GID = 0";
        double Xmax = OracleSpatialQuery.executeDouble(data,geom1,query);
        query = "SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.geom,2) FROM TEMP_REQUETE t WHERE t.GID = 0";
        double Ymin = OracleSpatialQuery.executeDouble(data,geom1,query);
        query = "SELECT SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.geom,2) FROM TEMP_REQUETE t WHERE t.GID = 0";
        double Ymax = OracleSpatialQuery.executeDouble(data,geom1,query);                
        return new GM_Envelope(Xmin,Xmax,Ymin,Ymax);
    }


    
    
    
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    /// requetes Oracle pour charger des objets //////////////////////////////////////////////////////////
    //////////////////////////////////////////////////////////////////////////////////////////////////////
    public static List loadAllFeatures(Geodatabase data, Class theClass, GM_Object geom) {
        String query = "WHERE tt.GID=0 AND SDO_RELATE(t.geom,tt.geom,'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'";
        return OracleSpatialQuery.executeFeatureList(data,geom,theClass,query);
    }

    
    public static List loadAllFeatures(Geodatabase data, Class theClass, GM_Object geom, double dist) {
        String query = " WHERE tt.GID=0 AND SDO_WITHIN_DISTANCE(t.geom,tt.geom,'distance="+dist+"') = 'TRUE'";
        return OracleSpatialQuery.executeFeatureList(data,geom,theClass,query);
    }    
    
   
   	 //////////////////////////////////////////////////////////////////////////////////////////////////////
     /// calcul de buffer "optimise" pour les agregats ... ////////////////////////////////////////////////
	//////////////////////////////////////////////////////////////////////////////////////////////////////    
     public static GM_Object bufferAgregat (Geodatabase data, double tolerance, double radius, GM_Object geom) {   

         // ceci sera le result
         GM_Object result = null;
                        
         try { 
             // initialise la connection a Oracle
             Connection conn = data.getConnection();
             Statement stm = conn.createStatement(); 
             
             // vide la table TEMP_REQUETE
             String update = "DELETE FROM TEMP_REQUETE";
             stm.executeQuery(update);