au.org.ala.layers.dao.TabulationDAOImpl.java Source code

Java tutorial

Introduction

Here is the source code for au.org.ala.layers.dao.TabulationDAOImpl.java

Source

/**************************************************************************
 *  Copyright (C) 2010 Atlas of Living Australia
 *  All Rights Reserved.
 *
 *  The contents of this file are subject to the Mozilla Public
 *  License Version 1.1 (the "License"); you may not use this file
 *  except in compliance with the License. You may obtain a copy of
 *  the License at http://www.mozilla.org/MPL/
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing
 *  rights and limitations under the License.
 ***************************************************************************/
package au.org.ala.layers.dao;

import au.org.ala.layers.dto.IntersectionFile;
import au.org.ala.layers.dto.Tabulation;
import au.org.ala.layers.tabulation.TabulationGenerator;
import au.org.ala.layers.tabulation.TabulationUtil;
import au.org.ala.layers.util.SpatialUtil;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;

/**
 * @author ajay
 */
@Service("tabulationDao")
public class TabulationDAOImpl implements TabulationDAO {

    /**
     * log4j logger
     */
    private static final Logger logger = Logger.getLogger(TabulationDAOImpl.class);
    private SimpleJdbcTemplate jdbcTemplate;

    @Resource(name = "layerIntersectDao")
    private LayerIntersectDAO layerIntersectDao;

    @Resource(name = "fieldDao")
    private FieldDAO fieldDao;

    @Resource(name = "objectDao")
    private ObjectDAO objectDao;

