001 //$HeadURL$
002 /*----------------------------------------------------------------------------
003 This file is part of deegree, http://deegree.org/
004 Copyright (C) 2001-2009 by:
005 Department of Geography, University of Bonn
006 and
007 lat/lon GmbH
008
009 This library is free software; you can redistribute it and/or modify it under
010 the terms of the GNU Lesser General Public License as published by the Free
011 Software Foundation; either version 2.1 of the License, or (at your option)
012 any later version.
013 This library is distributed in the hope that it will be useful, but WITHOUT
014 ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
015 FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
016 details.
017 You should have received a copy of the GNU Lesser General Public License
018 along with this library; if not, write to the Free Software Foundation, Inc.,
019 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020
021 Contact information:
022
023 lat/lon GmbH
024 Aennchenstr. 19, 53177 Bonn
025 Germany
026 http://lat-lon.de/
027
028 Department of Geography, University of Bonn
029 Prof. Dr. Klaus Greve
030 Postfach 1147, 53001 Bonn
031 Germany
032 http://www.geographie.uni-bonn.de/deegree/
033
034 e-mail: info@deegree.org
035 ----------------------------------------------------------------------------*/
036
037 package org.deegree.io.databaseloader;
038
039 import java.net.URI;
040 import java.sql.Connection;
041 import java.sql.PreparedStatement;
042 import java.sql.ResultSet;
043 import java.sql.ResultSetMetaData;
044 import java.sql.SQLException;
045 import java.util.UUID;
046
047 import org.deegree.datatypes.QualifiedName;
048 import org.deegree.datatypes.Types;
049 import org.deegree.framework.log.ILogger;
050 import org.deegree.framework.log.LoggerFactory;
051 import org.deegree.io.DBConnectionPool;
052 import org.deegree.io.DBPoolException;
053 import org.deegree.io.JDBCConnection;
054 import org.deegree.io.datastore.sql.postgis.PGgeometryAdapter;
055 import org.deegree.model.crs.CRSTransformationException;
056 import org.deegree.model.crs.CoordinateSystem;
057 import org.deegree.model.crs.GeoTransformer;
058 import org.deegree.model.feature.FeatureCollection;
059 import org.deegree.model.feature.FeatureFactory;
060 import org.deegree.model.feature.FeatureProperty;
061 import org.deegree.model.feature.schema.FeatureType;
062 import org.deegree.model.feature.schema.PropertyType;
063 import org.deegree.model.spatialschema.Envelope;
064 import org.deegree.model.spatialschema.GeometryException;
065 import org.deegree.model.spatialschema.GeometryFactory;
066 import org.deegree.model.spatialschema.Surface;
067 import org.deegree.model.spatialschema.WKTAdapter;
068 import org.deegree.ogcwebservices.wms.configuration.DatabaseDataSource;
069 import org.postgis.binary.BinaryParser;
070
071 /**
072 * class for loading data as feature collection from a postgis database
073 *
074 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
075 * @author last edited by: $Author: poth $
076 *
077 * @version $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
078 */
079 public class MySQLDataLoader {
080
081 private static final ILogger LOG = LoggerFactory.getLogger( MySQLDataLoader.class );
082
083 private static URI namespace;
084 static {
085 try {
086 namespace = new URI( "http://www.deegree.org/database" );
087 } catch ( Exception e ) {
088 LOG.logError( e.getMessage(), e );
089 }
090 }
091
092 /**
093 * @param datasource
094 * @param envelope
095 * @return the feature collection directly from the db
096 * @throws Exception
097 */
098 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope )
099 throws Exception {
100 return load( datasource, envelope, null );
101 }
102
103 /**
104 * @param datasource
105 * @param envelope
106 * @param sql
107 * @return the fc from the db
108 * @throws Exception
109 */
110 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql )
111 throws Exception {
112 return load( datasource, envelope, sql, null );
113 }
114
115 /**
116 *
117 * @param datasource
118 * @param envelope
119 * @param sql
120 * @param extraClauses
121 * @return featurecollection loaded from a postgis database
122 * @throws Exception
123 */
124 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql,
125 String extraClauses )
126 throws Exception {
127 if ( sql == null ) {
128 sql = datasource.getSqlTemplate();
129 }
130
131 JDBCConnection jdbc = datasource.getJDBCConnection();
132 PreparedStatement stmt = null;
133 Connection conn = null;
134 ResultSet rs = null;
135 FeatureCollection fc = FeatureFactory.createFeatureCollection( UUID.randomUUID().toString(), 10000 );
136 try {
137 CoordinateSystem crs = datasource.getNativeCRS();
138 conn = acquireConnection( jdbc );
139 stmt = createPreparedStatement( datasource, envelope, conn, crs, sql, extraClauses );
140
141 rs = stmt.executeQuery();
142
143 LOG.logDebug( "performing database query: " + sql );
144 ResultSetMetaData rsmd = rs.getMetaData();
145 FeatureType featureType = createFeatureType( datasource, datasource.getGeometryFieldName(), rsmd );
146 int ccnt = rsmd.getColumnCount();
147 int k = 0;
148
149 // read each line from database and create a feature from it
150 while ( rs.next() ) {
151 FeatureProperty[] properties = new FeatureProperty[ccnt];
152 for ( int i = 0; i < ccnt; i++ ) {
153 String name = rsmd.getColumnName( i + 1 );
154 Object value = rs.getObject( i + 1 );
155 // if column name equals geometry field name the value read from
156 // database must be converted into a deegree geometry
157 if ( name.equalsIgnoreCase( datasource.getGeometryFieldName() ) && value != null ) {
158 byte[] wkb = (byte[]) value;
159 org.postgis.Geometry pgGeometry = new BinaryParser().parse( wkb );
160 value = PGgeometryAdapter.wrap( pgGeometry, crs );
161 }
162 properties[i] = FeatureFactory.createFeatureProperty( featureType.getPropertyName( i ), value );
163 }
164 // because feature IDs are not important in case of database datasource
165 // it is just 'ID' as prefix plus a number of current row
166 fc.add( FeatureFactory.createFeature( "ID" + k++, featureType, properties ) );
167 }
168 LOG.logDebug( k + " features loaded from database" );
169 } catch ( Exception e ) {
170 LOG.logError( e.getMessage(), e );
171 throw e;
172 } finally {
173 try {
174 if ( rs != null ) {
175 rs.close();
176 }
177 } catch ( Exception e ) {
178 // what to do here anyway
179 }
180 try {
181 if ( stmt != null ) {
182 stmt.close();
183 }
184 } catch ( SQLException e ) {
185 // what to do here anyway
186 }
187 releaseConnection( jdbc, conn );
188 }
189 return fc;
190 }
191
192 private static PreparedStatement createPreparedStatement( DatabaseDataSource datasource, Envelope envelope,
193 Connection conn, CoordinateSystem crs, String sql,
194 String extraClauses )
195 throws GeometryException, SQLException, IllegalArgumentException,
196 CRSTransformationException {
197 PreparedStatement stmt;
198
199 String nativeCRS = crs.getLocalName();
200 String envCRS = nativeCRS;
201 if ( envelope.getCoordinateSystem() != null ) {
202 envCRS = envelope.getCoordinateSystem().getLocalName();
203 }
204
205 // use the bbox operator (&&) to filter using the spatial index
206 if ( !( nativeCRS.equals( envCRS ) ) ) {
207 GeoTransformer gt = new GeoTransformer( crs );
208 envelope = gt.transform( envelope, envelope.getCoordinateSystem() );
209 }
210 Surface surface = GeometryFactory.createSurface( envelope, envelope.getCoordinateSystem() );
211 StringBuffer query = new StringBuffer( 1000 );
212
213 query.append( " Intersects(" );
214 query.append( datasource.getGeometryFieldName() );
215 query.append( ",GeomFromText(?))" );
216
217 if ( extraClauses != null ) {
218 query.append( extraClauses );
219 }
220
221 if ( sql.trim().toUpperCase().endsWith( " WHERE" ) ) {
222 LOG.logDebug( "performed SQL: ", sql + query );
223 stmt = conn.prepareStatement( sql + query );
224 } else {
225 LOG.logDebug( "performed SQL: ", sql + " AND " + query );
226 stmt = conn.prepareStatement( sql + " AND " + query );
227 }
228
229 LOG.logDebug( "Envelope in WKT: ", WKTAdapter.export( surface ).toString() );
230
231 stmt.setString( 1, WKTAdapter.export( surface ).toString() );
232 return stmt;
233 }
234
235 /**
236 *
237 * @param geometryFiedName
238 * @param rsmd
239 * @return {@link FeatureType} created from column names and types
240 * @throws SQLException
241 */
242 private static FeatureType createFeatureType( DatabaseDataSource datasource, String geometryFiedName,
243 ResultSetMetaData rsmd )
244 throws SQLException {
245 int ccnt = rsmd.getColumnCount();
246 QualifiedName name = new QualifiedName( datasource.getName().getLocalName(), namespace );
247 PropertyType[] properties = new PropertyType[ccnt];
248 for ( int i = 0; i < ccnt; i++ ) {
249 QualifiedName propName = new QualifiedName( rsmd.getColumnName( i + 1 ), namespace );
250 LOG.logDebug( "propertyname: ", propName );
251 int typeCode = getTypeCode( geometryFiedName, rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ) );
252 properties[i] = FeatureFactory.createSimplePropertyType( propName, typeCode, true );
253 }
254 return FeatureFactory.createFeatureType( name, false, properties );
255 }
256
257 private static int getTypeCode( String geometryFiedName, String columnName, int columnType ) {
258 if ( columnName.equalsIgnoreCase( geometryFiedName ) ) {
259 return Types.GEOMETRY;
260 }
261 return columnType;
262 }
263
264 private static void releaseConnection( JDBCConnection jdbc, Connection conn ) {
265 try {
266 DBConnectionPool pool = DBConnectionPool.getInstance();
267 pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
268 } catch ( DBPoolException e ) {
269 // what to do here anyway
270 }
271 }
272
273 private static Connection acquireConnection( JDBCConnection jdbc )
274 throws DBPoolException {
275 DBConnectionPool pool = DBConnectionPool.getInstance();
276 return pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
277 }
278
279 }