001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) Copyright IBM Corporation, 2005-2007. All rights reserved.
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation;
009: * version 2.1 of the License.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: */
017: package org.geotools.data.db2;
018:
019: import org.geotools.factory.FactoryRegistryException;
020: import org.opengis.referencing.FactoryException;
021: import org.opengis.referencing.crs.CoordinateReferenceSystem;
022: import java.io.IOException;
023: import java.sql.Connection;
024: import java.sql.ResultSet;
025: import java.sql.SQLException;
026: import java.sql.Statement;
027: import java.util.HashMap;
028: import java.util.HashSet;
029: import java.util.Iterator;
030: import java.util.Map;
031: import java.util.Set;
032: import java.util.logging.Logger;
033:
034: /**
035: * Manage the DB2 Spatial Extender spatial catalog information in memory to
036: * improve performance.
037: *
038: * <p>
039: * This class is not intended to be used outside the DB2 plug-in package.
040: * </p>
041: *
042: * <p>
043: * Currently, a different catalog is managed for each DB2 database and schema
044: * specified in the creation of a DB2DataStore. Multiple data stores created
045: * for the same database and schema can share the same catalog.
046: * </p>
047: *
048: * <p>
049: * All schema, table and column names are case sensitive.
050: * </p>
051: *
052: * <p>
053: * Convenience methods provided for access to various types of catalog
054: * information like type names, srid, etc.
055: * </p>
056: *
057: * <p>
058: * <b>Note: the 'srid' value in DB2 is different from the srid value referenced
059: * in OGC documents. The OGC srid corresponds more closely to the DB2 'csid'
060: * value. This makes life quite confusing because the DB2 'srid' is needed to
061: * construct geometries in the database. GeoTools needs the 'csid' which
062: * generally corresponds to an EPSG coordinate system identifier in order to
063: * create an OGC coordinate system reference. </b>
064: * </p>
065: *
066: * <p>
067: * <b> We also assume that there is a single 'srid' associated with all the
068: * geometries in a particular geometry column. This is not required by DB2.
069: * </b>
070: * </p>
071: *
072: * <p>
073: * Do we need to consider freeing this up at some point as the HashMap of
074: * catalogs is stored in a class variable?
075: * </p>
076: *
077: * @author David Adler - IBM Corporation
078: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/db2/src/main/java/org/geotools/data/db2/DB2SpatialCatalog.java $
079: */
080: public class DB2SpatialCatalog {
081: private static final Logger LOGGER = org.geotools.util.logging.Logging
082: .getLogger("org.geotools.data.db2");
083:
084: /** A map of all the catalogs that have been created. */
085: private static Map catalogs = new HashMap();
086:
087: /** All the DB2 geometry columns in this catalog */
088: private Map geometryColumns;
089:
090: /**
091: * All the DB2 coordinate systems referenced by the geometry columns in
092: * this catalog.
093: */
094: private Map coordinateSystems = new HashMap();
095:
096: /** The specific database that this catalog was generated from. */
097: private String dbURL;
098:
099: /** The table schema that identifies all the geometries in this catalog. */
100: private String tableSchema;
101:
102: /**
103: * The constructor for a DB2SpatialCatalog.
104: *
105: * <p>
106: * This is only called from getInstance(String dbURL, String tableSchema,
107: * Connection conn).
108: * </p>
109: *
110: * <p>
111: * An alternative would be to use a factory object but it isn't clear what
112: * advantage that would offer.
113: * </p>
114: *
115: * @param dbURL the database URL
116: * @param tableSchema the table schema name
117: */
118: private DB2SpatialCatalog(String dbURL, String tableSchema) {
119: super ();
120: this .dbURL = dbURL;
121: this .tableSchema = tableSchema;
122: }
123:
124: /**
125: * This method should only be called by DB2SpatialCatalogTest
126: */
127: public static void reset() {
128: catalogs = new HashMap();
129: }
130:
131: /**
132: * Get an instance of the DB2SpatialCatalog for the specified datastore.
133: *
134: * <p>
135: * If it already exists, just return the current catalog for this database
136: * identified by the database connection URL and table schema.
137: * </p>
138: *
139: * <p>
140: * If it doesn't already exist and the connection parameter is null, return
141: * null.
142: * </p>
143: *
144: * <p>
145: * If not, read the DB2 Spatial Extender catalog tables and create an
146: * in-memory representation for efficient access.
147: * </p>
148: *
149: * @param dbURL the database URL
150: * @param tableSchema the table schema name
151: * @param conn an active database connection or null
152: *
153: * @return a DB2SpatialCatalog.
154: *
155: * @throws SQLException if there was a failure to create a catalog.
156: */
157: public static DB2SpatialCatalog getInstance(String dbURL,
158: String tableSchema, Connection conn) throws SQLException {
159: DB2SpatialCatalog catalog = (DB2SpatialCatalog) catalogs
160: .get(dbURL + tableSchema);
161:
162: // Try to create and populate a new catalog if it wasn't found and we have a database connection.
163: if ((catalog == null) && (conn != null)) {
164: catalog = new DB2SpatialCatalog(dbURL, tableSchema);
165: catalog.loadCatalog(conn, tableSchema);
166: catalogs.put(dbURL + tableSchema, catalog);
167: }
168:
169: return catalog;
170: }
171:
172: /**
173: * A convenience method to nicely format the schema, table and column name.
174: *
175: * @param tableSchema
176: * @param tableName
177: * @param columnName
178: *
179: * @return a concatenated key value.
180: */
181: public static String geomID(String tableSchema, String tableName,
182: String columnName) {
183: String key = tableSchema + "." + tableName + "(" + columnName
184: + ")";
185:
186: return key;
187: }
188:
189: /**
190: * Loads the DB2SpatialCatalog with the values from the DB2 Spatial
191: * Extender catalog views.
192: *
193: * <p>
194: * The spatial columns are found in the view db2gse.ST_Geometry_Columns.
195: * </p>
196: *
197: * <p>
198: * If the SRID value returned was null, attempt to get it from the actual
199: * data table with the method getSridFromTable.
200: * </p>
201: *
202: * <p>
203: * If the coordinate system for the geometry column just returned does not
204: * already exist in the map of coordinate systems construct a new
205: * coordinate system.
206: * </p>
207: *
208: * @param conn an active database connection
209: * @param schemaName the schema to identify tables in this catalog
210: *
211: * @throws SQLException
212: */
213: void loadCatalog(Connection conn, String schemaName)
214: throws SQLException {
215: this .geometryColumns = new HashMap();
216:
217: String tableSchema;
218: String tableName;
219: String columnName;
220: String typeName;
221: Integer srsId;
222:
223: String queryGeom = "SELECT table_schema, table_name, column_name, "
224: + " type_name, srs_id"
225: + " FROM db2gse.st_geometry_columns"
226: + " WHERE table_schema = '" + schemaName + "' ";
227: Statement stmt = conn.createStatement();
228: ResultSet rs = stmt.executeQuery(queryGeom);
229:
230: while (rs.next()) {
231: tableSchema = rs.getString(1).trim();
232: tableName = rs.getString(2).trim();
233: columnName = rs.getString(3).trim();
234: typeName = rs.getString(4).trim();
235: srsId = new Integer(rs.getInt(5));
236:
237: if (rs.wasNull()) {
238: srsId = getSridFromTable(conn, tableSchema, tableName,
239: columnName);
240: }
241:
242: // Try to get the coordinate system if it was already loaded.
243: DB2CoordinateSystem cs = (DB2CoordinateSystem) this .coordinateSystems
244: .get(srsId);
245:
246: if (cs == null) {
247: cs = new DB2CoordinateSystem(conn, srsId.intValue());
248: this .coordinateSystems.put(srsId, cs);
249: }
250:
251: DB2GeometryColumn gc = new DB2GeometryColumn(tableSchema,
252: tableName, columnName, typeName, srsId, cs);
253: LOGGER.fine("Spatial column: " + gc + " " + cs);
254: this .geometryColumns.put(geomKey(gc), gc); // Save this geometry
255: }
256:
257: rs.close();
258: stmt.close();
259: }
260:
261: /**
262: * Gets the DB2 srid value by selecting the first geometry value in the
263: * data table.
264: *
265: * <p>
266: * If no value is found, the returned default srid value is 0.
267: * </p>
268: *
269: * @param conn
270: * @param tableSchema
271: * @param tableName
272: * @param columnName
273: *
274: * @return the DB2 srid value.
275: *
276: * @throws SQLException may be thrown if there is a database problem. No
277: * local checking for exceptions.
278: */
279: private Integer getSridFromTable(Connection conn,
280: String tableSchema, String tableName, String columnName)
281: throws SQLException {
282: Integer srsId = new Integer(0);
283: String querySrid = "SELECT DB2GSE.ST_SRID(\"" + columnName
284: + "\")" + " FROM \"" + tableSchema + "\".\""
285: + tableName + "\"" + " WHERE \"" + columnName
286: + "\" IS NOT NULL" + " FETCH FIRST ROW ONLY";
287: Statement stmt = conn.createStatement();
288: ResultSet rs = stmt.executeQuery(querySrid);
289:
290: while (rs.next()) {
291: srsId = new Integer(rs.getInt(1));
292: }
293:
294: return srsId;
295: }
296:
297: /**
298: * Creates a key value to associate with this particular geometry.
299: *
300: * <p>
301: * This is used when a geometry is being stored in the map.
302: * </p>
303: *
304: * @param gc a geometry column
305: *
306: * @return a unique string representation which can be used to look up a
307: * geometry.
308: */
309: private String geomKey(DB2GeometryColumn gc) {
310: String key = this .dbURL + ":" + gc.getTableSchema() + "."
311: + gc.getTableName() + "." + gc.getColumnName();
312:
313: return key;
314: }
315:
316: /**
317: * Creates a key value that can be used to look up a geometry.
318: *
319: * <p>
320: * This is used when a geometry is going to be looked up in the map.
321: * </p>
322: *
323: * @param tableSchema
324: * @param tableName
325: * @param columnName
326: *
327: * @return a unique string representation which can be used to look up a
328: * geometry.
329: */
330: private String geomKey(String tableSchema, String tableName,
331: String columnName) {
332: String key = this .dbURL + ":" + tableSchema + "." + tableName
333: + "." + columnName;
334:
335: return key;
336: }
337:
338: /**
339: * Gets all the type (table) names in this catalog.
340: *
341: * <p>
342: * There could possibly be duplicate type names if a table has more than
343: * one geometry column. Any duplicates will be eliminated.
344: * </p>
345: *
346: * @return a String array of type names
347: */
348: String[] getTypeNames() {
349: Set typeNames = new HashSet(); // Use a Set to eliminate duplicates
350: Iterator it = this .geometryColumns.values().iterator();
351:
352: while (it.hasNext()) {
353: DB2GeometryColumn gc = (DB2GeometryColumn) it.next();
354: typeNames.add(gc.getTableName());
355: }
356:
357: return (String[]) typeNames
358: .toArray(new String[typeNames.size()]);
359: }
360:
361: /**
362: * Gets the DB2 geometry type name for this geometry.
363: *
364: * <p>
365: * The geometry type name will be a value like 'ST_POINT' or
366: * 'ST_MULTIPOLYGON'.
367: * </p>
368: *
369: * @param tableSchema
370: * @param tableName
371: * @param columnName
372: *
373: * @return the DB2 geometry type name.
374: *
375: * @throws IOException if a geometry was not found in the catalog for the
376: * specified schema, table and column.
377: */
378: String getDB2GeometryTypeName(String tableSchema, String tableName,
379: String columnName) throws IOException {
380: return getGeometryColumn(tableSchema, tableName, columnName)
381: .getTypeName();
382: }
383:
384: /**
385: * Gets the DB2 coordinate system identifier associated with this geometry
386: * column.
387: *
388: * @param tableSchema
389: * @param tableName
390: * @param columnName
391: *
392: * @return the coordinate system identifier.
393: *
394: * @throws IOException if a geometry was not found in the catalog for the
395: * specified schema, table and column.
396: */
397: int getCsId(String tableSchema, String tableName, String columnName)
398: throws IOException {
399: return getGeometryColumn(tableSchema, tableName, columnName)
400: .getCsId();
401: }
402:
403: /**
404: * Gets the OpenGIS CoordinateReferenceSystem of this geometry column.
405: *
406: * @param tableSchema
407: * @param tableName
408: * @param columnName
409: *
410: * @return the coordinate reference system.
411: *
412: * @throws FactoryRegistryException
413: * @throws FactoryException
414: * @throws IOException
415: */
416: CoordinateReferenceSystem getCRS(String tableSchema,
417: String tableName, String columnName)
418: throws FactoryRegistryException, FactoryException,
419: IOException {
420: return getGeometryColumn(tableSchema, tableName, columnName)
421: .getCRS();
422: }
423:
424: /**
425: * Gets the DB2 srid value associated with this geometry column.
426: *
427: * @param tableSchema
428: * @param tableName
429: * @param columnName
430: *
431: * @return the DB2 srid
432: *
433: * @throws IOException if a geometry was not found in the catalog for the
434: * specified schema, table and column.
435: */
436: int getSRID(String tableSchema, String tableName, String columnName)
437: throws IOException {
438: return getGeometryColumn(tableSchema, tableName, columnName)
439: .getSrsId().intValue();
440: }
441:
442: /**
443: * Gets the geometry colum or throws an exception if not found.
444: *
445: * @param tableSchema
446: * @param tableName
447: * @param columnName
448: *
449: * @return a DB2GeometryColumn
450: *
451: * @throws IOException if a geometry was not found in the catalog for the
452: * specified schema, table and column.
453: */
454: private DB2GeometryColumn getGeometryColumn(String tableSchema,
455: String tableName, String columnName) throws IOException {
456: String geomKey = geomKey(tableSchema, tableName, columnName);
457: DB2GeometryColumn gc = (DB2GeometryColumn) this .geometryColumns
458: .get(geomKey);
459:
460: if (gc == null) {
461: throw new IOException("Geometry not found: " + geomKey);
462: }
463:
464: return gc;
465: }
466:
467: /**
468: * Returns the database URL and table schema.
469: *
470: * @return the database URL and table schema as a String.
471: */
472: public String toString() {
473: return this .dbURL + "-" + this.tableSchema;
474: }
475: }
|