Statistics
| Revision:

svn-gvsig-desktop / trunk / extensions / extJDBC / src / com / iver / cit / gvsig / fmap / drivers / jdbc / postgis / PostGIS.java @ 28489

History | View | Annotate | Download (14.1 KB)

1
/*
2
 * Created on 26-oct-2005
3
 *
4
 * gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana
5
 *
6
 * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.
7
 *
8
 * This program is free software; you can redistribute it and/or
9
 * modify it under the terms of the GNU General Public License
10
 * as published by the Free Software Foundation; either version 2
11
 * of the License, or (at your option) any later version.
12
 *
13
 * This program is distributed in the hope that it will be useful,
14
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16
 * GNU General Public License for more details.
17
 *
18
 * You should have received a copy of the GNU General Public License
19
 * along with this program; if not, write to the Free Software
20
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
21
 *
22
 * For more information, contact:
23
 *
24
 *  Generalitat Valenciana
25
 *   Conselleria d'Infraestructures i Transport
26
 *   Av. Blasco Ib??ez, 50
27
 *   46010 VALENCIA
28
 *   SPAIN
29
 *
30
 *      +34 963862235
31
 *   gvsig@gva.es
32
 *      www.gvsig.gva.es
33
 *
34
 *    or
35
 *
36
 *   IVER T.I. S.A
37
 *   Salamanca 50
38
 *   46005 Valencia
39
 *   Spain
40
 *
41
 *   +34 963163400
42
 *   dac@iver.es
43
 */
44
package com.iver.cit.gvsig.fmap.drivers.jdbc.postgis;
45

    
46
import java.io.ByteArrayOutputStream;
47
import java.io.PrintStream;
48
import java.io.UnsupportedEncodingException;
49
import java.sql.SQLException;
50
import java.sql.Types;
51

    
52
import com.hardcode.gdbms.engine.values.NullValue;
53
import com.hardcode.gdbms.engine.values.Value;
54
import com.hardcode.gdbms.engine.values.ValueWriter;
55
import com.iver.cit.gvsig.exceptions.visitors.ProcessVisitorException;
56
import com.iver.cit.gvsig.fmap.core.FPolygon2D;
57
import com.iver.cit.gvsig.fmap.core.FPolyline2D;
58
import com.iver.cit.gvsig.fmap.core.FShape;
59
import com.iver.cit.gvsig.fmap.core.FShapeM;
60
import com.iver.cit.gvsig.fmap.core.GeneralPathX;
61
import com.iver.cit.gvsig.fmap.core.IFeature;
62
import com.iver.cit.gvsig.fmap.core.IGeometry;
63
import com.iver.cit.gvsig.fmap.core.IGeometry3D;
64
import com.iver.cit.gvsig.fmap.core.IGeometryM;
65
import com.iver.cit.gvsig.fmap.core.IRow;
66
import com.iver.cit.gvsig.fmap.core.ShapeFactory;
67
import com.iver.cit.gvsig.fmap.core.ShapeMFactory;
68
import com.iver.cit.gvsig.fmap.drivers.DBLayerDefinition;
69
import com.iver.cit.gvsig.fmap.drivers.DefaultJDBCDriver;
70
import com.iver.cit.gvsig.fmap.drivers.FieldDescription;
71
import com.iver.cit.gvsig.fmap.drivers.XTypes;
72
import com.vividsolutions.jts.geom.Geometry;
73

    
74
/**
75
 * @author fjp
76
 * Necesitamos que esta clase no trabaje con funciones est?ticas
77
 * porque puede haber capas que provengan de distintas bases de datos.
78
 */
