Revision 45008 trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.db/org.gvsig.fmap.dal.db.jdbc/src/main/java/org/gvsig/fmap/dal/store/jdbc2/spi/operations/CalculateEnvelopeOfColumnOperation.java
CalculateEnvelopeOfColumnOperation.java | ||
---|---|---|
4 | 4 |
import java.sql.ResultSet; |
5 | 5 |
import java.sql.SQLException; |
6 | 6 |
import java.sql.Statement; |
7 |
import java.util.List; |
|
8 | 7 |
import org.apache.commons.lang3.StringUtils; |
9 | 8 |
import org.cresques.cts.IProjection; |
10 | 9 |
import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
... | ... | |
18 | 17 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
19 | 18 |
import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
20 | 19 |
import org.gvsig.fmap.geom.Geometry; |
20 |
import org.gvsig.fmap.geom.GeometryUtils; |
|
21 | 21 |
import org.gvsig.fmap.geom.primitive.Envelope; |
22 | 22 |
|
23 | 23 |
public class CalculateEnvelopeOfColumnOperation extends AbstractConnectionOperation { |
... | ... | |
54 | 54 |
} |
55 | 55 |
|
56 | 56 |
public String getSQL() { |
57 |
if (this.helper.hasSpatialFunctions()) { |
|
58 |
return getSQLWithAggregate(); |
|
59 |
} else { |
|
60 |
return getSQLWihoutAggregate(); |
|
61 |
} |
|
62 |
} |
|
63 |
|
|
64 |
public String getSQLWihoutAggregate() { |
|
65 |
//Crear un select que devuelve todas las lineas |
|
66 |
JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder(); |
|
67 |
GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
68 |
|
|
69 |
sqlbuilder.select().column().name(columnName).as_geometry(); |
|
70 |
sqlbuilder.select().from().table() |
|
71 |
.database(this.table.getDatabase()) |
|
72 |
.schema(this.table.getSchema()) |
|
73 |
.name(this.table.getTable()); |
|
74 |
|
|
75 |
sqlbuilder.select().from().subquery(this.table.getSubquery()); |
|
76 |
if (!StringUtils.isEmpty(baseFilter)) { |
|
77 |
sqlbuilder.select().where().set(expbuilder.custom(baseFilter)); |
|
78 |
} |
|
79 |
// todo ? |
|
80 |
sqlbuilder.select().where().and(expbuilder.not_is_null(expbuilder.column(columnName))); |
|
81 |
String sql = sqlbuilder.select().toString(); |
|
82 |
return sql; |
|
83 |
} |
|
84 |
|
|
85 |
public String getSQLWithAggregate() { |
|
57 | 86 |
// |
58 | 87 |
// Parece ser que en versiones anteriores a SQL Server 2012 no esta |
59 | 88 |
// disponible la funcion ST_ExtentAggregate. |
... | ... | |
64 | 93 |
// |
65 | 94 |
JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder(); |
66 | 95 |
GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
67 |
|
|
96 |
|
|
68 | 97 |
sqlbuilder.select().column().value( |
69 |
// expbuilder.ifnull( |
|
70 |
// expbuilder.column(columnName), |
|
71 |
// expbuilder.constant(null), |
|
98 |
// expbuilder.ifnull(
|
|
99 |
// expbuilder.column(columnName),
|
|
100 |
// expbuilder.constant(null),
|
|
72 | 101 |
expbuilder.as_geometry( |
73 |
expbuilder.ST_ExtentAggregate( |
|
74 |
expbuilder.column(columnName) |
|
75 |
) |
|
102 |
expbuilder.ST_ExtentAggregate(
|
|
103 |
expbuilder.column(columnName)
|
|
104 |
)
|
|
76 | 105 |
) |
77 |
// ) |
|
106 |
// )
|
|
78 | 107 |
); |
79 | 108 |
// sqlbuilder.select().group_by(expbuilder.column(columnName)); |
80 | 109 |
sqlbuilder.select().from().table() |
... | ... | |
97 | 126 |
); |
98 | 127 |
} |
99 | 128 |
} else { |
100 |
sqlbuilder.select().where().set( expbuilder.custom(baseFilter) );
|
|
129 |
sqlbuilder.select().where().set(expbuilder.custom(baseFilter));
|
|
101 | 130 |
if (limit != null) { |
102 | 131 |
sqlbuilder.select().where().and( |
103 | 132 |
expbuilder.ST_Intersects( |
104 |
expbuilder.ST_Envelope( |
|
105 |
expbuilder.column(columnName) |
|
106 |
), |
|
107 |
expbuilder.ST_Envelope( |
|
108 |
expbuilder.geometry(limit.getGeometry(), crs) |
|
109 |
) |
|
133 |
expbuilder.ST_Envelope(
|
|
134 |
expbuilder.column(columnName)
|
|
135 |
),
|
|
136 |
expbuilder.ST_Envelope(
|
|
137 |
expbuilder.geometry(limit.getGeometry(), crs)
|
|
138 |
)
|
|
110 | 139 |
) |
111 | 140 |
); |
112 | 141 |
} |
113 | 142 |
} |
114 |
sqlbuilder.select().where().and(
|
|
115 |
expbuilder.not_is_null(expbuilder.column(columnName)) |
|
143 |
sqlbuilder.select().where().and( |
|
144 |
expbuilder.not_is_null(expbuilder.column(columnName))
|
|
116 | 145 |
); |
117 | 146 |
this.helper.processSpecialFunctions(sqlbuilder, featureType, null); |
118 | 147 |
sqlbuilder.setProperties( |
119 |
Variable.class,
|
|
148 |
Variable.class, |
|
120 | 149 |
PROP_TABLE, table |
121 | 150 |
); |
122 | 151 |
String sql = sqlbuilder.select().toString(); |
123 | 152 |
return sql; |
124 | 153 |
} |
125 |
|
|
154 |
|
|
126 | 155 |
public Envelope calculateEnvelopeOfColumn(Connection conn) throws DataException { |
156 |
if (this.helper.hasSpatialFunctions()) { |
|
157 |
return calculateEnvelopeOfColumnWithAggregate(conn); |
|
158 |
} else { |
|
159 |
return calculateEnvelopeOfColumnWithoutAggregate(conn); |
|
160 |
} |
|
161 |
} |
|
162 |
|
|
163 |
private Envelope calculateEnvelopeOfColumnWithAggregate(Connection conn) throws DataException { |
|
127 | 164 |
String sql = this.getSQL(); |
128 |
|
|
165 |
|
|
129 | 166 |
Statement st = null; |
130 | 167 |
ResultSet rs = null; |
131 | 168 |
try { |
132 | 169 |
st = conn.createStatement(); |
133 | 170 |
try { |
134 |
LOGGER.debug("execute query SQL:"+sql);
|
|
171 |
LOGGER.debug("execute query SQL:" + sql);
|
|
135 | 172 |
rs = st.executeQuery(sql); |
136 | 173 |
// No llamo al de JDBC utils para que no meta el error |
137 | 174 |
// en el log. |
138 | 175 |
// rs = JDBCUtils.executeQuery(st, sql); |
139 |
} catch(SQLException ex) { |
|
176 |
} catch (SQLException ex) {
|
|
140 | 177 |
// Para calcular el envelope se esta lanzando una colsulta como: |
141 | 178 |
// SELECT ST_AsBinary(ST_Extent("geometry")) |
142 | 179 |
// FROM "gis_osm_roads_free_1" |
... | ... | |
165 | 202 |
} |
166 | 203 |
} |
167 | 204 |
|
205 |
private Envelope calculateEnvelopeOfColumnWithoutAggregate(Connection conn) throws DataException { |
|
206 |
// crear una sql que devuelva un resultarSet |
|
207 |
//bucle |
|
208 |
// iif instacia de string: cast string |
|
209 |
// GeometryLocator.getGeometryManager().createFrom("") |
|
210 |
// elif instance de bytearray con cast a bytearray: |
|
211 |
// GeometryLocator.getGeometryManager().createFrom("") |
|
212 |
// else: |
|
213 |
// coerceToGeometry |
|
214 |
// |
|
215 |
// |
|
216 |
// Notas: atrapar error, y si se produce error hago un coerceToGeometry |
|
217 |
String sql = this.getSQL(); |
|
218 |
|
|
219 |
Statement st = null; |
|
220 |
ResultSet rs = null; |
|
221 |
try { |
|
222 |
st = conn.createStatement(); |
|
223 |
try { |
|
224 |
LOGGER.debug("execute query SQL:" + sql); |
|
225 |
rs = st.executeQuery(sql); |
|
226 |
// No llamo al de JDBC utils para que no meta el error |
|
227 |
// en el log. |
|
228 |
// rs = JDBCUtils.executeQuery(st, sql); |
|
229 |
} catch (SQLException ex) { |
|
230 |
// Para calcular el envelope se esta lanzando una colsulta como: |
|
231 |
// SELECT ST_AsBinary(ST_Extent("geometry")) |
|
232 |
// FROM "gis_osm_roads_free_1" |
|
233 |
// WHERE ( ("geometry") IS NOT NULL ) |
|
234 |
// Que falla cuando no hay registros al recibir un NULL en el |
|
235 |
// ST_AsBinary. |
|
236 |
// A falta de una forma mejor de detectar eso, si peta en este |
|
237 |
// punto asumiremos que no hay registros en la consulta y devolveremos |
|
238 |
// null como envelope. |
|
239 |
return null; |
|
240 |
} |
|
241 |
Envelope finalEnvelope = GeometryUtils.createEnvelope(Geometry.SUBTYPES.GEOM2D); |
|
242 |
while (rs.next()) { |
|
243 |
String geometryString = rs.getString("geometry"); |
|
244 |
Geometry geometry = GeometryUtils.createFrom(geometryString); |
|
245 |
finalEnvelope.add(geometry); |
|
246 |
} |
|
247 |
// Geometry geom = this.helper.getGeometryFromColumn(rs, 1); |
|
248 |
// if (geom == null) { |
|
249 |
// return null; |
|
250 |
// } |
|
251 |
// return geom.getEnvelope(); |
|
252 |
return finalEnvelope; |
|
253 |
} catch (SQLException ex) { |
|
254 |
throw new JDBCSQLException(ex); |
|
255 |
} finally { |
|
256 |
JDBCUtils.closeQuietly(st); |
|
257 |
JDBCUtils.closeQuietly(rs); |
|
258 |
} |
|
259 |
|
|
260 |
} |
|
261 |
|
|
168 | 262 |
} |
Also available in: Unified diff