📄 oraclespatialquery.java
字号:
// test
if (!(GM_Aggregate.class).isAssignableFrom(geom.getClass())) {
System.out.println("le GM_Object doit etre un agregat (GM_Aggregate ou sous-classe)");
System.out.println("le calcul de buffer renvoie NULL");
return null;
}
// copie des elements de l'agregat dans la table TEMP_REQUETE
GM_Aggregate aggr = (GM_Aggregate)geom;
aggr.initIterator();
int i=0;
while (aggr.hasNext()) {
i++;
STRUCT str = (STRUCT) GeometryConvertor.GM_Object2Sdo(aggr.next(), conn);
update = "INSERT INTO TEMP_REQUETE VALUES ("+i+",?)";
PreparedStatement ps = conn.prepareStatement(update);
ps.setObject(1, str);
ps.executeUpdate();
ps.close();
}
// calcul des buffers
update = "UPDATE TEMP_REQUETE t SET t.GEOM = (";
update = update+"SELECT SDO_GEOM.SDO_BUFFER(tt.geom, "+radius+","+tolerance+") FROM TEMP_REQUETE tt WHERE tt.gid = t.gid)";
stm.executeQuery(update);
// linearisation des geometries (sinon ca plante !!)
update = "UPDATE TEMP_REQUETE t SET t.GEOM = (";
update = update+"SELECT SDO_GEOM.SDO_ARC_DENSIFY(tt.geom, "+tolerance;
update = update+", 'arc_tolerance=0,1') FROM TEMP_REQUETE tt WHERE tt.gid = t.gid)";
stm.executeQuery(update);
// calcul de l'agregat result de l'union des buffer (c'est ca qui optimise en theorie )
String query = "SELECT SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(t.geom, "+tolerance+")) FROM TEMP_REQUETE t";
ResultSet rs = (ResultSet)stm.executeQuery(query);
while (rs.next())
result = (GM_Object)(GeometryConvertor.Sdo2GM_Object (rs.getObject(1)));
stm.close();
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
//////////////////////////////////////////////////////////////////////////////////////////////////////
/// calcul d'index spatial ///////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////
public static void spatialIndex(Geodatabase data, Class clazz) {
try {
Connection conn = data.getConnection();
Statement stm = conn.createStatement();
String tableName = data.getMetadata(clazz).getTableName().toUpperCase();
String columnName = data.getMetadata(clazz).getGeomColumnName().toUpperCase();
// on cherche si un index spatial existe - a revoir pour la multi-representation
String query = "SELECT INDEX_NAME FROM USER_SDO_INDEX_INFO WHERE TABLE_NAME='"+tableName+"' AND COLUMN_NAME='"+columnName+"'" ;
stm.executeQuery(query);
ResultSet rs = (ResultSet)stm.executeQuery(query);
String indexName = "";
while (rs.next()) {
indexName = rs.getString(1);
}
// creation de l'index
if (indexName.compareTo("") == 0) {
System.out.println("index spatial sur "+tableName+" inexistant...");
// on est oblige de faire ceci, sinon message d'erreur d'Oracle : nom d'index trop long...
if (tableName.length()>24) indexName = tableName.substring(0,24)+"_spidx";
else indexName = tableName+"_spidx";
System.out.println("creation index...");
query = "CREATE INDEX "+indexName+" ON "+tableName+"("+columnName+")";
query=query+"INDEXTYPE IS MDSYS.SPATIAL_INDEX ";
// query=query+"PARAMETERS ('TABLESPACE = USER_IDX')";
stm.executeQuery(query);
System.out.println("index spatial sur "+tableName+" cree (R-Tree) - nom : "+indexName);
} else {
System.out.println("index spatial sur "+tableName+" existant...");
System.out.println("reconstruction index...");
query = "ALTER INDEX "+indexName+" REBUILD";
stm.executeQuery(query);
System.out.println("index spatial sur "+tableName+" reconstruit (R-Tree) - nom : "+indexName);
}
stm.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////
/// calcul d'emprise ///////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////
public static void mbr(Geodatabase data, Class clazz) {
try {
Connection conn = data.getConnection();
Statement stm = conn.createStatement();
String tableName = data.getMetadata(clazz).getTableName().toUpperCase();
String columnName = data.getMetadata(clazz).getGeomColumnName().toUpperCase();
// on recupere l'enveloppe avec la fonction "sdo__aggr_mbr" d'Oracle
GM_Surface rect = null;
String query = "SELECT SDO_AGGR_MBR("+columnName+") FROM "+tableName;
ResultSet rs = (ResultSet)stm.executeQuery(query);
while (rs.next()) {
rect = (GM_Surface) GeometryConvertor.Sdo2GM_Object(rs.getObject(1));
}
// on recupere les coordonnees du rectangle
DirectPositionList theCoord = rect.exteriorCoord();
double Xmin = theCoord.get(0).getX();
double Ymin = theCoord.get(0).getY();
double Xmax = theCoord.get(2).getX();
double Ymax = theCoord.get(2).getY();
// on recupere le DimInfo d'Oracle et le met a jour - revoir pour geometrie multiple
query = "SELECT DIMINFO FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = '"+tableName+"'";
rs = (ResultSet)stm.executeQuery(query);
String sdoDimArrayString = "MDSYS.SDO_DIM_ARRAY(";
ARRAY sqlDiminfo = null;
while (rs.next())
sqlDiminfo = (oracle.sql.ARRAY)rs.getObject(1);
int dim;
// si pas de diminfo, on se met en 2D et on affecte une tolerance par defaut !!!!
if (sqlDiminfo == null) {
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('X', "+Xmin+", "+Xmax+", 0.0000000005),";
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('Y', "+Ymin+", "+Ymax+", 0.0000000005))";
} else {
dim = sqlDiminfo.length();
if (dim == 2) {
ResultSet X = (ResultSet)sqlDiminfo.getResultSet(1,1);
X.next();
STRUCT XX = (STRUCT)X.getObject(2);
Object[] attrX = XX.getAttributes();
double Xtol = ((BigDecimal)attrX[3]).doubleValue();
ResultSet Y = (ResultSet)sqlDiminfo.getResultSet(2,1);
Y.next();
STRUCT YY = (STRUCT)Y.getObject(2);
Object[] attrY = YY.getAttributes();
double Ytol = ((BigDecimal)attrY[3]).doubleValue();
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('X', "+Xmin+", "+Xmax+", "+Xtol+"),";
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('Y', "+Ymin+", "+Ymax+", "+Ytol+"))";
}
if (dim == 3) {
ResultSet X = (ResultSet)sqlDiminfo.getResultSet(1,1);
X.next();
STRUCT XX = (STRUCT)X.getObject(2);
Object[] attrX = XX.getAttributes();
double Xtol = ((BigDecimal)attrX[3]).doubleValue();
ResultSet Y = (ResultSet)sqlDiminfo.getResultSet(2,1);
Y.next();
STRUCT YY = (STRUCT)Y.getObject(2);
Object[] attrY = YY.getAttributes();
double Ytol = ((BigDecimal)attrY[3]).doubleValue();
ResultSet Z = (ResultSet)sqlDiminfo.getResultSet(3,1);
Z.next();
STRUCT ZZ = (STRUCT)Z.getObject(2);
Object[] attrZ = ZZ.getAttributes();
double Ztol = ((BigDecimal)attrZ[3]).doubleValue();
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('X', "+Xmin+", "+Xmax+", "+Xtol+"),";
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('Y', "+Ymin+", "+Ymax+", "+Ytol+"),";
sdoDimArrayString=sdoDimArrayString+"MDSYS.SDO_DIM_ELEMENT('Z', -1000.0, 10000.0, "+Ztol+"))";
}
}
// on ecrit le resultat de la mise a jour dans Oracle - revoir pour les geometries multiples
String update = "UPDATE USER_SDO_GEOM_METADATA SET DIMINFO = "+sdoDimArrayString+" WHERE TABLE_NAME = '"+tableName+"'";;
stm.executeUpdate(update);
// close and commit
System.out.println(tableName+" : emprise de la table renseignee");
stm.close();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////
/// intialisation des metadonnees ////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////
public static void initGeomMetadata (List metadataList, Connection conn) {
try {
int n = metadataList.size();
Statement stm = conn.createStatement();
String query = "SELECT TABLE_NAME, COLUMN_NAME, DIMINFO, SRID FROM USER_SDO_GEOM_METADATA";
ResultSet rs = (ResultSet)stm.executeQuery(query);
while (rs.next()) {
String sqlTableName = rs.getString(1);
for (int i=0; i<n; i++) {
String arrayTableName = ((Metadata)metadataList.get(i)).getTableName();
if (arrayTableName != null) // ceci car pour les classes abstraites, pas de table name
// On compare le nom de table de user_sdo_geom_metadata et le nom de table issu du mapping
if (sqlTableName.compareToIgnoreCase(arrayTableName) == 0) {
Metadata metadataElt = (Metadata)metadataList.get(i);
// colonne portant la geometrie
String sqlGeomcolumn = rs.getString(2);
metadataElt.setGeomColumnName(sqlGeomcolumn);
// SRID
// int sqlSRID = rs.getInt(4);
// metadataElt.setSRID(sqlSRID);
// DimInfo -> pour la tolerance et l'enveloppe
oracle.sql.ARRAY sqlDiminfo = (oracle.sql.ARRAY)rs.getObject(3);
if (sqlDiminfo != null) {
int dim = sqlDiminfo.length();
// on est en 2D
if (dim == 2) {
metadataElt.setDimension(2);
metadataElt.setTolerance(new double[2]);
ResultSet X = (ResultSet)sqlDiminfo.getResultSet(1,1);
X.next();
oracle.sql.STRUCT XX = (oracle.sql.STRUCT)X.getObject(2);
Object[] attrX = XX.getAttributes();
double Xmin = ((BigDecimal)attrX[1]).doubleValue();
double Xmax = ((BigDecimal)attrX[2]).doubleValue();
metadataElt.setTolerance(0,((BigDecimal)attrX[3]).doubleValue());
ResultSet Y = (ResultSet)sqlDiminfo.getResultSet(2,1);
Y.next();
oracle.sql.STRUCT YY = (oracle.sql.STRUCT)Y.getObject(2);
Object[] attrY = YY.getAttributes();
double Ymin = ((BigDecimal)attrY[1]).doubleValue();
double Ymax = ((BigDecimal)attrY[2]).doubleValue();
DirectPosition UpperCorner = new DirectPosition (Xmax,Ymax);
DirectPosition LowerCorner = new DirectPosition (Xmin,Ymin);
GM_Envelope theEnvelope = new GM_Envelope(UpperCorner,LowerCorner);
metadataElt.setEnvelope(theEnvelope);
metadataElt.setTolerance(1,((BigDecimal)attrY[3]).doubleValue());
}
// on est en 3D
else if (dim == 3) {
metadataElt.setDimension(3);
metadataElt.setTolerance (new double[3]);
ResultSet X = (ResultSet)sqlDiminfo.getResultSet(1,1);
X.next();
oracle.sql.STRUCT XX = (oracle.sql.STRUCT)X.getObject(2);
Object[] attrX = XX.getAttributes();
double Xmin = ((BigDecimal)attrX[1]).doubleValue();
double Xmax = ((BigDecimal)attrX[2]).doubleValue();
metadataElt.setTolerance(0,((BigDecimal)attrX[3]).doubleValue());
ResultSet Y = (ResultSet)sqlDiminfo.getResultSet(2,1);
Y.next();
oracle.sql.STRUCT YY = (oracle.sql.STRUCT)Y.getObject(2);
Object[] attrY = YY.getAttributes();
double Ymin = ((BigDecimal)attrY[1]).doubleValue();
double Ymax = ((BigDecimal)attrY[2]).doubleValue();
metadataElt.setTolerance(1,((BigDecimal)attrY[3]).doubleValue());
ResultSet Z = (ResultSet)sqlDiminfo.getResultSet(3,1);
Z.next();
oracle.sql.STRUCT ZZ = (oracle.sql.STRUCT)Z.getObject(2);
Object[] attrZ = ZZ.getAttributes();
double Zmin = ((BigDecimal)attrZ[1]).doubleValue();
double Zmax = ((BigDecimal)attrZ[2]).doubleValue();
DirectPosition UpperCorner = new DirectPosition (Xmax,Ymax,Zmax);
DirectPosition LowerCorner = new DirectPosition (Xmin,Ymin,Zmin);
GM_Envelope theEnvelope = new GM_Envelope(UpperCorner,LowerCorner);
metadataElt.setEnvelope(theEnvelope);
metadataElt.setTolerance(2,((BigDecimal)attrZ[3]).doubleValue());
}
// on n'est ni en 2D, ni en 3D !
else throw new Exception("Probl鑝e pour lire le DIMINFO de user_sdo_geom_metadata");
}
// sortie de boucle quand on a trouve une egalite entre tableName de user_sdo_geom_metadata et tableName du mapping
break;
}
}
}
stm.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -