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.CoordinateSystem;
056 import org.deegree.model.feature.FeatureCollection;
057 import org.deegree.model.feature.FeatureFactory;
058 import org.deegree.model.feature.FeatureProperty;
059 import org.deegree.model.feature.schema.FeatureType;
060 import org.deegree.model.feature.schema.PropertyType;
061 import org.deegree.model.spatialschema.Envelope;
062 import org.deegree.model.spatialschema.GeometryException;
063 import org.deegree.model.spatialschema.GeometryFactory;
064 import org.deegree.model.spatialschema.Surface;
065 import org.deegree.ogcwebservices.wms.configuration.DatabaseDataSource;
066 import org.postgis.PGboxbase;
067 import org.postgis.PGgeometry;
068 import org.postgresql.PGConnection;
069
070 /**
071 * class for loading data as feature collection from a postgis database
072 *
073 * @author <a href="mailto:poth@lat-lon.de">Andreas Poth</a>
074 * @author last edited by: $Author: poth $
075 *
076 * @version $Revision: 6251 $, $Date: 2007-03-19 16:59:28 +0100 (Mo, 19 Mrz 2007) $
077 */
078 public class PostgisDataLoader {
079
080 private static final ILogger LOG = LoggerFactory.getLogger( PostgisDataLoader.class );
081
082 private static final String GEOMETRY_DATATYPE_NAME = "geometry";
083
084 private static final String BOX3D_DATATYPE_NAME = "box3d";
085
086 private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
087
088 private static final String PG_BOX3D_CLASS_NAME = "org.postgis.PGbox3d";
089
090 private static Class<?> pgGeometryClass;
091
092 private static Class<?> pgBox3dClass;
093
094 private static URI namespace;
095 static {
096 try {
097 namespace = new URI( "http://www.deegree.org/database" );
098 } catch ( Exception e ) {
099 LOG.logError( e.getMessage(), e );
100 }
101 try {
102 pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
103 } catch ( ClassNotFoundException e ) {
104 LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
105 }
106 try {
107 pgBox3dClass = Class.forName( PG_BOX3D_CLASS_NAME );
108 } catch ( ClassNotFoundException e ) {
109 LOG.logError( "Cannot find class '" + PG_BOX3D_CLASS_NAME + "'.", e );
110 }
111 }
112
113 /**
114 * @param datasource
115 * @param envelope
116 * @param sql
117 * @return the fc
118 * @throws Exception
119 */
120 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql )
121 throws Exception {
122 return load( datasource, envelope, sql, null );
123 }
124
125 /**
126 * @param datasource
127 * @param envelope
128 * @param sql
129 * @param extraClauses
130 * @return the feature collection directly from the db
131 * @throws Exception
132 */
133 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope, String sql,
134 String extraClauses )
135 throws Exception {
136 if ( sql == null ) {
137 sql = datasource.getSqlTemplate();
138 }
139
140 JDBCConnection jdbc = datasource.getJDBCConnection();
141 PreparedStatement stmt = null;
142 Connection conn = null;
143 ResultSet rs = null;
144 FeatureCollection fc = FeatureFactory.createFeatureCollection( UUID.randomUUID().toString(), 10000 );
145 try {
146 CoordinateSystem crs = datasource.getNativeCRS();
147 conn = acquireConnection( jdbc );
148 stmt = createPreparedStatement( datasource, envelope, conn, crs, sql, extraClauses );
149
150 rs = stmt.executeQuery();
151
152 LOG.logDebug( "performing database query: " + sql );
153 ResultSetMetaData rsmd = rs.getMetaData();
154 FeatureType featureType = createFeatureType( datasource, datasource.getGeometryFieldName(), rsmd );
155 int ccnt = rsmd.getColumnCount();
156 int k = 0;
157
158 // read each line from database and create a feature from it
159 while ( rs.next() ) {
160 FeatureProperty[] properties = new FeatureProperty[ccnt];
161 for ( int i = 0; i < ccnt; i++ ) {
162 String name = rsmd.getColumnName( i + 1 );
163 Object value = rs.getObject( i + 1 );
164 // if column name equals geometry field name the value read from
165 // database must be converted into a deegree geometry
166 if ( name.equalsIgnoreCase( datasource.getGeometryFieldName() ) ) {
167 value = PGgeometryAdapter.wrap( (PGgeometry) value, crs );
168 }
169 properties[i] = FeatureFactory.createFeatureProperty( featureType.getPropertyName( i ), value );
170 }
171 // because feature IDs are not important in case of database datasource
172 // it is just 'ID' as prefix plus a number of current row
173 fc.add( FeatureFactory.createFeature( "ID" + k++, featureType, properties ) );
174 }
175 LOG.logDebug( k + " features loaded from database" );
176 } catch ( Exception e ) {
177 LOG.logError( e.getMessage(), e );
178 throw e;
179 } finally {
180 try {
181 if ( rs != null ) {
182 rs.close();
183 }
184 } catch ( Exception e ) {
185 // what to do here anyway
186 }
187 try {
188 if ( stmt != null ) {
189 stmt.close();
190 }
191 } catch ( SQLException e ) {
192 // what to do here anyway
193 }
194 releaseConnection( jdbc, conn );
195 }
196 return fc;
197 }
198
199 /**
200 *
201 * @param datasource
202 * @param envelope
203 * @return featurecollection loaded from a postgis database
204 * @throws Exception
205 */
206 public static FeatureCollection load( DatabaseDataSource datasource, Envelope envelope )
207 throws Exception {
208 return load( datasource, envelope, null );
209 }
210
211 private static PreparedStatement createPreparedStatement( DatabaseDataSource datasource, Envelope envelope,
212 Connection conn, CoordinateSystem crs, String sql,
213 String extraClauses )
214 throws GeometryException, SQLException {
215 PreparedStatement stmt;
216
217 String nativeCRS = getSRSCode( crs );
218 String envCRS;
219 if ( envelope.getCoordinateSystem() != null ) {
220 envCRS = getSRSCode( envelope.getCoordinateSystem() );
221 } else {
222 envCRS = nativeCRS;
223 }
224
225 // use the bbox operator (&&) to filter using the spatial index
226 PGboxbase box = PGgeometryAdapter.export( envelope );
227 Surface surface = GeometryFactory.createSurface( envelope, envelope.getCoordinateSystem() );
228 PGgeometry pggeom = PGgeometryAdapter.export( surface, Integer.parseInt( envCRS ) );
229 StringBuffer query = new StringBuffer( 1000 );
230 query.append( " (" );
231 query.append( datasource.getGeometryFieldName() );
232 query.append( " && transform(SetSRID( ?, " );
233 query.append( envCRS );
234 query.append( "), " );
235 query.append( nativeCRS );
236 query.append( ")) AND intersects(" );
237 query.append( datasource.getGeometryFieldName() );
238 query.append( ",transform(?, " );
239 query.append( nativeCRS );
240 query.append( "))" );
241
242 if ( extraClauses != null ) {
243 query.append( extraClauses );
244 }
245
246 if ( sql.indexOf( "$BBOX" ) == -1 ) {
247 if ( sql.trim().toUpperCase().endsWith( " WHERE" ) ) {
248 LOG.logDebug( "performed SQL: ", sql + query );
249 stmt = conn.prepareStatement( sql + query );
250 } else {
251 LOG.logDebug( "performed SQL: ", sql + " AND " + query );
252 stmt = conn.prepareStatement( sql + " AND " + query );
253 }
254 } else {
255 if ( sql.substring( 0, sql.indexOf( "$BBOX" ) ).trim().toUpperCase().endsWith( " WHERE" ) ) {
256 stmt = conn.prepareStatement( sql.replace( "$BBOX", query ) );
257 } else {
258 stmt = conn.prepareStatement( sql.replace( "$BBOX", " and " + query ) );
259 }
260 LOG.logDebug( "performed SQL: ", stmt );
261 }
262 stmt.setObject( 1, box, java.sql.Types.OTHER );
263 stmt.setObject( 2, pggeom, java.sql.Types.OTHER );
264 return stmt;
265 }
266
267 private static String getSRSCode( CoordinateSystem crs ) {
268 return crs.getLocalName();
269 }
270
271 /**
272 *
273 * @param geometryFiedName
274 * @param rsmd
275 * @return {@link FeatureType} created from column names and types
276 * @throws SQLException
277 */
278 private static FeatureType createFeatureType( DatabaseDataSource datasource, String geometryFiedName,
279 ResultSetMetaData rsmd )
280 throws SQLException {
281 int ccnt = rsmd.getColumnCount();
282 QualifiedName name = new QualifiedName( datasource.getName().getLocalName(), namespace );
283 PropertyType[] properties = new PropertyType[ccnt];
284 for ( int i = 0; i < ccnt; i++ ) {
285 QualifiedName propName = new QualifiedName( rsmd.getColumnName( i + 1 ), namespace );
286 LOG.logDebug( "propertyname: ", propName );
287 int typeCode = getTypeCode( geometryFiedName, rsmd.getColumnName( i + 1 ), rsmd.getColumnType( i + 1 ) );
288 properties[i] = FeatureFactory.createSimplePropertyType( propName, typeCode, true );
289 }
290 return FeatureFactory.createFeatureType( name, false, properties );
291 }
292
293 private static int getTypeCode( String geometryFiedName, String columnName, int columnType ) {
294 if ( columnName.equalsIgnoreCase( geometryFiedName ) ) {
295 return Types.GEOMETRY;
296 }
297 return columnType;
298 }
299
300 private static void releaseConnection( JDBCConnection jdbc, Connection conn ) {
301 try {
302 DBConnectionPool pool = DBConnectionPool.getInstance();
303 pool.releaseConnection( conn, jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
304 } catch ( DBPoolException e ) {
305 // what to do here anyway
306 }
307 }
308
309 private static Connection acquireConnection( JDBCConnection jdbc )
310 throws DBPoolException, SQLException {
311 Connection conn;
312 DBConnectionPool pool = DBConnectionPool.getInstance();
313 conn = pool.acquireConnection( jdbc.getDriver(), jdbc.getURL(), jdbc.getUser(), jdbc.getPassword() );
314 PGConnection pgConn = (PGConnection) conn;
315 pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
316 pgConn.addDataType( BOX3D_DATATYPE_NAME, pgBox3dClass );
317 return conn;
318 }
319
320 }