0001: /*
0002: * This file is part of the GeOxygene project source files.
0003: *
0004: * GeOxygene aims at providing an open framework which implements OGC/ISO specifications for
0005: * the development and deployment of geographic (GIS) applications. It is a open source
0006: * contribution of the COGIT laboratory at the Institut Géographique National (the French
0007: * National Mapping Agency).
0008: *
0009: * See: http://oxygene-project.sourceforge.net
0010: *
0011: * Copyright (C) 2005 Institut Géographique National
0012: *
0013: * This library is free software; you can redistribute it and/or modify it under the terms
0014: * of the GNU Lesser General Public License as published by the Free Software Foundation;
0015: * either version 2.1 of the License, or any later version.
0016: *
0017: * This library is distributed in the hope that it will be useful, but WITHOUT ANY
0018: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
0019: * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
0020: *
0021: * You should have received a copy of the GNU Lesser General Public License along with
0022: * this library (see file LICENSE if present); if not, write to the Free Software
0023: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
0024: *
0025: */
0026:
0027: package fr.ign.cogit.geoxygene.datatools.oracle;
0028:
0029: import java.math.BigDecimal;
0030: import java.sql.Connection;
0031: import java.sql.PreparedStatement;
0032: import java.sql.ResultSet;
0033: import java.sql.Statement;
0034: import java.util.ArrayList;
0035: import java.util.List;
0036:
0037: import oracle.sdoapi.OraSpatialManager;
0038: import oracle.sdoapi.geom.Curve;
0039: import oracle.sdoapi.geom.CurvePolygon;
0040: import oracle.sdoapi.geom.CurveString;
0041: import oracle.sdoapi.geom.Geometry;
0042: import oracle.sdoapi.geom.GeometryFactory;
0043: import oracle.sdoapi.geom.LineString;
0044: import oracle.sdoapi.geom.MultiCurve;
0045: import oracle.sdoapi.geom.MultiCurvePolygon;
0046: import oracle.sdoapi.geom.MultiCurveString;
0047: import oracle.sdoapi.geom.MultiLineString;
0048: import oracle.sdoapi.geom.MultiPolygon;
0049: import oracle.sdoapi.geom.MultiSurface;
0050: import oracle.sdoapi.geom.Polygon;
0051: import oracle.sdoapi.geom.Surface;
0052: import oracle.sql.ARRAY;
0053: import oracle.sql.STRUCT;
0054: import fr.ign.cogit.geoxygene.datatools.Geodatabase;
0055: import fr.ign.cogit.geoxygene.datatools.Metadata;
0056: import fr.ign.cogit.geoxygene.spatial.coordgeom.DirectPosition;
0057: import fr.ign.cogit.geoxygene.spatial.coordgeom.DirectPositionList;
0058: import fr.ign.cogit.geoxygene.spatial.coordgeom.GM_Envelope;
0059: import fr.ign.cogit.geoxygene.spatial.coordgeom.GM_Polygon;
0060: import fr.ign.cogit.geoxygene.spatial.geomaggr.GM_Aggregate;
0061: import fr.ign.cogit.geoxygene.spatial.geomprim.GM_Point;
0062: import fr.ign.cogit.geoxygene.spatial.geomprim.GM_Surface;
0063: import fr.ign.cogit.geoxygene.spatial.geomroot.GM_Object;
0064:
0065: /**
0066: * Methode pour encapsuler l'appel d'une requete spatiale dans Oracle, ou d'une methode de la SDOAPI.
0067: * Il peut s'agir de requetes geometriques, calcul d'index spatial, chargement d'objets, initialisation des metadonnees spatiales ...
0068: * Cette classe est appelee par les methodes de GM_Object, par des methodes de FT_Feature, par des methodes de Geodatabase.
0069: * Le but est d'assurer l'independance de ces classes vis a vis d'Oracle,
0070: * et de concentrer dans une classe tout ce qui depend d'Oracle.
0071: *
0072: * <P>On suppose l'existence d'une table TEMP_REQUETE avec une colonne GID(number) et GEOM(SDO_GEOMETRY).
0073: * On vide cette table, puis on recopie le(s) GM_Object passe(s) en parametre dans cette table,
0074: * et on execute sur lui(eux) la requete passee en parametre.
0075: *
0076: * INUTILE DEPUIS LE PASSAGE A JTS (sauf pour l'extraction par zone, et a l'initialisation des metadonnees) !
0077: *
0078: * @author Thierry Badard & Arnaud Braun
0079: * @version 1.1
0080: *
0081: */
0082:
0083: public class OracleSpatialQuery {
0084:
0085: //////////////////////////////////////////////////////////////////////////////////////////////////////
0086: /// methodes generiques //////////////////////////////////////////////////////////////////////////////
0087: //////////////////////////////////////////////////////////////////////////////////////////////////////
0088: /** 1 seul objet geometrique passe en parametre - renvoie une geometrie */
0089: private static GM_Object executeGeometry(Geodatabase data,
0090: GM_Object isoGeom, String query) {
0091:
0092: // ceci sera le result
0093: GM_Object result = null;
0094:
0095: try {
0096: // initialise la connection a Oracle
0097: Connection conn = data.getConnection();
0098:
0099: // vide la table TEMP_REQUETE
0100: String update = "DELETE FROM TEMP_REQUETE";
0101: PreparedStatement ps = conn.prepareStatement(update);
0102: ps.executeUpdate();
0103: ps.close();
0104:
0105: // convertit isoGeom en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0106: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0107: isoGeom, conn);
0108: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0109: ps = conn.prepareStatement(update);
0110: ps.setObject(1, str);
0111: ps.executeUpdate();
0112: ps.close();
0113:
0114: // execute la requete
0115: ps = conn.prepareStatement(query);
0116: ResultSet rs = (ResultSet) ps.executeQuery();
0117: while (rs.next())
0118: result = (GM_Object) (GeometryConvertor
0119: .Sdo2GM_Object(rs.getObject(1)));
0120: rs.close();
0121: ps.close();
0122:
0123: } catch (Exception e) {
0124: e.printStackTrace();
0125: }
0126:
0127: return result;
0128: }
0129:
0130: /** 2 objets geometriques passes en parametre - renvoie une geometrie */
0131: private static GM_Object executeGeometry(Geodatabase data,
0132: GM_Object isoGeom1, GM_Object isoGeom2, String query) {
0133:
0134: // ceci sera le result
0135: GM_Object result = null;
0136:
0137: try {
0138: // initialise la connection a Oracle
0139: Connection conn = data.getConnection();
0140:
0141: // vide la table TEMP_REQUETE
0142: String update = "DELETE FROM TEMP_REQUETE";
0143: PreparedStatement ps = conn.prepareStatement(update);
0144: ps.executeUpdate();
0145: ps.close();
0146:
0147: // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0148: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0149: isoGeom1, conn);
0150: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0151: ps = conn.prepareStatement(update);
0152: ps.setObject(1, str);
0153: ps.executeUpdate();
0154: ps.close();
0155:
0156: // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0157: str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2,
0158: conn);
0159: update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
0160: ps = conn.prepareStatement(update);
0161: ps.setObject(1, str);
0162: ps.executeUpdate();
0163: ps.close();
0164:
0165: // execute la requete
0166: ps = conn.prepareStatement(query);
0167: ResultSet rs = (ResultSet) ps.executeQuery();
0168: while (rs.next())
0169: result = (GM_Object) (GeometryConvertor
0170: .Sdo2GM_Object(rs.getObject(1)));
0171: rs.close();
0172: ps.close();
0173:
0174: } catch (Exception e) {
0175: e.printStackTrace();
0176: }
0177:
0178: return result;
0179: }
0180:
0181: /** 2 objets geometriques passes en parametre - renvoie un string */
0182: private static String executeString(Geodatabase data,
0183: GM_Object isoGeom1, GM_Object isoGeom2, String query) {
0184:
0185: // ceci sera le result
0186: String result = "";
0187:
0188: try {
0189: // initialise la connection a Oracle
0190: Connection conn = data.getConnection();
0191:
0192: // vide la table TEMP_REQUETE
0193: String update = "DELETE FROM TEMP_REQUETE";
0194: PreparedStatement ps = conn.prepareStatement(update);
0195: ps.executeUpdate();
0196: ps.close();
0197:
0198: // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0199: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0200: isoGeom1, conn);
0201: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0202: ps = conn.prepareStatement(update);
0203: ps.setObject(1, str);
0204: ps.executeUpdate();
0205: ps.close();
0206:
0207: // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0208: str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2,
0209: conn);
0210: update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
0211: ps = conn.prepareStatement(update);
0212: ps.setObject(1, str);
0213: ps.executeUpdate();
0214: ps.close();
0215:
0216: // execute la requete
0217: ps = conn.prepareStatement(query);
0218: ResultSet rs = (ResultSet) ps.executeQuery();
0219: while (rs.next())
0220: result = rs.getString(1);
0221: rs.close();
0222: ps.close();
0223:
0224: } catch (Exception e) {
0225: e.printStackTrace();
0226: }
0227:
0228: // renvoi du resultat
0229: return result;
0230: }
0231:
0232: /** 2 objets geometriques passes en parametre - renvoie un double */
0233: private static double executeDouble(Geodatabase data,
0234: GM_Object isoGeom1, GM_Object isoGeom2, String query) {
0235:
0236: // ceci sera le result
0237: double result = 0.0;
0238:
0239: try {
0240: // la requete renvoie un BigDecimal, qu'on convertira ensuite en double
0241: Object obj = null;
0242: BigDecimal theBig = null;
0243:
0244: // initialise la connection a Oracle
0245: Connection conn = data.getConnection();
0246:
0247: // vide la table TEMP_REQUETE
0248: String update = "DELETE FROM TEMP_REQUETE";
0249: PreparedStatement ps = conn.prepareStatement(update);
0250: ps.executeUpdate();
0251: ps.close();
0252:
0253: // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0254: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0255: isoGeom1, conn);
0256: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0257: ps = conn.prepareStatement(update);
0258: ps.setObject(1, str);
0259: ps.executeUpdate();
0260: ps.close();
0261:
0262: // convertit isoGeom2 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0263: str = (STRUCT) GeometryConvertor.GM_Object2Sdo(isoGeom2,
0264: conn);
0265: update = "INSERT INTO TEMP_REQUETE VALUES (1,?)";
0266: ps = conn.prepareStatement(update);
0267: ps.setObject(1, str);
0268: ps.executeUpdate();
0269: ps.close();
0270:
0271: // execute la requete
0272: ps = conn.prepareStatement(query);
0273: ResultSet rs = (ResultSet) ps.executeQuery();
0274: while (rs.next())
0275: obj = rs.getObject(1);
0276: rs.close();
0277: ps.close();
0278:
0279: // conversion du resultat en double
0280: theBig = (BigDecimal) obj;
0281: result = theBig.doubleValue();
0282:
0283: } catch (Exception e) {
0284: e.printStackTrace();
0285: }
0286:
0287: // renvoi du resultat
0288: return result;
0289: }
0290:
0291: /** 1 objet geometrique passe en parametre - renvoie un double */
0292: private static double executeDouble(Geodatabase data,
0293: GM_Object isoGeom1, String query) {
0294:
0295: // ceci sera le result
0296: double result = 0.0;
0297:
0298: try {
0299: // la requete renvoie un BigDecimal, qu'on convertira ensuite en double
0300: Object obj = null;
0301: BigDecimal theBig = null;
0302:
0303: // initialise la connection a Oracle
0304: Connection conn = data.getConnection();
0305:
0306: // vide la table TEMP_REQUETE
0307: String update = "DELETE FROM TEMP_REQUETE";
0308: PreparedStatement ps = conn.prepareStatement(update);
0309: ps.executeUpdate();
0310: ps.close();
0311:
0312: // convertit isoGeom1 en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0313: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0314: isoGeom1, conn);
0315: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0316: ps = conn.prepareStatement(update);
0317: ps.setObject(1, str);
0318: ps.executeUpdate();
0319: ps.close();
0320:
0321: // execute la requete
0322: ps = conn.prepareStatement(query);
0323: ResultSet rs = (ResultSet) ps.executeQuery();
0324: while (rs.next())
0325: obj = rs.getObject(1);
0326: rs.close();
0327: ps.close();
0328:
0329: // conversion du resultat en double
0330: theBig = (BigDecimal) obj;
0331: result = theBig.doubleValue();
0332:
0333: } catch (Exception e) {
0334: e.printStackTrace();
0335: }
0336:
0337: // renvoi du resultat
0338: return result;
0339: }
0340:
0341: /** 1 classe de FT_Feature et 1 GM_Objet en parametre - renvoie une liste d'identifiants */
0342: private static List executeFeatureList(Geodatabase data,
0343: GM_Object geom, Class theClass, String theQuery) {
0344:
0345: // ceci sera le resultat
0346: List idList = new ArrayList();
0347:
0348: try {
0349: // recherche du tableName
0350: String tableName = data.getMetadata(theClass)
0351: .getTableName();
0352: String pkColumn = data.getMetadata(theClass)
0353: .getIdColumnName();
0354: // initialise la connection a Oracle
0355: Connection conn = data.getConnection();
0356:
0357: // vide la table TEMP_REQUETE
0358: String update = "DELETE FROM TEMP_REQUETE";
0359: PreparedStatement ps = conn.prepareStatement(update);
0360: ps.executeUpdate();
0361: ps.close();
0362:
0363: // convertit geom en type sdoapi, et l'ecrit dans la table TEMP_REQUETE
0364: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(geom,
0365: conn);
0366: update = "INSERT INTO TEMP_REQUETE VALUES (0,?)";
0367: ps = conn.prepareStatement(update);
0368: ps.setObject(1, str);
0369: ps.executeUpdate();
0370: ps.close();
0371:
0372: // execute la requete
0373: String query = "SELECT t." + pkColumn + " FROM "
0374: + tableName + " t, TEMP_REQUETE tt ";
0375: query = query + theQuery;
0376: ps = conn.prepareStatement(query);
0377: ResultSet rs = (ResultSet) ps.executeQuery();
0378: while (rs.next())
0379: idList.add(rs.getObject(1));
0380: rs.close();
0381: ps.close();
0382:
0383: } catch (Exception e) {
0384: e.printStackTrace();
0385: }
0386:
0387: // renvoi du resultat
0388: return idList;
0389: }
0390:
0391: //////////////////////////////////////////////////////////////////////////////////////////////////////
0392: /// methodes de la SDOAPI ////////////////////////////////////////////////////////////////////////////
0393: //////////////////////////////////////////////////////////////////////////////////////////////////////
0394: public static boolean isSimple(GM_Object isoGeom) {
0395: try {
0396: GeometryFactory gf = OraSpatialManager.getGeometryFactory();
0397: Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf, isoGeom);
0398: return sdoGeom.isSimple();
0399: } catch (Exception e) {
0400: e.printStackTrace();
0401: return false;
0402: }
0403: }
0404:
0405: public static int getCoorDim(GM_Object isoGeom) {
0406: try {
0407: GeometryFactory gf = OraSpatialManager.getGeometryFactory();
0408: Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf, isoGeom);
0409: return sdoGeom.getCoordinateDimension();
0410: } catch (Exception e) {
0411: e.printStackTrace();
0412: return -1;
0413: }
0414: }
0415:
0416: public static double length(GM_Object isoGeom) {
0417: try {
0418: GeometryFactory gf = OraSpatialManager.getGeometryFactory();
0419: Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf, isoGeom);
0420: if (sdoGeom.getGeometryType() == Curve.class)
0421: return ((Curve) sdoGeom).length();
0422: else if (sdoGeom.getGeometryType() == LineString.class)
0423: return ((LineString) sdoGeom).length();
0424: else if (sdoGeom.getGeometryType() == CurveString.class)
0425: return ((CurveString) sdoGeom).length();
0426: else if (sdoGeom.getGeometryType() == MultiCurve.class)
0427: return ((MultiCurve) sdoGeom).length();
0428: else if (sdoGeom.getGeometryType() == MultiCurveString.class)
0429: return ((MultiCurveString) sdoGeom).length();
0430: else if (sdoGeom.getGeometryType() == MultiLineString.class)
0431: return ((MultiLineString) sdoGeom).length();
0432: else {
0433: System.out
0434: .println("### ATTENTION oracle.SpatialQuery.length() : mauvais type - renvoie -1 ###");
0435: return -1.0;
0436: }
0437: } catch (Exception e) {
0438: e.printStackTrace();
0439: return -1.0;
0440: }
0441: }
0442:
0443: public static double area(GM_Object isoGeom) {
0444: try {
0445: GeometryFactory gf = OraSpatialManager.getGeometryFactory();
0446: Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf, isoGeom);
0447: if (sdoGeom.getGeometryType() == Surface.class)
0448: return ((Surface) sdoGeom).area();
0449: else if (sdoGeom.getGeometryType() == CurvePolygon.class)
0450: return ((CurvePolygon) sdoGeom).area();
0451: else if (sdoGeom.getGeometryType() == Polygon.class)
0452: return ((Polygon) sdoGeom).area();
0453: else if (sdoGeom.getGeometryType() == MultiSurface.class)
0454: return ((MultiSurface) sdoGeom).area();
0455: else if (sdoGeom.getGeometryType() == MultiCurvePolygon.class)
0456: return ((MultiCurvePolygon) sdoGeom).area();
0457: else if (sdoGeom.getGeometryType() == MultiPolygon.class)
0458: return ((MultiPolygon) sdoGeom).area();
0459: else {
0460: System.out
0461: .println("### ATTENTION oracle.SpatialQuery.area() : mauvais type - renvoie -1 ###");
0462: return -1.0;
0463: }
0464: } catch (Exception e) {
0465: e.printStackTrace();
0466: return -1.0;
0467: }
0468: }
0469:
0470: public static double perimeter(GM_Object isoGeom) {
0471: try {
0472: GeometryFactory gf = OraSpatialManager.getGeometryFactory();
0473: Geometry sdoGeom = IsoAndSdo.iso2sdoapi(gf, isoGeom);
0474: if (sdoGeom.getGeometryType() == Surface.class)
0475: return ((Surface) sdoGeom).perimeter();
0476: else if (sdoGeom.getGeometryType() == CurvePolygon.class)
0477: return ((CurvePolygon) sdoGeom).perimeter();
0478: else if (sdoGeom.getGeometryType() == Polygon.class)
0479: return ((Polygon) sdoGeom).perimeter();
0480: else if (sdoGeom.getGeometryType() == MultiSurface.class)
0481: return ((MultiSurface) sdoGeom).perimeter();
0482: else if (sdoGeom.getGeometryType() == MultiCurvePolygon.class)
0483: return ((MultiCurvePolygon) sdoGeom).perimeter();
0484: else if (sdoGeom.getGeometryType() == MultiPolygon.class)
0485: return ((MultiPolygon) sdoGeom).perimeter();
0486: else {
0487: System.out
0488: .println("### ATTENTION oracle.SpatialQuery.perimeter() : mauvais type - renvoie -1 ###");
0489: return -1.0;
0490: }
0491: } catch (Exception e) {
0492: e.printStackTrace();
0493: return -1.0;
0494: }
0495: }
0496:
0497: //////////////////////////////////////////////////////////////////////////////////////////////////////
0498: /// requetes geometriques Oracle sur des objets //////////////////////////////////////////////////////
0499: //////////////////////////////////////////////////////////////////////////////////////////////////////
0500: public static double distance(Geodatabase data, double tolerance,
0501: GM_Object geom1, GM_Object geom2) {
0502: String query = "SELECT SDO_GEOM.SDO_DISTANCE(t.geom,tt.geom,"
0503: + tolerance
0504: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
0505: double result = OracleSpatialQuery.executeDouble(data, geom1,
0506: geom2, query);
0507: return result;
0508: }
0509:
0510: public static DirectPosition centroid(Geodatabase data,
0511: double tolerance, GM_Object geom1) {
0512: String query = "SELECT SDO_GEOM.SDO_CENTROID(t.geom,"
0513: + tolerance + ") FROM TEMP_REQUETE t WHERE t.GID = 0";
0514: GM_Point thePoint = (GM_Point) OracleSpatialQuery
0515: .executeGeometry(data, geom1, query);
0516: return thePoint.getPosition();
0517: }
0518:
0519: public static DirectPosition representativePoint(Geodatabase data,
0520: double tolerance, GM_Object geom1) {
0521: String query = "SELECT SDO_GEOM.SDO_POINTONSURFACE(t.geom,"
0522: + tolerance + ") FROM TEMP_REQUETE t WHERE t.GID = 0";
0523: GM_Point thePoint = (GM_Point) OracleSpatialQuery
0524: .executeGeometry(data, geom1, query);
0525: return thePoint.getPosition();
0526: }
0527:
0528: public static GM_Polygon convexHull(Geodatabase data,
0529: double tolerance, GM_Object geom1) {
0530: String query = "SELECT SDO_GEOM.SDO_CONVEXHULL(t.geom,"
0531: + tolerance + ") FROM TEMP_REQUETE t WHERE t.GID = 0";
0532: return (GM_Polygon) OracleSpatialQuery.executeGeometry(data,
0533: geom1, query);
0534: }
0535:
0536: public static GM_Object buffer(Geodatabase data, double tolerance,
0537: double radius, GM_Object geom1) {
0538: String query = "SELECT SDO_GEOM.SDO_BUFFER(t.geom," + radius
0539: + "," + tolerance
0540: + ") FROM TEMP_REQUETE t WHERE t.GID = 0";
0541: return OracleSpatialQuery.executeGeometry(data, geom1, query);
0542: }
0543:
0544: public static boolean intersects(Geodatabase data,
0545: double tolerance, GM_Object geom1, GM_Object geom2) {
0546: String query = "SELECT SDO_GEOM.RELATE(t.geom,'disjoint',tt.geom,"
0547: + tolerance
0548: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
0549: String result = OracleSpatialQuery.executeString(data, geom1,
0550: geom2, query);
0551: if (result.compareToIgnoreCase("FALSE") == 0) {
0552: query = "SELECT SDO_GEOM.RELATE(t.geom,'touch',tt.geom,"
0553: + tolerance
0554: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
0555: result = OracleSpatialQuery.executeString(data, geom1,
0556: geom2, query);
0557: if (result.compareToIgnoreCase("FALSE") == 0)
0558: return true;
0559: }
0560: return false;
0561: }
0562:
0563: public static boolean equals(Geodatabase data, double tolerance,
0564: GM_Object geom1, GM_Object geom2) {
0565: String query = "SELECT SDO_GEOM.RELATE(t.geom,'equal',tt.geom,"
0566: + tolerance
0567: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
0568: String result = OracleSpatialQuery.executeString(data, geom1,
0569: geom2, query);
0570: if (result.compareToIgnoreCase("TRUE") == 0)
0571: return true;
0572: else
0573: return false;
0574: }
0575:
0576: public static GM_Object union(Geodatabase data, double tolerance,
0577: GM_Object geom1, GM_Object geom2) {
0578: String query = "SELECT SDO_GEOM.SDO_UNION(t.geom,tt.geom,"
0579: + tolerance
0580: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
0581: return OracleSpatialQuery.executeGeometry(data, geom1, geom2,
0582: query);
0583: }
0584:
0585: public static GM_Object intersection(Geodatabase data,
0586: double tolerance, GM_Object geom1, GM_Object geom2) {
0587: String query = "SELECT SDO_GEOM.SDO_INTERSECTION(t.geom,tt.geom,"
0588: + tolerance
0589: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
0590: return OracleSpatialQuery.executeGeometry(data, geom1, geom2,
0591: query);
0592: }
0593:
0594: public static GM_Object difference(Geodatabase data,
0595: double tolerance, GM_Object geom1, GM_Object geom2) {
0596: String query = "SELECT SDO_GEOM.SDO_DIFFERENCE(t.geom,tt.geom,"
0597: + tolerance
0598: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
0599: return OracleSpatialQuery.executeGeometry(data, geom1, geom2,
0600: query);
0601: }
0602:
0603: public static GM_Object symmetricDifference(Geodatabase data,
0604: double tolerance, GM_Object geom1, GM_Object geom2) {
0605: String query = "SELECT SDO_GEOM.SDO_XOR(t.geom,tt.geom,"
0606: + tolerance
0607: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID = 0 AND tt.GID = 1";
0608: return OracleSpatialQuery.executeGeometry(data, geom1, geom2,
0609: query);
0610: }
0611:
0612: public static boolean contains(Geodatabase data, double tolerance,
0613: GM_Object geom1, GM_Object geom2) {
0614: String query = "SELECT SDO_GEOM.RELATE(t.geom,'contains',tt.geom,"
0615: + tolerance
0616: + ") FROM TEMP_REQUETE t, TEMP_REQUETE tt WHERE t.GID=0 AND tt.GID=1";
0617: String result = OracleSpatialQuery.executeString(data, geom1,
0618: geom2, query);
0619: if (result.compareToIgnoreCase("FALSE") == 0)
0620: return false;
0621: else
0622: return true;
0623: }
0624:
0625: public static boolean contains(Geodatabase data, double tolerance,
0626: GM_Object geom1, DirectPosition P) {
0627: GM_Point g = new GM_Point(P);
0628: return OracleSpatialQuery.contains(data, tolerance, geom1, g);
0629: }
0630:
0631: public static GM_Envelope mbr(Geodatabase data, GM_Object geom1) {
0632: String query = "SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.geom,1) FROM TEMP_REQUETE t WHERE t.GID = 0";
0633: double Xmin = OracleSpatialQuery.executeDouble(data, geom1,
0634: query);
0635: query = "SELECT SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.geom,1) FROM TEMP_REQUETE t WHERE t.GID = 0";
0636: double Xmax = OracleSpatialQuery.executeDouble(data, geom1,
0637: query);
0638: query = "SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(t.geom,2) FROM TEMP_REQUETE t WHERE t.GID = 0";
0639: double Ymin = OracleSpatialQuery.executeDouble(data, geom1,
0640: query);
0641: query = "SELECT SDO_GEOM.SDO_MAX_MBR_ORDINATE(t.geom,2) FROM TEMP_REQUETE t WHERE t.GID = 0";
0642: double Ymax = OracleSpatialQuery.executeDouble(data, geom1,
0643: query);
0644: return new GM_Envelope(Xmin, Xmax, Ymin, Ymax);
0645: }
0646:
0647: //////////////////////////////////////////////////////////////////////////////////////////////////////
0648: /// requetes Oracle pour charger des objets //////////////////////////////////////////////////////////
0649: //////////////////////////////////////////////////////////////////////////////////////////////////////
0650: public static List loadAllFeatures(Geodatabase data,
0651: Class theClass, GM_Object geom) {
0652: String query = "WHERE tt.GID=0 AND SDO_RELATE(t.geom,tt.geom,'mask=ANYINTERACT querytype=WINDOW') = 'TRUE'";
0653: return OracleSpatialQuery.executeFeatureList(data, geom,
0654: theClass, query);
0655: }
0656:
0657: public static List loadAllFeatures(Geodatabase data,
0658: Class theClass, GM_Object geom, double dist) {
0659: String query = " WHERE tt.GID=0 AND SDO_WITHIN_DISTANCE(t.geom,tt.geom,'distance="
0660: + dist + "') = 'TRUE'";
0661: return OracleSpatialQuery.executeFeatureList(data, geom,
0662: theClass, query);
0663: }
0664:
0665: //////////////////////////////////////////////////////////////////////////////////////////////////////
0666: /// calcul de buffer "optimise" pour les agregats ... ////////////////////////////////////////////////
0667: //////////////////////////////////////////////////////////////////////////////////////////////////////
0668: public static GM_Object bufferAgregat(Geodatabase data,
0669: double tolerance, double radius, GM_Object geom) {
0670:
0671: // ceci sera le result
0672: GM_Object result = null;
0673:
0674: try {
0675: // initialise la connection a Oracle
0676: Connection conn = data.getConnection();
0677: Statement stm = conn.createStatement();
0678:
0679: // vide la table TEMP_REQUETE
0680: String update = "DELETE FROM TEMP_REQUETE";
0681: stm.executeQuery(update);
0682:
0683: // test
0684: if (!(GM_Aggregate.class).isAssignableFrom(geom.getClass())) {
0685: System.out
0686: .println("le GM_Object doit etre un agregat (GM_Aggregate ou sous-classe)");
0687: System.out.println("le calcul de buffer renvoie NULL");
0688: return null;
0689: }
0690:
0691: // copie des elements de l'agregat dans la table TEMP_REQUETE
0692: GM_Aggregate aggr = (GM_Aggregate) geom;
0693: aggr.initIterator();
0694: int i = 0;
0695: while (aggr.hasNext()) {
0696: i++;
0697: STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(
0698: aggr.next(), conn);
0699: update = "INSERT INTO TEMP_REQUETE VALUES (" + i
0700: + ",?)";
0701: PreparedStatement ps = conn.prepareStatement(update);
0702: ps.setObject(1, str);
0703: ps.executeUpdate();
0704: ps.close();
0705: }
0706:
0707: // calcul des buffers
0708: update = "UPDATE TEMP_REQUETE t SET t.GEOM = (";
0709: update = update + "SELECT SDO_GEOM.SDO_BUFFER(tt.geom, "
0710: + radius + "," + tolerance
0711: + ") FROM TEMP_REQUETE tt WHERE tt.gid = t.gid)";
0712: stm.executeQuery(update);
0713:
0714: // linearisation des geometries (sinon ca plante !!)
0715: update = "UPDATE TEMP_REQUETE t SET t.GEOM = (";
0716: update = update
0717: + "SELECT SDO_GEOM.SDO_ARC_DENSIFY(tt.geom, "
0718: + tolerance;
0719: update = update
0720: + ", 'arc_tolerance=0,1') FROM TEMP_REQUETE tt WHERE tt.gid = t.gid)";
0721: stm.executeQuery(update);
0722:
0723: // calcul de l'agregat result de l'union des buffer (c'est ca qui optimise en theorie )
0724: String query = "SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(t.geom, "
0725: + tolerance + ")) FROM TEMP_REQUETE t";
0726: ResultSet rs = (ResultSet) stm.executeQuery(query);
0727: while (rs.next())
0728: result = (GM_Object) (GeometryConvertor
0729: .Sdo2GM_Object(rs.getObject(1)));
0730: stm.close();
0731:
0732: } catch (Exception e) {
0733: e.printStackTrace();
0734: }
0735:
0736: return result;
0737: }
0738:
0739: //////////////////////////////////////////////////////////////////////////////////////////////////////
0740: /// calcul d'index spatial ///////////////////////////////////////////////////////////////////////////
0741: //////////////////////////////////////////////////////////////////////////////////////////////////////
0742: public static void spatialIndex(Geodatabase data, Class clazz) {
0743: try {
0744: Connection conn = data.getConnection();
0745: Statement stm = conn.createStatement();
0746: String tableName = data.getMetadata(clazz).getTableName()
0747: .toUpperCase();
0748: String columnName = data.getMetadata(clazz)
0749: .getGeomColumnName().toUpperCase();
0750:
0751: // on cherche si un index spatial existe - a revoir pour la multi-representation
0752: String query = "SELECT INDEX_NAME FROM USER_SDO_INDEX_INFO WHERE TABLE_NAME='"
0753: + tableName
0754: + "' AND COLUMN_NAME='"
0755: + columnName
0756: + "'";
0757: stm.executeQuery(query);
0758: ResultSet rs = (ResultSet) stm.executeQuery(query);
0759: String indexName = "";
0760: while (rs.next()) {
0761: indexName = rs.getString(1);
0762: }
0763:
0764: // creation de l'index
0765: if (indexName.compareTo("") == 0) {
0766: System.out.println("index spatial sur " + tableName
0767: + " inexistant...");
0768:
0769: // on est oblige de faire ceci, sinon message d'erreur d'Oracle : nom d'index trop long...
0770: if (tableName.length() > 24)
0771: indexName = tableName.substring(0, 24) + "_spidx";
0772: else
0773: indexName = tableName + "_spidx";
0774:
0775: System.out.println("creation index...");
0776: query = "CREATE INDEX " + indexName + " ON "
0777: + tableName + "(" + columnName + ")";
0778: query = query + "INDEXTYPE IS MDSYS.SPATIAL_INDEX ";
0779: // query=query+"PARAMETERS ('TABLESPACE = USER_IDX')";
0780: stm.executeQuery(query);
0781: System.out.println("index spatial sur " + tableName
0782: + " cree (R-Tree) - nom : " + indexName);
0783: } else {
0784: System.out.println("index spatial sur " + tableName
0785: + " existant...");
0786:
0787: System.out.println("reconstruction index...");
0788: query = "ALTER INDEX " + indexName + " REBUILD";
0789: stm.executeQuery(query);
0790: System.out.println("index spatial sur " + tableName
0791: + " reconstruit (R-Tree) - nom : " + indexName);
0792: }
0793:
0794: stm.close();
0795: } catch (Exception e) {
0796: e.printStackTrace();
0797: }
0798: }
0799:
0800: //////////////////////////////////////////////////////////////////////////////////////////////////////
0801: /// calcul d'emprise ///////////////////////////////////////////////////////////////////////////
0802: //////////////////////////////////////////////////////////////////////////////////////////////////////
0803: public static void mbr(Geodatabase data, Class clazz) {
0804: try {
0805: Connection conn = data.getConnection();
0806: Statement stm = conn.createStatement();
0807: String tableName = data.getMetadata(clazz).getTableName()
0808: .toUpperCase();
0809: String columnName = data.getMetadata(clazz)
0810: .getGeomColumnName().toUpperCase();
0811:
0812: // on recupere l'enveloppe avec la fonction "sdo__aggr_mbr" d'Oracle
0813: GM_Surface rect = null;
0814: String query = "SELECT SDO_AGGR_MBR(" + columnName
0815: + ") FROM " + tableName;
0816: ResultSet rs = (ResultSet) stm.executeQuery(query);
0817: while (rs.next()) {
0818: rect = (GM_Surface) GeometryConvertor.Sdo2GM_Object(rs
0819: .getObject(1));
0820: }
0821:
0822: // on recupere les coordonnees du rectangle
0823: DirectPositionList theCoord = rect.exteriorCoord();
0824: double Xmin = theCoord.get(0).getX();
0825: double Ymin = theCoord.get(0).getY();
0826: double Xmax = theCoord.get(2).getX();
0827: double Ymax = theCoord.get(2).getY();
0828:
0829: // on recupere le DimInfo d'Oracle et le met a jour - revoir pour geometrie multiple
0830: query = "SELECT DIMINFO FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = '"
0831: + tableName + "'";
0832: rs = (ResultSet) stm.executeQuery(query);
0833: String sdoDimArrayString = "MDSYS.SDO_DIM_ARRAY(";
0834: ARRAY sqlDiminfo = null;
0835: while (rs.next())
0836: sqlDiminfo = (oracle.sql.ARRAY) rs.getObject(1);
0837:
0838: int dim;
0839:
0840: // si pas de diminfo, on se met en 2D et on affecte une tolerance par defaut !!!!
0841: if (sqlDiminfo == null) {
0842: sdoDimArrayString = sdoDimArrayString
0843: + "MDSYS.SDO_DIM_ELEMENT('X', " + Xmin + ", "
0844: + Xmax + ", 0.0000000005),";
0845: sdoDimArrayString = sdoDimArrayString
0846: + "MDSYS.SDO_DIM_ELEMENT('Y', " + Ymin + ", "
0847: + Ymax + ", 0.0000000005))";
0848:
0849: } else {
0850:
0851: dim = sqlDiminfo.length();
0852:
0853: if (dim == 2) {
0854: ResultSet X = (ResultSet) sqlDiminfo.getResultSet(
0855: 1, 1);
0856: X.next();
0857: STRUCT XX = (STRUCT) X.getObject(2);
0858: Object[] attrX = XX.getAttributes();
0859: double Xtol = ((BigDecimal) attrX[3]).doubleValue();
0860:
0861: ResultSet Y = (ResultSet) sqlDiminfo.getResultSet(
0862: 2, 1);
0863: Y.next();
0864: STRUCT YY = (STRUCT) Y.getObject(2);
0865: Object[] attrY = YY.getAttributes();
0866: double Ytol = ((BigDecimal) attrY[3]).doubleValue();
0867:
0868: sdoDimArrayString = sdoDimArrayString
0869: + "MDSYS.SDO_DIM_ELEMENT('X', " + Xmin
0870: + ", " + Xmax + ", " + Xtol + "),";
0871: sdoDimArrayString = sdoDimArrayString
0872: + "MDSYS.SDO_DIM_ELEMENT('Y', " + Ymin
0873: + ", " + Ymax + ", " + Ytol + "))";
0874: }
0875:
0876: if (dim == 3) {
0877: ResultSet X = (ResultSet) sqlDiminfo.getResultSet(
0878: 1, 1);
0879: X.next();
0880: STRUCT XX = (STRUCT) X.getObject(2);
0881: Object[] attrX = XX.getAttributes();
0882: double Xtol = ((BigDecimal) attrX[3]).doubleValue();
0883:
0884: ResultSet Y = (ResultSet) sqlDiminfo.getResultSet(
0885: 2, 1);
0886: Y.next();
0887: STRUCT YY = (STRUCT) Y.getObject(2);
0888: Object[] attrY = YY.getAttributes();
0889: double Ytol = ((BigDecimal) attrY[3]).doubleValue();
0890:
0891: ResultSet Z = (ResultSet) sqlDiminfo.getResultSet(
0892: 3, 1);
0893: Z.next();
0894: STRUCT ZZ = (STRUCT) Z.getObject(2);
0895: Object[] attrZ = ZZ.getAttributes();
0896: double Ztol = ((BigDecimal) attrZ[3]).doubleValue();
0897:
0898: sdoDimArrayString = sdoDimArrayString
0899: + "MDSYS.SDO_DIM_ELEMENT('X', " + Xmin
0900: + ", " + Xmax + ", " + Xtol + "),";
0901: sdoDimArrayString = sdoDimArrayString
0902: + "MDSYS.SDO_DIM_ELEMENT('Y', " + Ymin
0903: + ", " + Ymax + ", " + Ytol + "),";
0904: sdoDimArrayString = sdoDimArrayString
0905: + "MDSYS.SDO_DIM_ELEMENT('Z', -1000.0, 10000.0, "
0906: + Ztol + "))";
0907: }
0908: }
0909:
0910: // on ecrit le resultat de la mise a jour dans Oracle - revoir pour les geometries multiples
0911: String update = "UPDATE USER_SDO_GEOM_METADATA SET DIMINFO = "
0912: + sdoDimArrayString
0913: + " WHERE TABLE_NAME = '"
0914: + tableName + "'";
0915: ;
0916: stm.executeUpdate(update);
0917:
0918: // close and commit
0919: System.out.println(tableName
0920: + " : emprise de la table renseignee");
0921: stm.close();
0922: conn.commit();
0923:
0924: } catch (Exception e) {
0925: e.printStackTrace();
0926: }
0927: }
0928:
0929: //////////////////////////////////////////////////////////////////////////////////////////////////////
0930: /// intialisation des metadonnees ////////////////////////////////////////////////////////////////////
0931: //////////////////////////////////////////////////////////////////////////////////////////////////////
0932: public static void initGeomMetadata(List metadataList,
0933: Connection conn) {
0934: try {
0935: int n = metadataList.size();
0936: Statement stm = conn.createStatement();
0937: String query = "SELECT TABLE_NAME, COLUMN_NAME, DIMINFO, SRID FROM USER_SDO_GEOM_METADATA";
0938: ResultSet rs = (ResultSet) stm.executeQuery(query);
0939: while (rs.next()) {
0940: String sqlTableName = rs.getString(1);
0941: for (int i = 0; i < n; i++) {
0942: String arrayTableName = ((Metadata) metadataList
0943: .get(i)).getTableName();
0944: if (arrayTableName != null) // ceci car pour les classes abstraites, pas de table name
0945: // On compare le nom de table de user_sdo_geom_metadata et le nom de table issu du mapping
0946: if (sqlTableName
0947: .compareToIgnoreCase(arrayTableName) == 0) {
0948: Metadata metadataElt = (Metadata) metadataList
0949: .get(i);
0950: // colonne portant la geometrie
0951: String sqlGeomcolumn = rs.getString(2);
0952: metadataElt
0953: .setGeomColumnName(sqlGeomcolumn);
0954: // SRID
0955: // int sqlSRID = rs.getInt(4);
0956: // metadataElt.setSRID(sqlSRID);
0957: // DimInfo -> pour la tolerance et l'enveloppe
0958: oracle.sql.ARRAY sqlDiminfo = (oracle.sql.ARRAY) rs
0959: .getObject(3);
0960: if (sqlDiminfo != null) {
0961: int dim = sqlDiminfo.length();
0962: // on est en 2D
0963: if (dim == 2) {
0964: metadataElt.setDimension(2);
0965: metadataElt
0966: .setTolerance(new double[2]);
0967: ResultSet X = (ResultSet) sqlDiminfo
0968: .getResultSet(1, 1);
0969: X.next();
0970: oracle.sql.STRUCT XX = (oracle.sql.STRUCT) X
0971: .getObject(2);
0972: Object[] attrX = XX.getAttributes();
0973: double Xmin = ((BigDecimal) attrX[1])
0974: .doubleValue();
0975: double Xmax = ((BigDecimal) attrX[2])
0976: .doubleValue();
0977: metadataElt.setTolerance(0,
0978: ((BigDecimal) attrX[3])
0979: .doubleValue());
0980:
0981: ResultSet Y = (ResultSet) sqlDiminfo
0982: .getResultSet(2, 1);
0983: Y.next();
0984: oracle.sql.STRUCT YY = (oracle.sql.STRUCT) Y
0985: .getObject(2);
0986: Object[] attrY = YY.getAttributes();
0987: double Ymin = ((BigDecimal) attrY[1])
0988: .doubleValue();
0989: double Ymax = ((BigDecimal) attrY[2])
0990: .doubleValue();
0991:
0992: DirectPosition UpperCorner = new DirectPosition(
0993: Xmax, Ymax);
0994: DirectPosition LowerCorner = new DirectPosition(
0995: Xmin, Ymin);
0996: GM_Envelope theEnvelope = new GM_Envelope(
0997: UpperCorner, LowerCorner);
0998: metadataElt
0999: .setEnvelope(theEnvelope);
1000: metadataElt.setTolerance(1,
1001: ((BigDecimal) attrY[3])
1002: .doubleValue());
1003: }
1004: // on est en 3D
1005: else if (dim == 3) {
1006: metadataElt.setDimension(3);
1007: metadataElt
1008: .setTolerance(new double[3]);
1009: ResultSet X = (ResultSet) sqlDiminfo
1010: .getResultSet(1, 1);
1011: X.next();
1012: oracle.sql.STRUCT XX = (oracle.sql.STRUCT) X
1013: .getObject(2);
1014: Object[] attrX = XX.getAttributes();
1015: double Xmin = ((BigDecimal) attrX[1])
1016: .doubleValue();
1017: double Xmax = ((BigDecimal) attrX[2])
1018: .doubleValue();
1019: metadataElt.setTolerance(0,
1020: ((BigDecimal) attrX[3])
1021: .doubleValue());
1022:
1023: ResultSet Y = (ResultSet) sqlDiminfo
1024: .getResultSet(2, 1);
1025: Y.next();
1026: oracle.sql.STRUCT YY = (oracle.sql.STRUCT) Y
1027: .getObject(2);
1028: Object[] attrY = YY.getAttributes();
1029: double Ymin = ((BigDecimal) attrY[1])
1030: .doubleValue();
1031: double Ymax = ((BigDecimal) attrY[2])
1032: .doubleValue();
1033: metadataElt.setTolerance(1,
1034: ((BigDecimal) attrY[3])
1035: .doubleValue());
1036:
1037: ResultSet Z = (ResultSet) sqlDiminfo
1038: .getResultSet(3, 1);
1039: Z.next();
1040: oracle.sql.STRUCT ZZ = (oracle.sql.STRUCT) Z
1041: .getObject(2);
1042: Object[] attrZ = ZZ.getAttributes();
1043: double Zmin = ((BigDecimal) attrZ[1])
1044: .doubleValue();
1045: double Zmax = ((BigDecimal) attrZ[2])
1046: .doubleValue();
1047:
1048: DirectPosition UpperCorner = new DirectPosition(
1049: Xmax, Ymax, Zmax);
1050: DirectPosition LowerCorner = new DirectPosition(
1051: Xmin, Ymin, Zmin);
1052: GM_Envelope theEnvelope = new GM_Envelope(
1053: UpperCorner, LowerCorner);
1054: metadataElt
1055: .setEnvelope(theEnvelope);
1056: metadataElt.setTolerance(2,
1057: ((BigDecimal) attrZ[3])
1058: .doubleValue());
1059: }
1060:
1061: // on n'est ni en 2D, ni en 3D !
1062: else
1063: throw new Exception(
1064: "Problème pour lire le DIMINFO de user_sdo_geom_metadata");
1065: }
1066:
1067: // sortie de boucle quand on a trouve une egalite entre tableName de user_sdo_geom_metadata et tableName du mapping
1068: break;
1069: }
1070: }
1071: }
1072: stm.close();
1073: } catch (Exception e) {
1074: e.printStackTrace();
1075: }
1076: }
1077:
1078: }
|