root / trunk / extensions / extJDBC / src / com / iver / cit / gvsig / fmap / drivers / jdbc / postgis / PostGIS.java @ 7161
History | View | Annotate | Download (10.1 KB)
1 | 4748 | fjp | /*
|
---|---|---|---|
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.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 | 7161 | fjp | /**
|
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 | 7161 | fjp | private String toEncode; |
69 | |||
70 | 4748 | fjp | /**
|
71 | * Mover esto a IverUtiles
|
||
72 | *
|
||
73 | * @param val
|
||
74 | * @return
|
||
75 | */
|
||
76 | 7161 | fjp | 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 | * @param bCreateGID @DEPRECATED
|
||
97 | * @return
|
||
98 | 4748 | fjp | */
|
99 | 7161 | fjp | 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 | resul = "CREATE TABLE " + dbLayerDef.getTableName()
|
||
115 | 6908 | jorpiell | + " (gid 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 | 5595 | fjp | if (fieldsDescr[i].getFieldName().equalsIgnoreCase("gid")) |
125 | continue;
|
||
126 | |||
127 | 6908 | jorpiell | resul = resul + ", " + dbLayerDef.getFieldNames()[i] + " " + strType; |
128 | 5595 | fjp | j++; |
129 | 4748 | fjp | } |
130 | resul = resul + ");";
|
||
131 | return resul;
|
||
132 | } |
||
133 | |||
134 | 7161 | fjp | public String getSqlAlterTable(DBLayerDefinition dbLayerDef) { |
135 | 4748 | fjp | String strGeometryFieldType;
|
136 | strGeometryFieldType = "GEOMETRY";
|
||
137 | |||
138 | switch (dbLayerDef.getShapeType()) {
|
||
139 | case FShape.POINT:
|
||
140 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POINT2D); |
||
141 | break;
|
||
142 | case FShape.LINE:
|
||
143 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.LINE2D); |
||
144 | break;
|
||
145 | case FShape.POLYGON:
|
||
146 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.POLYGON2D); |
||
147 | break;
|
||
148 | case FShape.MULTI:
|
||
149 | strGeometryFieldType = XTypes.fieldTypeToString(XTypes.MULTI2D); |
||
150 | break;
|
||
151 | } |
||
152 | |||
153 | String result = "SELECT AddGeometryColumn('" |
||
154 | + dbLayerDef.getCatalogName() + "', '"
|
||
155 | + dbLayerDef.getTableName() + "', '"
|
||
156 | + dbLayerDef.getFieldGeometry() + "', "
|
||
157 | + dbLayerDef.getSRID_EPSG() + ", '" + strGeometryFieldType + "', " |
||
158 | + dbLayerDef.getDimension() + ");";
|
||
159 | |||
160 | return result;
|
||
161 | } |
||
162 | |||
163 | /**
|
||
164 | * From geotools Adds quotes to an object for storage in postgis. The object
|
||
165 | * should be a string or a number. To perform an insert strings need quotes
|
||
166 | * around them, and numbers work fine with quotes, so this method can be
|
||
167 | * called on unknown objects.
|
||
168 | *
|
||
169 | * @param value
|
||
170 | * The object to add quotes to.
|
||
171 | *
|
||
172 | * @return a string representation of the object with quotes.
|
||
173 | */
|
||
174 | 7161 | fjp | protected String addQuotes(Object value) { |
175 | 4748 | fjp | String retString;
|
176 | |||
177 | if (value != null) { |
||
178 | if (value instanceof NullValue) |
||
179 | retString = "null";
|
||
180 | else
|
||
181 | retString = "'" + doubleQuote(value) + "'"; |
||
182 | |||
183 | } else {
|
||
184 | retString = "null";
|
||
185 | } |
||
186 | |||
187 | return retString;
|
||
188 | } |
||
189 | |||
190 | 7161 | fjp | private String doubleQuote(Object obj) { |
191 | 4748 | fjp | String aux = obj.toString().replaceAll("'", "''"); |
192 | StringBuffer strBuf = new StringBuffer(aux); |
||
193 | ByteArrayOutputStream out = new ByteArrayOutputStream(strBuf.length()); |
||
194 | PrintStream printStream = new PrintStream(out); |
||
195 | printStream.print(aux); |
||
196 | String aux2 = "ERROR"; |
||
197 | try {
|
||
198 | aux2 = out.toString(toEncode); |
||
199 | System.out.println(aux + " " + aux2); |
||
200 | } catch (UnsupportedEncodingException e) { |
||
201 | // TODO Auto-generated catch block
|
||
202 | e.printStackTrace(); |
||
203 | } |
||
204 | |||
205 | return aux2;
|
||
206 | } |
||
207 | |||
208 | /**
|
||
209 | * Based in code from JUMP (VividSolutions) and Geotools Things to be aware:
|
||
210 | * We always will use Spatial Tables with Unique ID. IFeature has the same
|
||
211 | * field order than dbLayerDef.getFieldNames()
|
||
212 | *
|
||
213 | * @param dbLayerDef
|
||
214 | * @param feat
|
||
215 | * @return
|
||
216 | */
|
||
217 | 7161 | fjp | public String getSqlInsertFeature(DBLayerDefinition dbLayerDef, |
218 | 4748 | fjp | IFeature feat) { |
219 | StringBuffer sqlBuf = new StringBuffer("INSERT INTO " |
||
220 | + dbLayerDef.getTableName() + " (");
|
||
221 | String sql = null; |
||
222 | int numAlphanumericFields = dbLayerDef.getFieldNames().length;
|
||
223 | |||
224 | for (int i = 0; i < numAlphanumericFields; i++) { |
||
225 | String name = dbLayerDef.getFieldsDesc()[i].getFieldName();
|
||
226 | // if (cols.contains(name) && (!name.equals(uniqueCol) ||
|
||
227 | // existsUnique)) {
|
||
228 | if (name.equals(dbLayerDef.getFieldID()))
|
||
229 | continue;
|
||
230 | sqlBuf.append(" " + name + ","); |
||
231 | // }
|
||
232 | } |
||
233 | sqlBuf.append(" " + dbLayerDef.getFieldGeometry());
|
||
234 | // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
235 | sqlBuf.append(" ) VALUES (");
|
||
236 | String insertQueryHead = sqlBuf.toString();
|
||
237 | sqlBuf = new StringBuffer(insertQueryHead); |
||
238 | for (int j = 0; j < numAlphanumericFields; j++) { |
||
239 | String name = dbLayerDef.getFieldsDesc()[j].getFieldName();
|
||
240 | if (name.equals(dbLayerDef.getFieldID()))
|
||
241 | continue;
|
||
242 | |||
243 | if (isNumeric(feat.getAttribute(j)))
|
||
244 | sqlBuf.append(feat.getAttribute(j) + ", ");
|
||
245 | else
|
||
246 | sqlBuf.append(addQuotes(feat.getAttribute(j)) + ", ");
|
||
247 | } |
||
248 | |||
249 | sqlBuf.append(" GeometryFromText( '"
|
||
250 | + feat.getGeometry().toJTSGeometry().toText() + "', "
|
||
251 | + dbLayerDef.getSRID_EPSG() + ")");
|
||
252 | |||
253 | // sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
254 | sqlBuf.append(" ) ");
|
||
255 | sql = sqlBuf.toString(); |
||
256 | return sql;
|
||
257 | } |
||
258 | |||
259 | 7161 | fjp | public String getSqlCreateIndex(DBLayerDefinition lyrDef) { |
260 | 4748 | fjp | String indexName = lyrDef.getTableName() + "_" |
261 | + lyrDef.getFieldGeometry() + "_gist";
|
||
262 | String sql = "CREATE INDEX \"" + indexName + "\" ON \"" |
||
263 | + lyrDef.getTableName() + "\" USING GIST (\""
|
||
264 | + lyrDef.getFieldGeometry() + "\" GIST_GEOMETRY_OPS)";
|
||
265 | |||
266 | return sql;
|
||
267 | } |
||
268 | |||
269 | 7161 | fjp | public String getSqlModifyFeature(DBLayerDefinition dbLayerDef, IFeature feat) { |
270 | 4748 | fjp | /*
|
271 | UPDATE weather
|
||
272 | SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2
|
||
273 | WHERE date > '1994-11-28';
|
||
274 | */
|
||
275 | StringBuffer sqlBuf = new StringBuffer("UPDATE " |
||
276 | + dbLayerDef.getTableName() + " SET");
|
||
277 | String sql = null; |
||
278 | int numAlphanumericFields = dbLayerDef.getFieldsDesc().length;
|
||
279 | |||
280 | for (int i = 0; i < numAlphanumericFields; i++) { |
||
281 | FieldDescription fldDesc = dbLayerDef.getFieldsDesc()[i]; |
||
282 | if (fldDesc != null) |
||
283 | { |
||
284 | String name = fldDesc.getFieldName();
|
||
285 | 4799 | fjp | // El campo gid no lo actualizamos.
|
286 | if (name.equalsIgnoreCase(dbLayerDef.getFieldID()))
|
||
287 | continue;
|
||
288 | 4748 | fjp | Value val = feat.getAttribute(i); |
289 | if (val != null) |
||
290 | { |
||
291 | String strAux = val.getStringValue(ValueWriter.internalValueWriter);
|
||
292 | sqlBuf.append(" " + name + " = " + strAux + " ,"); |
||
293 | } |
||
294 | } |
||
295 | } |
||
296 | sqlBuf.deleteCharAt(sqlBuf.lastIndexOf(","));
|
||
297 | if (feat.getGeometry() != null) |
||
298 | { |
||
299 | sqlBuf.append(", " + dbLayerDef.getFieldGeometry());
|
||
300 | sqlBuf.append(" = ");
|
||
301 | sqlBuf.append(" GeometryFromText( '"
|
||
302 | + feat.getGeometry().toJTSGeometry().toText() + "', "
|
||
303 | + dbLayerDef.getSRID_EPSG() + ")");
|
||
304 | } |
||
305 | sqlBuf.append(" WHERE ");
|
||
306 | sqlBuf.append(dbLayerDef.getFieldID() + " = " + feat.getID());
|
||
307 | sql = sqlBuf.toString(); |
||
308 | return sql;
|
||
309 | |||
310 | } |
||
311 | |||
312 | 4799 | fjp | /**
|
313 | * TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
|
||
314 | * Esto provocar? errores, ya que getID que viene en un row no
|
||
315 | * nos sirve dentro de un writer para modificar y/o borrar entidades
|
||
316 | * Por ahora, cojo el ID del campo que me indica el dbLayerDef
|
||
317 | * @param dbLayerDef
|
||
318 | * @param row
|
||
319 | * @return
|
||
320 | */
|
||
321 | 7161 | fjp | public String getSqlDeleteFeature(DBLayerDefinition dbLayerDef, IRow row) { |
322 | 4748 | fjp | // DELETE FROM weather WHERE city = 'Hayward';
|
323 | 4799 | fjp | // TODO: NECESITAMOS OTRO M?TODO PARA BORRAR CORRECTAMENTE.
|
324 | // Esto provocar? errores, ya que getID que viene en un row no
|
||
325 | // nos sirve dentro de un writer para modificar y/o borrar entidades
|
||
326 | // Por ahora, cojo el ID del campo que me indica el dbLayerDev
|
||
327 | StringBuffer sqlBuf = new StringBuffer("DELETE FROM " |
||
328 | 4748 | fjp | + dbLayerDef.getTableName() + " WHERE ");
|
329 | String sql = null; |
||
330 | 4799 | fjp | int indexFieldId = dbLayerDef.getIdFieldID();
|
331 | sqlBuf.append(dbLayerDef.getFieldID() + " = " + row.getAttribute(indexFieldId));
|
||
332 | 4748 | fjp | sql = sqlBuf.toString(); |
333 | |||
334 | return sql;
|
||
335 | } |
||
336 | |||
337 | 7161 | fjp | public String getEncoding() { |
338 | return toEncode;
|
||
339 | } |
||
340 | public void setEncoding(String toEncode) |
||
341 | { |
||
342 | this.toEncode = toEncode;
|
||
343 | } |
||
344 | |||
345 | 4748 | fjp | } |