79
public class PostGIS {
80

    
81
        private String toEncode;
82

    
83
        /**
84
         * Mover esto a IverUtiles
85
         *
86
         * @param val
87
         * @return
88
         */
89
        public boolean isNumeric(Value val) {
90

    
91
                switch (val.getSQLType()) {
92
                case Types.DOUBLE:
93
                case Types.FLOAT:
94
                case Types.INTEGER:
95
                case Types.SMALLINT:
96
                case Types.BIGINT:
97
                case Types.NUMERIC:
98
                case Types.REAL:
99
                case Types.TINYINT:
100
                        return true;
101
                }
102

    
103
                return false;
104
        }
105

    
106
        /**
107
         * @param dbLayerDef
108
         * @param fieldsDescr
109
         * @param bCreateGID @DEPRECATED
110
         * @return
111
         */
112
        public String getSqlCreateSpatialTable(DBLayerDefinition dbLayerDef,
113
                        FieldDescription[] fieldsDescr, boolean bCreateGID) {
114

    
115
                String resul;
116
                /* boolean bExistGID = false;
117
                for (int i = 0; i < dbLayerDef.getFieldNames().length; i++) {
118
                        if (dbLayerDef.getFieldNames()[i].equalsIgnoreCase("gid")) {
119
                                bExistGID = true;
120
                                break;
121
                        }
122
                } */
123
                /* if (bExistGID) // Usamos el existente y no a?adimos ninguno nosotros
124
                        resul = "CREATE TABLE " + dbLayerDef.getTableName() + " (";
125
                else */
126
                // FJP: NUEVO: NO TOLERAMOS CAMPOS QUE SE LLAMEN GID. Lo reservamos para uso nuestro.
127
                resul = "CREATE TABLE " + dbLayerDef.getComposedTableName()
128
                                        + " ( " + dbLayerDef.getFieldID() +" serial PRIMARY KEY ";
129
                int j=0;
130
                for (int i = 0; i < dbLayerDef.getFieldNames().length; i++) {
131
                        int fieldType = fieldsDescr[i].getFieldType();
132
                        String strType = XTypes.fieldTypeToString(fieldType);
133
                        /*
134
                         * if (fieldType == Types.VARCHAR) strType = strType + "(" +
135
                         * fieldsDescr[i].getFieldLength() + ")";
136
                         */
137
                        if (fieldsDescr[i].getFieldName().equalsIgnoreCase(dbLayerDef.getFieldID()))
138
                                continue;
139
                        resul = resul + ", " + dbLayerDef.getFieldNames()[i] + " "        + strType;
140
                        j++;
141
                }
142
                resul = resul + ");";
143
                return resul;
144
        }
145

    
146
        public String getSqlAlterTable(DBLayerDefinition dbLayerDef) {
147
                String strGeometryFieldType;
148
                strGeometryFieldType = "GEOMETRY";
149

    
150
                switch (dbLayerDef.getShapeType()) {
151
                case FShape.POINT:
152
                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POINT2D);
153
                        break;
154
                case FShape.LINE:
155
                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.LINE2D);
156
                        break;
157
                case FShape.POLYGON:
158
                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POLYGON2D);
159
                        break;
160
                case FShape.MULTI:
161
                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.MULTI2D);
162
                        break;
163
                case FShape.MULTIPOINT:
164
                        strGeometryFieldType = XTypes.fieldTypeToString(XTypes.MULTIPOINT);
165
                        break;
166
                }
167

    
168
                String schema = dbLayerDef.getSchema();
169
                if (schema == null || schema.equals("")){
170
                        schema = " current_schema()::Varchar ";
171
                } else {
172
                        schema = "'" +schema + "'";
173
                }
174

    
175
                String result = "SELECT AddGeometryColumn("
176
                                + schema + ", '"
177
                                + dbLayerDef.getTableName() + "', '"
178
                                + dbLayerDef.getFieldGeometry() + "', "
179
                                + DefaultJDBCDriver.removePrefix(dbLayerDef.getSRID_EPSG()) + ", '" + strGeometryFieldType + "', "
180
                                + dbLayerDef.getDimension() + ");";
181

    
182
                return result;
183
        }
184

    
185
        /**
186
         * From geotools Adds quotes to an object for storage in postgis. The object
187
         * should be a string or a number. To perform an insert strings need quotes
188
         * around them, and numbers work fine with quotes, so this method can be
189
         * called on unknown objects.
190
         *
191
         * @param value
192
         *            The object to add quotes to.
193
         *
194
         * @return a string representation of the object with quotes.
195
         */
