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