001 //$HeadURL: https://svn.wald.intevation.org/svn/deegree/base/branches/2.3_testing/src/org/deegree/io/datastore/sql/postgis/PostGISDatastore.java $
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.datastore.sql.postgis;
038
039 import static java.lang.Integer.parseInt;
040 import static org.deegree.model.crs.CRSFactory.create;
041
042 import java.io.IOException;
043 import java.io.InputStream;
044 import java.sql.Connection;
045 import java.sql.ResultSet;
046 import java.sql.SQLException;
047 import java.sql.Statement;
048 import java.util.HashMap;
049 import java.util.Map;
050 import java.util.Properties;
051
052 import org.deegree.framework.log.ILogger;
053 import org.deegree.framework.log.LoggerFactory;
054 import org.deegree.i18n.Messages;
055 import org.deegree.io.JDBCConnection;
056 import org.deegree.io.datastore.Datastore;
057 import org.deegree.io.datastore.DatastoreException;
058 import org.deegree.io.datastore.schema.MappedFeatureType;
059 import org.deegree.io.datastore.schema.MappedGeometryPropertyType;
060 import org.deegree.io.datastore.schema.TableRelation;
061 import org.deegree.io.datastore.schema.content.ConstantContent;
062 import org.deegree.io.datastore.schema.content.FieldContent;
063 import org.deegree.io.datastore.schema.content.FunctionParam;
064 import org.deegree.io.datastore.schema.content.MappingGeometryField;
065 import org.deegree.io.datastore.schema.content.SQLFunctionCall;
066 import org.deegree.io.datastore.sql.AbstractSQLDatastore;
067 import org.deegree.io.datastore.sql.SQLDatastoreConfiguration;
068 import org.deegree.io.datastore.sql.TableAliasGenerator;
069 import org.deegree.io.datastore.sql.VirtualContentProvider;
070 import org.deegree.io.datastore.sql.wherebuilder.WhereBuilder;
071 import org.deegree.model.crs.CoordinateSystem;
072 import org.deegree.model.crs.UnknownCRSException;
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: 18195 $, $Date: 2009-06-18 17:55:39 +0200 (Do, 18. Jun 2009) $
090 */
091 public class PostGISDatastore extends AbstractSQLDatastore {
092
093 private 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> to a deegree
159 * <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.getIdentifier() + "')" );
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 arguments (BBOX, etc.)
268 * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s.
269 * <p>
270 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be
271 * overwritten to return the original input {@link Query}.
272 *
273 * @param query
274 * query to be transformed
275 * @return query with spatial arguments transformed to target SRS
276 */
277 @Override
278 protected Query transformQuery( Query query ) {
279 return query;
280 }
281
282 /**
283 * Transforms the {@link FeatureCollection} so that the geometries of all contained geometry properties use the
284 * requested SRS.
285 *
286 * @param fc
287 * feature collection to be transformed
288 * @param targetSRS
289 * requested SRS
290 * @return transformed FeatureCollection
291 */
292 @Override
293 protected FeatureCollection transformResult( FeatureCollection fc, String targetSRS ) {
294 return fc;
295 }
296
297 /**
298 * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function
299 * call for example) into the given SRS.
300 *
301 * @param targetSRS
302 * target spatial reference system (usually "EPSG:XYZ")
303 * @return true, if the datastore can perform the coordinate transformation, false otherwise
304 */
305 @Override
306 protected boolean canTransformTo( String targetSRS ) {
307 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
308 }
309
310 /**
311 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target
312 * SRS using a database specific SQL function.
313 *
314 * @param geoProperty
315 * geometry property
316 * @param targetSRS
317 * target spatial reference system (usually "EPSG:XYZ")
318 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
319 * @throws DatastoreException
320 */
321 @Override
322 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
323 throws DatastoreException {
324
325 int nativeSRSCode = getNativeSRSCode( targetSRS );
326 if ( nativeSRSCode == SRS_UNDEFINED ) {
327 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
328 targetSRS );
329 throw new DatastoreException( msg );
330 }
331
332 MappingGeometryField field = geoProperty.getMappingField();
333 FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
334 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
335
336 SQLFunctionCall transformCall = new SQLFunctionCall( "transform($1,$2)", field.getType(), param1, param2 );
337 return transformCall;
338 }
339
340 @Override
341 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
342 throws DatastoreException {
343 String call = "transform(" + geomIdentifier + "," + nativeSRSCode + ")";
344 return call;
345 }
346
347 @Override
348 public int getNativeSRSCode( String srsName ) {
349 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
350
351 if ( nativeSRSCode == null ) {
352 try {
353 return parseInt( create( srsName ).getCRS().getIdentifier().split( ":" )[1] );
354 } catch ( NumberFormatException e ) {
355 LOG.logError( "Error while checking for srid code", e );
356 } catch ( UnknownCRSException e ) {
357 LOG.logError( "Error while checking for srid code", e );
358 }
359 return SRS_UNDEFINED;
360 }
361 return nativeSRSCode;
362 }
363
364 private static void initSRSCodeMap()
365 throws IOException {
366 InputStream is = PostGISDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
367 Properties props = new Properties();
368 props.load( is );
369 for ( Object key : props.keySet() ) {
370 String nativeCodeStr = props.getProperty( (String) key ).trim();
371 try {
372 int nativeCode = Integer.parseInt( nativeCodeStr );
373 nativeSrsCodeMap.put( (String) key, nativeCode );
374 } catch ( NumberFormatException e ) {
375 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr, key );
376 throw new IOException( msg );
377 }
378 }
379 }
380 }