196
        protected String addQuotes(Object value) {
197
                String retString;
198

    
199
                if (value != null) {
200
                        if (value instanceof NullValue)
201
                                retString = "null";
202
                        else
203
                                retString = "'" + doubleQuote(value) + "'";
204

    
205
                } else {
206
                        retString = "null";
207
                }
208

    
209
                return retString;
210
        }
211

    
212
        private String doubleQuote(Object obj) {
213
                String aux = obj.toString().replaceAll("'", "''");
214
                StringBuffer strBuf = new StringBuffer(aux);
215
                ByteArrayOutputStream out = new ByteArrayOutputStream(strBuf.length());
216
                PrintStream printStream = new PrintStream(out);
217
                printStream.print(aux);
218
                String aux2 = "ERROR";
219
                try {
220
                        aux2 = out.toString(toEncode);
221
                        System.out.println(aux + " " + aux2);
222
                } catch (UnsupportedEncodingException e) {
223
                        // TODO Auto-generated catch block
224
                        e.printStackTrace();
225
                }
226

    
227
                return aux2;
228
        }
229

    
230
        /**
231
         * Based in code from JUMP (VividSolutions) and Geotools Things to be aware:
232
         * We always will use Spatial Tables with Unique ID. IFeature has the same
233
         * field order than dbLayerDef.getFieldNames()
234
         *
235
         * @param dbLayerDef
236
         * @param feat
237
         * @return
238
         * @throws SQLException
239
         * @throws ProcessVisitorException
240
         */
241
        public String getSqlInsertFeature(DBLayerDefinition dbLayerDef,
242
                        IFeature feat) throws ProcessVisitorException {
243
                StringBuffer sqlBuf = new StringBuffer("INSERT INTO "
244
                                + dbLayerDef.getComposedTableName() + " (");
245
                String sql = null;
246
                int numAlphanumericFields = dbLayerDef.getFieldNames().length;
247

    
248
                for (int i = 0; i < numAlphanumericFields; i++) {
249
                        String name = dbLayerDef.getFieldsDesc()[i].getFieldName();
250
                        // if (cols.contains(name) && (!name.equals(uniqueCol) ||
251
                        // existsUnique)) {
252
                        if (name.equals(dbLayerDef.getFieldID()))
253
                                continue;
254
                        sqlBuf.append(" " + name + ",");
255
                        // }
256
                }
257
                sqlBuf.append(" " + dbLayerDef.getFieldGeometry());
258
                // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
259
                sqlBuf.append(" ) VALUES (");
260
                String insertQueryHead = sqlBuf.toString();
261
                sqlBuf = new StringBuffer(insertQueryHead);
262
                for (int j = 0; j < numAlphanumericFields; j++) {
263
                        String name = dbLayerDef.getFieldsDesc()[j].getFieldName();
264
                        if (name.equals(dbLayerDef.getFieldID()))
265
                                continue;
266

    
267
                        if (isNumeric(feat.getAttribute(j)))
268
                                sqlBuf.append(feat.getAttribute(j) + ", ");
269
                        else
270
                                sqlBuf.append(addQuotes(feat.getAttribute(j)) + ", ");
271
                }
272
                IGeometry geometry=feat.getGeometry();
273
                int type=dbLayerDef.getShapeType();
274
                if (geometry.getGeometryType()!=type){
275
                        if (type==FShape.POLYGON){
276
                                geometry=ShapeFactory.createPolygon2D(new GeneralPathX(geometry.getInternalShape()));
277
                        }else if (type==FShape.LINE){
278
                                geometry=ShapeFactory.createPolyline2D(new GeneralPathX(geometry.getInternalShape()));
279
                        }else if (type==(FShape.POLYGON|FShape.Z)){
280
                                geometry=ShapeFactory.createPolygon3D(new GeneralPathX(geometry.getInternalShape()),((IGeometry3D)geometry).getZs());
281
                        }else if (type==(FShape.LINE|FShape.Z)){
282
                                geometry=ShapeFactory.createPolyline3D(new GeneralPathX(geometry.getInternalShape()),((IGeometry3D)geometry).getZs());
283
                        }else if (type==(FShape.LINE|FShape.M)){ //MCoord
284
                                geometry=ShapeMFactory.createPolyline2DM(new GeneralPathX(geometry.getInternalShape()),((IGeometryM)geometry).getMs()); //MCoord
285
                        }
286
                }
287
                if (!isCorrectGeometry(geometry, type))
288
                        throw new ProcessVisitorException("incorrect_geometry",new Exception());
289
                //MCoord
290
                if ((type == (FShape.LINE|FShape.M)) || (type == (FShape.POINT|FShape.M))){
291
                        sqlBuf.append(" GeometryFromText( '"
292
                                        + ((FShapeM)geometry.getInternalShape()).toText() + "', "
293
                                        + DefaultJDBCDriver.removePrefix(dbLayerDef.getSRID_EPSG()) + ")");
294
                }else{
295
                        Geometry jtsGeom=geometry.toJTSGeometry();
296
                        if (jtsGeom==null || !isCorrectType(jtsGeom, type)){
297
                                throw new ProcessVisitorException("incorrect_geometry",new Exception());
298
                        }
299
                        sqlBuf.append(" GeometryFromText( '"
300
                                + jtsGeom.toText() + "', "
301
                                + DefaultJDBCDriver.removePrefix(dbLayerDef.getSRID_EPSG()) + ")");
302
                }
303

    
304
                // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
305
                sqlBuf.append(" ) ");
306
                sql = sqlBuf.toString();
307
                return sql;
308
        }
