root / branches / v10 / extensions / extJDBC / src / com / iver / cit / gvsig / fmap / drivers / jdbc / postgis / PostGIS.java @ 15780
History | View | Annotate | Download (10.4 KB)
1 | 4748 | fjp | /*
|
---|---|---|---|
2 | * Created on 26-oct-2005
|
||
3 | *
|
||
4 | * gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana
|
||
5 | 10727 | caballero | *
|
6 | 4748 | fjp | * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.
|
7 | 10727 | caballero | *
|
8 | 4748 | fjp | * 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 | 10727 | caballero | *
|
13 | 4748 | fjp | * 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 | 10727 | caballero | *
|
18 | 4748 | fjp | * 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 | 10727 | caballero | *
|
22 | 4748 | fjp | * 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 | 10727 | caballero | *
|
34 | 4748 | fjp | * or
|
35 | 10727 | caballero | *
|
36 | 4748 | fjp | * IVER T.I. S.A
|
37 | * Salamanca 50
|
||
38 | * 46005 Valencia
|
||
39 | * Spain
|
||
40 | 10727 | caballero | *
|
41 | 4748 | fjp | * +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.Types; |
||
50 | |||
51 | import com.hardcode.gdbms.engine.values.NullValue; |
||
52 | import com.hardcode.gdbms.engine.values.Value; |
||
53 | import com.hardcode.gdbms.engine.values.ValueWriter; |
||
54 | import com.iver.cit.gvsig.fmap.core.FShape; |
||
55 | import com.iver.cit.gvsig.fmap.core.IFeature; |
||
56 | 4799 | fjp | import com.iver.cit.gvsig.fmap.core.IRow; |
57 | 4748 | fjp | import com.iver.cit.gvsig.fmap.drivers.DBLayerDefinition; |
58 | import com.iver.cit.gvsig.fmap.drivers.FieldDescription; |
||
59 | 5714 | fjp | import com.iver.cit.gvsig.fmap.drivers.XTypes; |
60 | 4748 | fjp | |
61 | 8765 | jjdelcerro | /**
|
62 | * @author fjp
|
||
63 | * Necesitamos que esta clase no trabaje con funciones est?ticas
|
||
64 | * porque puede haber capas que provengan de distintas bases de datos.
|
||
65 | */
|
||
66 | 4748 | fjp | public class PostGIS { |
67 | |||
68 | 8765 | jjdelcerro | private String toEncode; |
69 | 10727 | caballero | |
70 | 4748 | fjp | /**
|
71 | * Mover esto a IverUtiles
|
||
72 | 10727 | caballero | *
|
73 | 4748 | fjp | * @param val
|
74 | * @return
|
||
75 | */
|
||
76 | 8765 | jjdelcerro | public boolean isNumeric(Value val) { |
77 | 4748 | fjp | |
78 | switch (val.getSQLType()) {
|
||
79 | case Types.DOUBLE: |
||
80 | case Types.FLOAT: |
||
81 | case Types.INTEGER: |
||
82 | case Types.SMALLINT: |
||
83 | case Types.BIGINT: |
||
84 | case Types.NUMERIC: |
||
85 | case Types.REAL: |
||
86 | case Types.TINYINT: |
||
87 | return true; |
||
88 | } |
||
89 | |||
90 | return false; |
||
91 | } |
||
92 | |||
93 | /**
|
||
94 | * @param dbLayerDef
|
||
95 | 5595 | fjp | * @param fieldsDescr
|
96 | 10727 | caballero | * @param bCreateGID @DEPRECATED
|
97 | 5595 | fjp | * @return
|
98 | 4748 | fjp | */
|
99 | 8765 | jjdelcerro | public String getSqlCreateSpatialTable(DBLayerDefinition dbLayerDef, |
100 | 4748 | fjp | FieldDescription[] fieldsDescr, boolean bCreateGID) { |
101 | |||
102 | String resul;
|
||
103 | /* boolean bExistGID = false;
|
||
104 | for (int i = 0; i < dbLayerDef.getFieldNames().length; i++) {
|
||
105 | if (dbLayerDef.getFieldNames()[i].equalsIgnoreCase("gid")) {
|
||
106 | bExistGID = true;
|
||
107 | break;
|
||
108 | }
|
||
109 | 5595 | fjp | } */
|
110 | /* if (bExistGID) // Usamos el existente y no a?adimos ninguno nosotros
|
||
111 | 4748 | fjp | resul = "CREATE TABLE " + dbLayerDef.getTableName() + " (";
|
112 | 5595 | fjp | else */
|
113 | // FJP: NUEVO: NO TOLERAMOS CAMPOS QUE SE LLAMEN GID. Lo reservamos para uso nuestro.
|
||
114 | 11867 | jmvivo | resul = "CREATE TABLE " + dbLayerDef.getComposedTableName()
|
115 | 15780 | jmvivo | + " ( " + dbLayerDef.getFieldID() +" serial PRIMARY KEY "; |
116 | 5595 | fjp | int j=0; |
117 | 4748 | fjp | for (int i = 0; i < dbLayerDef.getFieldNames().length; i++) { |
118 | int fieldType = fieldsDescr[i].getFieldType();
|
||
119 | String strType = XTypes.fieldTypeToString(fieldType);
|
||
120 | /*
|
||
121 | * if (fieldType == Types.VARCHAR) strType = strType + "(" +
|
||
122 | * fieldsDescr[i].getFieldLength() + ")";
|
||
123 | */
|
||
124 | 14881 | jmvivo | if (fieldsDescr[i].getFieldName().equalsIgnoreCase(dbLayerDef.getFieldID()))
|
125 | 5595 | fjp | continue;
|
126 | 6908 | jorpiell | resul = resul + ", " + dbLayerDef.getFieldNames()[i] + " " + strType; |
127 | 5595 | fjp | j++; |
128 | 4748 | fjp | } |
129 | resul = resul + ");";
|
||
130 | return resul;
|
||
131 | } |
||
132 | |||
133 | 8765 | jjdelcerro | public String getSqlAlterTable(DBLayerDefinition dbLayerDef) { |
134 | 4748 | fjp | String strGeometryFieldType;
|
135 | strGeometryFieldType = "GEOMETRY";
|
||
136 | |||
137 | switch (dbLayerDef.getShapeType()) {
|
||
138 | case FShape.POINT:
|
||
139 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POINT2D); |
||
140 | break;
|
||
141 | case FShape.LINE:
|
||
142 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.LINE2D); |
||
143 | break;
|
||
144 | case FShape.POLYGON:
|
||
145 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POLYGON2D); |
||
146 | break;
|
||
147 | case FShape.MULTI:
|
||
148 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.MULTI2D); |
||
149 | break;
|
||
150 | } |
||
151 | 12513 | jmvivo | String schema = dbLayerDef.getSchema();
|
152 | if (schema == null || schema.equals("")){ |
||
153 | 14881 | jmvivo | schema = " current_schema()::varchar ";
|
154 | 12513 | jmvivo | } else {
|
155 | schema = "'" +schema + "'"; |
||
156 | } |
||
157 | 4748 | fjp | |
158 | 12513 | jmvivo | String result = "SELECT AddGeometryColumn(" |
159 | + schema + ", '"
|
||
160 | 4748 | fjp | + dbLayerDef.getTableName() + "', '"
|
161 | + dbLayerDef.getFieldGeometry() + "', "
|
||
162 | + dbLayerDef.getSRID_EPSG() + ", '" + strGeometryFieldType + "', " |
||
163 | + dbLayerDef.getDimension() + ");";
|
||
164 | |||
165 | return result;
|
||
166 | } |
||
167 | |||
168 | /**
|
||
169 | * From geotools Adds quotes to an object for storage in postgis. The object
|
||
170 | * should be a string or a number. To perform an insert strings need quotes
|
||
171 | * around them, and numbers work fine with quotes, so this method can be
|
||
172 | * called on unknown objects.
|
||
173 | 10727 | caballero | *
|
174 | 4748 | fjp | * @param value
|
175 | * The object to add quotes to.
|
||
176 | 10727 | caballero | *
|
177 | 4748 | fjp | * @return a string representation of the object with quotes.
|
178 | */
|
||
179 | 8765 | jjdelcerro | protected String addQuotes(Object value) { |
180 | 4748 | fjp | String retString;
|
181 | |||
182 | if (value != null) { |
||
183 | if (value instanceof NullValue) |
||
184 | retString = "null";
|
||
185 | else
|
||
186 | retString = "'" + doubleQuote(value) + "'"; |
||
187 | 10727 | caballero | |
188 | 4748 | fjp | } else {
|
189 | retString = "null";
|
||
190 | } |
||
191 | |||
192 | return retString;
|
||
193 | } |
||
194 | |||
195 | 8765 | jjdelcerro | private String doubleQuote(Object obj) { |
196 | 4748 | fjp | String aux = obj.toString().replaceAll("'", "''"); |
197 | StringBuffer strBuf = new StringBuffer(aux); |
||
198 | ByteArrayOutputStream out = new ByteArrayOutputStream(strBuf.length()); |
||
199 | PrintStream printStream = new PrintStream(out); |
||
200 | printStream.print(aux); |
||
201 | String aux2 = "ERROR"; |
||
202 | try {
|
||
203 | aux2 = out.toString(toEncode); |
||
204 | System.out.println(aux + " " + aux2); |
||
205 | } catch (UnsupportedEncodingException e) { |
||
206 | // TODO Auto-generated catch block
|
||
207 | e.printStackTrace(); |
||
208 | } |
||
209 | |||
210 | return aux2;
|
||
211 | } |
||
212 | |||
213 | /**
|
||
214 | * Based in code from JUMP (VividSolutions) and Geotools Things to be aware:
|
||
215 | * We always will use Spatial Tables with Unique ID. IFeature has the same
|
||
216 | * field order than dbLayerDef.getFieldNames()
|
||
217 | 10727 | caballero | *
|
218 | 4748 | fjp | * @param dbLayerDef
|
219 | * @param feat
|
||
220 | * @return
|
||
221 | */
|
||
222 | 8765 | jjdelcerro | public String getSqlInsertFeature(DBLayerDefinition dbLayerDef, |
223 | 12513 | jmvivo | IFeature feat) { |
224 | 4748 | fjp | StringBuffer sqlBuf = new StringBuffer("INSERT INTO " |
225 | 11867 | jmvivo | + dbLayerDef.getComposedTableName() + " (");
|
226 | 4748 | fjp | String sql = null; |
227 | int numAlphanumericFields = dbLayerDef.getFieldNames().length;
|
||
228 | |||
229 | for (int i = 0; i < numAlphanumericFields; i++) { |
||
230 | String name = dbLayerDef.getFieldsDesc()[i].getFieldName();
|
||
231 | // if (cols.contains(name) && (!name.equals(uniqueCol) ||
|
||
232 | // existsUnique)) {
|
||
233 | if (name.equals(dbLayerDef.getFieldID()))
|
||
234 | continue;
|
||
235 | sqlBuf.append(" " + name + ","); |
||
236 | // }
|
||
237 | } |
||
238 | sqlBuf.append(" " + dbLayerDef.getFieldGeometry());
|
||
239 | // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
240 | sqlBuf.append(" ) VALUES (");
|
||
241 | String insertQueryHead = sqlBuf.toString();
|
||
242 | sqlBuf = new StringBuffer(insertQueryHead); |
||
243 | for (int j = 0; j < numAlphanumericFields; j++) { |
||
244 | String name = dbLayerDef.getFieldsDesc()[j].getFieldName();
|
||
245 | if (name.equals(dbLayerDef.getFieldID()))
|
||
246 | continue;
|
||
247 | |||
248 | if (isNumeric(feat.getAttribute(j)))
|
||
249 | sqlBuf.append(feat.getAttribute(j) + ", ");
|
||
250 | else
|
||
251 | sqlBuf.append(addQuotes(feat.getAttribute(j)) + ", ");
|
||
252 | } |
||
253 | 10727 | caballero | |
254 | 4748 | fjp | sqlBuf.append(" GeometryFromText( '"
|
255 | + feat.getGeometry().toJTSGeometry().toText() + "', "
|
||
256 | + dbLayerDef.getSRID_EPSG() + ")");
|
||
257 | |||
258 | // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
259 | sqlBuf.append(" ) ");
|
||
260 | sql = sqlBuf.toString(); |
||
261 | return sql;
|
||
262 | } |
||
263 | |||
264 | 8765 | jjdelcerro | public String getSqlCreateIndex(DBLayerDefinition lyrDef) { |
265 | 4748 | fjp | String indexName = lyrDef.getTableName() + "_" |
266 | + lyrDef.getFieldGeometry() + "_gist";
|
||
267 | String sql = "CREATE INDEX \"" + indexName + "\" ON \"" |
||
268 | 12513 | jmvivo | + lyrDef.getComposedTableName() + "\" USING GIST (\""
|
269 | 4748 | fjp | + lyrDef.getFieldGeometry() + "\" GIST_GEOMETRY_OPS)";
|
270 | |||
271 | return sql;
|
||
272 | } |
||
273 | |||
274 | 8765 | jjdelcerro | public String getSqlModifyFeature(DBLayerDefinition dbLayerDef, IFeature feat) { |
275 | 10727 | caballero | /*
|
276 | 4748 | fjp | UPDATE weather
|
277 | SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
|
||
278 | WHERE date > '1994-11-28';
|
||
279 | */
|
||
280 | StringBuffer sqlBuf = new StringBuffer("UPDATE " |
||
281 | 11867 | jmvivo | + dbLayerDef.getComposedTableName() + " SET");
|
282 | 4748 | fjp | String sql = null; |
283 | int numAlphanumericFields = dbLayerDef.getFieldsDesc().length;
|
||
284 | |||
285 | for (int i = 0; i < numAlphanumericFields; i++) { |
||
286 | FieldDescription fldDesc = dbLayerDef.getFieldsDesc()[i]; |
||
287 | if (fldDesc != null) |
||
288 | { |
||
289 | String name = fldDesc.getFieldName();
|
||
290 | 4799 | fjp | // El campo gid no lo actualizamos.
|
291 | if (name.equalsIgnoreCase(dbLayerDef.getFieldID()))
|
||
292 | continue;
|
||
293 | 4748 | fjp | Value val = feat.getAttribute(i); |
294 | if (val != null) |
||
295 | { |
||
296 | String strAux = val.getStringValue(ValueWriter.internalValueWriter);
|
||
297 | sqlBuf.append(" " + name + " = " + strAux + " ,"); |
||
298 | } |
||
299 | } |
||
300 | } |
||
301 | sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
302 | if (feat.getGeometry() != null) |
||
303 | { |
||
304 | 10727 | caballero | sqlBuf.append(", " + dbLayerDef.getFieldGeometry());
|
305 | 4748 | fjp | sqlBuf.append(" = ");
|
306 | sqlBuf.append(" GeometryFromText( '"
|
||
307 | + feat.getGeometry().toJTSGeometry().toText() + "', "
|
||
308 | + dbLayerDef.getSRID_EPSG() + ")");
|
||
309 | } |
||
310 | sqlBuf.append(" WHERE ");
|
||
311 | sqlBuf.append(dbLayerDef.getFieldID() + " = " + feat.getID());
|
||
312 | sql = sqlBuf.toString(); |
||
313 | return sql;
|
||
314 | |||
315 | } |
||
316 | |||
317 | 4799 | fjp | /**
|
318 | * TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
|
||
319 | 10727 | caballero | * Esto provocar? errores, ya que getID que viene en un row no
|
320 | 4799 | fjp | * nos sirve dentro de un writer para modificar y/o borrar entidades
|
321 | * Por ahora, cojo el ID del campo que me indica el dbLayerDef
|
||
322 | * @param dbLayerDef
|
||
323 | * @param row
|
||
324 | * @return
|
||
325 | */
|
||
326 | 8765 | jjdelcerro | public String getSqlDeleteFeature(DBLayerDefinition dbLayerDef, IRow row) { |
327 | 4748 | fjp | // DELETE FROM weather WHERE city = 'Hayward';
|
328 | 4799 | fjp | // TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
|
329 | 10727 | caballero | // Esto provocar? errores, ya que getID que viene en un row no
|
330 | 4799 | fjp | // nos sirve dentro de un writer para modificar y/o borrar entidades
|
331 | // Por ahora, cojo el ID del campo que me indica el dbLayerDev
|
||
332 | StringBuffer sqlBuf = new StringBuffer("DELETE FROM " |
||
333 | 11867 | jmvivo | + dbLayerDef.getComposedTableName() + " WHERE ");
|
334 | 4748 | fjp | String sql = null; |
335 | 4799 | fjp | int indexFieldId = dbLayerDef.getIdFieldID();
|
336 | sqlBuf.append(dbLayerDef.getFieldID() + " = " + row.getAttribute(indexFieldId));
|
||
337 | 4748 | fjp | sql = sqlBuf.toString(); |
338 | |||
339 | return sql;
|
||
340 | } |
||
341 | |||
342 | 8765 | jjdelcerro | public String getEncoding() { |
343 | return toEncode;
|
||
344 | } |
||
345 | 10727 | caballero | public void setEncoding(String toEncode){ |
346 | if (toEncode.compareToIgnoreCase("SQL_ASCII") == 0){ |
||
347 | this.toEncode = "ASCII"; |
||
348 | } else {
|
||
349 | this.toEncode = toEncode;
|
||
350 | } |
||
351 | 8765 | jjdelcerro | } |
352 | |||
353 | 4748 | fjp | } |