Revision 65 org.gvsig.oracle/trunk/org.gvsig.oracle/org.gvsig.oracle.provider/src/main/java/org/gvsig/oracle/dal/operations/OracleFetchFeatureTypeOperation.java
OracleFetchFeatureTypeOperation.java | ||
---|---|---|
2 | 2 |
package org.gvsig.oracle.dal.operations; |
3 | 3 |
|
4 | 4 |
import java.sql.Connection; |
5 |
import java.sql.PreparedStatement; |
|
5 | 6 |
import java.sql.ResultSet; |
6 | 7 |
import java.sql.ResultSetMetaData; |
7 | 8 |
import java.sql.SQLException; |
8 | 9 |
import java.sql.Statement; |
9 |
import java.util.ArrayList; |
|
10 | 10 |
import java.util.HashMap; |
11 |
import java.util.HashSet; |
|
11 | 12 |
import java.util.List; |
12 | 13 |
import java.util.Map; |
13 | 14 |
|
... | ... | |
16 | 17 |
import org.gvsig.fmap.dal.exception.DataException; |
17 | 18 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
18 | 19 |
import org.gvsig.fmap.dal.feature.EditableFeatureType; |
19 |
import org.gvsig.fmap.dal.feature.spi.ExpressionBuilderBase; |
|
20 | 20 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
21 | 21 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
22 | 22 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
... | ... | |
85 | 85 |
if (type>=2000 && type<=4449) { |
86 | 86 |
return DataTypes.GEOMETRY; |
87 | 87 |
} |
88 |
String typeName = rsMetadata.getColumnTypeName(colIndex); |
|
89 |
if( "MDSYS.SDO_GEOMETRY".equalsIgnoreCase(typeName) ) { |
|
90 |
return DataTypes.GEOMETRY; |
|
88 |
switch (type) { |
|
89 |
case java.sql.Types.INTEGER: |
|
90 |
return DataTypes.INT; |
|
91 |
|
|
92 |
case java.sql.Types.BIGINT: |
|
93 |
return DataTypes.LONG; |
|
94 |
|
|
95 |
case java.sql.Types.REAL: |
|
96 |
return DataTypes.DOUBLE; |
|
97 |
|
|
98 |
case java.sql.Types.DOUBLE: |
|
99 |
return DataTypes.DOUBLE; |
|
100 |
|
|
101 |
case java.sql.Types.CHAR: |
|
102 |
return DataTypes.STRING; |
|
103 |
|
|
104 |
case java.sql.Types.VARCHAR: |
|
105 |
case java.sql.Types.LONGVARCHAR: |
|
106 |
return DataTypes.STRING; |
|
107 |
|
|
108 |
case java.sql.Types.FLOAT: |
|
109 |
return DataTypes.DOUBLE; |
|
110 |
|
|
111 |
case java.sql.Types.NUMERIC: |
|
112 |
if (rsMetadata.getScale(colIndex)==0) { |
|
113 |
if (rsMetadata.getPrecision(colIndex)>9) { |
|
114 |
return DataTypes.LONG; |
|
115 |
} |
|
116 |
} |
|
117 |
return DataTypes.DOUBLE; |
|
118 |
|
|
119 |
case java.sql.Types.DECIMAL: |
|
120 |
return DataTypes.FLOAT; |
|
121 |
|
|
122 |
case java.sql.Types.DATE: |
|
123 |
return DataTypes.DATE; |
|
124 |
|
|
125 |
case java.sql.Types.TIME: |
|
126 |
return DataTypes.TIME; |
|
127 |
|
|
128 |
case java.sql.Types.TIMESTAMP: |
|
129 |
return DataTypes.TIMESTAMP; |
|
130 |
|
|
131 |
case java.sql.Types.BOOLEAN: |
|
132 |
case java.sql.Types.BIT: |
|
133 |
return DataTypes.BOOLEAN; |
|
134 |
|
|
135 |
case java.sql.Types.BLOB: |
|
136 |
case java.sql.Types.BINARY: |
|
137 |
case java.sql.Types.LONGVARBINARY: |
|
138 |
return DataTypes.BYTEARRAY; |
|
139 |
default: |
|
140 |
String typeName = rsMetadata.getColumnTypeName(colIndex); |
|
141 |
if( "MDSYS.SDO_GEOMETRY".equalsIgnoreCase(typeName) ) { |
|
142 |
return DataTypes.GEOMETRY; |
|
143 |
} |
|
144 |
return DataTypes.OBJECT; |
|
91 | 145 |
} |
92 |
return super.getDataTypeFromMetadata(rsMetadata, colIndex); |
|
93 | 146 |
} |
94 | 147 |
|
95 | 148 |
|
... | ... | |
102 | 155 |
if( attr.getType()!=DataTypes.GEOMETRY ) { |
103 | 156 |
return; |
104 | 157 |
} |
158 |
doFetchFromMetadata(attr, rsMetadata, colIndex); |
|
159 |
} |
|
160 |
|
|
161 |
|
|
162 |
/** |
|
163 |
* Fetches the SRID and geom type from several Oracle metadata |
|
164 |
* tables: |
|
165 |
* ALL_SDO_GEOM_METADATA, ALL_SDO_INDEX_METADATA, ALL_SDO_INDEX_INFO |
|
166 |
* |
|
167 |
* @param attr |
|
168 |
* @param rsMetadata |
|
169 |
* @param colIndex |
|
170 |
*/ |
|
171 |
protected void doFetchFromMetadata( |
|
172 |
EditableFeatureAttributeDescriptor attr, |
|
173 |
ResultSetMetaData rsMetadata, |
|
174 |
int colIndex |
|
175 |
) { |
|
176 |
|
|
177 |
int srid = -1; |
|
178 |
int dimensions = 2; |
|
179 |
try { |
|
180 |
String sql = "SELECT SRID, " |
|
181 |
+ "( SELECT count(*) FROM TABLE(t1.diminfo) ) dimensions " |
|
182 |
+ "FROM ALL_SDO_GEOM_METADATA t1 " |
|
183 |
+ "WHERE OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?" ; |
|
184 |
|
|
185 |
ResultSet rs = null; |
|
186 |
PreparedStatement st = null; |
|
187 |
try { |
|
188 |
st = this.getConnection().prepareStatement(sql); |
|
189 |
st.setString(1, this.getSchema()); |
|
190 |
st.setString(2, this.getTablename()); |
|
191 |
st.setString(3, attr.getName()); |
|
192 |
|
|
193 |
rs = JDBCUtils.executeQuery(st, sql); |
|
194 |
|
|
195 |
if (rs.next()) { |
|
196 |
srid = rs.getInt(1); |
|
197 |
dimensions = rs.getInt(2); |
|
198 |
} |
|
199 |
if( srid!=-1 ) { |
|
200 |
attr.setSRS(this.helper.getProjectionFromDatabaseCode(new Integer(srid).toString())); |
|
201 |
} |
|
202 |
} catch (Exception ex) { |
|
203 |
logger.debug("Can't get srs for column '"+attr.getName()+"'.",ex); |
|
204 |
} |
|
205 |
finally { |
|
206 |
JDBCUtils.closeQuietly(rs); |
|
207 |
JDBCUtils.closeQuietly(st); |
|
208 |
} |
|
209 |
} catch (Exception ex) { |
|
210 |
logger.debug("Can't get geometry type and srs from column '"+attr.getName()+"'.",ex); |
|
211 |
} |
|
212 |
String oracleGeomType = doFetchFromSpatialIndex(attr.getName()); |
|
213 |
GeometryType gt = getGeometryTypeFromOracleName(oracleGeomType, dimensions); |
|
214 |
attr.setGeometryType(gt); |
|
215 |
} |
|
216 |
|
|
217 |
protected GeometryType getGeometryTypeFromOracleName(String geomType, int dimensions) { |
|
218 |
String geomTypeWKT; |
|
219 |
geomType = geomType.toUpperCase(); |
|
220 |
if ("POINT".equals((geomType))) { |
|
221 |
geomTypeWKT = "POINT"; |
|
222 |
} |
|
223 |
else if ("LINE".equals(geomType) || "CURVE".equals(geomType)) { |
|
224 |
geomTypeWKT = "LINESTRING"; |
|
225 |
} |
|
226 |
else if ("POLYGON".equals(geomType) || "SURFACE".equals(geomType)) { |
|
227 |
geomTypeWKT = "POLYGON"; |
|
228 |
} |
|
229 |
else if ("MULTIPOINT".equals(geomType)) { |
|
230 |
geomTypeWKT = "MULTIPOINT"; |
|
231 |
} |
|
232 |
else if ("MULTILINE".equals(geomType) || "MULTICURVE".equals(geomType)) { |
|
233 |
geomTypeWKT = "MULTILINESTRING"; |
|
234 |
} |
|
235 |
else if ("MULTIPOLYGON".equals(geomType) || "MULTISURFACE".equals(geomType)) { |
|
236 |
geomTypeWKT = "MULTIPOLYGON"; |
|
237 |
} |
|
238 |
else { |
|
239 |
geomTypeWKT = "GEOMETRY"; |
|
240 |
} |
|
241 |
if (dimensions==3) { |
|
242 |
geomTypeWKT = geomTypeWKT + "Z"; |
|
243 |
} |
|
244 |
if (dimensions==4) { |
|
245 |
geomTypeWKT = geomTypeWKT + "ZM"; |
|
246 |
} |
|
247 |
return getGeometryTypeFromDatabaseTypeName(geomTypeWKT); |
|
248 |
} |
|
249 |
|
|
250 |
/** |
|
251 |
* Fetches the geometry type from the spatial index definition. |
|
252 |
* Note that this method does not allow to distinguish XYZ geoms |
|
253 |
* from XYM geoms, so XYM is reported as XYZ. |
|
254 |
* |
|
255 |
* @param geomColumnName the name of the column containing the geometry |
|
256 |
* @return An Oracle geometry type name, or "DEFAULT" if an spatial index |
|
257 |
* is not defined for the geom |
|
258 |
*/ |
|
259 |
protected String doFetchFromSpatialIndex( |
|
260 |
String geomColumnName |
|
261 |
) { |
|
105 | 262 |
try { |
106 |
/** |
|
107 |
* Directly querying the table to get the geometry type and srid. |
|
108 |
* This will perform a full scan on the table :-( |
|
109 |
* |
|
110 |
* We can also get the SRID and number of dimensions from |
|
111 |
* MDSYS.USER_SDO_GEOM_METADATA or MDSYS.SDO_GEOM_METADATA_TABLE, |
|
112 |
* but we can't get the geometry type (point, line, etc) from there. |
|
113 |
*/ |
|
263 |
String sql = "SELECT SDO_LAYER_GTYPE " |
|
264 |
+ "FROM ALL_SDO_INDEX_METADATA tmd, ALL_SDO_INDEX_INFO tinfo " |
|
265 |
+ "WHERE tmd.SDO_INDEX_OWNER = tinfo.SDO_INDEX_OWNER " |
|
266 |
+ "AND tmd.SDO_INDEX_NAME = tinfo.INDEX_NAME " |
|
267 |
+ "AND TABLE_OWNER = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?" ; |
|
268 |
|
|
269 |
ResultSet rs = null; |
|
270 |
PreparedStatement st = null; |
|
271 |
|
|
272 |
HashSet<Integer> sridList = new HashSet<Integer>(); |
|
273 |
HashSet<Integer> geometryCodeList = new HashSet<Integer>(); |
|
274 |
try { |
|
275 |
st = this.getConnection().prepareStatement(sql); |
|
276 |
st.setString(1, this.getSchema()); |
|
277 |
st.setString(2, this.getTablename()); |
|
278 |
st.setString(3, geomColumnName); |
|
279 |
|
|
280 |
rs = JDBCUtils.executeQuery(st, sql); |
|
281 |
String geomType = null; |
|
282 |
if (rs.next()) { |
|
283 |
geomType = rs.getString(1); |
|
284 |
return geomType; |
|
285 |
} |
|
286 |
} finally { |
|
287 |
JDBCUtils.closeQuietly(rs); |
|
288 |
JDBCUtils.closeQuietly(st); |
|
289 |
} |
|
290 |
} catch (Exception ex) { |
|
291 |
logger.debug("Can't get geometry type from column '"+geomColumnName+"'.",ex); |
|
292 |
} |
|
293 |
return "DEFAULT"; |
|
294 |
} |
|
295 |
|
|
296 |
/** |
|
297 |
* Fetchs the geom and srid type by directly querying the table. |
|
298 |
* Warning: This will perform a full scan on the table, so it is an |
|
299 |
* expensive method. |
|
300 |
* |
|
301 |
* @param attr |
|
302 |
* @param rsMetadata |
|
303 |
* @param colIndex |
|
304 |
*/ |
|
305 |
protected void doFetchFromTableFullScan( |
|
306 |
EditableFeatureAttributeDescriptor attr, |
|
307 |
ResultSetMetaData rsMetadata, |
|
308 |
int colIndex |
|
309 |
) { |
|
310 |
try { |
|
114 | 311 |
JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder(); |
115 |
; |
|
116 | 312 |
sqlbuilder.select().distinct(); |
117 | 313 |
|
118 | 314 |
sqlbuilder.select().column().value(sqlbuilder.function("SDO_GTYPE", "({0}).SDO_GTYPE", sqlbuilder.variable(attr.getName()))); |
... | ... | |
123 | 319 |
Statement st = null; |
124 | 320 |
ResultSet rs = null; |
125 | 321 |
|
126 |
ArrayList<Integer> sridList = new ArrayList<Integer>();
|
|
127 |
ArrayList<Integer> geometryCodeList = new ArrayList<Integer>();
|
|
322 |
HashSet<Integer> sridList = new HashSet<Integer>();
|
|
323 |
HashSet<Integer> geometryCodeList = new HashSet<Integer>();
|
|
128 | 324 |
try { |
129 | 325 |
st = this.getConnection().createStatement(); |
130 | 326 |
rs = JDBCUtils.executeQuery(st, sqlbuilder.toString()); |
... | ... | |
138 | 334 |
} |
139 | 335 |
try { |
140 | 336 |
if( sridList.size()==1 ) { |
141 |
attr.setSRS(this.helper.getProjectionFromDatabaseCode(sridList.get(0).toString())); |
|
337 |
Integer srsCode = sridList.iterator().next(); |
|
338 |
attr.setSRS(this.helper.getProjectionFromDatabaseCode(srsCode.toString())); |
|
142 | 339 |
} |
143 | 340 |
else if (sridList.size()>1) { |
144 |
logger.error("More than one CRS detected on the layer: "+sridList.toString()+". Some geometries will be incorrectly handled."); |
|
341 |
logger.error("More than one CRS detected on the layer: "+sridList.toArray().toString()+". Some geometries will be incorrectly handled.");
|
|
145 | 342 |
} |
146 | 343 |
} catch (Exception ex) { |
147 | 344 |
logger.debug("Can't get srs from column '"+attr.getName()+"'.",ex); |
148 | 345 |
} |
149 | 346 |
if( geometryCodeList.size() == 1 ) { |
150 |
GeometryType gt = getGeometryTypeFromDatabaseTypeNumber(geometryCodeList.get(0)); |
|
347 |
Integer geomCode = geometryCodeList.iterator().next(); |
|
348 |
GeometryType gt = getGeometryTypeFromDatabaseTypeNumber(geomCode); |
|
151 | 349 |
attr.setGeometryType(gt); |
152 | 350 |
} |
153 | 351 |
return; |
... | ... | |
156 | 354 |
} |
157 | 355 |
attr.setGeometryType(getGeometryTypeFromDatabaseTypeName("GEOMETRY")); |
158 | 356 |
} |
159 |
|
|
357 |
|
|
160 | 358 |
private GeometryType getGeometryTypeFromDatabaseTypeName(String typeName) { |
161 | 359 |
if( databaseGeometryTypes==null ) { |
162 | 360 |
GeometryManager manager = GeometryLocator.getGeometryManager(); |
Also available in: Unified diff