001 //$HeadURL: svn+ssh://rbezema@svn.wald.intevation.org/deegree/base/tags/2.1/src/org/deegree/io/datastore/sql/postgis/PostGISDatastore.java $
002 /*---------------- FILE HEADER ------------------------------------------
003 This file is part of deegree.
004 Copyright (C) 2001-2006 by:
005 Department of Geography, University of Bonn
006 http://www.giub.uni-bonn.de/deegree/
007 lat/lon GmbH
008 http://www.lat-lon.de
009
010 This library is free software; you can redistribute it and/or
011 modify it under the terms of the GNU Lesser General Public
012 License as published by the Free Software Foundation; either
013 version 2.1 of the License, or (at your option) any later version.
014
015 This library is distributed in the hope that it will be useful,
016 but WITHOUT ANY WARRANTY; without even the implied warranty of
017 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
018 Lesser General Public License for more details.
019
020 You should have received a copy of the GNU Lesser General Public
021 License along with this library; if not, write to the Free Software
022 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
023
024 Contact:
025
026 Andreas Poth
027 lat/lon GmbH
028 Aennchenstraße 19
029 53177 Bonn
030 Germany
031 E-Mail: poth@lat-lon.de
032
033 Jens Fitzke
034 lat/lon GmbH
035 Aennchenstraße 19
036 53177 Bonn
037 Germany
038 E-Mail: jens.fitzke@uni-bonn.de
039 ---------------------------------------------------------------------------*/
040
041 package org.deegree.io.datastore.sql.postgis;
042
043 import java.io.IOException;
044 import java.io.InputStream;
045 import java.sql.Connection;
046 import java.sql.ResultSet;
047 import java.sql.SQLException;
048 import java.sql.Statement;
049 import java.util.HashMap;
050 import java.util.Map;
051 import java.util.Properties;
052
053 import org.deegree.framework.log.ILogger;
054 import org.deegree.framework.log.LoggerFactory;
055 import org.deegree.i18n.Messages;
056 import org.deegree.io.JDBCConnection;
057 import org.deegree.io.datastore.Datastore;
058 import org.deegree.io.datastore.DatastoreException;
059 import org.deegree.io.datastore.schema.MappedFeatureType;
060 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
061 import org.deegree.io.datastore.schema.TableRelation;
062 import org.deegree.io.datastore.schema.content.ConstantContent;
063 import org.deegree.io.datastore.schema.content.FieldContent;
064 import org.deegree.io.datastore.schema.content.FunctionParam;
065 import org.deegree.io.datastore.schema.content.MappingGeometryField;
066 import org.deegree.io.datastore.schema.content.SQLFunctionCall;
067 import org.deegree.io.datastore.sql.AbstractSQLDatastore;
068 import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
069 import org.deegree.io.datastore.sql.TableAliasGenerator;
070 import org.deegree.io.datastore.sql.VirtualContentProvider;
071 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
072 import org.deegree.model.crs.CoordinateSystem;
073 import org.deegree.model.feature.FeatureCollection;
074 import org.deegree.model.filterencoding.Filter;
075 import org.deegree.model.spatialschema.Geometry;
076 import org.deegree.model.spatialschema.GeometryException;
077 import org.deegree.ogcbase.SortProperty;
078 import org.deegree.ogcwebservices.wfs.operation.Query;
079 import org.postgis.PGgeometry;
080 import org.postgresql.PGConnection;
081
082 /**
083 * {@link Datastore} implementation for PostGIS/PostgreSQL databases.
084 *
085 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
086 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
087 * @author last edited by: $Author: mschneider $
088 *
089 * @version $Revision: 6633 $, $Date: 2007-04-18 16:32:16 +0200 (Mi, 18 Apr 2007) $
090 */
091 public class PostGISDatastore extends AbstractSQLDatastore {
092
093 protected static final ILogger LOG = LoggerFactory.getLogger( PostGISDatastore.class );
094
095 private static final String GEOMETRY_DATATYPE_NAME = "geometry";
096
097 private static final String BOX3D_DATATYPE_NAME = "box3d";
098
099 private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
100
101 private static final String PG_BOX3D_CLASS_NAME = "org.postgis.PGbox3d";
102
103 private static Class pgGeometryClass;
104
105 private static Class pgBox3dClass;
106
107 private static final String SRS_CODE_PROP_FILE = "srs_codes_postgis.properties";
108
109 private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>();
110
111 private static final int SRS_UNDEFINED = -1;
112
113 static {
114 try {
115 pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
116 } catch ( ClassNotFoundException e ) {
117 LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
118 }
119 try {
120 pgBox3dClass = Class.forName( PG_BOX3D_CLASS_NAME );
121 } catch ( ClassNotFoundException e ) {
122 LOG.logError( "Cannot find class '" + PG_BOX3D_CLASS_NAME + "'.", e );
123 }
124 try {
125 initSRSCodeMap();
126 } catch ( IOException e ) {
127 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
128 LOG.logError( msg, e );
129 }
130 }
131
132 /**
133 * Returns a specific {@link WhereBuilder} implementation for PostGIS.
134 *
135 * @param rootFts
136 * involved (requested) feature types
137 * @param aliases
138 * aliases for the feature types, may be null
139 * @param filter
140 * filter that restricts the matched features
141 * @param sortProperties
142 * sort criteria for the result, may be null or empty
143 * @param aliasGenerator
144 * used to generate unique table aliases
145 * @param vcProvider
146 * @return <code>WhereBuilder</code> implementation for PostGIS
147 * @throws DatastoreException
148 */
149 @Override
150 public PostGISWhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
151 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
152 VirtualContentProvider vcProvider )
153 throws DatastoreException {
154 return new PostGISWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
155 }
156
157 /**
158 * Converts a PostGIS specific geometry <code>Object</code> from the <code>ResultSet</code>
159 * to a deegree <code>Geometry</code>.
160 *
161 * @param value
162 * @param targetCS
163 * @param conn
164 * @return corresponding deegree geometry
165 * @throws SQLException
166 */
167 @Override
168 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
169 throws SQLException {
170 Geometry geometry = null;
171 if ( value != null && value instanceof PGgeometry ) {
172 try {
173 LOG.logDebug( "Converting PostGIS geometry to deegree geometry ('" + targetCS.getName() + "')" );
174 geometry = PGgeometryAdapter.wrap( (PGgeometry) value, targetCS );
175 } catch ( Exception e ) {
176 throw new SQLException( "Error converting PostGIS geometry to deegree geometry: " + e.getMessage() );
177 }
178 }
179 return geometry;
180 }
181
182 /**
183 * Converts a deegree <code>Geometry</code> to a PostGIS specific geometry object.
184 *
185 * @param geometry
186 * @param targetSRS
187 * @param conn
188 * @return corresponding PostGIS specific geometry object
189 * @throws DatastoreException
190 */
191 @Override
192 public PGgeometry convertDeegreeToDBGeometry( Geometry geometry, int targetSRS, Connection conn )
193 throws DatastoreException {
194 PGgeometry pgGeometry;
195 try {
196 pgGeometry = PGgeometryAdapter.export( geometry, targetSRS );
197 } catch ( GeometryException e ) {
198 throw new DatastoreException( "Error converting deegree geometry to PostGIS geometry: " + e.getMessage(), e );
199 }
200 return pgGeometry;
201 }
202
203 @Override
204 protected Connection acquireConnection()
205 throws DatastoreException {
206 JDBCConnection jdbcConnection = ( (SQLDatastoreConfiguration) this.getConfiguration() ).getJDBCConnection();
207 Connection conn = null;
208 try {
209 conn = pool.acquireConnection( jdbcConnection.getDriver(), jdbcConnection.getURL(),
210 jdbcConnection.getUser(), jdbcConnection.getPassword() );
211 PGConnection pgConn = (PGConnection) conn;
212 pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
213 pgConn.addDataType( BOX3D_DATATYPE_NAME, pgBox3dClass );
214 } catch ( Exception e ) {
215 String msg = "Cannot acquire database connection: " + e.getMessage();
216 LOG.logInfo( msg );
217 throw new DatastoreException( msg, e );
218 }
219 return conn;
220 }
221
222 /**
223 * Returns the next value of the given SQL sequence.
224 *
225 * @param conn
226 * JDBC connection to be used
227 * @param sequence
228 * name of the SQL sequence
229 * @return next value of the given SQL sequence
230 * @throws DatastoreException
231 * if the value could not be retrieved
232 */
233 @Override
234 public Object getSequenceNextVal( Connection conn, String sequence )
235 throws DatastoreException {
236
237 Object nextVal = null;
238 Statement stmt = null;
239 ResultSet rs = null;
240
241 try {
242 try {
243 stmt = conn.createStatement();
244 rs = stmt.executeQuery( "SELECT NEXTVAL('" + sequence + "')" );
245 if ( rs.next() ) {
246 nextVal = rs.getObject( 1 );
247 }
248 } finally {
249 try {
250 if ( rs != null ) {
251 rs.close();
252 }
253 } finally {
254 if ( stmt != null ) {
255 stmt.close();
256 }
257 }
258 }
259 } catch ( SQLException e ) {
260 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
261 throw new DatastoreException( msg, e );
262 }
263 return nextVal;
264 }
265
266 /**
267 * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial
268 * arguments (BBOX, etc.) in the {@link Filter} match the SRS of the targeted
269 * {@link MappingGeometryField}s.
270 * <p>
271 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this
272 * method should be overwritten to return the original input {@link Query}.
273 *
274 * @param query
275 * query to be transformed
276 * @return query with spatial arguments transformed to target SRS
277 */
278 @Override
279 protected Query transformQuery( Query query ) {
280 return query;
281 }
282
283 /**
284 * Transforms the {@link FeatureCollection} so that the geometries of all contained geometry
285 * properties use the requested SRS.
286 *
287 * @param fc
288 * feature collection to be transformed
289 * @param targetSRS
290 * requested SRS
291 * @return transformed FeatureCollection
292 */
293 @Override
294 protected FeatureCollection transformResult( FeatureCollection fc, String targetSRS ) {
295 return fc;
296 }
297
298 /**
299 * Returns whether the datastore is capable of performing a native coordinate transformation
300 * (using an SQL function call for example) into the given SRS.
301 *
302 * @param targetSRS
303 * target spatial reference system (usually "EPSG:XYZ")
304 * @return true, if the datastore can perform the coordinate transformation, false otherwise
305 */
306 @Override
307 protected boolean canTransformTo( String targetSRS ) {
308 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
309 }
310
311 /**
312 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in
313 * the specified target SRS using a database specific SQL function.
314 *
315 * @param geoProperty
316 * geometry property
317 * @param targetSRS
318 * target spatial reference system (usually "EPSG:XYZ")
319 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
320 * @throws DatastoreException
321 */
322 @Override
323 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
324 throws DatastoreException {
325
326 int nativeSRSCode = getNativeSRSCode( targetSRS );
327 if ( nativeSRSCode == SRS_UNDEFINED ) {
328 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
329 targetSRS );
330 throw new DatastoreException( msg );
331 }
332
333 MappingGeometryField field = geoProperty.getMappingField();
334 FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
335 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
336
337 SQLFunctionCall transformCall = new SQLFunctionCall( "transform($1,$2)", field.getType(), param1, param2 );
338 return transformCall;
339 }
340
341 @Override
342 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
343 throws DatastoreException {
344 String call = "transform(" + geomIdentifier + "," + nativeSRSCode + ")";
345 return call;
346 }
347
348 @Override
349 public int getNativeSRSCode( String srsName ) {
350 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
351 if ( nativeSRSCode == null ) {
352 return SRS_UNDEFINED;
353 }
354 return nativeSRSCode;
355 }
356
357 private static void initSRSCodeMap()
358 throws IOException {
359 InputStream is = PostGISDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
360 Properties props = new Properties();
361 props.load( is );
362 for ( Object key : props.keySet() ) {
363 String nativeCodeStr = props.getProperty( (String) key ).trim();
364 try {
365 int nativeCode = Integer.parseInt( nativeCodeStr );
366 nativeSrsCodeMap.put( (String) key, nativeCode );
367 } catch ( NumberFormatException e ) {
368 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr, key );
369 throw new IOException( msg );
370 }
371 }
372 }
373 }