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 }