    @Resource(name = "dataSource")
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    @Override
    public List<Tabulation> getTabulation(String fid1, String fid2, String wkt) {
        List<Tabulation> tabulations = null;

        String min, max;
        if (fid1.compareTo(fid2) < 0) {
            min = fid1;
            max = fid2;
        } else {
            min = fid2;
            max = fid1;
        }

        if (wkt == null || wkt.length() == 0) {
            /*  after "tabulation" table is updated with column "occurrences" and column "species"
             * String sql = "SELECT i.pid1, i.pid2, i.fid1, i.fid2, i.area, i.occurrences, i.species, o1.name as name1, o2.name as name2 FROM "
                + "(SELECT * FROM tabulation WHERE fid1= ? AND fid2 = ? ) i, "
                + "(SELECT * FROM objects WHERE fid= ? ) o1, "
                + "(SELECT * FROM objects WHERE fid= ? ) o2 "
                + "WHERE i.pid1=o1.pid AND i.pid2=o2.pid ;";
                * 
                */
            /* before "tabulation" table is updated with column "occurrences", to just make sure column "area" is all good */
            String sql = "SELECT i.pid1, i.pid2, i.fid1, i.fid2, i.area, o1.name as name1, o2.name as name2, i.occurrences, i.species FROM "
                    + "(SELECT pid1, pid2, fid1, fid2, area, occurrences, species FROM tabulation WHERE fid1= ? AND fid2 = ? ) i, "
                    + "(select t1.pid1 as pid, name from tabulation t1 left join objects o3 on t1.fid1=o3.fid and t1.pid1=o3.pid where t1.fid1= ? group by t1.pid1, name) o1, "
                    //+ "(SELECT pid, name FROM objects WHERE fid= ? ) o1, "
                    + "(select t2.pid2 as pid, name from tabulation t2 left join objects o4 on t2.fid2=o4.fid and t2.pid2=o4.pid where t2.fid2= ? group by t2.pid2, name) o2 "
                    //+ "(SELECT pid, name FROM objects WHERE fid= ? ) o2 "
                    + "WHERE i.pid1=o1.pid AND i.pid2=o2.pid ;";

            tabulations = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class),
                    min, max, min, max);

        } else {
            String sql = "SELECT fid1, pid1, fid2, pid2, ST_AsText(newgeom) as geometry, name1, name2, occurrences, species FROM "
                    + "(SELECT fid1, pid1, fid2, pid2, (ST_INTERSECTION(ST_GEOMFROMTEXT( ? ,4326), i.the_geom)) as newgeom, o1.name as name1, o2.name as name2, i.occurrences, i.species FROM "
                    + "(SELECT * FROM tabulation WHERE fid1= ? AND fid2 = ? ) i, "
                    + "(select t1.pid1 as pid, name from tabulation t1 left join objects o3 on t1.fid1=o3.fid and t1.pid1=o3.pid where t1.fid1= ? group by t1.pid1, name) o1, "
                    //+ "(SELECT pid, name FROM objects WHERE fid= ? ) o1, "
                    + "(select t2.pid2 as pid, name from tabulation t2 left join objects o4 on t2.fid2=o4.fid and t2.pid2=o4.pid where t2.fid2= ? group by t2.pid2, name) o2 "
                    //+ "(SELECT pid, name FROM objects WHERE fid= ? ) o2 "
                    + "WHERE i.pid1=o1.pid AND i.pid2=o2.pid) a "
                    + "WHERE a.newgeom is not null AND ST_Area(a.newgeom) > 0;";

            tabulations = jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class),
                    wkt, min, max, min, max);

            for (Tabulation t : tabulations) {
                try {
                    t.setArea(SpatialUtil.calculateArea(t.getGeometry()));
                    t.setOccurrences(TabulationUtil.calculateOccurrences(
                            layerIntersectDao.getConfig().getOccurrenceSpeciesRecordsFilename(), t.getGeometry()));
                    t.setSpecies(TabulationUtil.calculateSpecies(
                            layerIntersectDao.getConfig().getOccurrenceSpeciesRecordsFilename(), t.getGeometry()));
                } catch (Exception e) {
                    logger.error("fid1:" + fid1 + " fid2:" + fid2 + " wkt:" + wkt, e);
                }
            }
        }

        //fill in 'name' for 'grids as classes'/fields.type='a'/pids with ':'
        IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(min);
        if (f.getType().equals("a")) {
            for (Tabulation t : tabulations) {
                t.setName1(f.getClasses().get(Integer.parseInt(t.getPid1().split(":")[1])).getName());
            }
        }
        f = layerIntersectDao.getConfig().getIntersectionFile(max);
        if (f.getType().equals("a")) {
            for (Tabulation t : tabulations) {
                t.setName2(f.getClasses().get(Integer.parseInt(t.getPid2().split(":")[1])).getName());
            }
        }

        return tabulations;
    }

    @Override
    public List<Tabulation> listTabulations() {
        String incompleteTabulations = "select fid1, fid2 from tabulation where area is null and the_geom is not null group by fid1, fid2";
        String sql = "SELECT fid1, fid2, f1.name as name1, f2.name as name2 " + " FROM (select t1.* from "
                + "(select fid1, fid2, sum(area) a from tabulation group by fid1, fid2) t1 left join " + " ("
                + incompleteTabulations + ") i on t1.fid1=i.fid1 and t1.fid2=i.fid2 where i.fid1 is null" + ") t"
                + ", fields f1, fields f2 " + " WHERE f1.id = fid1 AND f2.id = fid2 AND a > 0 "
                + " AND f1.intersect=true AND f2.intersect=true " + " GROUP BY fid1, fid2, name1, name2;";

        return jdbcTemplate.query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class));
    }

    @Override
    public List<Tabulation> getTabulationSingle(String fid, String wkt) {
        //is it wkt or pid?
        boolean isPid = wkt.indexOf('(') < 0;
        //is it grid as contextual layer?
        IntersectionFile f = layerIntersectDao.getConfig().getIntersectionFile(fid);

        if (f.getType().equalsIgnoreCase("c")) {
            if (wkt != null && wkt.length() > 0) {
                String sql;
                List<Tabulation> tabulations;

                if (isPid) {
                    sql = "SELECT fid1, pid1, name1," + " fid2, pid2, name2, "
                            + " ST_AsText(newgeom) as geometry FROM " + "("
                            + "SELECT a.fid as fid1, a.pid as pid1, a.name as name1, b.fid as fid2, b.pid as pid2, b.name as name2 "
                            + ", (ST_INTERSECTION(b.the_geom, a.the_geom)) as newgeom FROM "
                            + "(SELECT * FROM objects WHERE fid = ? ) a, (SELECT * FROM objects WHERE pid = ? ) b "
                            + "WHERE ST_INTERSECTS(ST_GEOMFROMTEXT(a.bbox, 4326), ST_GEOMFROMTEXT(b.bbox ,4326))"
                            + ") o " + "WHERE newgeom is not null AND ST_Area(newgeom) > 0;";

                    tabulations = jdbcTemplate.query(sql,
                            ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class), fid, wkt);
                } else {
                    sql = "SELECT fid as fid1, pid as pid1, name as name1,"
                            + " 'user area' as fid2, 'user area' as pid2, 'user area' as name2, "
                            + " ST_AsText(newgeom) as geometry FROM "
                            + "(SELECT fid, pid, name, (ST_INTERSECTION(ST_GEOMFROMTEXT( ? ,4326), the_geom)) as newgeom FROM "
                            + "objects WHERE fid= ? and ST_INTERSECTS(ST_GEOMFROMTEXT(bbox, 4326), ST_ENVELOPE(ST_GEOMFROMTEXT( ? ,4326)))"
                            + ") o " + "WHERE newgeom is not null AND ST_Area(newgeom) > 0;";

                    tabulations = jdbcTemplate.query(sql,
                            ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class), wkt, fid, wkt);
                }

                for (Tabulation t : tabulations) {
                    try {
                        t.setArea(SpatialUtil.calculateArea(t.getGeometry()));
                    } catch (Exception e) {
                        logger.error("fid:" + fid + " wkt:" + wkt, e);
                    }
                    //don't return geometry
                    t.setGeometry(null);
                }

                return tabulations;
            } else {
                String sql = "SELECT fid1, pid1, name as name1,"
                        + " 'world' as fid2, 'world' as pid2, 'world' as name2, " + " area_km as area FROM "
                        + "(SELECT name, fid as fid1, pid as pid1, the_geom as newgeom, area_km FROM "
                        + "objects WHERE fid= ? ) t " + "WHERE newgeom is not null AND ST_Area(newgeom) > 0;";

                List<Tabulation> tabulations = jdbcTemplate.query(sql,
                        ParameterizedBeanPropertyRowMapper.newInstance(Tabulation.class), fid);

                return tabulations;
            }
        } else {
            System.out.println("wkt: " + wkt);
            String w = wkt;
            if (isPid) {
                //get wkt
                w = objectDao.getObjectsGeometryById(wkt, "wkt");
            }
            System.out.println("w: " + w);
            return TabulationGenerator.calc(fid, w);
        }
    }
}