Revision 32679 branches/v2_0_0_prep/libraries/org.gvsig.fmap.dal.db.h2spatial/src/main/java/org/gvsig/fmap/dal/db/h2spatial/impl/H2SpatialHelper.java
| H2SpatialHelper.java | ||
|---|---|---|
| 7 | 7 |
import java.sql.SQLException; |
| 8 | 8 |
import java.sql.Statement; |
| 9 | 9 |
import java.util.ArrayList; |
| 10 |
import java.util.Comparator; |
|
| 10 | 11 |
import java.util.Iterator; |
| 11 | 12 |
import java.util.List; |
| 12 | 13 |
import java.util.Map; |
| 13 | 14 |
import java.util.TreeMap; |
| 15 |
import java.util.TreeSet; |
|
| 14 | 16 |
|
| 17 |
import net.sourceforge.hatbox.MetaNode; |
|
| 15 | 18 |
import net.sourceforge.hatbox.RTreeDml; |
| 16 | 19 |
import net.sourceforge.hatbox.RTreeSessionDb; |
| 17 | 20 |
|
| ... | ... | |
| 38 | 41 |
import org.gvsig.fmap.dal.store.jdbc.JDBCHelperUser; |
| 39 | 42 |
import org.gvsig.fmap.dal.store.jdbc.JDBCStoreParameters; |
| 40 | 43 |
import org.gvsig.fmap.dal.store.jdbc.TransactionalAction; |
| 44 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCException; |
|
| 41 | 45 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecutePreparedSQLException; |
| 42 | 46 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCExecuteSQLException; |
| 43 | 47 |
import org.gvsig.fmap.dal.store.jdbc.exception.JDBCPreparingSQLException; |
| ... | ... | |
| 51 | 55 |
import org.gvsig.fmap.geom.primitive.Envelope; |
| 52 | 56 |
import org.gvsig.fmap.geom.primitive.Point; |
| 53 | 57 |
import org.gvsig.tools.exception.BaseException; |
| 58 |
import org.postgresql.PGResultSetMetaData; |
|
| 54 | 59 |
import org.slf4j.Logger; |
| 55 | 60 |
import org.slf4j.LoggerFactory; |
| 56 | 61 |
|
| ... | ... | |
| 58 | 63 |
|
| 59 | 64 |
private static Logger logger = LoggerFactory |
| 60 | 65 |
.getLogger(H2SpatialHelper.class); |
| 61 |
private Map srsID2slSR = new TreeMap(); |
|
| 62 |
private Map slSR2SRSID = new TreeMap(); |
|
| 66 |
private String rsid=null; |
|
| 63 | 67 |
private GeometryManager gm; |
| 64 | 68 |
|
| 65 | 69 |
public H2SpatialHelper(JDBCHelperUser consumer, |
| ... | ... | |
| 122 | 126 |
e.printStackTrace(); |
| 123 | 127 |
} |
| 124 | 128 |
return gvSigEnvelope; |
| 125 |
|
|
| 126 |
|
|
| 127 |
// StringBuilder strb = new StringBuilder(); |
|
| 128 |
// strb.append("SELECT ");
|
|
| 129 |
// strb.append(geometryAttrName); |
|
| 130 |
// strb.append(" FROM ");
|
|
| 131 |
// strb.append(storeParams.getTable()); |
|
| 132 |
|
|
| 133 |
|
|
| 134 |
|
|
| 135 |
|
|
| 136 |
|
|
| 137 |
|
|
| 138 |
|
|
| 139 |
|
|
| 140 |
// strb.append("SELECT Min(MbrMinX(");
|
|
| 141 |
// strb.append(geometryAttrName); |
|
| 142 |
// strb.append(")), Min(MbrMinY(");
|
|
| 143 |
// strb.append(geometryAttrName); |
|
| 144 |
// strb.append(")), Max(MbrMaxX(");
|
|
| 145 |
// strb.append(geometryAttrName); |
|
| 146 |
// strb.append(")), Max(MbrMaxY(");
|
|
| 147 |
// strb.append(geometryAttrName); |
|
| 148 |
// strb.append(")) FROM ");
|
|
| 149 |
// strb.append(storeParams.getTable()); |
|
| 150 |
// |
|
| 151 |
// if (limit != null) {
|
|
| 152 |
// strb.append(" WHERE intersects(GeomFromText('");
|
|
| 153 |
// strb.append(limit.toString()); |
|
| 154 |
// strb.append("')), boundary(");
|
|
| 155 |
// strb.append(geometryAttrName); |
|
| 156 |
// strb.append(")) ");
|
|
| 157 |
// } |
|
| 158 |
|
|
| 159 |
// final String sql = strb.toString(); |
|
| 160 |
// |
|
| 161 |
// try {
|
|
| 162 |
// initializeFromWKBOperation(); |
|
| 163 |
// } catch (BaseException e1) {
|
|
| 164 |
// throw new ReadException(this.name, e1); |
|
| 165 |
// } |
|
| 166 |
// |
|
| 167 |
// this.open(); |
|
| 168 |
// |
|
| 169 |
// return (Envelope) getResource().execute(new ResourceAction() {
|
|
| 170 |
// public Object run() throws Exception {
|
|
| 171 |
// ResultSet rs = null; |
|
| 172 |
// Statement st = null; |
|
| 173 |
// Connection conn = null; |
|
| 174 |
// Envelope fullEnvelope = null; |
|
| 175 |
// try {
|
|
| 176 |
// conn = getConnection(); |
|
| 177 |
// st = conn.createStatement(); |
|
| 178 |
// try {
|
|
| 179 |
// rs = st.executeQuery(sql); |
|
| 180 |
// } catch (java.sql.SQLException e) {
|
|
| 181 |
// throw new JDBCExecuteSQLException(sql, e); |
|
| 182 |
// } |
|
| 183 |
// while (rs.next()) {
|
|
| 184 |
// byte[] bytes = rs.getBytes(1); |
|
| 185 |
// Geometry geom=getGeometry(bytes); |
|
| 186 |
// if (fullEnvelope==null) |
|
| 187 |
// fullEnvelope = geom.getEnvelope(); |
|
| 188 |
// else |
|
| 189 |
// fullEnvelope.add(geom.getEnvelope()); |
|
| 190 |
// } |
|
| 191 |
// |
|
| 192 |
// return fullEnvelope; |
|
| 193 |
// } catch (java.sql.SQLException e) {
|
|
| 194 |
// throw new JDBCSQLException(e); |
|
| 195 |
// } catch (BaseException e) {
|
|
| 196 |
// throw new ReadException(user.getName(), e); |
|
| 197 |
// } finally {
|
|
| 198 |
// try {
|
|
| 199 |
// rs.close(); |
|
| 200 |
// } catch (Exception e) {
|
|
| 201 |
// } |
|
| 202 |
// try {
|
|
| 203 |
// st.close(); |
|
| 204 |
// } catch (Exception e) {
|
|
| 205 |
// } |
|
| 206 |
// try {
|
|
| 207 |
// conn.close(); |
|
| 208 |
// } catch (Exception e) {
|
|
| 209 |
// } |
|
| 210 |
// rs = null; |
|
| 211 |
// st = null; |
|
| 212 |
// conn = null; |
|
| 213 |
// } |
|
| 214 |
// } |
|
| 215 |
// }); |
|
| 216 | 129 |
} |
| 217 | 130 |
|
| 218 | 131 |
public String getSqlColumnTypeDescription(FeatureAttributeDescriptor attr) {
|
| ... | ... | |
| 285 | 198 |
.getColumnName(colIndex), DataTypes.GEOMETRY); |
| 286 | 199 |
efad.setGeometrySubType(Geometry.SUBTYPES.GEOM2D); |
| 287 | 200 |
efad.setGeometryType(Geometry.TYPES.GEOMETRY); |
| 201 |
if (rsid!=null) |
|
| 202 |
efad.setSRS(CRSFactory.getCRS(rsid)); |
|
| 288 | 203 |
return efad; |
| 289 | 204 |
} |
| 290 | 205 |
// } |
| ... | ... | |
| 345 | 260 |
} |
| 346 | 261 |
|
| 347 | 262 |
public int getProviderSRID(String srs) {
|
| 348 |
if (srs != null) {
|
|
| 349 |
Integer slSRID = (Integer) srsID2slSR.get(srs); |
|
| 350 |
if (slSRID != null) {
|
|
| 351 |
return slSRID.intValue(); |
|
| 352 |
} |
|
| 353 |
|
|
| 354 |
return searchslSRID(srs); |
|
| 355 |
|
|
| 356 |
} |
|
| 263 |
// if (srs != null) {
|
|
| 264 |
// Integer slSRID = (Integer) srsID2slSR.get(srs);
|
|
| 265 |
// if (slSRID != null) {
|
|
| 266 |
// return slSRID.intValue();
|
|
| 267 |
// }
|
|
| 268 |
// |
|
| 269 |
// return searchslSRID(srs);
|
|
| 270 |
// |
|
| 271 |
// }
|
|
| 357 | 272 |
return -1; |
| 358 | 273 |
} |
| 359 | 274 |
|
| 360 |
private int searchslSRID(final IProjection srs) {
|
|
| 361 |
if (srs == null) {
|
|
| 362 |
return -1; |
|
| 363 |
} |
|
| 364 |
return searchslSRID(srs.getAbrev()); |
|
| 365 |
} |
|
| 366 |
|
|
| 367 |
private int searchslSRID(final String srsID) {
|
|
| 368 |
if (srsID == null) {
|
|
| 369 |
return -1; |
|
| 370 |
} |
|
| 371 |
|
|
| 372 |
ConnectionAction action = new ConnectionAction() {
|
|
| 373 |
|
|
| 374 |
public Object action(Connection conn) throws DataException {
|
|
| 375 |
// select srid from spatial_ref_sys where auth_name = 'EPSG' and |
|
| 376 |
// auth_srid = 23030 |
|
| 377 |
String[] abrev = srsID.split(":");
|
|
| 378 |
StringBuilder sqlb = new StringBuilder(); |
|
| 379 |
sqlb.append("select srid from spatial_ref_sys where ");
|
|
| 380 |
if (abrev.length > 1) {
|
|
| 381 |
sqlb.append("auth_name = ? and ");
|
|
| 382 |
} |
|
| 383 |
sqlb.append("auth_srid = ?");
|
|
| 384 |
|
|
| 385 |
String sql = sqlb.toString(); |
|
| 386 |
PreparedStatement st; |
|
| 387 |
try {
|
|
| 388 |
st = conn.prepareStatement(sql); |
|
| 389 |
} catch (SQLException e) {
|
|
| 390 |
throw new JDBCPreparingSQLException(sql, e); |
|
| 391 |
} |
|
| 392 |
ResultSet rs = null; |
|
| 393 |
try {
|
|
| 394 |
int i = 0; |
|
| 395 |
if (abrev.length > 1) {
|
|
| 396 |
st.setString(i + 1, abrev[i]); |
|
| 397 |
i++; |
|
| 398 |
} |
|
| 399 |
st.setInt(i + 1, Integer.parseInt(abrev[i])); |
|
| 400 |
|
|
| 401 |
try {
|
|
| 402 |
rs = st.executeQuery(); |
|
| 403 |
} catch (SQLException e) {
|
|
| 404 |
throw new JDBCExecutePreparedSQLException(sql, abrev, e); |
|
| 405 |
} |
|
| 406 |
|
|
| 407 |
if (!rs.next()) {
|
|
| 408 |
return null; |
|
| 409 |
} |
|
| 410 |
|
|
| 411 |
return new Integer(rs.getInt(1)); |
|
| 412 |
|
|
| 413 |
} catch (SQLException e) {
|
|
| 414 |
throw new JDBCSQLException(e); |
|
| 415 |
} finally {
|
|
| 416 |
try {
|
|
| 417 |
rs.close(); |
|
| 418 |
} catch (Exception e) {
|
|
| 419 |
} |
|
| 420 |
; |
|
| 421 |
try {
|
|
| 422 |
st.close(); |
|
| 423 |
} catch (Exception e) {
|
|
| 424 |
} |
|
| 425 |
; |
|
| 426 |
} |
|
| 427 |
|
|
| 428 |
} |
|
| 429 |
|
|
| 430 |
}; |
|
| 431 |
|
|
| 432 |
Integer slSRSID = null; |
|
| 433 |
try {
|
|
| 434 |
slSRSID = (Integer) doConnectionAction(action); |
|
| 435 |
} catch (Exception e) {
|
|
| 436 |
logger.error("Excetion searching slSRS", e);
|
|
| 437 |
return -1; |
|
| 438 |
} |
|
| 439 |
|
|
| 440 |
if (slSRSID != null) {
|
|
| 441 |
addToSlSRToSRSID(slSRSID.intValue(), srsID); |
|
| 442 |
return slSRSID.intValue(); |
|
| 443 |
} |
|
| 444 |
return -1; |
|
| 445 |
|
|
| 446 |
} |
|
| 447 |
|
|
| 448 |
private void addToSlSRToSRSID(int slSRID, String srsId) {
|
|
| 449 |
if (slSRID < 0 || srsId == null || srsId.length() == 0) {
|
|
| 450 |
return; |
|
| 451 |
} |
|
| 452 |
Integer slSRIDInteger = new Integer(slSRID); |
|
| 453 |
slSR2SRSID.put(slSRIDInteger, srsId); |
|
| 454 |
srsID2slSR.put(srsId, slSRIDInteger); |
|
| 455 |
} |
|
| 456 |
|
|
| 457 | 275 |
public int getH2SpatialGeomDimensions(int geometrySubType) {
|
| 458 | 276 |
switch (geometrySubType) {
|
| 459 | 277 |
case Geometry.SUBTYPES.GEOM2D: |
| ... | ... | |
| 473 | 291 |
|
| 474 | 292 |
public List getSqlGeometyFieldAdd(FeatureAttributeDescriptor attr, |
| 475 | 293 |
String table, String schema) {
|
| 476 |
// SELECT AddGeometryColumn({schema}, {table}, {field}, {srid}(int),
|
|
| 477 |
// {geomType}(Str), {dimensions}(int))
|
|
| 478 |
|
|
| 479 |
// gemoType: |
|
| 480 |
/* |
|
| 481 |
* POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, |
|
| 482 |
* MULTIPOLYGON, GEOMETRYCOLLECTION POINTM, LINESTRINGM, POLYGONM, |
|
| 483 |
* MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM, GEOMETRYCOLLECTIONM |
|
| 484 |
*/ |
|
| 485 |
|
|
| 486 | 294 |
List sqls = new ArrayList(); |
| 487 |
// |
|
| 488 |
// StringBuilder strb = new StringBuilder(); |
|
| 489 |
// strb.append("SELECT AddGeometryColumn('");
|
|
| 490 |
// if (schema != null && schema.length() > 0) {
|
|
| 491 |
// strb.append(schema); |
|
| 492 |
// strb.append("', '");
|
|
| 493 |
// } |
|
| 494 |
// strb.append(table); |
|
| 495 |
// strb.append("', '");
|
|
| 496 |
// strb.append(attr.getName()); |
|
| 497 |
// strb.append("', ");
|
|
| 498 |
// // strb.append("-1");
|
|
| 499 |
// strb.append(getProviderSRID(attr.getSRS())); |
|
| 500 |
// strb.append(", '");
|
|
| 501 |
// strb.append(getH2SpatialGeomType(attr.getGeometryType(), attr |
|
| 502 |
// .getGeometrySubType())); |
|
| 503 |
// strb.append("', ");
|
|
| 504 |
// strb.append(getH2SpatialGeomDimensions(attr.getGeometrySubType())); |
|
| 505 |
// strb.append(")");
|
|
| 506 |
// |
|
| 507 |
// sqls.add(strb.toString()); |
|
| 508 |
|
|
| 509 |
|
|
| 510 |
|
|
| 511 |
|
|
| 512 |
// StringBuffer sql = new StringBuffer(); |
|
| 513 |
// sql.append( "ALTER TABLE "); |
|
| 514 |
// sql.append(table); |
|
| 515 |
//// sql.append( " ADD CONSTRAINT " ); |
|
| 516 |
//// encodeTableName( tableName + "_"+propertyName + "GeometryType", sql ); |
|
| 517 |
//// sql.append( " CHECK "); |
|
| 518 |
//// encodeColumnName( propertyName, sql ); |
|
| 519 |
//// sql.append( " IS NULL OR"); |
|
| 520 |
// sql.append( " GeometryType(");
|
|
| 521 |
// sql.append(getH2SpatialGeomType(attr.getGeometryType(), attr |
|
| 522 |
// .getGeometrySubType())); |
|
| 523 |
// sql.append( ") = '").append( getSqlFieldName(attr)).append( "'"); |
|
| 524 |
// |
|
| 525 |
// sqls.add( sql.toString() ); |
|
| 526 |
|
|
| 527 |
//create a spatial index |
|
| 528 |
// CoordinateReferenceSystem crs = gd.getCoordinateReferenceSystem(); |
|
| 529 |
// if (crs == null) {
|
|
| 530 |
// continue; |
|
| 531 |
// } |
|
| 532 |
|
|
| 533 |
// Integer epsg = null; |
|
| 534 |
// String abrev= attr.getSRS().getAbrev(); |
|
| 535 |
// try{
|
|
| 536 |
// epsg =Integer.parseInt(abrev.substring(abrev.indexOf(":")+1, abrev.length()));
|
|
| 537 |
// }catch (Exception e) {
|
|
| 538 |
// e.printStackTrace(); |
|
| 539 |
// } |
|
| 540 |
// if (epsg != null) {
|
|
| 541 |
// sql = new StringBuffer(); |
|
| 542 |
// sql.append("CALL CreateSpatialIndex(");
|
|
| 543 |
// if (schema == null) {
|
|
| 544 |
// sql.append("NULL");
|
|
| 545 |
// } |
|
| 546 |
// else {
|
|
| 547 |
// sql.append("'").append(schema).append("'");
|
|
| 548 |
// } |
|
| 549 |
// |
|
| 550 |
// sql.append(",'").append(table).append("'");
|
|
| 551 |
//// sql.append(",'").append(propertyName).append("'");
|
|
| 552 |
// sql.append(",'").append(epsg).append("')");
|
|
| 553 |
// |
|
| 554 |
// sqls.add(sql.toString()); |
|
| 555 |
// } |
|
| 556 |
return sqls; |
|
| 557 |
|
|
| 295 |
return sqls; |
|
| 558 | 296 |
} |
| 559 | 297 |
|
| 560 | 298 |
|
| 561 |
|
|
| 562 |
|
|
| 563 |
|
|
| 564 |
|
|
| 565 |
|
|
| 566 |
|
|
| 567 | 299 |
public List getAdditionalSqlToCreate(NewDataStoreParameters ndsp, |
| 568 | 300 |
FeatureType fType) {
|
| 569 | 301 |
FeatureAttributeDescriptor attr; |
| ... | ... | |
| 580 | 312 |
|
| 581 | 313 |
return result; |
| 582 | 314 |
} |
| 583 |
|
|
| 584 |
/* |
|
| 585 |
* de MYSQL |
|
| 586 |
* |
|
| 587 |
* initializeResource()- getDefaultSchema - getFullEnvelopeOfField - |
|
| 588 |
* initializeFromWKBOperation (no lo pongo hace lo mismo en el padre) |
|
| 589 |
* getGeometry (no lo pongo hace lo mismo en el padre) |
|
| 590 |
* getSqlColumnTypeDescription - getSqlFieldName - createAttributeFromJDBC - |
|
| 591 |
* (he cogido el de postgres) allowAutomaticValues - getSqlFieldDescription |
|
| 592 |
* - supportsUnion - |
|
| 315 |
|
|
| 316 |
/** |
|
| 317 |
* Fill <code>featureType</code> geometry attributes with SRS and ShapeType |
|
| 318 |
* information stored in the table GEOMETRY_COLUMNS |
|
| 319 |
* |
|
| 320 |
* @param conn |
|
| 321 |
* @param rsMetadata |
|
| 322 |
* @param featureType |
|
| 323 |
* @throws ReadException |
|
| 593 | 324 |
*/ |
| 325 |
protected void loadSRS_and_shapeType(Connection conn, |
|
| 326 |
ResultSetMetaData rsMetadata, EditableFeatureType featureType, |
|
| 327 |
String baseSchema, String baseTable) |
|
| 328 |
throws JDBCException {
|
|
| 594 | 329 |
|
| 595 |
/* |
|
| 596 |
* DE PS Q NO ESTAN EN MYSQL |
|
| 597 |
* |
|
| 598 |
* loadSRS_and_shapeType getPostgisGeomDimensions - getPostgisGeomType |
|
| 599 |
* (getSpatiaLiteGeomType) - getProviderSRID(String srs) - |
|
| 600 |
* getProviderSRID(IProjection srs) searchpgSRID(final IProjection srs) |
|
| 601 |
* (searchslSRID) - searchpgSRID(final String srsID) (searchpgSRID) - |
|
| 602 |
* addToPgSRToSRSID (addToSlSRToSRSID) - getSqlGeometyFieldAdd - |
|
| 603 |
* getAdditionalSqlToCreate - |
|
| 604 |
*/ |
|
| 330 |
Statement st = null; |
|
| 331 |
ResultSet rs = null; |
|
| 332 |
try {
|
|
| 333 |
// Sacamos la lista de los attributos geometricos |
|
| 334 |
EditableFeatureAttributeDescriptor attr; |
|
| 335 |
List geoAttrs = new ArrayList(); |
|
| 605 | 336 |
|
| 337 |
Iterator iter = featureType.iterator(); |
|
| 338 |
while (iter.hasNext()) {
|
|
| 339 |
attr = (EditableFeatureAttributeDescriptor) iter.next(); |
|
| 340 |
if (attr.getDataType() == DataTypes.GEOMETRY) {
|
|
| 341 |
geoAttrs.add(attr); |
|
| 342 |
// Preparamos una sql para que nos saque el resultado |
|
| 343 |
StringBuilder strb = new StringBuilder(); |
|
| 344 |
strb.append("select NODE_DATA from \"" + baseSchema + "\".\"" + baseTable.toUpperCase()+"_HATBOX" + "\" where ID = 1");
|
|
| 345 |
|
|
| 346 |
String sql = strb.toString(); |
|
| 347 |
|
|
| 348 |
st = conn.createStatement(); |
|
| 349 |
try {
|
|
| 350 |
rs = st.executeQuery(sql); |
|
| 351 |
} catch (SQLException e) {
|
|
| 352 |
throw new JDBCExecuteSQLException(sql, e); |
|
| 353 |
} |
|
| 354 |
|
|
| 355 |
if (rs.next()) {
|
|
| 356 |
MetaNode metaNode = new MetaNode(rs.getBytes(1)); |
|
| 357 |
String epsg = "EPSG:"+metaNode.getSrid(); |
|
| 358 |
attr.setSRS(CRSFactory.getCRS(epsg)); |
|
| 359 |
rsid=epsg; |
|
| 360 |
} |
|
| 361 |
} |
|
| 362 |
} |
|
| 363 |
if (geoAttrs.size() < 1) {
|
|
| 364 |
return; |
|
| 365 |
} |
|
| 366 |
} catch (java.sql.SQLException e) {
|
|
| 367 |
throw new JDBCSQLException(e); |
|
| 368 |
} finally {
|
|
| 369 |
try {rs.close();} catch (Exception e) { };
|
|
| 370 |
try {st.close();} catch (Exception e) { };
|
|
| 371 |
} |
|
| 372 |
|
|
| 373 |
} |
|
| 606 | 374 |
} |
Also available in: Unified diff