001: /*
002: * This file is part of the GeOxygene project source files.
003: *
004: * GeOxygene aims at providing an open framework which implements OGC/ISO specifications for
005: * the development and deployment of geographic (GIS) applications. It is a open source
006: * contribution of the COGIT laboratory at the Institut Géographique National (the French
007: * National Mapping Agency).
008: *
009: * See: http://oxygene-project.sourceforge.net
010: *
011: * Copyright (C) 2005 Institut Géographique National
012: *
013: * This library is free software; you can redistribute it and/or modify it under the terms
014: * of the GNU Lesser General Public License as published by the Free Software Foundation;
015: * either version 2.1 of the License, or any later version.
016: *
017: * This library is distributed in the hope that it will be useful, but WITHOUT ANY
018: * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A
019: * PARTICULAR PURPOSE. See the GNU Lesser General Public License for more details.
020: *
021: * You should have received a copy of the GNU Lesser General Public License along with
022: * this library (see file LICENSE if present); if not, write to the Free Software
023: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
024: *
025: */
026:
027: package fr.ign.cogit.geoxygene.datatools.postgis;
028:
029: import java.sql.Connection;
030: import java.sql.PreparedStatement;
031: import java.sql.ResultSet;
032: import java.sql.Statement;
033: import java.util.ArrayList;
034: import java.util.List;
035:
036: import fr.ign.cogit.geoxygene.datatools.Geodatabase;
037: import fr.ign.cogit.geoxygene.datatools.Metadata;
038: import fr.ign.cogit.geoxygene.spatial.geomroot.GM_Object;
039:
040: /**
041: * Encapsulation d'appels a Postgis.
042: *
043: * @author Thierry Badard & Arnaud Braun
044: * @version 1.1
045: *
046: */
047: public class PostgisSpatialQuery {
048:
049: //////////////////////////////////////////////////////////////////////////////////////////////////////
050: /// calcul d'index spatial ///////////////////////////////////////////////////////////////////////////
051: //////////////////////////////////////////////////////////////////////////////////////////////////////
052: public static void spatialIndex(Geodatabase data, Class clazz) {
053: try {
054: Connection conn = data.getConnection();
055: Statement stm = conn.createStatement();
056: String tableName = data.getMetadata(clazz).getTableName()
057: .toLowerCase();
058: String columnName = data.getMetadata(clazz)
059: .getGeomColumnName().toLowerCase();
060:
061: // on est oblige de faire ceci, sinon message d'erreur d'Oracle : nom d'index trop long...
062: String indexName;
063: if (tableName.length() > 24)
064: indexName = tableName.substring(0, 24) + "_spidx";
065: else
066: indexName = tableName + "_spidx";
067:
068: try {
069: String query = "CREATE INDEX " + indexName + " ON "
070: + tableName + " USING GIST (" + columnName
071: + " GIST_GEOMETRY_OPS)";
072: stm.executeUpdate(query);
073: conn.commit();
074: } catch (Exception ee) { // l'index existe
075: conn.commit();
076: String query = "REINDEX INDEX " + indexName + " FORCE";
077: stm.executeUpdate(query);
078: }
079: stm.close();
080:
081: } catch (Exception e) {
082: e.printStackTrace();
083: }
084: }
085:
086: //////////////////////////////////////////////////////////////////////////////////////////////////////
087: /// intialisation des metadonnees ////////////////////////////////////////////////////////////////////
088: //////////////////////////////////////////////////////////////////////////////////////////////////////
089: public static void initGeomMetadata(List metadataList,
090: Connection conn) {
091: try {
092: int n = metadataList.size();
093: Statement stm = conn.createStatement();
094: String query = "SELECT F_TABLE_NAME, F_GEOMETRY_COLUMN FROM GEOMETRY_COLUMNS";
095: ResultSet rs = (ResultSet) stm.executeQuery(query);
096: while (rs.next()) {
097: String sqlTableName = rs.getString(1);
098: for (int i = 0; i < n; i++) {
099: String arrayTableName = ((Metadata) metadataList
100: .get(i)).getTableName();
101: if (arrayTableName != null) // ceci car pour les classes abstraites, pas de table name
102: // On compare le nom de table de GEOMETRY_COLUMNS et le nom de table issu du mapping
103: if (sqlTableName
104: .compareToIgnoreCase(arrayTableName) == 0) {
105: Metadata metadataElt = (Metadata) metadataList
106: .get(i);
107:
108: // colonne portant la geometrie
109: String sqlGeomcolumn = rs.getString(2);
110: metadataElt
111: .setGeomColumnName(sqlGeomcolumn);
112:
113: // sortie de boucle quand on a trouve une egalite entre tableName de user_sdo_geom_metadata et tableName du mapping
114: break;
115:
116: // et les emprises ??
117: }
118: }
119: }
120: stm.close();
121: } catch (Exception e) {
122: e.printStackTrace();
123: }
124: }
125:
126: //////////////////////////////////////////////////////////////////////////////////////////////////////
127: /// chargement d'objets par zones ////////////////////////////////////////////////////////////////////
128: //////////////////////////////////////////////////////////////////////////////////////////////////////
129: public static List loadAllFeatures(Geodatabase data,
130: Class theClass, GM_Object geom) {
131: String query = "Intersects";
132: return executeFeatureList(data, geom, theClass, query);
133: }
134:
135: public static List loadAllFeatures(Geodatabase data,
136: Class theClass, GM_Object geom, double dist) {
137: // On crée un buffer autour de la géometrie
138: GM_Object buffer = geom.buffer(dist);
139: String query = "Intersects";
140: return executeFeatureList(data, buffer, theClass, query);
141: }
142:
143: /** Renvoie une liste d'identifiants résultats d'une requete spatiale.*/
144: private static List executeFeatureList(Geodatabase data,
145: GM_Object geom, Class theClass, String theQuery) {
146:
147: // ceci sera le resultat
148: List idList = new ArrayList();
149:
150: try {
151: // recherche du tableName et nom des colonnes
152: String tableName = data.getMetadata(theClass)
153: .getTableName();
154: String pkColumn = data.getMetadata(theClass)
155: .getIdColumnName();
156: String geomColumn = data.getMetadata(theClass)
157: .getGeomColumnName();
158:
159: // récupère la connection
160: Connection conn = data.getConnection();
161:
162: // définition de la requête
163: String query = "SELECT t." + pkColumn + " FROM "
164: + tableName + " t ";
165: query = query + "WHERE t." + geomColumn + " && '"
166: + geom.toString() + "'";
167: query = query + " AND " + theQuery + "('" + geom.toString()
168: + "',t." + geomColumn + ")";
169:
170: // execute la requete
171: PreparedStatement ps = conn.prepareStatement(query);
172: ResultSet rs = (ResultSet) ps.executeQuery();
173: while (rs.next())
174: idList.add(rs.getObject(1));
175: rs.close();
176: ps.close();
177:
178: } catch (Exception e) {
179: e.printStackTrace();
180: }
181:
182: // renvoi du resultat
183: return idList;
184: }
185:
186: }
|