svn-gvsig-desktop / 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 @ 45534
History | View | Annotate | Download (11.5 KB)
1 | 45065 | jjdelcerro | /**
|
---|---|---|---|
2 | * gvSIG. Desktop Geographic Information System.
|
||
3 | *
|
||
4 | * Copyright (C) 2007-2020 gvSIG Association.
|
||
5 | *
|
||
6 | * This program is free software; you can redistribute it and/or
|
||
7 | * modify it under the terms of the GNU General Public License
|
||
8 | * as published by the Free Software Foundation; either version 3
|
||
9 | * of the License, or (at your option) any later version.
|
||
10 | *
|
||
11 | * This program is distributed in the hope that it will be useful,
|
||
12 | * but WITHOUT ANY WARRANTY; without even the implied warranty of
|
||
13 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
||
14 | * GNU General Public License for more details.
|
||
15 | *
|
||
16 | * You should have received a copy of the GNU General Public License
|
||
17 | * along with this program; if not, write to the Free Software
|
||
18 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
|
||
19 | * MA 02110-1301, USA.
|
||
20 | *
|
||
21 | * For any additional information, do not hesitate to contact us
|
||
22 | * at info AT gvsig.com, or visit our website www.gvsig.com.
|
||
23 | */
|
||
24 | 43020 | jjdelcerro | package org.gvsig.fmap.dal.store.jdbc2.spi.operations; |
25 | |||
26 | import java.sql.Connection; |
||
27 | import java.sql.ResultSet; |
||
28 | import java.sql.SQLException; |
||
29 | import java.sql.Statement; |
||
30 | 45131 | fdiaz | import java.util.logging.Level; |
31 | import java.util.logging.Logger; |
||
32 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
33 | import org.cresques.cts.IProjection; |
||
34 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
35 | 44644 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
36 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
37 | 45131 | fdiaz | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
38 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
39 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
40 | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
||
41 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
42 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
43 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
44 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
45 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.Geometry; |
46 | 45131 | fdiaz | import org.gvsig.fmap.geom.GeometryLocator; |
47 | 45008 | omartinez | import org.gvsig.fmap.geom.GeometryUtils; |
48 | 45131 | fdiaz | import org.gvsig.fmap.geom.exception.CreateEnvelopeException; |
49 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.primitive.Envelope; |
50 | 45131 | fdiaz | import org.gvsig.fmap.geom.type.GeometryType; |
51 | 43020 | jjdelcerro | |
52 | public class CalculateEnvelopeOfColumnOperation extends AbstractConnectionOperation { |
||
53 | |||
54 | 45131 | fdiaz | protected final TableReference table; |
55 | protected final String columnName; |
||
56 | protected final String baseFilter; |
||
57 | protected final Envelope limit; |
||
58 | protected final IProjection crs; |
||
59 | protected final FeatureType featureType; |
||
60 | 43020 | jjdelcerro | |
61 | public CalculateEnvelopeOfColumnOperation(
|
||
62 | JDBCHelper helper, |
||
63 | 44376 | jjdelcerro | FeatureType featureType, |
64 | 44058 | jjdelcerro | TableReference table, |
65 | 43020 | jjdelcerro | String columnName,
|
66 | String baseFilter,
|
||
67 | Envelope limit, |
||
68 | IProjection crs |
||
69 | ) { |
||
70 | super(helper);
|
||
71 | 44376 | jjdelcerro | this.featureType = featureType;
|
72 | 44058 | jjdelcerro | this.table = table;
|
73 | 43020 | jjdelcerro | this.columnName = columnName;
|
74 | this.baseFilter = baseFilter;
|
||
75 | this.limit = limit;
|
||
76 | this.crs = crs;
|
||
77 | } |
||
78 | |||
79 | @Override
|
||
80 | public final Object perform(Connection conn) throws DataException { |
||
81 | 44678 | jjdelcerro | Envelope env = calculateEnvelopeOfColumn(conn); |
82 | 43020 | jjdelcerro | return env;
|
83 | } |
||
84 | |||
85 | 44678 | jjdelcerro | public String getSQL() { |
86 | 45008 | omartinez | if (this.helper.hasSpatialFunctions()) { |
87 | return getSQLWithAggregate();
|
||
88 | } else {
|
||
89 | return getSQLWihoutAggregate();
|
||
90 | } |
||
91 | } |
||
92 | |||
93 | public String getSQLWihoutAggregate() { |
||
94 | //Crear un select que devuelve todas las lineas
|
||
95 | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
||
96 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
97 | |||
98 | sqlbuilder.select().column().name(columnName).as_geometry(); |
||
99 | sqlbuilder.select().from().table() |
||
100 | .database(this.table.getDatabase())
|
||
101 | .schema(this.table.getSchema())
|
||
102 | .name(this.table.getTable());
|
||
103 | |||
104 | sqlbuilder.select().from().subquery(this.table.getSubquery());
|
||
105 | if (!StringUtils.isEmpty(baseFilter)) {
|
||
106 | sqlbuilder.select().where().set(expbuilder.custom(baseFilter)); |
||
107 | } |
||
108 | // todo ?
|
||
109 | sqlbuilder.select().where().and(expbuilder.not_is_null(expbuilder.column(columnName))); |
||
110 | String sql = sqlbuilder.select().toString();
|
||
111 | return sql;
|
||
112 | } |
||
113 | |||
114 | public String getSQLWithAggregate() { |
||
115 | 43088 | jjdelcerro | //
|
116 | // Parece ser que en versiones anteriores a SQL Server 2012 no esta
|
||
117 | // disponible la funcion ST_ExtentAggregate.
|
||
118 | // Habria que determinar si es necesario implementar una alternativa
|
||
119 | // para estos casos.
|
||
120 | //
|
||
121 | // https://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/
|
||
122 | //
|
||
123 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
124 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
125 | 45008 | omartinez | |
126 | 43020 | jjdelcerro | sqlbuilder.select().column().value( |
127 | 45008 | omartinez | // expbuilder.ifnull(
|
128 | // expbuilder.column(columnName),
|
||
129 | // expbuilder.constant(null),
|
||
130 | 44345 | jjdelcerro | expbuilder.as_geometry( |
131 | 45008 | omartinez | expbuilder.ST_ExtentAggregate( |
132 | expbuilder.column(columnName) |
||
133 | ) |
||
134 | 43020 | jjdelcerro | ) |
135 | 45008 | omartinez | // )
|
136 | 43020 | jjdelcerro | ); |
137 | 44361 | jjdelcerro | // sqlbuilder.select().group_by(expbuilder.column(columnName));
|
138 | 44058 | jjdelcerro | sqlbuilder.select().from().table() |
139 | .database(this.table.getDatabase())
|
||
140 | .schema(this.table.getSchema())
|
||
141 | .name(this.table.getTable());
|
||
142 | sqlbuilder.select().from().subquery(this.table.getSubquery());
|
||
143 | 43020 | jjdelcerro | |
144 | if (StringUtils.isEmpty(baseFilter)) {
|
||
145 | if (limit != null) { |
||
146 | sqlbuilder.select().where().set( |
||
147 | 44198 | jjdelcerro | expbuilder.ST_Intersects( |
148 | expbuilder.ST_Envelope( |
||
149 | expbuilder.column(columnName) |
||
150 | 43020 | jjdelcerro | ), |
151 | 44198 | jjdelcerro | expbuilder.ST_Envelope( |
152 | expbuilder.geometry(limit.getGeometry(), crs) |
||
153 | 43020 | jjdelcerro | ) |
154 | ) |
||
155 | ); |
||
156 | } |
||
157 | } else {
|
||
158 | 45008 | omartinez | sqlbuilder.select().where().set(expbuilder.custom(baseFilter)); |
159 | 43020 | jjdelcerro | if (limit != null) { |
160 | sqlbuilder.select().where().and( |
||
161 | 44198 | jjdelcerro | expbuilder.ST_Intersects( |
162 | 45008 | omartinez | expbuilder.ST_Envelope( |
163 | expbuilder.column(columnName) |
||
164 | ), |
||
165 | expbuilder.ST_Envelope( |
||
166 | expbuilder.geometry(limit.getGeometry(), crs) |
||
167 | ) |
||
168 | 43020 | jjdelcerro | ) |
169 | ); |
||
170 | } |
||
171 | } |
||
172 | 45008 | omartinez | sqlbuilder.select().where().and( |
173 | expbuilder.not_is_null(expbuilder.column(columnName)) |
||
174 | 44361 | jjdelcerro | ); |
175 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, featureType, null); |
176 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
177 | 45008 | omartinez | Variable.class, |
178 | 44198 | jjdelcerro | PROP_TABLE, table |
179 | ); |
||
180 | 43020 | jjdelcerro | String sql = sqlbuilder.select().toString();
|
181 | 44678 | jjdelcerro | return sql;
|
182 | } |
||
183 | 45008 | omartinez | |
184 | 44678 | jjdelcerro | public Envelope calculateEnvelopeOfColumn(Connection conn) throws DataException { |
185 | 45008 | omartinez | if (this.helper.hasSpatialFunctions()) { |
186 | return calculateEnvelopeOfColumnWithAggregate(conn);
|
||
187 | } else {
|
||
188 | return calculateEnvelopeOfColumnWithoutAggregate(conn);
|
||
189 | } |
||
190 | } |
||
191 | |||
192 | 45131 | fdiaz | protected Envelope calculateEnvelopeOfColumnWithAggregate(Connection conn) throws DataException { |
193 | 44678 | jjdelcerro | String sql = this.getSQL(); |
194 | 45008 | omartinez | |
195 | 43020 | jjdelcerro | Statement st = null; |
196 | ResultSet rs = null; |
||
197 | try {
|
||
198 | st = conn.createStatement(); |
||
199 | 44533 | jjdelcerro | try {
|
200 | 45008 | omartinez | LOGGER.debug("execute query SQL:" + sql);
|
201 | 44533 | jjdelcerro | rs = st.executeQuery(sql); |
202 | // No llamo al de JDBC utils para que no meta el error
|
||
203 | // en el log.
|
||
204 | // rs = JDBCUtils.executeQuery(st, sql);
|
||
205 | 45008 | omartinez | } catch (SQLException ex) { |
206 | 45152 | fdiaz | // Para calcular el envelope se esta lanzando una consulta como:
|
207 | 44533 | jjdelcerro | // SELECT ST_AsBinary(ST_Extent("geometry"))
|
208 | // FROM "gis_osm_roads_free_1"
|
||
209 | // WHERE ( ("geometry") IS NOT NULL )
|
||
210 | // Que falla cuando no hay registros al recibir un NULL en el
|
||
211 | // ST_AsBinary.
|
||
212 | // A falta de una forma mejor de detectar eso, si peta en este
|
||
213 | // punto asumiremos que no hay registros en la consulta y devolveremos
|
||
214 | // null como envelope.
|
||
215 | 45152 | fdiaz | // A?ado un warn al LOGGER para tener constancia de lo que est? pasando.
|
216 | LOGGER.warn("Fail executing sql: "+sql, ex);
|
||
217 | 44533 | jjdelcerro | return null; |
218 | } |
||
219 | 43020 | jjdelcerro | if (!rs.next()) {
|
220 | return null; |
||
221 | } |
||
222 | Geometry geom = this.helper.getGeometryFromColumn(rs, 1); |
||
223 | if (geom == null) { |
||
224 | return null; |
||
225 | } |
||
226 | return geom.getEnvelope();
|
||
227 | |||
228 | } catch (SQLException ex) { |
||
229 | throw new JDBCSQLException(ex); |
||
230 | } finally {
|
||
231 | JDBCUtils.closeQuietly(st); |
||
232 | JDBCUtils.closeQuietly(rs); |
||
233 | } |
||
234 | } |
||
235 | |||
236 | 45131 | fdiaz | protected Envelope calculateEnvelopeOfColumnWithoutAggregate(Connection conn) throws DataException { |
237 | 45008 | omartinez | // crear una sql que devuelva un resultarSet
|
238 | //bucle
|
||
239 | // iif instacia de string: cast string
|
||
240 | // GeometryLocator.getGeometryManager().createFrom("")
|
||
241 | // elif instance de bytearray con cast a bytearray:
|
||
242 | // GeometryLocator.getGeometryManager().createFrom("")
|
||
243 | // else:
|
||
244 | // coerceToGeometry
|
||
245 | //
|
||
246 | //
|
||
247 | // Notas: atrapar error, y si se produce error hago un coerceToGeometry
|
||
248 | String sql = this.getSQL(); |
||
249 | |||
250 | Statement st = null; |
||
251 | ResultSet rs = null; |
||
252 | try {
|
||
253 | st = conn.createStatement(); |
||
254 | try {
|
||
255 | LOGGER.debug("execute query SQL:" + sql);
|
||
256 | rs = st.executeQuery(sql); |
||
257 | // No llamo al de JDBC utils para que no meta el error
|
||
258 | // en el log.
|
||
259 | // rs = JDBCUtils.executeQuery(st, sql);
|
||
260 | } catch (SQLException ex) { |
||
261 | // Para calcular el envelope se esta lanzando una colsulta como:
|
||
262 | // SELECT ST_AsBinary(ST_Extent("geometry"))
|
||
263 | // FROM "gis_osm_roads_free_1"
|
||
264 | // WHERE ( ("geometry") IS NOT NULL )
|
||
265 | // Que falla cuando no hay registros al recibir un NULL en el
|
||
266 | // ST_AsBinary.
|
||
267 | // A falta de una forma mejor de detectar eso, si peta en este
|
||
268 | // punto asumiremos que no hay registros en la consulta y devolveremos
|
||
269 | // null como envelope.
|
||
270 | return null; |
||
271 | } |
||
272 | Envelope finalEnvelope = GeometryUtils.createEnvelope(Geometry.SUBTYPES.GEOM2D); |
||
273 | while (rs.next()) {
|
||
274 | String geometryString = rs.getString("geometry"); |
||
275 | Geometry geometry = GeometryUtils.createFrom(geometryString); |
||
276 | finalEnvelope.add(geometry); |
||
277 | } |
||
278 | // Geometry geom = this.helper.getGeometryFromColumn(rs, 1);
|
||
279 | // if (geom == null) {
|
||
280 | // return null;
|
||
281 | // }
|
||
282 | // return geom.getEnvelope();
|
||
283 | return finalEnvelope;
|
||
284 | } catch (SQLException ex) { |
||
285 | throw new JDBCSQLException(ex); |
||
286 | } finally {
|
||
287 | JDBCUtils.closeQuietly(st); |
||
288 | JDBCUtils.closeQuietly(rs); |
||
289 | } |
||
290 | |||
291 | } |
||
292 | |||
293 | 43020 | jjdelcerro | } |