root / trunk / org.gvsig.postgresql / org.gvsig.postgresql.provider / src / main / java / org / gvsig / fmap / dal / store / postgresql / PostgreSQLHelper.java @ 9
History | View | Annotate | Download (22.4 KB)
1 | 5 | jldominguez | /* gvSIG. Geographic Information System of the Valencian Government
|
---|---|---|---|
2 | *
|
||
3 | * Copyright (C) 2007-2008 Infrastructures and Transports Department
|
||
4 | * of the Valencian Government (CIT)
|
||
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 2
|
||
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 | */
|
||
22 | |||
23 | /*
|
||
24 | * AUTHORS (In addition to CIT):
|
||
25 | * 2009 IVER T.I {{Task}}
|
||
26 | */
|
||
27 | |||
28 | /**
|
||
29 | *
|
||
30 | */
|
||
31 | package org.gvsig.fmap.dal.store.postgresql; |
||
32 | |||
33 | import java.sql.Connection; |
||
34 | import java.sql.PreparedStatement; |
||
35 | import java.sql.ResultSet; |
||
36 | import java.sql.ResultSetMetaData; |
||
37 | import java.sql.SQLException; |
||
38 | import java.sql.Statement; |
||
39 | import java.util.ArrayList; |
||
40 | import java.util.Comparator; |
||
41 | import java.util.Iterator; |
||
42 | import java.util.List; |
||
43 | import java.util.Map; |
||
44 | import java.util.TreeMap; |
||
45 | import java.util.TreeSet; |
||
46 | |||
47 | import org.cresques.cts.IProjection; |
||
48 | import org.postgresql.PGResultSetMetaData; |
||
49 | import org.slf4j.Logger; |
||
50 | import org.slf4j.LoggerFactory; |
||
51 | |||
52 | import org.gvsig.fmap.crs.CRSFactory; |
||
53 | import org.gvsig.fmap.dal.DALLocator; |
||
54 | import org.gvsig.fmap.dal.DataTypes; |
||
55 | import org.gvsig.fmap.dal.NewDataStoreParameters; |
||
56 | import org.gvsig.fmap.dal.exception.DataException; |
||
57 | import org.gvsig.fmap.dal.exception.InitializeException; |
||
58 | import org.gvsig.fmap.dal.exception.ReadException; |
||
59 | import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
||
60 | import org.gvsig.fmap.dal.feature.EditableFeatureType; |
||
61 | import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
||
62 | import org.gvsig.fmap.dal.feature.FeatureType; |
||
63 | import org.gvsig.fmap.dal.feature.exception.UnsupportedDataTypeException; |
||
64 | import org.gvsig.fmap.dal.feature.exception.UnsupportedGeometryException; |
||
65 | import org.gvsig.fmap.dal.resource.ResourceAction; |
||
66 | import org.gvsig.fmap.dal.resource.spi.ResourceManagerProviderServices; |
||
67 | import org.gvsig.fmap.dal.store.jdbc.ConnectionAction; |
||
68 | import org.gvsig.fmap.dal.store.jdbc.JDBCHelper; |
||
69 | import org.gvsig.fmap.dal.store.jdbc.JDBCHelperUser; |
||
70 | import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
||
71 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException; |
||
72 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecutePreparedSQLException; |
||
73 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
||
74 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException; |
||
75 | import org.gvsig.fmap.dal.store.jdbc.exception.JDBCSQLException; |
||
76 | import org.gvsig.fmap.geom.Geometry; |
||
77 | import org.gvsig.fmap.geom.GeometryLocator; |
||
78 | import org.gvsig.fmap.geom.GeometryManager; |
||
79 | import org.gvsig.fmap.geom.operation.fromwkb.FromWKB; |
||
80 | import org.gvsig.fmap.geom.operation.fromwkb.FromWKBGeometryOperationContext; |
||
81 | import org.gvsig.fmap.geom.primitive.Envelope; |
||
82 | import org.gvsig.tools.ToolsLocator; |
||
83 | import org.gvsig.tools.exception.BaseException; |
||
84 | |||
85 | /**
|
||
86 | * @author jmvivo
|
||
87 | *
|
||
88 | */
|
||
89 | public class PostgreSQLHelper extends JDBCHelper { |
||
90 | |||
91 | private static Logger logger = LoggerFactory |
||
92 | .getLogger(PostgreSQLHelper.class); |
||
93 | |||
94 | private Map pgSR2SRSID = new TreeMap(); |
||
95 | private Map srsID2pgSR = new TreeMap(); |
||
96 | |||
97 | |||
98 | PostgreSQLHelper(JDBCHelperUser consumer, |
||
99 | PostgreSQLConnectionParameters params) |
||
100 | throws InitializeException {
|
||
101 | |||
102 | super(consumer, params);
|
||
103 | } |
||
104 | |||
105 | protected void initializeResource() throws InitializeException { |
||
106 | ResourceManagerProviderServices manager = (ResourceManagerProviderServices) DALLocator |
||
107 | .getResourceManager(); |
||
108 | PostgreSQLResource resource = (PostgreSQLResource) manager |
||
109 | .createAddResource( |
||
110 | PostgreSQLResource.NAME, new Object[] { |
||
111 | params.getUrl(), params.getHost(), |
||
112 | params.getPort(), params.getDBName(), params.getUser(), |
||
113 | params.getPassword(), |
||
114 | params.getJDBCDriverClassName(), |
||
115 | ((PostgreSQLConnectionParameters) params).getUseSSL() }); |
||
116 | this.setResource(resource);
|
||
117 | } |
||
118 | |||
119 | |||
120 | protected String getDefaultSchema(Connection conn) |
||
121 | throws JDBCException {
|
||
122 | if (defaultSchema == null) { |
||
123 | String sql = "Select current_schema()"; |
||
124 | ResultSet rs = null; |
||
125 | Statement st = null; |
||
126 | String schema = null; |
||
127 | try {
|
||
128 | st = conn.createStatement(); |
||
129 | try {
|
||
130 | rs = st.executeQuery(sql); |
||
131 | } catch (java.sql.SQLException e) {
|
||
132 | throw new JDBCExecuteSQLException(sql, e); |
||
133 | } |
||
134 | rs.next(); |
||
135 | schema = rs.getString(1);
|
||
136 | } catch (java.sql.SQLException e) {
|
||
137 | throw new JDBCSQLException(e); |
||
138 | } finally {
|
||
139 | try {rs.close();} catch (Exception e) {logger.error("Exception clossing resulset", e);}; |
||
140 | try {st.close();} catch (Exception e) {logger.error("Exception clossing statement", e);}; |
||
141 | rs = null;
|
||
142 | st = null;
|
||
143 | } |
||
144 | defaultSchema = schema; |
||
145 | } |
||
146 | |||
147 | return defaultSchema;
|
||
148 | } |
||
149 | |||
150 | public Envelope getFullEnvelopeOfField(
|
||
151 | JDBCStoreParameters storeParams, |
||
152 | String geometryAttrName, Envelope limit)
|
||
153 | throws DataException {
|
||
154 | |||
155 | StringBuilder strb = new StringBuilder(); |
||
156 | strb.append("Select asbinary(extent(");
|
||
157 | strb.append(escapeFieldName(geometryAttrName)); |
||
158 | strb.append(")) from ");
|
||
159 | |||
160 | if (storeParams.getSQL() != null |
||
161 | && storeParams.getSQL().trim().length() == 0) {
|
||
162 | strb.append('(');
|
||
163 | strb.append(storeParams.getSQL()); |
||
164 | strb.append(") as __extentfield__ ");
|
||
165 | } else {
|
||
166 | strb.append(storeParams.tableID()); |
||
167 | } |
||
168 | |||
169 | |||
170 | if (limit != null){ |
||
171 | strb.append(" where intersects(GeomFromText('");
|
||
172 | strb.append(limit.toString()); |
||
173 | strb.append("')), envelope(");
|
||
174 | strb.append(escapeFieldName(geometryAttrName)); |
||
175 | strb.append(")) ");
|
||
176 | } |
||
177 | |||
178 | final String sql = strb.toString(); |
||
179 | |||
180 | GeometryManager geoMan = GeometryLocator.getGeometryManager(); |
||
181 | |||
182 | this.open();
|
||
183 | |||
184 | return (Envelope) getResource().execute(new ResourceAction() { |
||
185 | public Object run() throws Exception { |
||
186 | ResultSet rs = null; |
||
187 | Statement st = null; |
||
188 | String schema = null; |
||
189 | Connection conn = null; |
||
190 | Envelope fullEnvelope = null;
|
||
191 | try {
|
||
192 | |||
193 | conn = getConnection(); |
||
194 | st = conn.createStatement(); |
||
195 | try {
|
||
196 | rs = st.executeQuery(sql); |
||
197 | } catch (java.sql.SQLException e) {
|
||
198 | throw new JDBCExecuteSQLException(sql, e); |
||
199 | } |
||
200 | if (!rs.next()) {
|
||
201 | return null; |
||
202 | } |
||
203 | |||
204 | byte[] data = rs.getBytes(1); |
||
205 | if (data == null) { |
||
206 | return null; |
||
207 | } |
||
208 | |||
209 | Geometry geom = geomManager.createFrom(data); |
||
210 | |||
211 | fullEnvelope = geom.getEnvelope(); |
||
212 | |||
213 | return fullEnvelope;
|
||
214 | } catch (java.sql.SQLException e) {
|
||
215 | throw new JDBCSQLException(e); |
||
216 | } catch (BaseException e) {
|
||
217 | throw new ReadException(user.getProviderName(), e); |
||
218 | } finally {
|
||
219 | try {
|
||
220 | rs.close(); |
||
221 | } catch (Exception e) { |
||
222 | } |
||
223 | try {
|
||
224 | st.close(); |
||
225 | } catch (Exception e) { |
||
226 | } |
||
227 | try {
|
||
228 | conn.close(); |
||
229 | } catch (Exception e) { |
||
230 | } |
||
231 | rs = null;
|
||
232 | st = null;
|
||
233 | conn = null;
|
||
234 | } |
||
235 | } |
||
236 | }); |
||
237 | } |
||
238 | |||
239 | @Override
|
||
240 | protected boolean supportsGeometry() { |
||
241 | return true; |
||
242 | } |
||
243 | |||
244 | /**
|
||
245 | * Fill <code>featureType</code> geometry attributes with SRS and ShapeType
|
||
246 | * information stored in the table GEOMETRY_COLUMNS
|
||
247 | *
|
||
248 | * @param conn
|
||
249 | * @param rsMetadata
|
||
250 | * @param featureType
|
||
251 | * @throws ReadException
|
||
252 | */
|
||
253 | protected void loadSRS_and_shapeType(Connection conn, |
||
254 | ResultSetMetaData rsMetadata, EditableFeatureType featureType,
|
||
255 | String baseSchema, String baseTable) |
||
256 | throws JDBCException {
|
||
257 | |||
258 | Statement st = null; |
||
259 | ResultSet rs = null; |
||
260 | try {
|
||
261 | // Sacamos la lista de los attributos geometricos
|
||
262 | EditableFeatureAttributeDescriptor attr; |
||
263 | List geoAttrs = new ArrayList(); |
||
264 | |||
265 | Iterator iter = featureType.iterator();
|
||
266 | while (iter.hasNext()) {
|
||
267 | attr = (EditableFeatureAttributeDescriptor) iter.next(); |
||
268 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
269 | geoAttrs.add(attr); |
||
270 | } |
||
271 | } |
||
272 | if (geoAttrs.size() < 1) { |
||
273 | return;
|
||
274 | } |
||
275 | |||
276 | |||
277 | // preparamos un set con las lista de tablas de origen
|
||
278 | // de los campos
|
||
279 | class TableId { |
||
280 | public String schema=null; |
||
281 | public String table=null; |
||
282 | public String field = null; |
||
283 | |||
284 | public void appendToSQL(StringBuilder strb) { |
||
285 | if (schema == null || schema.length() == 0) { |
||
286 | strb |
||
287 | .append("( F_TABLE_SCHEMA = current_schema() AND F_TABLE_NAME = '");
|
||
288 | } else {
|
||
289 | strb.append("( F_TABLE_SCHEMA = '");
|
||
290 | strb.append(schema); |
||
291 | strb.append("' AND F_TABLE_NAME = '");
|
||
292 | } |
||
293 | strb.append(table); |
||
294 | strb.append("' AND F_GEOMETRY_COLUMN = '");
|
||
295 | strb.append(field); |
||
296 | strb.append("' )");
|
||
297 | } |
||
298 | |||
299 | } |
||
300 | Comparator cmp = new Comparator(){ |
||
301 | public int compare(Object arg0, Object arg1) { |
||
302 | TableId a0 = (TableId) arg0; |
||
303 | TableId a1 = (TableId) arg1; |
||
304 | |||
305 | int aux = a0.field.compareTo(a1.field);
|
||
306 | if (aux != 0) { |
||
307 | return aux;
|
||
308 | } |
||
309 | |||
310 | aux = a0.table.compareTo(a1.table); |
||
311 | if (aux != 0) { |
||
312 | return aux;
|
||
313 | } |
||
314 | |||
315 | if (a0.schema == null) { |
||
316 | if (a1.schema == null) { |
||
317 | aux = 0;
|
||
318 | } else {
|
||
319 | aux = -1;
|
||
320 | } |
||
321 | } else {
|
||
322 | if (a1.schema == null) { |
||
323 | aux = -1;
|
||
324 | } else {
|
||
325 | aux = a0.schema.compareTo(a1.schema); |
||
326 | } |
||
327 | } |
||
328 | return aux;
|
||
329 | } |
||
330 | }; |
||
331 | TreeSet set = new TreeSet(cmp); |
||
332 | TableId tableId; |
||
333 | iter = geoAttrs.iterator(); |
||
334 | int rsIndex;
|
||
335 | while (iter.hasNext()) {
|
||
336 | attr = (EditableFeatureAttributeDescriptor) iter.next(); |
||
337 | tableId = new TableId();
|
||
338 | rsIndex = attr.getIndex() + 1;
|
||
339 | |||
340 | if (baseSchema == null && baseTable == null) { |
||
341 | if (rsMetadata instanceof PGResultSetMetaData) { |
||
342 | tableId.schema = ((PGResultSetMetaData) rsMetadata) |
||
343 | .getBaseSchemaName(rsIndex); |
||
344 | tableId.table = ((PGResultSetMetaData) rsMetadata) |
||
345 | .getBaseTableName(rsIndex); |
||
346 | tableId.field = ((PGResultSetMetaData) rsMetadata) |
||
347 | .getBaseColumnName(rsIndex); |
||
348 | |||
349 | } else {
|
||
350 | tableId.schema = rsMetadata.getSchemaName(rsIndex); |
||
351 | tableId.table = rsMetadata.getTableName(rsIndex); |
||
352 | tableId.field = rsMetadata.getColumnName(rsIndex); |
||
353 | } |
||
354 | } else {
|
||
355 | tableId.schema = baseSchema; |
||
356 | tableId.table = baseTable; |
||
357 | tableId.field = rsMetadata.getColumnName(rsIndex); |
||
358 | } |
||
359 | if (tableId.table == null || tableId.table.length() == 0) { |
||
360 | // Si no tiene tabla origen (viene de algun calculo por ej.)
|
||
361 | // lo saltamos ya que no estara en la tabla GEOMETRY_COLUMNS
|
||
362 | continue;
|
||
363 | } |
||
364 | set.add(tableId); |
||
365 | } |
||
366 | |||
367 | if (set.size() == 0) { |
||
368 | return;
|
||
369 | } |
||
370 | |||
371 | // Preparamos una sql para que nos saque el resultado
|
||
372 | StringBuilder strb = new StringBuilder(); |
||
373 | strb.append("Select geometry_columns.*,auth_name || ':' || auth_srid as SRSID ");
|
||
374 | strb.append("from geometry_columns left join spatial_ref_sys on ");
|
||
375 | strb.append("geometry_columns.srid = spatial_ref_sys.srid WHERE ");
|
||
376 | iter = set.iterator(); |
||
377 | for (int i=0;i<set.size()-1;i++) { |
||
378 | tableId = (TableId) iter.next(); |
||
379 | tableId.appendToSQL(strb); |
||
380 | strb.append(" OR ");
|
||
381 | } |
||
382 | tableId = (TableId) iter.next(); |
||
383 | tableId.appendToSQL(strb); |
||
384 | String sql = strb.toString();
|
||
385 | |||
386 | |||
387 | st = conn.createStatement(); |
||
388 | try {
|
||
389 | rs = st.executeQuery(sql); |
||
390 | } catch (SQLException e) { |
||
391 | throw new JDBCExecuteSQLException(sql, e); |
||
392 | } |
||
393 | String srsID;
|
||
394 | int pgSrid;
|
||
395 | int geometryType;
|
||
396 | int geometrySubtype;
|
||
397 | String geomTypeStr;
|
||
398 | int dimensions;
|
||
399 | IProjection srs; |
||
400 | |||
401 | while (rs.next()){
|
||
402 | srsID = rs.getString("SRSID");
|
||
403 | pgSrid = rs.getInt("SRID");
|
||
404 | geomTypeStr = rs.getString("TYPE").toUpperCase();
|
||
405 | geometryType = Geometry.TYPES.GEOMETRY; |
||
406 | if (geomTypeStr.startsWith("POINT")) { |
||
407 | geometryType = Geometry.TYPES.POINT; |
||
408 | } else if (geomTypeStr.startsWith("LINESTRING")) { |
||
409 | geometryType = Geometry.TYPES.CURVE; |
||
410 | } else if (geomTypeStr.startsWith("POLYGON")) { |
||
411 | geometryType = Geometry.TYPES.SURFACE; |
||
412 | } else if (geomTypeStr.startsWith("MULTIPOINT")) { |
||
413 | geometryType = Geometry.TYPES.MULTIPOINT; |
||
414 | } else if (geomTypeStr.startsWith("MULTILINESTRING")) { |
||
415 | geometryType = Geometry.TYPES.MULTICURVE; |
||
416 | } else if (geomTypeStr.startsWith("MULTIPOLYGON")) { |
||
417 | geometryType = Geometry.TYPES.MULTISURFACE; |
||
418 | } |
||
419 | dimensions = rs.getInt("coord_dimension");
|
||
420 | geometrySubtype = Geometry.SUBTYPES.GEOM2D; |
||
421 | if (dimensions > 2) { |
||
422 | if (dimensions == 3) { |
||
423 | if (geomTypeStr.endsWith("M")) { |
||
424 | geometrySubtype = Geometry.SUBTYPES.GEOM2DM; |
||
425 | } else {
|
||
426 | geometrySubtype = Geometry.SUBTYPES.GEOM3D; |
||
427 | } |
||
428 | |||
429 | } else {
|
||
430 | geometrySubtype = Geometry.SUBTYPES.GEOM3DM; |
||
431 | } |
||
432 | } |
||
433 | addToPgSRToSRSID(pgSrid, srsID); |
||
434 | |||
435 | |||
436 | iter = geoAttrs.iterator(); |
||
437 | while (iter.hasNext()) {
|
||
438 | attr = (EditableFeatureAttributeDescriptor) iter.next(); |
||
439 | rsIndex = attr.getIndex() + 1;
|
||
440 | if (!rsMetadata.getColumnName(rsIndex).equals(
|
||
441 | rs.getString("f_geometry_column"))) {
|
||
442 | continue;
|
||
443 | } |
||
444 | |||
445 | if (baseSchema == null && baseTable == null) { |
||
446 | |||
447 | if (rsMetadata instanceof PGResultSetMetaData) { |
||
448 | if (!((PGResultSetMetaData) rsMetadata)
|
||
449 | .getBaseTableName(rsIndex).equals( |
||
450 | rs.getString("f_table_name"))) {
|
||
451 | continue;
|
||
452 | } |
||
453 | String curSchema = rs.getString("f_table_schema"); |
||
454 | String metaSchema = ((PGResultSetMetaData) rsMetadata)
|
||
455 | .getBaseSchemaName(rsIndex); |
||
456 | if (!metaSchema.equals(curSchema)) {
|
||
457 | if (metaSchema.length() == 0 |
||
458 | && metaSchema == getDefaultSchema(conn)) { |
||
459 | } else {
|
||
460 | continue;
|
||
461 | } |
||
462 | } |
||
463 | |||
464 | } else {
|
||
465 | |||
466 | if (!rsMetadata.getTableName(rsIndex).equals(
|
||
467 | rs.getString("f_table_name"))) {
|
||
468 | continue;
|
||
469 | } |
||
470 | String curSchema = rs.getString("f_table_schema"); |
||
471 | String metaSchema = rsMetadata
|
||
472 | .getSchemaName(rsIndex); |
||
473 | if (!metaSchema.equals(curSchema)) {
|
||
474 | if (metaSchema.length() == 0 |
||
475 | && metaSchema == getDefaultSchema(conn)) { |
||
476 | } else {
|
||
477 | continue;
|
||
478 | } |
||
479 | } |
||
480 | } |
||
481 | } |
||
482 | attr.setGeometryType(geometryType); |
||
483 | attr.setGeometrySubType(geometrySubtype); |
||
484 | if (srsID != null && srsID.length() > 0) { |
||
485 | attr.setSRS(CRSFactory.getCRS(srsID)); |
||
486 | } |
||
487 | iter.remove(); |
||
488 | } |
||
489 | iter = geoAttrs.iterator(); |
||
490 | while (iter.hasNext()) {
|
||
491 | attr = (EditableFeatureAttributeDescriptor) iter.next(); |
||
492 | attr.setSRS(null);
|
||
493 | attr.setGeometryType(Geometry.TYPES.GEOMETRY); |
||
494 | |||
495 | } |
||
496 | } |
||
497 | |||
498 | } catch (java.sql.SQLException e) {
|
||
499 | throw new JDBCSQLException(e); |
||
500 | } finally {
|
||
501 | try {rs.close();} catch (Exception e) { }; |
||
502 | try {st.close();} catch (Exception e) { }; |
||
503 | } |
||
504 | |||
505 | } |
||
506 | |||
507 | |||
508 | public String getSqlColumnTypeDescription(FeatureAttributeDescriptor attr) { |
||
509 | if (attr.getType() == DataTypes.GEOMETRY) {
|
||
510 | return "geometry"; |
||
511 | } |
||
512 | return super.getSqlColumnTypeDescription(attr); |
||
513 | } |
||
514 | |||
515 | |||
516 | public int getPostgisGeomDimensions(int geometrySubType) { |
||
517 | switch (geometrySubType) {
|
||
518 | case Geometry.SUBTYPES.GEOM2D:
|
||
519 | return 2; |
||
520 | case Geometry.SUBTYPES.GEOM2DM:
|
||
521 | case Geometry.SUBTYPES.GEOM3D:
|
||
522 | return 3; |
||
523 | |||
524 | case Geometry.SUBTYPES.GEOM3DM:
|
||
525 | return 4; |
||
526 | default:
|
||
527 | throw new UnsupportedDataTypeException( |
||
528 | ToolsLocator.getDataTypesManager().getTypeName(DataTypes.GEOMETRY), |
||
529 | DataTypes.GEOMETRY); |
||
530 | } |
||
531 | } |
||
532 | |||
533 | public String getPostgisGeomType(int geometryType, int geometrySubType) { |
||
534 | String pgGeomType;
|
||
535 | switch (geometryType) {
|
||
536 | case Geometry.TYPES.GEOMETRY:
|
||
537 | pgGeomType = "GEOMETRY";
|
||
538 | break;
|
||
539 | case Geometry.TYPES.POINT:
|
||
540 | pgGeomType = "POINT";
|
||
541 | break;
|
||
542 | case Geometry.TYPES.CURVE:
|
||
543 | pgGeomType = "LINESTRING";
|
||
544 | break;
|
||
545 | case Geometry.TYPES.SURFACE:
|
||
546 | pgGeomType = "POLYGON";
|
||
547 | break;
|
||
548 | case Geometry.TYPES.MULTIPOINT:
|
||
549 | pgGeomType = "MULTIPOINT";
|
||
550 | break;
|
||
551 | case Geometry.TYPES.MULTICURVE:
|
||
552 | pgGeomType = "MULTILINESTRING";
|
||
553 | break;
|
||
554 | case Geometry.TYPES.MULTISURFACE:
|
||
555 | pgGeomType = "MULTIPOLYGON";
|
||
556 | break;
|
||
557 | default:
|
||
558 | throw new UnsupportedGeometryException(geometryType, |
||
559 | geometrySubType); |
||
560 | } |
||
561 | if (geometrySubType == Geometry.SUBTYPES.GEOM2DM
|
||
562 | || geometrySubType == Geometry.SUBTYPES.GEOM3DM) { |
||
563 | pgGeomType = pgGeomType + "M";
|
||
564 | } else if (geometrySubType == Geometry.SUBTYPES.GEOM3D) { |
||
565 | throw new UnsupportedGeometryException(geometryType, |
||
566 | geometrySubType); |
||
567 | } |
||
568 | return pgGeomType;
|
||
569 | } |
||
570 | |||
571 | public int getProviderSRID(String srs) { |
||
572 | if (srs != null) { |
||
573 | Integer pgSRID = (Integer) srsID2pgSR.get(srs); |
||
574 | if (pgSRID != null) { |
||
575 | return pgSRID.intValue();
|
||
576 | } |
||
577 | |||
578 | return searchpgSRID(srs);
|
||
579 | |||
580 | } |
||
581 | return -1; |
||
582 | } |
||
583 | |||
584 | |||
585 | public int getProviderSRID(IProjection srs) { |
||
586 | if (srs != null) { |
||
587 | Integer pgSRID = (Integer) srsID2pgSR.get(srs.getAbrev()); |
||
588 | if (pgSRID != null) { |
||
589 | return pgSRID.intValue();
|
||
590 | } |
||
591 | |||
592 | return searchpgSRID(srs);
|
||
593 | |||
594 | } |
||
595 | return -1; |
||
596 | } |
||
597 | |||
598 | private int searchpgSRID(final IProjection srs) { |
||
599 | if (srs == null) { |
||
600 | return -1; |
||
601 | } |
||
602 | return searchpgSRID(srs.getAbrev());
|
||
603 | } |
||
604 | |||
605 | private int searchpgSRID(final String srsID) { |
||
606 | if (srsID == null) { |
||
607 | return -1; |
||
608 | } |
||
609 | |||
610 | ConnectionAction action = new ConnectionAction(){
|
||
611 | |||
612 | public Object action(Connection conn) throws DataException { |
||
613 | |||
614 | String[] abrev = srsID.split(":"); |
||
615 | StringBuilder sqlb = new StringBuilder(); |
||
616 | sqlb.append("select srid from spatial_ref_sys where ");
|
||
617 | if (abrev.length > 1) { |
||
618 | sqlb.append("auth_name = ? and ");
|
||
619 | } |
||
620 | sqlb.append("auth_srid = ?");
|
||
621 | |||
622 | String sql = sqlb.toString();
|
||
623 | PreparedStatement st;
|
||
624 | try {
|
||
625 | st = conn.prepareStatement(sql); |
||
626 | } catch (SQLException e){ |
||
627 | throw new JDBCPreparingSQLException(sql,e); |
||
628 | } |
||
629 | ResultSet rs = null; |
||
630 | try{
|
||
631 | int i=0; |
||
632 | if (abrev.length > 1){ |
||
633 | st.setString(i+1, abrev[i]);
|
||
634 | i++; |
||
635 | } |
||
636 | st.setInt(i + 1, Integer.parseInt(abrev[i])); |
||
637 | |||
638 | try{
|
||
639 | rs = st.executeQuery(); |
||
640 | } catch (SQLException e){ |
||
641 | throw new JDBCExecutePreparedSQLException(sql, abrev, e); |
||
642 | } |
||
643 | |||
644 | if (!rs.next()) {
|
||
645 | return null; |
||
646 | } |
||
647 | |||
648 | return new Integer(rs.getInt(1)); |
||
649 | |||
650 | } catch (SQLException e){ |
||
651 | throw new JDBCSQLException(e); |
||
652 | } finally{
|
||
653 | try {rs.close(); } catch (Exception e) {}; |
||
654 | try {st.close(); } catch (Exception e) {}; |
||
655 | } |
||
656 | |||
657 | } |
||
658 | |||
659 | }; |
||
660 | |||
661 | Integer pgSRSID = null; |
||
662 | try {
|
||
663 | pgSRSID = (Integer) doConnectionAction(action);
|
||
664 | } catch (Exception e) { |
||
665 | logger.error("Excetion searching pgSRS", e);
|
||
666 | return -1; |
||
667 | } |
||
668 | |||
669 | if (pgSRSID != null) { |
||
670 | addToPgSRToSRSID(pgSRSID.intValue(), srsID); |
||
671 | return pgSRSID.intValue();
|
||
672 | } |
||
673 | return -1; |
||
674 | |||
675 | } |
||
676 | |||
677 | private void addToPgSRToSRSID(int pgSRID, String srsId) { |
||
678 | if (pgSRID < 0 || srsId == null || srsId.length() == 0) { |
||
679 | return;
|
||
680 | } |
||
681 | Integer pgSRIDInteger = new Integer(pgSRID); |
||
682 | pgSR2SRSID.put(pgSRIDInteger, srsId); |
||
683 | srsID2pgSR.put(srsId, pgSRIDInteger); |
||
684 | } |
||
685 | |||
686 | public List<String> getSqlGeometyFieldAdd(FeatureAttributeDescriptor attr, |
||
687 | String table, String schema) { |
||
688 | // SELECT AddGeometryColumn({schema}, {table}, {field}, {srid}(int),
|
||
689 | // {geomType}(Str), {dimensions}(int))
|
||
690 | |||
691 | // gemoType:
|
||
692 | /*
|
||
693 | * POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
|
||
694 | * MULTIPOLYGON, GEOMETRYCOLLECTION POINTM, LINESTRINGM, POLYGONM,
|
||
695 | * MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM
|
||
696 | */
|
||
697 | |||
698 | List<String> sqls = new ArrayList<String>(); |
||
699 | |||
700 | StringBuilder strb = new StringBuilder(); |
||
701 | strb.append("SELECT AddGeometryColumn('");
|
||
702 | if (schema != null && schema.length() > 0) { |
||
703 | strb.append(schema); |
||
704 | strb.append("', '");
|
||
705 | } |
||
706 | strb.append(table); |
||
707 | strb.append("', '");
|
||
708 | strb.append(attr.getName().toLowerCase()); |
||
709 | strb.append("', ");
|
||
710 | // strb.append("-1");
|
||
711 | strb.append(getProviderSRID(attr.getSRS())); |
||
712 | strb.append(", '");
|
||
713 | |||
714 | // ===========================================================================
|
||
715 | // TODO Improve this. Keep in mind that MULTIPOLYGON will not accept POLYGON
|
||
716 | strb.append("GEOMETRY");
|
||
717 | /*
|
||
718 | strb.append(getPostgisGeomType(attr.getGeometryType(), attr
|
||
719 | .getGeometrySubType()));
|
||
720 | */
|
||
721 | // ===========================================================================
|
||
722 | strb.append("', ");
|
||
723 | strb.append(getPostgisGeomDimensions(attr.getGeometrySubType())); |
||
724 | strb.append(")");
|
||
725 | |||
726 | |||
727 | sqls.add(strb.toString()); |
||
728 | |||
729 | /*ALTER TABLE muni10000_peq_test DROP CONSTRAINT enforce_srid_the_geom;*/
|
||
730 | /*
|
||
731 | strb = new StringBuilder();
|
||
732 | strb.append("Alter table ");
|
||
733 | if (schema != null && schema.length() > 0) {
|
||
734 | strb.append(schema);
|
||
735 | strb.append(".");
|
||
736 | }
|
||
737 | strb.append("f_table_name = '");
|
||
738 | strb.append(table);
|
||
739 | strb.append("' AND f_geometry_column = '");
|
||
740 | strb.append(attr.getName());
|
||
741 | strb.append("' AND srid = -1");
|
||
742 | |||
743 | |||
744 | sqls.add(strb.toString());
|
||
745 | */
|
||
746 | return sqls;
|
||
747 | } |
||
748 | |||
749 | public String getSqlFieldName(FeatureAttributeDescriptor attribute) { |
||
750 | if (attribute.getType() == DataTypes.GEOMETRY) {
|
||
751 | return "asBinary(" + super.getSqlFieldName(attribute) + ")"; |
||
752 | } |
||
753 | return super.getSqlFieldName(attribute); |
||
754 | } |
||
755 | |||
756 | protected EditableFeatureAttributeDescriptor createAttributeFromJDBC(
|
||
757 | EditableFeatureType type, Connection conn,
|
||
758 | ResultSetMetaData rsMetadata, int colIndex) throws SQLException { |
||
759 | if (rsMetadata.getColumnType(colIndex) == java.sql.Types.OTHER) {
|
||
760 | if (rsMetadata.getColumnTypeName(colIndex).equalsIgnoreCase(
|
||
761 | "geometry")) {
|
||
762 | return type.add(rsMetadata.getColumnName(colIndex),
|
||
763 | DataTypes.GEOMETRY); |
||
764 | |||
765 | } |
||
766 | } |
||
767 | |||
768 | return super.createAttributeFromJDBC(type, conn, rsMetadata, colIndex); |
||
769 | } |
||
770 | |||
771 | public List getAdditionalSqlToCreate(NewDataStoreParameters ndsp, |
||
772 | FeatureType fType) { |
||
773 | FeatureAttributeDescriptor attr; |
||
774 | Iterator iter = fType.iterator();
|
||
775 | List result = new ArrayList(); |
||
776 | PostgreSQLNewStoreParameters pgNdsp = (PostgreSQLNewStoreParameters) ndsp; |
||
777 | while (iter.hasNext()){
|
||
778 | attr = (FeatureAttributeDescriptor) iter.next(); |
||
779 | if (attr.getType() == DataTypes.GEOMETRY){
|
||
780 | result.addAll(getSqlGeometyFieldAdd(attr, pgNdsp.getTable(), |
||
781 | pgNdsp |
||
782 | .getSchema())); |
||
783 | } |
||
784 | } |
||
785 | |||
786 | return result;
|
||
787 | } |
||
788 | |||
789 | public String getSqlFieldDescription(FeatureAttributeDescriptor attr) |
||
790 | throws DataException {
|
||
791 | if (attr.getType() == DataTypes.GEOMETRY){
|
||
792 | return null; |
||
793 | } |
||
794 | return super.getSqlFieldDescription(attr); |
||
795 | } |
||
796 | |||
797 | |||
798 | public boolean allowAutomaticValues() { |
||
799 | return Boolean.TRUE; |
||
800 | } |
||
801 | |||
802 | public boolean supportOffset() { |
||
803 | return true; |
||
804 | } |
||
805 | |||
806 | public boolean supportsUnion() { |
||
807 | return true; |
||
808 | } |
||
809 | 9 | jldominguez | |
810 | public String escapeFieldName(String field) { |
||
811 | /*
|
||
812 | if (!reservedWord(field) &&
|
||
813 | field.matches("[a-z][a-z0-9_]*")) {
|
||
814 | return field;
|
||
815 | }
|
||
816 | */
|
||
817 | String quote = getIdentifierQuoteString();
|
||
818 | return quote + field + quote;
|
||
819 | } |
||
820 | 5 | jldominguez | } |