001: /*
002: * GeoTools - OpenSource mapping toolkit
003: * http://geotools.org
004: * (C) 2002-2006, GeoTools Project Managment Committee (PMC)
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: package org.geotools.data.mysql;
017:
018: import java.sql.Connection;
019: import java.sql.ResultSet;
020: import java.sql.ResultSetMetaData;
021: import java.sql.SQLException;
022: import java.sql.Statement;
023: import java.util.HashMap;
024: import java.util.Map;
025: import java.util.logging.Logger;
026:
027: import org.geotools.data.DataSourceException;
028: import org.geotools.feature.AttributeType;
029: import org.geotools.feature.AttributeTypeFactory;
030: import org.geotools.feature.FeatureType;
031: import org.geotools.feature.FeatureTypeFactory;
032: import org.geotools.feature.SchemaException;
033:
034: import com.vividsolutions.jts.geom.Geometry;
035: import com.vividsolutions.jts.geom.GeometryFactory;
036: import com.vividsolutions.jts.io.ParseException;
037: import com.vividsolutions.jts.io.WKTReader;
038:
039: /**
040: * MysqlGeoColumn is used by MysqlDataSource to query its specific geometric
041: * information. There should be one created for each geometry column of each
042: * feature table. It encapsulates information about the column, such as the
043: * name of the corresponding geometric table, the storage type used by that
044: * table, the type of geometry contained, and various other useful information
045: * culled from the GEOMETRY_COLUMNS table. It also generates the geometries
046: * of the column when queried with the ID from the feature table.
047: *
048: * @author Chris Holmes, Vision for New York
049: * @source $URL: http://svn.geotools.org/geotools/tags/2.4.1/modules/unsupported/mysql/src/main/java/org/geotools/data/mysql/MysqlGeomColumn.java $
050: * @version $Id: MysqlGeomColumn.java 27862 2007-11-12 19:51:19Z desruisseaux $
051: */
052: public class MysqlGeomColumn {
053: /** For get and set Storage type, see SFS for SQL spec */
054: public static final int NORMALIZED_STORAGE_TYPE = 0;
055:
056: /**
057: * For get and set Storage type, see SFS for SQL spec, the Well Known
058: * Binary
059: */
060: public static final int WKB_STORAGE_TYPE = 1;
061:
062: /** From the SFS for SQL spec, always has the meta data */
063: public static final String GEOMETRY_META_NAME = "GEOMETRY_COLUMNS";
064:
065: /**
066: * For use when reading in attributes. One off due to sql columns starting
067: * at 1 instead of 0, another one for Feature ID in first column.
068: */
069: private static final int COLUMN_OFFSET = 2;
070:
071: /** Map of sql Type to Java class */
072: private static Map sqlTypeMap = new HashMap();
073:
074: /** The logger for the default core module. */
075: private static final Logger LOGGER = org.geotools.util.logging.Logging
076: .getLogger("org.geotools.mysql");
077:
078: /** Standard logging instance */
079:
080: // private static Category _log = Category.getInstance(MysqlGeomColumn.class.getName());
081: /** Factory for producing geometries (from JTS). */
082: private static GeometryFactory geometryFactory = new GeometryFactory();
083:
084: /** Well Known Text reader (from JTS). */
085: private static WKTReader geometryReader = new WKTReader(
086: geometryFactory);
087:
088: /** A map containing the raw geometric data, accessed by its geom ID */
089: private static Map gidMap = new HashMap();
090:
091: /** factory for attribute types. */
092: private static AttributeTypeFactory attFactory = AttributeTypeFactory
093: .defaultInstance();
094:
095: static {
096: sqlTypeMap.put("TINY", Byte.class);
097: sqlTypeMap.put("SHORT", Short.class);
098: sqlTypeMap.put("INT", Integer.class);
099: sqlTypeMap.put("LONG", Integer.class); //check this
100: sqlTypeMap.put("LONGLONG", Long.class);
101: sqlTypeMap.put("DOUBLE", Double.class);
102: sqlTypeMap.put("VARCHAR", String.class);
103: sqlTypeMap.put("DECIMAL", String.class);
104: sqlTypeMap.put("CHAR", String.class);
105: sqlTypeMap.put("TEXT", String.class);
106: sqlTypeMap.put("BLOB", String.class); //figure this shit out
107: sqlTypeMap.put("FLOAT", Float.class);
108: }
109:
110: /** The catalog containing the feature table using this geometry column */
111: private String feaTabCatalog;
112:
113: /** The schema containing the feature table using this geometry column */
114: private String feaTabSchema;
115:
116: /** The name of the feature table using this geometry column */
117: private String feaTabName;
118:
119: /**
120: * The name of the geometry column in the feature table. This class is
121: * basically all the information this column points to
122: */
123: private String feaGeomColumn;
124:
125: /** The catalog of the geometry table where the column is stored */
126: private String geomTabCatalog;
127:
128: /** The schema of the geometry table where the column is stored */
129: private String geomTabSchema;
130:
131: /** The name of the geometry table where the column is stored */
132: private String geomTabName;
133:
134: /** The storage type, 0 for normalized SQL, 1 for WKB */
135: private int storageType;
136:
137: /** The geometry type, see OGC SFS for SQL section 3.1.2.3 */
138: private int geomType;
139:
140: /**
141: * The number of ordinates used, corresponds to the number of dimensions in
142: * the spatial reference system.
143: */
144: private int coordDimension;
145:
146: /** The Max Points Per Row, only used in normalized SQL 92 implementation */
147: private int maxPPR;
148:
149: /**
150: * The ID of the spatial reference system. It is a foreign key reference
151: * to the SPATIAL_REF_SYS table.
152: */
153: private int spacRefID;
154:
155: /** The featureType schema corresponding to this geometry column. */
156: private FeatureType schema = null;
157:
158: /**
159: * Default constructor
160: */
161: public MysqlGeomColumn() {
162: }
163:
164: /**
165: * Convenience constructor with the minimum meta information needed to do
166: * anything useful.
167: *
168: * @param feaTabName The name of the feature table for this geometry.
169: * @param feaGeomColumn The name of the column in the feature table that
170: * refers to the MysqlGeomColumn.
171: * @param geomTabName The name of the table holding the geometry data.
172: */
173: public MysqlGeomColumn(String feaTabName, String feaGeomColumn,
174: String geomTabName) {
175: this .feaTabName = feaTabName;
176: this .feaGeomColumn = feaGeomColumn;
177: this .geomTabName = geomTabName;
178: }
179:
180: /**
181: * A convenience constructor, when you there is an open connection, and
182: * only using flat features. This constructor will not work with feature
183: * tables that contain multiple geometries as the query on the feature
184: * table will return multiple rows, which will be discarded. For multiple
185: * geometries an array of MysqlGeomColumns must be created, each
186: * initialized with the default constructor, filling in the values through
187: * the accesssor functions.
188: *
189: * @param dbConnection An open connection to the database.
190: * @param feaTableName The feature table that references this Geometry Col.
191: *
192: * @throws SQLException if there were problems accessing the database.
193: * @throws SchemaException if there were problems creating the schema.
194: *
195: * @task TODO: Get rid of this constructor, move the functionality outside.
196: */
197: public MysqlGeomColumn(Connection dbConnection, String feaTableName)
198: throws SQLException, SchemaException {
199: this .feaTabName = feaTableName;
200:
201: try {
202: Statement statement = dbConnection.createStatement();
203:
204: //MySQL does not pre-compile statements, so making prepared
205: //statements leads to no performance increases.
206: String sqlQuery = makeGeomSql(feaTableName);
207: LOGGER.warning("SQL q = " + sqlQuery);
208:
209: ResultSet result = statement.executeQuery(sqlQuery);
210:
211: while (result.next()) {
212: //only flat features for now, with multiple geometries
213: //all but the last feature will be discarded
214: feaTabCatalog = result.getString(1);
215: feaTabSchema = result.getString(2);
216: feaGeomColumn = result.getString(4);
217: geomTabCatalog = result.getString(5);
218: geomTabSchema = result.getString(6);
219: geomTabName = result.getString(7);
220: storageType = result.getInt(8);
221: geomType = result.getInt(9);
222: coordDimension = result.getInt(10);
223: maxPPR = result.getInt(11);
224: spacRefID = result.getInt(12);
225: }
226:
227: LOGGER.finer("creating new geometry column with values: "
228: + feaTabName + " " + feaGeomColumn + " "
229: + geomTabName);
230: result = statement.executeQuery("SELECT * FROM "
231: + geomTabName);
232:
233: //currently selects all, should be more elegant as we get complex
234: //queries. Ideally move outside and call populate data on results.
235: int gid = 0;
236: String wkb = null; //now it is actually Well Known Text, waiting for WKB reader
237:
238: while (result.next()) {
239: gid = result.getInt(1);
240: wkb = result.getString(6);
241: populateData(gid, wkb);
242: }
243:
244: result.close();
245: statement.close();
246: } catch (SQLException e) {
247: LOGGER.warning("Some sort of database connection error: "
248: + e.getMessage());
249: }
250: }
251:
252: /**
253: * Creates a sql select statement to get the information on the Geometry
254: * column of a feature table.
255: *
256: * @param feaTableName The feature table we want information about.
257: *
258: * @return The SQL statement to get the geometry data.
259: */
260: private String makeGeomSql(String feaTableName) {
261: return "SELECT * FROM " + GEOMETRY_META_NAME
262: + " WHERE F_TABLE_NAME = '" + feaTableName + "';";
263: }
264:
265: /**
266: * Stores the geometry information by geometry ID, so that it can be
267: * queried later. Currently only takes Well Known Text. This should
268: * eventually change to Well Known Binary, possible stored as a bit
269: * stream? And in time an overloaded populateData that allows for
270: * normalized SQL 92 storage.
271: *
272: * @param geomID the primary key for a row in the Geometry Table;
273: * @param wellKnownText the WKT representation of the geometry; tasks:
274: * TODO: Well Known Binary, and normalized SQL 92 (see SFS for for
275: * SQL spec 2.2.5)
276: */
277: public void populateData(int geomID, String wellKnownText) {
278: LOGGER.finer("putting " + wellKnownText + " into gidMap");
279: gidMap.put(new Integer(geomID), wellKnownText);
280:
281: //we should probably change to objects, GID not necessarily an
282: //int, and the getString will change to blob when we do WKB
283: }
284:
285: /**
286: * Takes out a geometry according to its ID.
287: *
288: * @param geomID the primary key for a rwo in the Geometry Table
289: */
290: public void removeData(int geomID) {
291: gidMap.remove(new Integer(geomID));
292: }
293:
294: /**
295: * Returns a jts Geometry when queried with a geometry ID.
296: *
297: * @param geomID the ID of the feature geometry.
298: *
299: * @return a jts geometry represention of the stored data, returns null is
300: * it is not found.
301: *
302: * @throws DataSourceException if there is trouble with the Database.
303: */
304: public Geometry getGeometry(int geomID) throws DataSourceException {
305: String wellKnownText;
306: Geometry returnGeometry = null;
307: wellKnownText = (String) gidMap.get(new Integer(geomID));
308: LOGGER.finer("about to create geometry for " + wellKnownText);
309:
310: if (wellKnownText == null) {
311: return null;
312: } else {
313: try {
314: returnGeometry = geometryReader.read(wellKnownText);
315: } catch (ParseException e) {
316: LOGGER
317: .finer("Failed to parse the geometry from Mysql: "
318: + e.getMessage());
319: }
320:
321: return returnGeometry;
322: }
323: }
324:
325: /**
326: * Setter method for feature catalog.
327: *
328: * @param catalog the name of the catalog.
329: */
330: public void setFeaTableCat(String catalog) {
331: feaTabCatalog = catalog;
332: }
333:
334: /**
335: * Getter method for Feature Catalog.
336: *
337: * @return the name of the catalog.
338: */
339: public String getFeaTableCat() {
340: return feaTabCatalog;
341: }
342:
343: /**
344: * Setter method for feature schema.
345: *
346: * @param schema the name of the schema.
347: */
348: public void setFeaTableSchema(String schema) {
349: feaTabSchema = schema;
350: }
351:
352: /**
353: * Getter method for feature schema.
354: *
355: * @return the name of the schema.
356: */
357: public String getFeaTableSchema() {
358: return feaTabSchema;
359: }
360:
361: /**
362: * Setter method for feature table name.
363: *
364: * @param name the name of the feature table.
365: */
366: public void setFeaTableName(String name) {
367: feaTabName = name;
368: }
369:
370: /**
371: * Getter method for feature table name.
372: *
373: * @return the name of the feature table.
374: */
375: public String getFeaTableName() {
376: return feaTabName;
377: }
378:
379: /**
380: * Setter method for geometry column.
381: *
382: * @param name the name of the column.
383: */
384: public void setGeomColName(String name) {
385: feaGeomColumn = name;
386: }
387:
388: /**
389: * Getter method for geometry column.
390: *
391: * @return the name of the column.
392: */
393: public String getGeomColName() {
394: return feaGeomColumn;
395: }
396:
397: /**
398: * Setter method for geometry catalog.
399: *
400: * @param catalog the name of the catalog.
401: */
402: public void setGeomTableCat(String catalog) {
403: geomTabCatalog = catalog;
404: }
405:
406: /**
407: * Getter method for Geometry Catalog.
408: *
409: * @return the name of the catalog.
410: */
411: public String getGeomTableCat() {
412: return geomTabCatalog;
413: }
414:
415: /**
416: * Setter method for geometry schema.
417: *
418: * @param schema the name of the catalog.
419: */
420: public void setGeomTableSchema(String schema) {
421: geomTabSchema = schema;
422: }
423:
424: /**
425: * Getter method for geometry schema
426: *
427: * @return the name of the schema.
428: */
429: public String getGeomTableSchema() {
430: return geomTabSchema;
431: }
432:
433: /**
434: * Setter method for geometry table name.
435: *
436: * @param name the name of the geometry table.
437: */
438: public void setGeomTableName(String name) {
439: geomTabName = name;
440: }
441:
442: /**
443: * Getter method for geometry table name.
444: *
445: * @return the name of the catalog.
446: */
447: public String getGeomTableName() {
448: return geomTabName;
449: }
450:
451: /**
452: * Sets the type used for storage in the geometry column.
453: *
454: * @param sType 0 for NORMALIZED_STORAGE_TYPE 1, for WKB_STORAGE_TYPE
455: */
456: public void setStorageType(int sType) {
457: storageType = sType;
458: }
459:
460: /**
461: * Gets the type used for storage in the geometry column.
462: *
463: * @return 0 for NORMALIZED_STORAGE_TYPE, 1 for WKB_STORAGE_TYPE
464: */
465: public int getStorageType() {
466: return storageType;
467: }
468:
469: /**
470: * Sets the Geometry type of the geometry column.
471: *
472: * @param gType the geometery type
473: */
474: public void setGeomType(int gType) {
475: geomType = gType;
476: }
477:
478: /**
479: * Gets the Geometry type of the geometry column.
480: *
481: * @return the int representation of the GeometryType
482: *
483: * @task TODO: implement a hashmap so we return jts Geometry Class Types
484: * instead of ints.
485: */
486: public int getGeomType() {
487: return geomType;
488: }
489:
490: /**
491: * Gets the schema for this geometry column.
492: *
493: * @return the schema corresponding to this geometry column.
494: */
495: public FeatureType getSchema() {
496: return schema;
497: }
498:
499: /**
500: * sets the schema for this geometry column.
501: *
502: * @param schema for this geometry column.
503: */
504: public void setSchema(FeatureType schema) {
505: //TODO: check to make sure the schema is correct (geom col names are same, etc.)
506: this .schema = schema;
507: }
508:
509: /**
510: * Creates the schema, a FeatureType of the attributes.
511: *
512: * @param metaData from the query of the feature table.
513: * @param geoColumn the name of the geometry column in the feature table.
514: *
515: * @return a FeatureType of the attributes.
516: *
517: * @throws SQLException if there was database connectivity issues.
518: * @throws SchemaException if there was problems creating the FeatureType.
519: *
520: * @todo Fix FeatureType name - IanS tasks TODO: put this method
521: * MysqlGeomColumn or a SchemaFactory.
522: */
523: public static FeatureType makeSchema(ResultSetMetaData metaData,
524: String geoColumn) throws SQLException, SchemaException {
525: String columnName = null;
526: Class colClass = null;
527: int numCols = metaData.getColumnCount();
528: AttributeType[] attributes = new AttributeType[numCols - 1];
529:
530: LOGGER.finer("about to loop through cols");
531:
532: // loop through all columns except first, as it's the featureID
533: for (int i = 2; i <= numCols; i++) {
534: columnName = metaData.getColumnName(i);
535: LOGGER.finer("reading col: " + i + " named: " + columnName);
536: LOGGER.finer("reading col: "
537: + metaData.getColumnTypeName(i));
538:
539: // set column name and type from database
540: //TODO: use MysqlGeomColumn.getGeomType, once it's fully implemented
541: if (columnName.equals(geoColumn)) { //if it is a geomtry column, by name
542: attributes[i - COLUMN_OFFSET] = AttributeTypeFactory
543: .newAttributeType(columnName, Geometry.class);
544: } else {
545: colClass = (Class) sqlTypeMap.get(metaData
546: .getColumnTypeName(i));
547: attributes[i - COLUMN_OFFSET] = AttributeTypeFactory
548: .newAttributeType(columnName, colClass);
549: }
550: }
551:
552: // @todo Fix FeatureType name - IanS
553: return FeatureTypeFactory.newFeatureType(attributes,
554: "mysql-feature");
555: }
556: }
|