309

    
310
        private boolean isCorrectType(Geometry jtsGeom, int type) {
311
                if (FShape.POLYGON==type){
312
                        if (!jtsGeom.getGeometryType().equals("MultiPolygon") && !jtsGeom.getGeometryType().equals("Polygon") )
313
                                return false;
314
                }
315
                return true;
316
        }
317

    
318
        private boolean isCorrectGeometry(IGeometry geometry, int type) {
319
                if (FShape.POLYGON==type){
320
                        FPolygon2D polygon = (FPolygon2D)geometry.getInternalShape();
321
                        if (!(polygon.getBounds2D().getWidth()>0 && polygon.getBounds2D().getHeight()>0))
322
                                return false;
323
                }else if (FShape.LINE==type){
324
                        FPolyline2D line = (FPolyline2D)geometry.getInternalShape();
325
                        if (!(line.getBounds2D().getWidth()>0 || line.getBounds2D().getHeight()>0))
326
                                return false;
327
                }
328

    
329
                return true;
330
        }
331

    
332
        public String getSqlCreateIndex(DBLayerDefinition lyrDef) {
333
                String indexName = lyrDef.getTableName() + "_"
334
                                + lyrDef.getFieldGeometry() + "_gist";
335
                String sql = "CREATE INDEX \"" + indexName + "\" ON \""
336
                                + lyrDef.getComposedTableName() + "\" USING GIST (\""
337
                                + lyrDef.getFieldGeometry() + "\" GIST_GEOMETRY_OPS)";
338

    
339
                return sql;
340
        }
