/*
* Searches for the sensors, which are contained within the specified envelope
* */
public static ArrayList<String> getListOfSensors(String envelope) throws ParseException {
String spatial_query =
"select location, name from sensors\n"
+ "where ST_CONTAINS(ST_GeomFromText('"
+ envelope
+ "'), location)";
ArrayList<String> sensors = new ArrayList<String>();
try {
Connection conn = connect(dburl, dbuser, dbpass);
Statement s2 = conn.createStatement();
ResultSet r2 = s2.executeQuery(spatial_query);
int count = 0;
while (r2.next()) {
PGgeometry geom = (PGgeometry) r2.getObject(1);
String name = r2.getString(2);
sensors.add(name);
logger.warn("Matching Geometry " + geom.toString() + " : " + name);
count++;
}
logger.warn("count = " + count);
s2.close();
conn.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
} catch (ClassNotFoundException e) {
logger.warn(e.getMessage(), e);
}
return sensors;
}
/*
* Builds sensors table from list of sensors currently loaded in the system
* */
public static boolean buildGeoIndex() {
boolean success = true;
sensors = new Vector<String>();
coordinates = new Vector<Point>();
getListOfSensors();
Properties properties = loadProperties();
if (properties != null) {
try {
dburl = properties.getProperty("dburl");
dbuser = properties.getProperty("dbuser");
dbpass = properties.getProperty("dbpass");
Connection conn = connect(dburl, dbuser, dbpass);
// ((org.postgresql.PGConnection) conn).addDataType("geometry", "org.postgis.PGgeometry");
// ((org.postgresql.PGConnection) conn).addDataType("box3d", "org.postgis.PGbox3d");
String st_create_table =
"DROP INDEX IF EXISTS gist_sensors;"
+ " DROP TABLE IF EXISTS sensors;"
+ " CREATE TABLE sensors ( \"name\" character(255) NOT NULL, \"location\" geometry NOT NULL );"
+ " CREATE INDEX gist_sensors ON sensors USING GIST ( location ); ";
logger.warn("Running query: " + st_create_table);
PreparedStatement prepareStatement = conn.prepareStatement(st_create_table);
prepareStatement.execute();
prepareStatement.close();
for (int i = 0; i < coordinates.size(); i++) {
String insert =
"insert into sensors values ( '"
+ sensors.get(i)
+ "', ST_MakePoint("
+ coordinates.get(i).getX()
+ " , "
+ coordinates.get(i).getY()
+ " , "
+ coordinates.get(i).getZ()
+ ") );";
PreparedStatement ps = conn.prepareStatement(insert);
ps.execute();
ps.close();
logger.warn(insert);
}
Statement s = conn.createStatement();
ResultSet r = s.executeQuery("select location, name from sensors");
while (r.next()) {
PGgeometry geom = (PGgeometry) r.getObject(1);
String name = r.getString(2);
logger.warn("Geometry " + geom.toString() + " : " + name);
}
s.close();
conn.close();
} catch (SQLException e) {
logger.warn(e.getMessage(), e);
success = false;
} catch (ClassNotFoundException e) {
logger.warn(e.getMessage(), e);
success = false;
}
} else {
logger.warn("Couldn't load properties files for PostGIS");
success = false;
}
return success;
}
public void read(AbstractCityObject cityObject, long cityObjectId, HashSet<Long> generalizesToSet)
throws SQLException {
for (Long generalizationId : generalizesToSet) {
ResultSet rs = null;
try {
psGeneralization.setLong(1, generalizationId);
rs = psGeneralization.executeQuery();
if (rs.next()) {
String gmlId = rs.getString("GMLID");
if (rs.wasNull() || gmlId == null) continue;
int classId = rs.getInt("CLASS_ID");
CityGMLClass type = Util.classId2cityObject(classId);
PGgeometry pgGeom = (PGgeometry) rs.getObject("ENVELOPE");
if (!rs.wasNull() && pgGeom != null && boundingBoxFilter.isActive()) {
Geometry geom = pgGeom.getGeometry();
Envelope env = new EnvelopeImpl();
Point lower =
new Point(geom.getFirstPoint().x, geom.getFirstPoint().y, geom.getFirstPoint().z);
Point upper = new Point(geom.getPoint(2).x, geom.getPoint(2).y, geom.getPoint(2).z);
env.setLowerCorner(lower);
env.setUpperCorner(upper);
if (boundingBoxFilter.filter(env)) continue;
}
if (featureGmlIdFilter.isActive() && featureGmlIdFilter.filter(gmlId)) continue;
if (featureClassFilter.isActive() && featureClassFilter.filter(type)) continue;
if (featureGmlNameFilter.isActive()) {
// we need to get the gml:name of the feature
// we only check top-level features
TableEnum table = null;
switch (type) {
case BUILDING:
table = TableEnum.BUILDING;
break;
case CITY_FURNITURE:
table = TableEnum.CITY_FURNITURE;
break;
case LAND_USE:
table = TableEnum.LAND_USE;
break;
case WATER_BODY:
table = TableEnum.WATERBODY;
break;
case PLANT_COVER:
table = TableEnum.SOLITARY_VEGETAT_OBJECT;
break;
case SOLITARY_VEGETATION_OBJECT:
table = TableEnum.PLANT_COVER;
break;
case TRANSPORTATION_COMPLEX:
case ROAD:
case RAILWAY:
case TRACK:
case SQUARE:
table = TableEnum.TRANSPORTATION_COMPLEX;
break;
case RELIEF_FEATURE:
table = TableEnum.RELIEF_FEATURE;
break;
case GENERIC_CITY_OBJECT:
table = TableEnum.GENERIC_CITYOBJECT;
break;
case CITY_OBJECT_GROUP:
table = TableEnum.CITYOBJECTGROUP;
break;
}
if (table != null) {
Statement stmt = null;
ResultSet nameRs = null;
try {
String query =
"select NAME from " + table.toString() + " where ID=" + generalizationId;
stmt = connection.createStatement();
nameRs = stmt.executeQuery(query);
if (nameRs.next()) {
String gmlName = nameRs.getString("NAME");
if (gmlName != null && featureGmlNameFilter.filter(gmlName)) continue;
}
} catch (SQLException sqlEx) {
continue;
} finally {
if (nameRs != null) {
try {
nameRs.close();
} catch (SQLException sqlEx) {
//
}
nameRs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
//
}
stmt = null;
}
}
}
}
GeneralizationRelation generalizesTo = new GeneralizationRelationImpl();
generalizesTo.setHref("#" + gmlId);
cityObject.addGeneralizesTo(generalizesTo);
}
} finally {
if (rs != null) rs.close();
}
}
}