PageRenderTime 106ms CodeModel.GetById 23ms app.highlight 73ms RepoModel.GetById 1ms app.codeStats 0ms

/layers-store/src/main/java/org/ala/layers/dao/ObjectDAOImpl.java

http://alageospatialportal.googlecode.com/
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}