001 //$HeadURL: svn+ssh://jwilden@svn.wald.intevation.org/deegree/base/branches/2.5_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.filterencoding.Filter;
074 import org.deegree.model.spatialschema.Geometry;
075 import org.deegree.model.spatialschema.GeometryException;
076 import org.deegree.ogcbase.SortProperty;
077 import org.deegree.ogcwebservices.wfs.operation.Query;
078 import org.postgis.PGgeometry;
079 import org.postgresql.PGConnection;
080
081 /**
082 * {@link Datastore} implementation for PostGIS/PostgreSQL databases.
083 *
084 * @author <a href="mailto:schneider@lat-lon.de">Markus Schneider </a>
085 * @author <a href="mailto:tfr@users.sourceforge.net">Torsten Friebe </a>
086 * @author last edited by: $Author: mschneider $
087 *
088 * @version $Revision: 23694 $, $Date: 2010-04-20 14:47:40 +0200 (Di, 20 Apr 2010) $
089 */
090 public class PostGISDatastore extends AbstractSQLDatastore {
091
092 private static final ILogger LOG = LoggerFactory.getLogger( PostGISDatastore.class );
093
094 private static final String GEOMETRY_DATATYPE_NAME = "geometry";
095
096 private static final String BOX3D_DATATYPE_NAME = "box3d";
097
098 private static final String PG_GEOMETRY_CLASS_NAME = "org.postgis.PGgeometry";
099
100 private static final String PG_BOX3D_CLASS_NAME = "org.postgis.PGbox3d";
101
102 private static Class<?> pgGeometryClass;
103
104 private static Class<?> pgBox3dClass;
105
106 private static final String SRS_CODE_PROP_FILE = "srs_codes_postgis.properties";
107
108 private static Map<String, Integer> nativeSrsCodeMap = new HashMap<String, Integer>();
109
110 private static boolean useNativeSrsCodeMap = true;
111
112 static {
113 try {
114 pgGeometryClass = Class.forName( PG_GEOMETRY_CLASS_NAME );
115 } catch ( ClassNotFoundException e ) {
116 LOG.logError( "Cannot find class '" + PG_GEOMETRY_CLASS_NAME + "'.", e );
117 }
118 try {
119 pgBox3dClass = Class.forName( PG_BOX3D_CLASS_NAME );
120 } catch ( ClassNotFoundException e ) {
121 LOG.logError( "Cannot find class '" + PG_BOX3D_CLASS_NAME + "'.", e );
122 }
123 try {
124 initSRSCodeMap();
125 } catch ( IOException e ) {
126 String msg = "Cannot load native srs code file '" + SRS_CODE_PROP_FILE + "'.";
127 LOG.logError( msg, e );
128 }
129 }
130
131 /**
132 * Returns a specific {@link WhereBuilder} implementation for PostGIS.
133 *
134 * @param rootFts
135 * involved (requested) feature types
136 * @param aliases
137 * aliases for the feature types, may be null
138 * @param filter
139 * filter that restricts the matched features
140 * @param sortProperties
141 * sort criteria for the result, may be null or empty
142 * @param aliasGenerator
143 * used to generate unique table aliases
144 * @param vcProvider
145 * @return <code>WhereBuilder</code> implementation for PostGIS
146 * @throws DatastoreException
147 */
148 @Override
149 public PostGISWhereBuilder getWhereBuilder( MappedFeatureType[] rootFts, String[] aliases, Filter filter,
150 SortProperty[] sortProperties, TableAliasGenerator aliasGenerator,
151 VirtualContentProvider vcProvider )
152 throws DatastoreException {
153 return new PostGISWhereBuilder( rootFts, aliases, filter, sortProperties, aliasGenerator, vcProvider );
154 }
155
156 /**
157 * Converts a PostGIS specific geometry <code>Object</code> from the <code>ResultSet</code> to a deegree
158 * <code>Geometry</code>.
159 *
160 * @param value
161 * @param targetCS
162 * @param conn
163 * @return corresponding deegree geometry
164 * @throws SQLException
165 */
166 @Override
167 public Geometry convertDBToDeegreeGeometry( Object value, CoordinateSystem targetCS, Connection conn )
168 throws SQLException {
169 Geometry geometry = null;
170 if ( value != null && value instanceof PGgeometry ) {
171 try {
172 LOG.logDebug( "Converting PostGIS geometry to deegree geometry ('" + targetCS.getIdentifier() + "')" );
173 geometry = PGgeometryAdapter.wrap( (PGgeometry) value, targetCS );
174 } catch ( Exception e ) {
175 throw new SQLException( "Error converting PostGIS geometry to deegree geometry: " + e.getMessage() );
176 }
177 }
178 return geometry;
179 }
180
181 /**
182 * Converts a deegree <code>Geometry</code> to a PostGIS specific geometry object.
183 *
184 * @param geometry
185 * @param targetSRS
186 * @param conn
187 * @return corresponding PostGIS specific geometry object
188 * @throws DatastoreException
189 */
190 @Override
191 public PGgeometry convertDeegreeToDBGeometry( Geometry geometry, int targetSRS, Connection conn )
192 throws DatastoreException {
193 PGgeometry pgGeometry;
194 try {
195 pgGeometry = PGgeometryAdapter.export( geometry, targetSRS );
196 } catch ( GeometryException e ) {
197 throw new DatastoreException( "Error converting deegree geometry to PostGIS geometry: " + e.getMessage(), e );
198 }
199 return pgGeometry;
200 }
201
202 @Override
203 protected Connection acquireConnection()
204 throws DatastoreException {
205 JDBCConnection jdbcConnection = ( (SQLDatastoreConfiguration) this.getConfiguration() ).getJDBCConnection();
206 Connection conn = null;
207 try {
208 conn = pool.acquireConnection( jdbcConnection.getDriver(), jdbcConnection.getURL(),
209 jdbcConnection.getUser(), jdbcConnection.getPassword() );
210 PGConnection pgConn = (PGConnection) conn;
211 pgConn.addDataType( GEOMETRY_DATATYPE_NAME, pgGeometryClass );
212 pgConn.addDataType( BOX3D_DATATYPE_NAME, pgBox3dClass );
213 } catch ( Exception e ) {
214 String msg = "Cannot acquire database connection: " + e.getMessage();
215 LOG.logInfo( msg );
216 throw new DatastoreException( msg, e );
217 }
218 return conn;
219 }
220
221 /**
222 * Returns the next value of the given SQL sequence.
223 *
224 * @param conn
225 * JDBC connection to be used
226 * @param sequence
227 * name of the SQL sequence
228 * @return next value of the given SQL sequence
229 * @throws DatastoreException
230 * if the value could not be retrieved
231 */
232 @Override
233 public Object getSequenceNextVal( Connection conn, String sequence )
234 throws DatastoreException {
235
236 Object nextVal = null;
237 Statement stmt = null;
238 ResultSet rs = null;
239
240 try {
241 try {
242 stmt = conn.createStatement();
243 rs = stmt.executeQuery( "SELECT NEXTVAL('" + sequence + "')" );
244 if ( rs.next() ) {
245 nextVal = rs.getObject( 1 );
246 }
247 } finally {
248 try {
249 if ( rs != null ) {
250 rs.close();
251 }
252 } finally {
253 if ( stmt != null ) {
254 stmt.close();
255 }
256 }
257 }
258 } catch ( SQLException e ) {
259 String msg = "Could not retrieve value for sequence '" + sequence + "': " + e.getMessage();
260 throw new DatastoreException( msg, e );
261 }
262 return nextVal;
263 }
264
265 /**
266 * Transforms the incoming {@link Query} so that the {@link CoordinateSystem} of all spatial arguments (BBOX, etc.)
267 * in the {@link Filter} match the SRS of the targeted {@link MappingGeometryField}s.
268 * <p>
269 * NOTE: If this transformation can be performed by the backend (e.g. by Oracle Spatial), this method should be
270 * overwritten to return the original input {@link Query}.
271 *
272 * @param query
273 * query to be transformed
274 * @return query with spatial arguments transformed to target SRS
275 */
276 @Override
277 protected Query transformQuery( Query query ) {
278 if ( query.getSrsName() == null || canTransformTo( query.getSrsName() ) ) {
279 return query;
280 }
281 return super.transformQuery( query );
282 }
283
284 /**
285 * Returns whether the datastore is capable of performing a native coordinate transformation (using an SQL function
286 * call for example) into the given SRS.
287 *
288 * @param targetSRS
289 * target spatial reference system (usually "EPSG:XYZ")
290 * @return true, if the datastore can perform the coordinate transformation, false otherwise
291 */
292 @Override
293 protected boolean canTransformTo( String targetSRS ) {
294 return getNativeSRSCode( targetSRS ) != SRS_UNDEFINED;
295 }
296
297 /**
298 * Returns an {@link SQLFunctionCall} that refers to the given {@link MappingGeometryField} in the specified target
299 * SRS using a database specific SQL function.
300 *
301 * @param geoProperty
302 * geometry property
303 * @param targetSRS
304 * target spatial reference system (usually "EPSG:XYZ")
305 * @return an {@link SQLFunctionCall} that refers to the geometry in the specified srs
306 * @throws DatastoreException
307 */
308 @Override
309 public SQLFunctionCall buildSRSTransformCall( MappedGeometryPropertyType geoProperty, String targetSRS )
310 throws DatastoreException {
311
312 int nativeSRSCode = getNativeSRSCode( targetSRS );
313 if ( nativeSRSCode == SRS_UNDEFINED ) {
314 String msg = Messages.getMessage( "DATASTORE_SQL_NATIVE_CT_UNKNOWN_SRS", this.getClass().getName(),
315 targetSRS );
316 throw new DatastoreException( msg );
317 }
318
319 MappingGeometryField field = geoProperty.getMappingField();
320 FunctionParam param1 = new FieldContent( field, new TableRelation[0] );
321 FunctionParam param2 = new ConstantContent( "" + nativeSRSCode );
322
323 SQLFunctionCall transformCall = new SQLFunctionCall( "transform($1,$2)", field.getType(), param1, param2 );
324 return transformCall;
325 }
326
327 @Override
328 public String buildSRSTransformCall( String geomIdentifier, int nativeSRSCode )
329 throws DatastoreException {
330 String call = "transform(" + geomIdentifier + "," + nativeSRSCode + ")";
331 return call;
332 }
333
334 @Override
335 public int getNativeSRSCode( String srsName ) {
336 if ( !useNativeSrsCodeMap ) {
337 try {
338 return parseInt( create( srsName ).getCRS().getIdentifier().split( ":" )[1] );
339 } catch ( NumberFormatException e ) {
340 LOG.logError( "Error while checking for srid code", e );
341 } catch ( UnknownCRSException e ) {
342 LOG.logError( "Error while checking for srid code", e );
343 }
344 }
345 Integer nativeSRSCode = nativeSrsCodeMap.get( srsName );
346 if ( nativeSRSCode == null ) {
347 return SRS_UNDEFINED;
348 }
349 return nativeSRSCode;
350 }
351
352 private static void initSRSCodeMap()
353 throws IOException {
354 InputStream is = PostGISDatastore.class.getResourceAsStream( SRS_CODE_PROP_FILE );
355 if ( is != null ) {
356 Properties props = new Properties();
357 props.load( is );
358 for ( Object key : props.keySet() ) {
359 String nativeCodeStr = props.getProperty( (String) key ).trim();
360 try {
361 int nativeCode = Integer.parseInt( nativeCodeStr );
362 nativeSrsCodeMap.put( (String) key, nativeCode );
363 } catch ( NumberFormatException e ) {
364 String msg = Messages.getMessage( "DATASTORE_SRS_CODE_INVALID", SRS_CODE_PROP_FILE, nativeCodeStr,
365 key );
366 throw new IOException( msg );
367 }
368 }
369 } else {
370 LOG.logInfo( "Not using '" + SRS_CODE_PROP_FILE
371 + "' for customizing PostGIS transformations. Not found on classpath." );
372 useNativeSrsCodeMap = false;
373 }
374 }
375 }