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 oracle.spatial.geometry.JGeometry;
048 import oracle.sql.STRUCT;
049
050 import org.deegree.datatypes.QualifiedName;
051 import org.deegree.datatypes.Types;
052 import org.deegree.framework.log.ILogger;
053 import org.deegree.framework.log.LoggerFactory;
054 import org.deegree.io.DBConnectionPool;
055 import org.deegree.io.DBPoolException;
056 import org.deegree.io.JDBCConnection;
057 import org.deegree.io.datastore.sql.oracle.JGeometryAdapter;
058 import org.deegree.model.crs.CRSTransformationException;
059 import org.deegree.model.crs.CoordinateSystem;
060 import org.deegree.model.crs.GeoTransformer;
061 import org.deegree.model.feature.FeatureCollection;
062 import org.deegree.model.feature.FeatureFactory;
063 import org.deegree.model.feature.FeatureProperty;
064 import org.deegree.model.feature.schema.FeatureType;
065 import org.deegree.model.feature.schema.PropertyType;
066 import org.deegree.model.spatialschema.Envelope;
067 import org.deegree.model.spatialschema.GeometryException;
068 import org.deegree.model.spatialschema.GeometryFactory;
069 import org.deegree.model.spatialschema.Surface;
070 import org.deegree.ogcwebservices.wms.configuration.DatabaseDataSource;
071
072 /**
073 * class for loading data as feature collection from a postgis database
074 *
075 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
076 * @author last edited by: $Author: poth $
077 *
078 * @version $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
079 */
080 public class OracleDataLoader {
081
082 private static final ILogger LOG = LoggerFactory.getLogger( OracleDataLoader.class );
083
084 private static URI namespace;
085 static {
086 try {
087 namespace = new URI( "http://www.deegree.org/database" );
088 } catch ( Exception e ) {
089 LOG.logError( e.getMessage(), e );
090 }
091 }
092
093 /**
094 * @param datasource
095 * @param envelope
096 * @return the feature collection directly from the db
097 * @throws Exception
098 */
099 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope )
100 throws Exception {
101 return load( datasource, envelope, null );
102 }
103
104 /**
105 * @param datasource
106 * @param envelope
107 * @param sql
108 * @return the fc from the db
109 * @throws Exception
110 */
111 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql )
112 throws Exception {
113 return load( datasource, envelope, sql, null );
114 }
115
116 /**
117 *
118 * @param datasource
119 * @param envelope
120 * @param sql
121 * @param extraClauses
122 * @return featurecollection loaded from a postgis database
123 * @throws Exception
124 */
125 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql,
126 String extraClauses )
127 throws Exception {
128 if ( sql == null ) {
129 sql = datasource.getSqlTemplate();
130 }
131
132 JDBCConnection jdbc = datasource.getJDBCConnection();
133 PreparedStatement stmt = null;
134 Connection conn = null;
135 ResultSet rs = null;
136 FeatureCollection fc = FeatureFactory.createFeatureCollection( UUID.randomUUID().toString(), 10000 );
137 try {
138 CoordinateSystem crs = datasource.getNativeCRS();
139 conn = acquireConnection( jdbc );
140 stmt = createPreparedStatement( datasource, envelope, conn, crs, sql, extraClauses );
141
142 rs = stmt.executeQuery();
143
144 LOG.logDebug( "performing database query: " + sql );
145 ResultSetMetaData rsmd = rs.getMetaData();
146 FeatureType featureType = createFeatureType( datasource, datasource.getGeometryFieldName(), rsmd );
147 int ccnt = rsmd.getColumnCount();
148 int k = 0;
149
150 // read each line from database and create a feature from it
151 while ( rs.next() ) {
152 FeatureProperty[] properties = new FeatureProperty[ccnt];
153 for ( int i = 0; i < ccnt; i++ ) {
154 String name = rsmd.getColumnName( i + 1 );
155 Object value = rs.getObject( i + 1 );
156 // if column name equals geometry field name the value read from
157 // database must be converted into a deegree geometry
158 if ( name.equalsIgnoreCase( datasource.getGeometryFieldName() ) ) {
159 JGeometry jGeometry = JGeometry.load( (STRUCT) value );
160 value = JGeometryAdapter.wrap( jGeometry, 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 JGeometry jgeom = JGeometryAdapter.export( surface, Integer.parseInt( nativeCRS ) );
212 StringBuffer query = new StringBuffer( 1000 );
213 query.append( " MDSYS.SDO_RELATE(" );
214 query.append( datasource.getGeometryFieldName() );
215 query.append( ',' );
216 query.append( '?' );
217 query.append( ",'MASK=ANYINTERACT QUERYTYPE=WINDOW')='TRUE'" );
218
219 if ( extraClauses != null ) {
220 query.append( extraClauses );
221 }
222
223 if ( sql.trim().toUpperCase().endsWith( " WHERE" ) ) {
224 LOG.logDebug( "performed SQL: ", sql );
225 stmt = conn.prepareStatement( sql + query );
226 } else {
227 LOG.logDebug( "performed SQL: ", sql + " AND " + query );
228 stmt = conn.prepareStatement( sql + " AND " + query );
229 }
230
231 LOG.logDebug( "Converting JGeometry to STRUCT." );
232 STRUCT struct = JGeometry.store( jgeom, conn );
233 stmt.setObject( 1, struct, java.sql.Types.STRUCT );
234 return stmt;
235 }
236
237 /**
238 *
239 * @param geometryFiedName
240 * @param rsmd
241 * @return {@link FeatureType} created from column names and types
242 * @throws SQLException
243 */
244 private static FeatureType createFeatureType( DatabaseDataSource datasource, String geometryFiedName,
245 ResultSetMetaData rsmd )
246 throws SQLException {
247 int ccnt = rsmd.getColumnCount();
248 QualifiedName name = new QualifiedName( datasource.getName().getLocalName(), namespace );
249 PropertyType[] properties = new PropertyType[ccnt];
250 for ( int i = 0; i < ccnt; i++ ) {
251 QualifiedName propName = new QualifiedName( rsmd.getColumnName( i + 1 ), namespace );
252 LOG.logDebug( "propertyname: ", propName );
253 int typeCode = getTypeCode( geometryFiedName, rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ) );
254 properties[i] = FeatureFactory.createSimplePropertyType( propName, typeCode, true );
255 }
256 return FeatureFactory.createFeatureType( name, false, properties );
257 }
258
259 private static int getTypeCode( String geometryFiedName, String columnName, int columnType ) {
260 if ( columnName.equalsIgnoreCase( geometryFiedName ) ) {
261 return Types.GEOMETRY;
262 }
263 return columnType;
264 }
265
266 private static void releaseConnection( JDBCConnection jdbc, Connection conn ) {
267 try {
268 DBConnectionPool pool = DBConnectionPool.getInstance();
269 pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
270 } catch ( DBPoolException e ) {
271 // what to do here anyway
272 }
273 }
274
275 private static Connection acquireConnection( JDBCConnection jdbc )
276 throws DBPoolException {
277 DBConnectionPool pool = DBConnectionPool.getInstance();
278 return pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
279 }
280
281 }