/layers-store/src/main/java/org/ala/layers/dao/ObjectDAOImpl.java
Java | 811 lines | 631 code | 96 blank | 84 comment | 114 complexity | fa6e5a341847a757453a6d13e1ea979e MD5 | raw file
1/************************************************************************** 2 * Copyright (C) 2010 Atlas of Living Australia 3 * All Rights Reserved. 4 * 5 * The contents of this file are subject to the Mozilla Public 6 * License Version 1.1 (the "License"); you may not use this file 7 * except in compliance with the License. You may obtain a copy of 8 * the License at http://www.mozilla.org/MPL/ 9 * 10 * Software distributed under the License is distributed on an "AS 11 * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or 12 * implied. See the License for the specific language governing 13 * rights and limitations under the License. 14 ***************************************************************************/ 15package org.ala.layers.dao; 16 17import java.io.BufferedInputStream; 18import java.io.ByteArrayOutputStream; 19import java.io.File; 20import java.io.FileInputStream; 21import java.io.IOException; 22import java.io.InputStreamReader; 23import java.io.OutputStream; 24import java.io.RandomAccessFile; 25import java.io.UnsupportedEncodingException; 26import java.net.URLEncoder; 27import java.text.MessageFormat; 28import java.util.ArrayList; 29import java.util.HashMap; 30import java.util.List; 31import java.util.Map; 32import java.util.Map.Entry; 33import java.util.Vector; 34import java.util.zip.ZipInputStream; 35 36import javax.annotation.Resource; 37import javax.sql.DataSource; 38 39import org.ala.layers.dto.GridClass; 40import org.ala.layers.dto.IntersectionFile; 41import org.ala.layers.dto.Objects; 42import org.ala.layers.intersect.Grid; 43import org.ala.layers.intersect.IntersectConfig; 44import org.ala.layers.util.LayerFilter; 45import org.ala.layers.util.SpatialConversionUtils; 46import org.ala.layers.util.SpatialUtil; 47import org.apache.commons.io.FileUtils; 48import org.apache.log4j.Logger; 49import org.geotools.data.DataUtilities; 50import org.geotools.feature.simple.SimpleFeatureBuilder; 51import org.geotools.geojson.feature.FeatureJSON; 52import org.geotools.kml.KML; 53import org.geotools.kml.KMLConfiguration; 54import org.geotools.xml.Encoder; 55import org.opengis.feature.simple.SimpleFeatureType; 56import org.springframework.dao.DataAccessException; 57import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper; 58import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; 59import org.springframework.scheduling.annotation.Async; 60import org.springframework.stereotype.Service; 61import org.springframework.transaction.annotation.Transactional; 62 63import com.vividsolutions.jts.geom.Geometry; 64import com.vividsolutions.jts.io.WKTReader; 65 66/** 67 * 68 * @author ajay 69 */ 70@Service("objectDao") 71public class ObjectDAOImpl implements ObjectDAO { 72 73 // sld substitution strings 74 private static final String SUB_LAYERNAME = "*layername*"; 75 private static final String SUB_COLOUR = "0xff0000"; // "*colour*"; 76 private static final String SUB_MIN_MINUS_ONE = "*min_minus_one*"; 77 private static final String SUB_MIN = "*min*"; 78 private static final String SUB_MAX = "*max*"; 79 private static final String SUB_MAX_PLUS_ONE = "*max_plus_one*"; 80 /** log4j logger */ 81 private static final Logger logger = Logger.getLogger(ObjectDAOImpl.class); 82 private SimpleJdbcTemplate jdbcTemplate; 83 @Resource(name = "layerIntersectDao") 84 private LayerIntersectDAO layerIntersectDao; 85 86 @Resource(name = "dataSource") 87 public void setDataSource(DataSource dataSource) { 88 this.jdbcTemplate = new SimpleJdbcTemplate(dataSource); 89 } 90 91 static final String objectWmsUrl = "/wms?service=WMS&version=1.1.0&request=GetMap&layers=ALA:Objects&format=image/png&viewparams=s:<pid>"; 92 static final String gridPolygonWmsUrl = "/wms?service=WMS&version=1.1.0&request=GetMap&layers=ALA:" + SUB_LAYERNAME + "&format=image/png&sld_body="; 93 static final String gridPolygonSld; 94 static final String gridClassSld; 95 96 static { 97 String polygonSld = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><StyledLayerDescriptor xmlns=\"http://www.opengis.net/sld\">" + "<NamedLayer><Name>ALA:" + SUB_LAYERNAME + "</Name>" 98 + "<UserStyle><FeatureTypeStyle><Rule><RasterSymbolizer><Geometry></Geometry>" + "<ColorMap>" + "<ColorMapEntry color=\"" + SUB_COLOUR + "\" opacity=\"0\" quantity=\"" 99 + SUB_MIN_MINUS_ONE + "\"/>" + "<ColorMapEntry color=\"" + SUB_COLOUR + "\" opacity=\"1\" quantity=\"" + SUB_MIN + "\"/>" + "<ColorMapEntry color=\"" + SUB_COLOUR 100 + "\" opacity=\"0\" quantity=\"" + SUB_MAX_PLUS_ONE + "\"/>" + "</ColorMap></RasterSymbolizer></Rule></FeatureTypeStyle></UserStyle></NamedLayer></StyledLayerDescriptor>"; 101 102 String classSld = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><StyledLayerDescriptor xmlns=\"http://www.opengis.net/sld\">" + "<NamedLayer><Name>ALA:" + SUB_LAYERNAME + "</Name>" 103 + "<UserStyle><FeatureTypeStyle><Rule><RasterSymbolizer><Geometry></Geometry>" + "<ColorMap>" + "<ColorMapEntry color=\"" + SUB_COLOUR + "\" opacity=\"0\" quantity=\"" 104 + SUB_MIN_MINUS_ONE + "\"/>" + "<ColorMapEntry color=\"" + SUB_COLOUR + "\" opacity=\"1\" quantity=\"" + SUB_MIN + "\"/>" + "<ColorMapEntry color=\"" + SUB_COLOUR 105 + "\" opacity=\"1\" quantity=\"" + SUB_MAX + "\"/>" + "<ColorMapEntry color=\"" + SUB_COLOUR + "\" opacity=\"0\" quantity=\"" + SUB_MAX_PLUS_ONE + "\"/>" 106 + "</ColorMap></RasterSymbolizer></Rule></FeatureTypeStyle></UserStyle></NamedLayer></StyledLayerDescriptor>"; 107 try { 108 polygonSld = URLEncoder.encode(polygonSld, "UTF-8"); 109 } catch (UnsupportedEncodingException ex) { 110 logger.fatal("Invalid polygon sld string defined in ObjectDAOImpl."); 111 } 112 try { 113 classSld = URLEncoder.encode(classSld, "UTF-8"); 114 } catch (UnsupportedEncodingException ex) { 115 logger.fatal("Invalid class sld string defined in ObjectDAOImpl."); 116 } 117 118 gridPolygonSld = polygonSld; 119 gridClassSld = classSld; 120 121 } 122 123 @Override 124 public List<Objects> getObjects() { 125 // return hibernateTemplate.find("from Objects"); 126 logger.info("Getting a list of all objects"); 127 String sql = "select o.pid as pid, o.id as id, o.name as name, o.desc as description, o.fid as fid, f.name as fieldname from objects o, fields f where o.fid = f.id"; 128 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class)); 129 updateObjectWms(objects); 130 return objects; 131 } 132 133 @Override 134 public List<Objects> getObjectsById(String id) { 135 // return hibernateTemplate.find("from Objects where id = ?", id); 136 logger.info("Getting object info for fid = " + id); 137 // String sql = "select * from objects where fid = ?"; 138 String sql = "select o.pid as pid, o.id as id, o.name as name, o.desc as description, o.fid as fid, f.name as fieldname, o.bbox, o.area_km from objects o, fields f where o.fid = ? and o.fid = f.id"; 139 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), id); 140 141 updateObjectWms(objects); 142 143 // get grid classes 144 if (objects == null || objects.isEmpty()) { 145 objects = new ArrayList<Objects>(); 146 IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(id); 147 if (f != null && f.getClasses() != null) { 148 for (Entry<Integer, GridClass> c : f.getClasses().entrySet()) { 149 if (f.getType().equals("a")) { // class pid 150 Objects o = new Objects(); 151 o.setPid(f.getLayerPid() + ":" + c.getKey()); 152 o.setId(f.getLayerPid() + ":" + c.getKey()); 153 o.setName(c.getValue().getName()); 154 o.setFid(f.getFieldId()); 155 o.setFieldname(f.getFieldName()); 156 o.setBbox(c.getValue().getBbox()); 157 o.setArea_km(c.getValue().getArea_km()); 158 o.setWmsurl(getGridClassWms(f.getLayerName(), c.getValue())); 159 objects.add(o); 160 } else { // polygon pid 161 try { 162 // BufferedReader br = new BufferedReader(new 163 // FileReader(f.getFilePath() + File.separator + 164 // c.getKey() + ".wkt.index")); 165 // String line; 166 // while((line = br.readLine()) != null) { 167 // if(line.length() > 0) { 168 // String [] cells = line.split(","); 169 // Objects o = new Objects(); 170 // o.setPid(f.getLayerPid() + ":" + c.getKey() + ":" 171 // + cells[0]); 172 // o.setId(f.getLayerPid() + ":" + c.getKey() + ":" 173 // + cells[0]); 174 // o.setName(c.getValue().getName()); 175 // o.setFid(f.getFieldId()); 176 // o.setFieldname(f.getFieldName()); 177 // 178 // //Too costly to calculate on the fly, and not 179 // pre-calculated. 180 // // o.setBbox(c.getValue().getBbox()); 181 // // o.setArea_km(c.getValue().getArea_km()); 182 // objects.add(o); 183 // } 184 // } 185 // br.close(); 186 RandomAccessFile raf = new RandomAccessFile(f.getFilePath() + File.separator + c.getKey() + ".wkt.index.dat", "r"); 187 long len = raf.length() / (4 + 4 + 4 * 4 + 4); // group 188 // number, 189 // character 190 // offset, 191 // minx, 192 // miny, 193 // maxx, 194 // maxy, 195 // area 196 // sq 197 // km 198 for (int i = 0; i < len; i++) { 199 int n = raf.readInt(); 200 /* int charoffset = */raf.readInt(); 201 float minx = raf.readFloat(); 202 float miny = raf.readFloat(); 203 float maxx = raf.readFloat(); 204 float maxy = raf.readFloat(); 205 float area = raf.readFloat(); 206 207 Objects o = new Objects(); 208 o.setPid(f.getLayerPid() + ":" + c.getKey() + ":" + n); 209 o.setId(f.getLayerPid() + ":" + c.getKey() + ":" + n); 210 o.setName(c.getValue().getName()); 211 o.setFid(f.getFieldId()); 212 o.setFieldname(f.getFieldName()); 213 214 o.setBbox("POLYGON((" + minx + " " + miny + "," + minx + " " + maxy + "," + +maxx + " " + maxy + "," + +maxx + " " + miny + "," + +minx + " " + miny + "))"); 215 o.setArea_km(1.0 * area); 216 217 o.setWmsurl(getGridPolygonWms(f.getLayerName(), n)); 218 219 objects.add(o); 220 } 221 raf.close(); 222 223 } catch (Exception e) { 224 e.printStackTrace(); 225 } 226 } 227 } 228 } 229 } 230 231 return objects; 232 } 233 234 @Override 235 public String getObjectsGeometryById(String id, String geomtype) { 236 ByteArrayOutputStream baos = new ByteArrayOutputStream(); 237 try { 238 streamObjectsGeometryById(baos, id, geomtype); 239 } catch (IOException e) { 240 e.printStackTrace(); 241 } finally { 242 try { 243 baos.close(); 244 } catch (IOException e) { 245 e.printStackTrace(); 246 } 247 } 248 249 return new String(baos.toByteArray()); 250 } 251 252 @Override 253 public void streamObjectsGeometryById(OutputStream os, String id, String geomtype) throws IOException { 254 logger.info("Getting object info for id = " + id + " and geometry as " + geomtype); 255 String sql = ""; 256 if ("kml".equals(geomtype)) { 257 sql = "SELECT ST_AsKml(the_geom) as geometry FROM objects WHERE pid=?;"; 258 } else if ("wkt".equals(geomtype)) { 259 sql = "SELECT ST_AsText(the_geom) as geometry FROM objects WHERE pid=?;"; 260 } else if ("geojson".equals(geomtype)) { 261 sql = "SELECT ST_AsGeoJSON(the_geom) as geometry FROM objects WHERE pid=?;"; 262 } else if ("shp".equals(geomtype)) { 263 sql = "SELECT ST_AsText(the_geom) as geometry, name, \"desc\" as description FROM objects WHERE pid=?;"; 264 } 265 266 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), id); 267 268 if (l.size() > 0) { 269 if ("shp".equals(geomtype)) { 270 String wkt = l.get(0).getGeometry(); 271 File zippedShapeFile = SpatialConversionUtils.buildZippedShapeFile(wkt, id, l.get(0).getName(), l.get(0).getDescription()); 272 FileUtils.copyFile(zippedShapeFile, os); 273 } else { 274 os.write(l.get(0).getGeometry().getBytes()); 275 } 276 277 } else { 278 // get grid classes 279 if (id.length() > 0) { 280 // grid class pids are, 'layerPid:gridClassNumber' 281 try { 282 String[] s = id.split(":"); 283 if (s.length >= 2) { 284 int n = Integer.parseInt(s[1]); 285 IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(s[0]); 286 if (f != null && f.getClasses() != null) { 287 GridClass gc = f.getClasses().get(n); 288 if (gc != null && ("kml".equals(geomtype) || "wkt".equals(geomtype) || "geojson".equals(geomtype) || "shp".equals(geomtype))) { 289 // TODO: enable for type 'a' after 290 // implementation of fields table defaultLayer 291 // field 292 293 if (f.getType().equals("a") || s.length == 2) { 294 // class 295 File file = new File(f.getFilePath() + File.separator + s[1] + "." + geomtype + ".zip"); 296 if (file.exists()) { 297 ZipInputStream zis = new ZipInputStream(new FileInputStream(file)); 298 zis.getNextEntry(); 299 byte[] buffer = new byte[1024]; 300 int size; 301 while ((size = zis.read(buffer)) > 0) { 302 os.write(buffer, 0, size); 303 } 304 zis.close(); 305 } 306 } else { // polygon 307 BufferedInputStream bis = null; 308 InputStreamReader isr = null; 309 try { 310 String[] cells = null; 311 312 HashMap<String, Object> map = getGridIndexEntry(f.getFilePath() + File.separator + s[1], s[2]); 313 314 cells = new String[] { s[2], String.valueOf(map.get("charoffset")) }; 315 if (cells != null) { 316 // get polygon wkt string 317 File file = new File(f.getFilePath() + File.separator + s[1] + ".wkt"); 318 bis = new BufferedInputStream(new FileInputStream(file)); 319 isr = new InputStreamReader(bis); 320 isr.skip(Long.parseLong(cells[1])); 321 char[] buffer = new char[1024]; 322 int size; 323 StringBuilder sb = new StringBuilder(); 324 sb.append("POLYGON"); 325 int end = -1; 326 while (end < 0 && (size = isr.read(buffer)) > 0) { 327 sb.append(buffer, 0, size); 328 end = sb.toString().indexOf("))"); 329 } 330 end += 2; 331 332 String wkt = sb.toString().substring(0, end); 333 334 if (geomtype.equals("wkt")) { 335 os.write(wkt.getBytes()); 336 } else { 337 WKTReader r = new WKTReader(); 338 Geometry g = r.read(wkt); 339 340 if (geomtype.equals("kml")) { 341 Encoder encoder = new Encoder(new KMLConfiguration()); 342 encoder.setIndenting(true); 343 encoder.encode(g, KML.Geometry, os); 344 } else if (geomtype.equals("geojson")) { 345 FeatureJSON fjson = new FeatureJSON(); 346 final SimpleFeatureType TYPE = DataUtilities.createType("class", "the_geom:MultiPolygon,name:String"); 347 SimpleFeatureBuilder featureBuilder = new SimpleFeatureBuilder(TYPE); 348 featureBuilder.add(g); 349 featureBuilder.add(gc.getName()); 350 fjson.writeFeature(featureBuilder.buildFeature(null), os); 351 } else if (geomtype == "shp") { 352 File zippedShapeFile = SpatialConversionUtils.buildZippedShapeFile(wkt, id, gc.getName(), null); 353 FileUtils.copyFile(zippedShapeFile, os); 354 } 355 } 356 } 357 358 } catch (Exception e) { 359 e.printStackTrace(); 360 } finally { 361 if (bis != null) { 362 try { 363 bis.close(); 364 } catch (Exception e) { 365 e.printStackTrace(); 366 } 367 } 368 if (isr != null) { 369 try { 370 isr.close(); 371 } catch (Exception e) { 372 e.printStackTrace(); 373 } 374 } 375 } 376 } 377 } 378 } 379 } 380 } catch (Exception e) { 381 e.printStackTrace(); 382 } 383 } 384 } 385 } 386 387 @Override 388 public Objects getObjectByPid(String pid) { 389 // List<Objects> l = 390 // hibernateTemplate.find("from Objects where pid = ?", pid); 391 logger.info("Getting object info for pid = " + pid); 392 String sql = "select o.pid, o.id, o.name, o.desc as description, o.fid as fid, f.name as fieldname, o.bbox, o.area_km from objects o, fields f where o.pid = ? and o.fid = f.id"; 393 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), pid); 394 395 updateObjectWms(l); 396 397 // get grid classes 398 if ((l == null || l.isEmpty()) && pid.length() > 0) { 399 // grid class pids are, 'layerPid:gridClassNumber' 400 try { 401 String[] s = pid.split(":"); 402 if (s.length >= 2) { 403 int n = Integer.parseInt(s[1]); 404 IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(s[0]); 405 if (f != null && f.getClasses() != null) { 406 GridClass gc = f.getClasses().get(n); 407 if (gc != null) { 408 Objects o = new Objects(); 409 o.setPid(pid); 410 o.setId(pid); 411 o.setName(gc.getName()); 412 o.setFid(f.getFieldId()); 413 o.setFieldname(f.getFieldName()); 414 415 if (/* f.getType().equals("a") || */s.length == 2) { 416 o.setBbox(gc.getBbox()); 417 o.setArea_km(gc.getArea_km()); 418 o.setWmsurl(getGridClassWms(f.getLayerName(), gc)); 419 } else { 420 HashMap<String, Object> map = getGridIndexEntry(f.getFilePath() + File.separator + s[1], s[2]); 421 if (!map.isEmpty()) { 422 o.setBbox("POLYGON(" + map.get("minx") + " " + map.get("miny") + "," + map.get("minx") + " " + map.get("maxy") + "," + map.get("maxx") + " " + map.get("maxy") 423 + "," + map.get("maxx") + " " + map.get("miny") + "," + map.get("minx") + " " + map.get("miny") + ")"); 424 425 o.setArea_km(((Float) map.get("area")).doubleValue()); 426 427 o.setWmsurl(getGridPolygonWms(f.getLayerName(), Integer.parseInt(s[2]))); 428 } 429 } 430 431 l.add(o); 432 } 433 } 434 } 435 } catch (Exception e) { 436 e.printStackTrace(); 437 } 438 } 439 440 if (l.size() > 0) { 441 return l.get(0); 442 } else { 443 return null; 444 } 445 } 446 447 @Override 448 public Objects getObjectByIdAndLocation(String fid, Double lng, Double lat) { 449 logger.info("Getting object info for fid = " + fid + " at loc: (" + lng + ", " + lat + ") "); 450 String sql = MessageFormat 451 .format("select o.pid, o.id, o.name, o.desc as description, o.fid as fid, f.name as fieldname, o.bbox, o.area_km from search_objects_by_geometry_intersect(?, ST_GeomFromText(''POINT({0} {1})'', 4326)) o, fields f WHERE o.fid = f.id", 452 lng, lat); 453 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), new Object[] { fid }); 454 updateObjectWms(l); 455 if (l == null || l.isEmpty()) { 456 // get grid classes intersection 457 l = new ArrayList<Objects>(); 458 IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(fid); 459 if (f != null && f.getClasses() != null) { 460 Vector v = layerIntersectDao.samplingFull(fid, lng, lat); 461 if (v != null && v.size() > 0 && v.get(0) != null) { 462 Map m = (Map) v.get(0); 463 int key = (int) Double.parseDouble(((String) m.get("pid")).split(":")[1]); 464 GridClass gc = f.getClasses().get(key); 465 if (f.getType().equals("a")) { // class pid 466 Objects o = new Objects(); 467 o.setName(gc.getName()); 468 o.setFid(f.getFieldId()); 469 o.setFieldname(f.getFieldName()); 470 o.setPid(f.getLayerPid() + ":" + gc.getId()); 471 o.setId(f.getLayerPid() + ":" + gc.getId()); 472 o.setBbox(gc.getBbox()); 473 o.setArea_km(gc.getArea_km()); 474 o.setWmsurl(getGridClassWms(f.getLayerName(), gc)); 475 l.add(o); 476 } else { // if(f.getType().equals("b")) {//polygon pid 477 Grid g = new Grid(f.getFilePath() + File.separator + "polygons"); 478 if (g != null) { 479 float[] vs = g.getValues(new double[][] { { lng, lat } }); 480 String pid = f.getLayerPid() + ":" + gc.getId() + ":" + ((int) vs[0]); 481 l.add(getObjectByPid(pid)); 482 } 483 } 484 } 485 } 486 } 487 if (l.size() > 0) { 488 return l.get(0); 489 } else { 490 return null; 491 } 492 } 493 494 @Override 495 public List<Objects> getNearestObjectByIdAndLocation(String fid, int limit, Double lng, Double lat) { 496 logger.info("Getting " + limit + " nearest objects in field fid = " + fid + " to loc: (" + lng + ", " + lat + ") "); 497 498 String sql = "select fid, name, \"desc\", pid, id, ST_AsText(the_geom) as geometry, " + "st_Distance_Sphere(ST_SETSRID(ST_Point( ? , ? ),4326), the_geom) as distance, " 499 + "degrees(Azimuth( ST_SETSRID(ST_Point( ? , ? ),4326), the_geom)) as degrees " + "from objects where fid= ? order by distance limit ? "; 500 501 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), lng, lat, lng, lat, fid, new Integer(limit)); 502 updateObjectWms(objects); 503 return objects; 504 } 505 506 @Override 507 public List<Objects> getObjectByFidAndName(String fid, String name) { 508 logger.info("Getting object info for fid = " + fid + " and name: (" + name + ") "); 509 String sql = "select o.pid, o.id, o.name, o.desc as description, o.fid as fid, f.name as fieldname, o.bbox, o.area_km, ST_AsText(the_geom) as geometry from objects o, fields f where o.fid = ? and o.name like ? and o.fid = f.id"; 510 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), new Object[] { fid, name }); 511 updateObjectWms(objects); 512 return objects; 513 } 514 515 private String getGridPolygonWms(String layername, int n) { 516 return layerIntersectDao.getConfig().getGeoserverUrl() + gridPolygonWmsUrl.replace(SUB_LAYERNAME, layername) 517 + formatSld(gridPolygonSld, layername, String.valueOf(n - 1), String.valueOf(n), String.valueOf(n), String.valueOf(n + 1)); 518 } 519 520 private String getGridClassWms(String layername, GridClass gc) { 521 return layerIntersectDao.getConfig().getGeoserverUrl() 522 + gridPolygonWmsUrl.replace(SUB_LAYERNAME, layername) 523 + formatSld(gridClassSld, layername, String.valueOf(gc.getMinShapeIdx() - 1), String.valueOf(gc.getMinShapeIdx()), String.valueOf(gc.getMaxShapeIdx()), 524 String.valueOf(gc.getMaxShapeIdx() + 1)); 525 } 526 527 private String formatSld(String sld, String layername, String min_minus_one, String min, String max, String max_plus_one) { 528 return sld.replace(SUB_LAYERNAME, layername).replace(SUB_MIN_MINUS_ONE, min_minus_one).replace(SUB_MIN, min).replace(SUB_MAX, max).replace(SUB_MAX_PLUS_ONE, max_plus_one); 529 } 530 531 private void updateObjectWms(List<Objects> objects) { 532 for (Objects o : objects) { 533 o.setWmsurl(layerIntersectDao.getConfig().getGeoserverUrl() + objectWmsUrl.replace("<pid>", o.getPid())); 534 } 535 } 536 537 private HashMap<String, Object> getGridIndexEntry(String path, String objectId) { 538 HashMap<String, Object> map = new HashMap<String, Object>(); 539 RandomAccessFile raf = null; 540 try { 541 raf = new RandomAccessFile(path + ".wkt.index.dat", "r"); 542 543 int s2 = Integer.parseInt(objectId); 544 545 // it is all in order, seek to the record 546 int recordSize = 4 * 7; // 2 int + 5 float 547 int start = raf.readInt(); 548 raf.seek(recordSize * (s2 - start)); 549 550 map.put("gn", raf.readInt()); 551 map.put("charoffset", raf.readInt()); 552 map.put("minx", raf.readFloat()); 553 map.put("miny", raf.readFloat()); 554 map.put("maxx", raf.readFloat()); 555 map.put("maxy", raf.readFloat()); 556 map.put("area", raf.readFloat()); 557 } catch (Exception e) { 558 e.printStackTrace(); 559 } finally { 560 try { 561 if (raf != null) { 562 raf.close(); 563 } 564 } catch (Exception e) { 565 e.printStackTrace(); 566 } 567 } 568 569 return map; 570 } 571 572 @Override 573 public List<Objects> getObjectsByIdAndArea(String id, Integer limit, String wkt) { 574 String sql = "select fid, name, \"desc\", pid, id, ST_AsText(the_geom) as geometry " + "from objects where fid= ? and " + "ST_Within(the_geom, ST_GeomFromText( ? , 4326)) " + "limit ? "; 575 576 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), id, wkt, new Integer(limit)); 577 updateObjectWms(objects); 578 return objects; 579 } 580 581 @Override 582 public List<Objects> getObjectsByIdAndIntersection(String id, Integer limit, LayerFilter layerFilter) { 583 String world = "POLYGON((-180 -90,-180 90,180 90,180 -90,-180 -90))"; 584 List<Objects> objects = getObjectsByIdAndArea(id, Integer.MAX_VALUE, world); 585 586 double[][] points = new double[objects.size()][2]; 587 for (int i = 0; i < objects.size(); i++) { 588 try { 589 String[] s = objects.get(i).getGeometry().substring("POINT(".length(), objects.get(i).getGeometry().length() - 1).split(" "); 590 points[i][0] = Double.parseDouble(s[0]); 591 points[i][1] = Double.parseDouble(s[1]); 592 } catch (Exception e) { 593 // don't intersect this one 594 points[i][0] = Integer.MIN_VALUE; 595 points[i][1] = Integer.MIN_VALUE; 596 } 597 } 598 599 // sampling 600 ArrayList<String> sample = layerIntersectDao.sampling(new String[] { layerFilter.getLayername() }, points); 601 602 // filter 603 List<Objects> matched = new ArrayList<Objects>(); 604 String[] sampling = sample.get(0).split("\n"); 605 IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(layerFilter.getLayername()); 606 if (f != null && (f.getType().equals("a") || f.getType().equals("b"))) { 607 String target = f.getClasses().get((int) layerFilter.getMinimum_value()).getName(); 608 for (int i = 0; i < sampling.length; i++) { 609 if (sampling[i].length() > 0) { 610 if (sampling[i].equals(target)) { 611 matched.add(objects.get(i)); 612 } 613 } 614 } 615 } else { 616 for (int i = 0; i < sampling.length; i++) { 617 if (sampling[i].length() > 0) { 618 double v = Double.parseDouble(sampling[i]); 619 if (v >= layerFilter.getMinimum_value() && v <= layerFilter.getMaximum_value()) { 620 matched.add(objects.get(i)); 621 } 622 } 623 } 624 } 625 626 updateObjectWms(matched); 627 return matched; 628 } 629 630 @Override 631 public List<Objects> getObjectsByIdAndIntersection(String id, Integer limit, String intersectingPid) { 632 String sql = "select fid, name, \"desc\", pid, id, ST_AsText(the_geom) as geometry " + "from objects, " + "(select the_geom as g from Objects where pid = ? ) t " + "where fid= ? and " 633 + "ST_Within(the_geom, g) " + "limit ? "; 634 635 List<Objects> objects = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), intersectingPid, id, new Integer(limit)); 636 updateObjectWms(objects); 637 638 return objects; 639 } 640 641 @Transactional 642 @Override 643 public String createUserUploadedObject(String wkt, String name, String description, String userid) { 644 645 double area_km = SpatialUtil.calculateArea(wkt) / 1000.0 / 1000.0; 646 647 try { 648 // Insert shape into geometry table 649 String sql = "INSERT INTO objects (pid, id, name, \"desc\", fid, the_geom, namesearch, bbox, area_km) values (nextval('objects_id_seq'::regclass), nextval('uploaded_objects_metadata_id_seq'::regclass), ?, ?, ?, ST_GeomFromText(?, 4326), true, ST_AsText(Box2D(ST_GeomFromText(?, 4326))), ?)"; 650 jdbcTemplate.update(sql, name, description, IntersectConfig.getUploadedShapesFieldId(), wkt, wkt, area_km); 651 652 // Now write to metadata table 653 String sql2 = "INSERT INTO uploaded_objects_metadata (pid, id, user_id, time_last_updated) values (currval('objects_id_seq'::regclass), currval('uploaded_objects_metadata_id_seq'::regclass), ?, now())"; 654 jdbcTemplate.update(sql2, userid); 655 656 // get pid and id of new object 657 String sql3 = "SELECT MAX(pid) from uploaded_objects_metadata"; 658 int pid = jdbcTemplate.queryForInt(sql3); 659 660 return Integer.toString(pid); 661 } catch (DataAccessException ex) { 662 throw new IllegalArgumentException("Error writing to database. Check validity of wkt.", ex); 663 } 664 } 665 666 @Override 667 @Transactional 668 public boolean updateUserUploadedObject(int pid, String wkt, String name, String description, String userid) { 669 670 if (!shapePidIsForUploadedShape(pid)) { 671 throw new IllegalArgumentException("Supplied pid does not match an uploaded shape."); 672 } 673 674 try { 675 double area_km = SpatialUtil.calculateArea(wkt) / 1000.0 / 1000.0; 676 677 // First update metadata table 678 String sql = "UPDATE uploaded_objects_metadata SET user_id = ?, time_last_updated = now() WHERE pid = ?"; 679 jdbcTemplate.update(sql, userid, Integer.toString(pid)); 680 681 // Then update objects table 682 String sql2 = "UPDATE objects SET the_geom = ST_GeomFromText(?, 4326), bbox = ST_AsText(Box2D(ST_GeomFromText(?, 4326))), name = ?, \"desc\" = ?, area_km = ? where pid = ?"; 683 int rowsUpdated = jdbcTemplate.update(sql2, wkt, wkt, name, description, area_km, Integer.toString(pid)); 684 return (rowsUpdated > 0); 685 } catch (DataAccessException ex) { 686 throw new IllegalArgumentException("Error writing to database. Check validity of wkt.", ex); 687 } 688 } 689 690 @Override 691 @Transactional 692 public boolean deleteUserUploadedObject(int pid) { 693 if (!shapePidIsForUploadedShape(pid)) { 694 throw new IllegalArgumentException("Supplied pid does not match an uploaded shape."); 695 } 696 697 String sql = "DELETE FROM uploaded_objects_metadata WHERE pid = ?; DELETE FROM objects where pid = ?"; 698 int rowsAffected = jdbcTemplate.update(sql, Integer.toString(pid), Integer.toString(pid)); 699 return (rowsAffected > 0); 700 } 701 702 @Async 703 public void updateObjectNames() { 704 String sql = "INSERT INTO obj_names (name)" + " SELECT lower(objects.name) FROM fields, objects" + " LEFT OUTER JOIN obj_names ON lower(objects.name)=obj_names.name" 705 + " WHERE obj_names.name IS NULL" + " AND fields.namesearch = true" + " AND fields.id = objects.fid" + " GROUP BY lower(objects.name);" 706 + " UPDATE objects SET name_id=obj_names.id FROM obj_names WHERE name_id IS NULL AND lower(objects.name)=obj_names.name;"; 707 jdbcTemplate.update(sql); 708 } 709 710 private boolean shapePidIsForUploadedShape(int pid) { 711 String sql = "SELECT * from uploaded_objects_metadata WHERE pid = ?"; 712 List<Map<String, Object>> queryResult = jdbcTemplate.queryForList(sql, Integer.toString(pid)); 713 if (queryResult == null || queryResult.isEmpty()) { 714 return false; 715 } else { 716 return true; 717 } 718 719 } 720 721 @Override 722 public int createPointOfInterest(String objectId, String name, String type, Double latitude, Double longitude, Double bearing, String userId, String description, Double focalLength) { 723 String sql = "INSERT INTO points_of_interest (id, object_id, name, type, latitude, longitude, bearing, user_id, description, focal_length_millimetres, the_geom) VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ST_SetSRID(ST_MakePoint(?, ?),4326))"; 724 jdbcTemplate.update(sql, objectId, name, type, latitude, longitude, bearing, userId, description, focalLength, longitude, latitude); 725 726 // get pid and id of new object 727 String sql2 = "SELECT MAX(id) from points_of_interest"; 728 int id = jdbcTemplate.queryForInt(sql2); 729 return id; 730 } 731 732 @Override 733 public boolean updatePointOfInterest(int id, String objectId, String name, String type, Double latitude, Double longitude, Double bearing, String userId, String description, Double focalLength) { 734 String sql = "UPDATE points_of_interest SET object_id = ?, name = ?, type = ?, latitude = ?, longitude = ?, bearing = ?, user_id = ?, description = ?, focal_length_millimetres = ? WHERE id = ?; " 735 + "UPDATE points_of_interest SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude),4326) WHERE id = ?"; 736 int rowsUpdated = jdbcTemplate.update(sql, objectId, name, type, latitude, longitude, bearing, userId, description, focalLength, id, id); 737 return (rowsUpdated > 0); 738 } 739 740 @Override 741 public Map<String, Object> getPointOfInterestDetails(int id) { 742 String sql = "SELECT id, object_id, name, type, latitude, longitude, bearing, user_id, description, focal_length_millimetres from points_of_interest WHERE id = ?"; 743 Map<String, Object> poiDetails = jdbcTemplate.queryForMap(sql, id); 744 if (poiDetails.isEmpty()) { 745 throw new IllegalArgumentException("Invalid point of interest id"); 746 } 747 748 return poiDetails; 749 } 750 751 @Override 752 public boolean deletePointOfInterest(int id) { 753 String sql = "DELETE FROM points_of_interest WHERE id = ?;"; 754 int rowsAffected = jdbcTemplate.update(sql, id); 755 return (rowsAffected > 0); 756 } 757 758 @Override 759 public List<Objects> getObjectsWithinRadius(String fid, double latitude, double longitude, double radiusKm) { 760 String sql = MessageFormat 761 .format("SELECT o.pid, o.id, o.name, o.desc AS description, o.fid AS fid, f.name AS fieldname, o.bbox, o.area_km FROM objects o, fields f WHERE o.fid = ? AND o.fid = f.id AND ST_DWithin(ST_GeographyFromText(''POINT({0} {1})''), geography(the_geom), ?)", 762 longitude, latitude); 763 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), fid, radiusKm * 1000); 764 updateObjectWms(l); 765 return l; 766 } 767 768 @Override 769 public List<Objects> getObjectsIntersectingWithGeometry(String fid, String wkt) { 770 // String sql = 771 // "SELECT o.pid, o.id, o.name, o.desc AS description, o.fid AS fid, f.name AS fieldname, o.bbox, o.area_km FROM objects o, fields f WHERE o.fid = ? AND o.fid = f.id AND ST_Intersects(ST_GeomFromText(?, 4326), the_geom)"; 772 String sql = "SELECT o.pid, o.id, o.name, o.desc AS description, o.fid AS fid, f.name AS fieldname, o.bbox, o.area_km from search_objects_by_geometry_intersect(?, ST_GeomFromText(?, 4326)) o, fields f WHERE o.fid = f.id"; 773 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), fid, wkt); 774 updateObjectWms(l); 775 return l; 776 } 777 778 @Override 779 public List<Objects> getObjectsIntersectingWithObject(String fid, String objectPid) { 780 // String sql = 781 // "SELECT o.pid, o.id, o.name, o.desc AS description, o.fid AS fid, f.name AS fieldname, o.bbox, o.area_km FROM objects o, fields f WHERE o.fid = ? AND o.fid = f.id AND ST_Intersects((SELECT the_geom FROM objects WHERE pid = ?), the_geom)"; 782 String sql = "SELECT o.pid, o.id, o.name, o.desc AS description, o.fid AS fid, f.name AS fieldname, o.bbox, o.area_km FROM search_objects_by_geometry_intersect(?, (SELECT the_geom FROM objects WHERE pid = ?)) o, fields f WHERE o.fid = f.id"; 783 List<Objects> l = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Objects.class), fid, objectPid); 784 updateObjectWms(l); 785 return l; 786 } 787 788 @Override 789 public List<Map<String, Object>> getPointsOfInterestWithinRadius(double latitude, double longitude, double radiusKm) { 790 String sql = MessageFormat 791 .format("SELECT id, object_id, name, type, latitude, longitude, bearing, user_id, description, focal_length_millimetres from points_of_interest WHERE ST_DWithin(ST_GeographyFromText(''POINT({0} {1})''), geography(the_geom), ?)", 792 longitude, latitude); 793 List<Map<String, Object>> l = jdbcTemplate.queryForList(sql, radiusKm * 1000); 794 return l; 795 } 796 797 @Override 798 public List<Map<String, Object>> pointsOfInterestGeometryIntersect(String wkt) { 799 String sql = "SELECT id, object_id, name, type, latitude, longitude, bearing, user_id, description, focal_length_millimetres from points_of_interest WHERE ST_Intersects(ST_GeomFromText(?, 4326), the_geom)"; 800 List<Map<String, Object>> l = jdbcTemplate.queryForList(sql, wkt); 801 return l; 802 } 803 804 @Override 805 public List<Map<String, Object>> pointsOfInterestObjectIntersect(String objectPid) { 806 String sql = "SELECT id, object_id, name, type, latitude, longitude, bearing, user_id, description, focal_length_millimetres from points_of_interest WHERE ST_Intersects((SELECT the_geom FROM objects where pid = ?), the_geom)"; 807 List<Map<String, Object>> l = jdbcTemplate.queryForList(sql, objectPid); 808 return l; 809 } 810 811}