341

    
342
        public String getSqlModifyFeature(DBLayerDefinition dbLayerDef, IFeature feat) {
343
                /*
344
                         UPDATE weather
345
                         SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
346
                         WHERE date > '1994-11-28';
347
                 */
348
                StringBuffer sqlBuf = new StringBuffer("UPDATE "
349
                                + dbLayerDef.getComposedTableName() + " SET");
350
                String sql = null;
351
                int numAlphanumericFields = dbLayerDef.getFieldsDesc().length;
352

    
353
                for (int i = 0; i < numAlphanumericFields; i++) {
354
                        FieldDescription fldDesc = dbLayerDef.getFieldsDesc()[i];
355
                        if (fldDesc != null)
356
                        {
357
                                String name = fldDesc.getFieldName();
358
                                // El campo gid no lo actualizamos.
359
                                if (name.equalsIgnoreCase(dbLayerDef.getFieldID()))
360
                                        continue;
361
                                Value val = feat.getAttribute(i);
362
                                if (val != null)
363
                                {
364
                                        String strAux = val.getStringValue(ValueWriter.internalValueWriter);
365
                                        sqlBuf.append(" " + name + " = " + strAux + " ,");
366
                                }
367
                        }
368
                }
369
                //If pos > 0 there is at least one field..
370
                int pos = sqlBuf.lastIndexOf(",");
371
                if (pos > -1){
372
                        sqlBuf.deleteCharAt(pos);
373
                }
374
                if (feat.getGeometry() != null)
375
                {
376
                        if (pos > -1){
377
                                sqlBuf.append(",");
378
                        }
379
                        sqlBuf.append(" " + dbLayerDef.getFieldGeometry());
380
                        sqlBuf.append(" = ");
381
                        //MCoord
382
                        int type = feat.getGeometry().getGeometryType();
383
                        if ((type == (FShape.LINE|FShape.M)) || (type == (FShape.POINT|FShape.M))){
384
                                sqlBuf.append(" GeometryFromText( '"
385
                                                + ((FShapeM)feat.getGeometry().getInternalShape()).toText() + "', "
386
                                                + DefaultJDBCDriver.removePrefix(dbLayerDef.getSRID_EPSG()) + ")");
387
                        }else{
388
                                sqlBuf.append(" GeometryFromText( '"
389
                                + feat.getGeometry().toJTSGeometry().toText() + "', "
390
                                + DefaultJDBCDriver.removePrefix(dbLayerDef.getSRID_EPSG()) + ")");
391
                        }
392
                }
393
                sqlBuf.append(" WHERE ");
394
                sqlBuf.append(dbLayerDef.getFieldID() + " = " + feat.getID());
395
                sql = sqlBuf.toString();
396
                return sql;
397

    
398
        }
399

    
400
        /**
401
         * TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
402
         *         Esto provocar? errores, ya que getID que viene en un row no
403
         *         nos sirve dentro de un writer para modificar y/o borrar entidades
404
         *         Por ahora, cojo el ID del campo que me indica el dbLayerDef
405
         * @param dbLayerDef
406
         * @param row
407
         * @return
408
         */
409
        public String getSqlDeleteFeature(DBLayerDefinition dbLayerDef, IRow row) {
410
                // DELETE FROM weather WHERE city = 'Hayward';
411
                // TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
412
                // Esto provocar? errores, ya que getID que viene en un row no
413
                // nos sirve dentro de un writer para modificar y/o borrar entidades
414
                // Por ahora, cojo el ID del campo que me indica el dbLayerDev
415
                StringBuffer sqlBuf = new StringBuffer("DELETE FROM "
416
                                + dbLayerDef.getComposedTableName() + " WHERE ");
417
                String sql = null;
418
                int indexFieldId = dbLayerDef.getIdFieldID();
419
                sqlBuf.append(dbLayerDef.getFieldID() + " = " + row.getAttribute(indexFieldId));
420
                sql = sqlBuf.toString();
421

    
422
                return sql;
423
        }
424

    
425
        public String getEncoding() {
426
                return toEncode;
427
        }
428
        public void setEncoding(String toEncode){
429
                if (toEncode.compareToIgnoreCase("SQL_ASCII") == 0){
430
                   this.toEncode = "ASCII";
431
                  } else {
432
                          this.toEncode = toEncode;
433
                  }
434
        }
435

    
436
        static String escapeFieldName(String name){
437
                if (!name.toLowerCase().equals(name)){
438
                        return "\""+name.trim()+"\"";
439
                }
440
                if (!name.matches("[a-z][\\d\\S\\w]*")){
441
                        return "\""+name.trim()+"\"";
442
                }
443
                return name;
444
        }
445
}