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 @ 46517
History | View | Annotate | Download (11.2 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.ResultSet; |
||
27 | import java.sql.SQLException; |
||
28 | import java.sql.Statement; |
||
29 | 46507 | jjdelcerro | import static org.apache.commons.collections.CollectionUtils.select; |
30 | 43020 | jjdelcerro | import org.apache.commons.lang3.StringUtils; |
31 | import org.cresques.cts.IProjection; |
||
32 | 44198 | jjdelcerro | import org.gvsig.expressionevaluator.ExpressionBuilder.Variable; |
33 | 44644 | jjdelcerro | import org.gvsig.expressionevaluator.GeometryExpressionBuilder; |
34 | 46507 | jjdelcerro | import org.gvsig.fmap.dal.SQLBuilder; |
35 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.exception.DataException; |
36 | 44376 | jjdelcerro | import org.gvsig.fmap.dal.feature.FeatureType; |
37 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
38 | 46315 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCConnection; |
39 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
40 | import org.gvsig.fmap.dal.store.jdbc2.JDBCUtils; |
||
41 | 44058 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
42 | 43020 | jjdelcerro | import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
43 | 44198 | jjdelcerro | import static org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase.PROP_TABLE; |
44 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.Geometry; |
45 | 45008 | omartinez | import org.gvsig.fmap.geom.GeometryUtils; |
46 | 43020 | jjdelcerro | import org.gvsig.fmap.geom.primitive.Envelope; |
47 | |||
48 | public class CalculateEnvelopeOfColumnOperation extends AbstractConnectionOperation { |
||
49 | |||
50 | 45131 | fdiaz | protected final TableReference table; |
51 | protected final String columnName; |
||
52 | protected final String baseFilter; |
||
53 | protected final Envelope limit; |
||
54 | protected final IProjection crs; |
||
55 | protected final FeatureType featureType; |
||
56 | 43020 | jjdelcerro | |
57 | public CalculateEnvelopeOfColumnOperation(
|
||
58 | JDBCHelper helper, |
||
59 | 44376 | jjdelcerro | FeatureType featureType, |
60 | 44058 | jjdelcerro | TableReference table, |
61 | 43020 | jjdelcerro | String columnName,
|
62 | String baseFilter,
|
||
63 | Envelope limit, |
||
64 | IProjection crs |
||
65 | ) { |
||
66 | super(helper);
|
||
67 | 44376 | jjdelcerro | this.featureType = featureType;
|
68 | 44058 | jjdelcerro | this.table = table;
|
69 | 43020 | jjdelcerro | this.columnName = columnName;
|
70 | this.baseFilter = baseFilter;
|
||
71 | this.limit = limit;
|
||
72 | this.crs = crs;
|
||
73 | } |
||
74 | |||
75 | @Override
|
||
76 | 46315 | jjdelcerro | public final Object perform(JDBCConnection conn) throws DataException { |
77 | 44678 | jjdelcerro | Envelope env = calculateEnvelopeOfColumn(conn); |
78 | 43020 | jjdelcerro | return env;
|
79 | } |
||
80 | |||
81 | 44678 | jjdelcerro | public String getSQL() { |
82 | 45008 | omartinez | if (this.helper.hasSpatialFunctions()) { |
83 | return getSQLWithAggregate();
|
||
84 | } else {
|
||
85 | return getSQLWihoutAggregate();
|
||
86 | } |
||
87 | } |
||
88 | |||
89 | public String getSQLWihoutAggregate() { |
||
90 | //Crear un select que devuelve todas las lineas
|
||
91 | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
||
92 | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
||
93 | |||
94 | sqlbuilder.select().from().table() |
||
95 | .database(this.table.getDatabase())
|
||
96 | .schema(this.table.getSchema())
|
||
97 | .name(this.table.getTable());
|
||
98 | |||
99 | 46507 | jjdelcerro | sqlbuilder.select().column().name(columnName).as_geometry(); |
100 | 45008 | omartinez | sqlbuilder.select().from().subquery(this.table.getSubquery());
|
101 | if (!StringUtils.isEmpty(baseFilter)) {
|
||
102 | sqlbuilder.select().where().set(expbuilder.custom(baseFilter)); |
||
103 | } |
||
104 | // todo ?
|
||
105 | sqlbuilder.select().where().and(expbuilder.not_is_null(expbuilder.column(columnName))); |
||
106 | String sql = sqlbuilder.select().toString();
|
||
107 | return sql;
|
||
108 | } |
||
109 | |||
110 | public String getSQLWithAggregate() { |
||
111 | 43088 | jjdelcerro | //
|
112 | // Parece ser que en versiones anteriores a SQL Server 2012 no esta
|
||
113 | // disponible la funcion ST_ExtentAggregate.
|
||
114 | // Habria que determinar si es necesario implementar una alternativa
|
||
115 | // para estos casos.
|
||
116 | //
|
||
117 | // https://alastaira.wordpress.com/2011/07/26/determining-the-geographic-extent-of-spatial-features-in-a-sql-server-table/
|
||
118 | //
|
||
119 | 43020 | jjdelcerro | JDBCSQLBuilderBase sqlbuilder = this.createSQLBuilder();
|
120 | 44644 | jjdelcerro | GeometryExpressionBuilder expbuilder = sqlbuilder.expression(); |
121 | 45008 | omartinez | |
122 | 46507 | jjdelcerro | SQLBuilder.SelectBuilder select = sqlbuilder.select(); |
123 | select.from().table() |
||
124 | .database(this.table.getDatabase())
|
||
125 | .schema(this.table.getSchema())
|
||
126 | .name(this.table.getTable());
|
||
127 | |||
128 | select.column().value( |
||
129 | 44345 | jjdelcerro | expbuilder.as_geometry( |
130 | 45008 | omartinez | expbuilder.ST_ExtentAggregate( |
131 | 46507 | jjdelcerro | sqlbuilder.column(select.from().table(),columnName) |
132 | 45008 | omartinez | ) |
133 | 43020 | jjdelcerro | ) |
134 | ); |
||
135 | 46507 | jjdelcerro | select.from().subquery(this.table.getSubquery());
|
136 | 43020 | jjdelcerro | |
137 | if (StringUtils.isEmpty(baseFilter)) {
|
||
138 | if (limit != null) { |
||
139 | 46507 | jjdelcerro | select.where().set( |
140 | 44198 | jjdelcerro | expbuilder.ST_Intersects( |
141 | expbuilder.ST_Envelope( |
||
142 | 46507 | jjdelcerro | sqlbuilder.column(select.from().table(),columnName) |
143 | 43020 | jjdelcerro | ), |
144 | 44198 | jjdelcerro | expbuilder.ST_Envelope( |
145 | expbuilder.geometry(limit.getGeometry(), crs) |
||
146 | 43020 | jjdelcerro | ) |
147 | ) |
||
148 | ); |
||
149 | } |
||
150 | } else {
|
||
151 | 46507 | jjdelcerro | select.where().set(expbuilder.custom(baseFilter)); |
152 | 43020 | jjdelcerro | if (limit != null) { |
153 | 46507 | jjdelcerro | select.where().and( |
154 | 44198 | jjdelcerro | expbuilder.ST_Intersects( |
155 | 45008 | omartinez | expbuilder.ST_Envelope( |
156 | 46507 | jjdelcerro | sqlbuilder.column(select.from().table(),columnName) |
157 | 45008 | omartinez | ), |
158 | expbuilder.ST_Envelope( |
||
159 | expbuilder.geometry(limit.getGeometry(), crs) |
||
160 | ) |
||
161 | 43020 | jjdelcerro | ) |
162 | ); |
||
163 | } |
||
164 | } |
||
165 | 46507 | jjdelcerro | select.where().and( |
166 | expbuilder.not_is_null(sqlbuilder.column(select.from().table(),columnName)) |
||
167 | 44361 | jjdelcerro | ); |
168 | 44748 | jjdelcerro | this.helper.processSpecialFunctions(sqlbuilder, featureType, null); |
169 | 44198 | jjdelcerro | sqlbuilder.setProperties( |
170 | 45008 | omartinez | Variable.class, |
171 | 44198 | jjdelcerro | PROP_TABLE, table |
172 | ); |
||
173 | 43020 | jjdelcerro | String sql = sqlbuilder.select().toString();
|
174 | 44678 | jjdelcerro | return sql;
|
175 | } |
||
176 | 45008 | omartinez | |
177 | 46315 | jjdelcerro | public Envelope calculateEnvelopeOfColumn(JDBCConnection conn) throws DataException { |
178 | 45008 | omartinez | if (this.helper.hasSpatialFunctions()) { |
179 | return calculateEnvelopeOfColumnWithAggregate(conn);
|
||
180 | } else {
|
||
181 | return calculateEnvelopeOfColumnWithoutAggregate(conn);
|
||
182 | } |
||
183 | } |
||
184 | |||
185 | 46315 | jjdelcerro | protected Envelope calculateEnvelopeOfColumnWithAggregate(JDBCConnection conn) throws DataException { |
186 | 44678 | jjdelcerro | String sql = this.getSQL(); |
187 | 45008 | omartinez | |
188 | 43020 | jjdelcerro | Statement st = null; |
189 | ResultSet rs = null; |
||
190 | try {
|
||
191 | st = conn.createStatement(); |
||
192 | 44533 | jjdelcerro | try {
|
193 | 45008 | omartinez | LOGGER.debug("execute query SQL:" + sql);
|
194 | 44533 | jjdelcerro | rs = st.executeQuery(sql); |
195 | // No llamo al de JDBC utils para que no meta el error
|
||
196 | // en el log.
|
||
197 | // rs = JDBCUtils.executeQuery(st, sql);
|
||
198 | 45008 | omartinez | } catch (SQLException ex) { |
199 | 45152 | fdiaz | // Para calcular el envelope se esta lanzando una consulta como:
|
200 | 44533 | jjdelcerro | // SELECT ST_AsBinary(ST_Extent("geometry"))
|
201 | // FROM "gis_osm_roads_free_1"
|
||
202 | // WHERE ( ("geometry") IS NOT NULL )
|
||
203 | // Que falla cuando no hay registros al recibir un NULL en el
|
||
204 | // ST_AsBinary.
|
||
205 | // A falta de una forma mejor de detectar eso, si peta en este
|
||
206 | // punto asumiremos que no hay registros en la consulta y devolveremos
|
||
207 | // null como envelope.
|
||
208 | 45152 | fdiaz | // A?ado un warn al LOGGER para tener constancia de lo que est? pasando.
|
209 | LOGGER.warn("Fail executing sql: "+sql, ex);
|
||
210 | 44533 | jjdelcerro | return null; |
211 | } |
||
212 | 43020 | jjdelcerro | if (!rs.next()) {
|
213 | return null; |
||
214 | } |
||
215 | Geometry geom = this.helper.getGeometryFromColumn(rs, 1); |
||
216 | if (geom == null) { |
||
217 | return null; |
||
218 | } |
||
219 | return geom.getEnvelope();
|
||
220 | |||
221 | } catch (SQLException ex) { |
||
222 | 46148 | jjdelcerro | throw new JDBCSQLException(ex,sql); |
223 | 43020 | jjdelcerro | } finally {
|
224 | JDBCUtils.closeQuietly(st); |
||
225 | JDBCUtils.closeQuietly(rs); |
||
226 | } |
||
227 | } |
||
228 | |||
229 | 46315 | jjdelcerro | protected Envelope calculateEnvelopeOfColumnWithoutAggregate(JDBCConnection conn) throws DataException { |
230 | 45008 | omartinez | // crear una sql que devuelva un resultarSet
|
231 | //bucle
|
||
232 | // iif instacia de string: cast string
|
||
233 | // GeometryLocator.getGeometryManager().createFrom("")
|
||
234 | // elif instance de bytearray con cast a bytearray:
|
||
235 | // GeometryLocator.getGeometryManager().createFrom("")
|
||
236 | // else:
|
||
237 | // coerceToGeometry
|
||
238 | //
|
||
239 | //
|
||
240 | // Notas: atrapar error, y si se produce error hago un coerceToGeometry
|
||
241 | String sql = this.getSQL(); |
||
242 | |||
243 | Statement st = null; |
||
244 | ResultSet rs = null; |
||
245 | try {
|
||
246 | st = conn.createStatement(); |
||
247 | try {
|
||
248 | LOGGER.debug("execute query SQL:" + sql);
|
||
249 | rs = st.executeQuery(sql); |
||
250 | // No llamo al de JDBC utils para que no meta el error
|
||
251 | // en el log.
|
||
252 | // rs = JDBCUtils.executeQuery(st, sql);
|
||
253 | } catch (SQLException ex) { |
||
254 | // Para calcular el envelope se esta lanzando una colsulta como:
|
||
255 | // SELECT ST_AsBinary(ST_Extent("geometry"))
|
||
256 | // FROM "gis_osm_roads_free_1"
|
||
257 | // WHERE ( ("geometry") IS NOT NULL )
|
||
258 | // Que falla cuando no hay registros al recibir un NULL en el
|
||
259 | // ST_AsBinary.
|
||
260 | // A falta de una forma mejor de detectar eso, si peta en este
|
||
261 | // punto asumiremos que no hay registros en la consulta y devolveremos
|
||
262 | // null como envelope.
|
||
263 | return null; |
||
264 | } |
||
265 | Envelope finalEnvelope = GeometryUtils.createEnvelope(Geometry.SUBTYPES.GEOM2D); |
||
266 | while (rs.next()) {
|
||
267 | String geometryString = rs.getString("geometry"); |
||
268 | Geometry geometry = GeometryUtils.createFrom(geometryString); |
||
269 | finalEnvelope.add(geometry); |
||
270 | } |
||
271 | // Geometry geom = this.helper.getGeometryFromColumn(rs, 1);
|
||
272 | // if (geom == null) {
|
||
273 | // return null;
|
||
274 | // }
|
||
275 | // return geom.getEnvelope();
|
||
276 | return finalEnvelope;
|
||
277 | } catch (SQLException ex) { |
||
278 | 46148 | jjdelcerro | throw new JDBCSQLException(ex,sql); |
279 | 45008 | omartinez | } finally {
|
280 | JDBCUtils.closeQuietly(st); |
||
281 | JDBCUtils.closeQuietly(rs); |
||
282 | } |
||
283 | |||
284 | } |
||
285 | |||
286 | 43020 | jjdelcerro | } |