org.geoserver.wfs.response.SpatiaLiteOutputFormatDev.java Source code

Java tutorial

Introduction

Here is the source code for org.geoserver.wfs.response.SpatiaLiteOutputFormatDev.java

Source

/* Copyright (c) 2001 - 2007 TOPP - www.openplans.org. All rights reserved.
 * This code is licensed under the GPL 2.0 license, availible at the root
 * application directory.
 */
package org.geoserver.wfs.response;

import java.io.BufferedWriter;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileDescriptor;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Serializable;
import java.io.StringWriter;
import java.net.MalformedURLException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.SimpleTimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.xml.namespace.QName;

import net.opengis.wfs.FeatureCollectionType;
import net.opengis.wfs.GetFeatureType;
import net.opengis.wfs.QueryType;

import org.apache.commons.io.FileUtils;
import org.geoserver.catalog.Catalog;
import org.geoserver.catalog.CatalogBuilder;
import org.geoserver.catalog.FeatureTypeInfo;
import org.geoserver.catalog.MetadataMap;
import org.geoserver.config.GeoServer;
import org.geoserver.data.util.IOUtils;
import org.geoserver.feature.RetypingFeatureCollection;
import org.geoserver.ows.Dispatcher;
import org.geoserver.ows.Request;
import org.geoserver.ows.util.OwsUtils;
import org.geoserver.platform.GeoServerExtensions;
import org.geoserver.platform.GeoServerResourceLoader;
import org.geoserver.platform.Operation;
import org.geoserver.platform.ServiceException;
import org.geoserver.template.GeoServerTemplateLoader;
import org.geoserver.wfs.WFSException;
import org.geoserver.wfs.WFSGetFeatureOutputFormat;
import org.geoserver.wfs.WFSInfo;
import org.geoserver.wfs.response.ShapeZipOutputFormat.FileNameSource;
import org.geotools.data.DataStore;
import org.geotools.data.DataUtilities;
import org.geotools.data.FeatureWriter;
import org.geotools.data.Transaction;
import org.geotools.data.shapefile.ShapefileDataStore;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.data.simple.SimpleFeatureSource;
import org.geotools.data.simple.SimpleFeatureStore;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.geometry.jts.Geometries;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.referencing.CRS;
import org.geotools.util.logging.Logging;
import org.geotools.wfs.v1_1.WFS;
import org.geotools.wfs.v1_1.WFSConfiguration;
import org.geotools.xml.Encoder;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.feature.type.AttributeDescriptor;
import org.opengis.feature.type.AttributeType;
import org.opengis.feature.type.GeometryDescriptor;
import org.opengis.feature.type.GeometryType;
import org.opengis.feature.type.Name;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.ReferenceIdentifier;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.util.InternationalString;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;

import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryCollection;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.MultiPoint;
import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;

import freemarker.template.Configuration;
import freemarker.template.Template;

import org.apache.commons.dbcp.BasicDataSource;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.JDBCDataStoreFactory;
import org.geotools.jdbc.SQLDialect;
import org.sqlite.SQLiteConfig;

/**
 *
 * WFS output format for a GetFeature operation in which the outputFormat is "spatialite".
 * The reference documentation for this format can be found in this link:
 * @link:http://www.gaia-gis.it/spatialite/docs.html.
 * 
 * Based on CSVOutputFormat.java and ShapeZipOutputFormat.java from geoserver 2.2.x
 *
 * @author ported to gs 2.2.x by Pablo Emanuel Velazquez, TSAVO SRL Group, pvelazquez@gmail.com.ar
 *
 */

public class SpatiaLiteOutputFormatDev extends WFSGetFeatureOutputFormat {

    //A Hashtable to convert the native data types to data types supported by SQLite
    private Hashtable<String, String> dataTypes = new Hashtable();
    private Hashtable<Class<? extends Geometry>, String> geomTypes = new Hashtable();
    private String driverClassName = "org.sqlite.JDBC";

    public SpatiaLiteOutputFormatDev(GeoServer gs) {
        super(gs, "SpatiaLite");

        //Initializing the dataTypes Hashtable
        dataTypes.put("STRING", "TEXT");
        dataTypes.put("INT", "INTEGER");
        dataTypes.put("LONG", "INTEGER");
        dataTypes.put("BOOL", "INTEGER");
        dataTypes.put("DOUBLE", "REAL");
        dataTypes.put("FLOAT", "REAL");

        //Initializing the geomTypes Hashtable
        geomTypes.put(Geometries.POINT.getBinding(), "POINT");
        geomTypes.put(Geometries.LINESTRING.getBinding(), "LINESTRING");
        geomTypes.put(Geometries.POLYGON.getBinding(), "POLYGON");
        geomTypes.put(Geometries.MULTIPOINT.getBinding(), "MULTIPOINT");
        geomTypes.put(Geometries.MULTILINESTRING.getBinding(), "MULTILINESTRING");
        geomTypes.put(Geometries.MULTIPOLYGON.getBinding(), "MULTIPOLYGON");
        geomTypes.put(Geometries.GEOMETRY.getBinding(), "GEOMETRY");
        geomTypes.put(Geometries.GEOMETRYCOLLECTION.getBinding(), "GEOMETRYCOLLECTION");

    }

    /**
     * @return "application/x-sqlite3";
     */
    @Override
    public String getMimeType(Object value, Operation operation) throws ServiceException {
        //return "application/x-sqlite3";
        return "application/x-sqlite3";
    }

    @Override
    protected boolean canHandleInternal(Operation operation) {
        //any additional checks that need to be performed to 
        // determine when the output format should be "engaged" 
        // should go here
        return super.canHandleInternal(operation);
    }

    @Override
    protected void write(FeatureCollectionType featureCollection, OutputStream output, Operation getFeature)
            throws IOException, ServiceException {
        List<SimpleFeatureCollection> collections = new ArrayList<SimpleFeatureCollection>();
        collections.addAll(featureCollection.getFeature());
        Charset charset = Charset.forName("UTF-8");
        write(collections, charset, output, (GetFeatureType) getFeature.getParameters()[0]);
    }

    protected void write(List<SimpleFeatureCollection> collections, Charset charset, OutputStream output,
            GetFeatureType request) throws IOException, ServiceException {

        Connection conn = null;

        /**
         * Get the necessary JDBC object.
         */
        try {
            Class.forName(this.driverClassName);
        } catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        /**
         * base location to temporally store spatialite database files
         */
        File tempDir = File.createTempFile("spatialitemp", ".sqlite");

        /**
         * enables load extension
         */
        SQLiteConfig config = new SQLiteConfig();
        config.enableLoadExtension(true);

        /**
         * the Url for the temporally sqlite file
         */
        String JDBCFileUrl = tempDir.getAbsolutePath();

        try {
            //create a connection to database
            conn = DriverManager.getConnection("jdbc:sqlite:" + JDBCFileUrl, config.toProperties());
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(30);

            /**
             * A string to store the statements to run to create the Spatialite DataBase 
             */
            String sql = null;
            conn.setAutoCommit(false);
            // FIXME: we are now working on PATH - we will define different JAR files to change this as required by platform
            sql = "SELECT load_extension('/tmp/libspatialite-2-4.dll');";
            stmt.execute(sql);
            sql = "SELECT InitSpatialMetaData();";
            stmt.execute(sql);
            conn.commit();

            /**
             * A string to store the names of the columns that will be used to populate the table 
             */
            String column_names = null;

            //We might get multiple feature collections in our response (multiple queries?) so we need to
            //write out multiple tables, one for each query response.
            for (SimpleFeatureCollection fc : collections) {

                //get the current feature
                SimpleFeatureType ft = fc.getSchema();

                //To check if the current feature has a geometry.
                String the_geom = null;
                if (ft.getGeometryDescriptor() != null) {
                    the_geom = ft.getGeometryDescriptor().getLocalName();
                }

                //Get the table name for the current feature
                String tbl_name = ft.getName().getLocalPart();

                /**
                 * Create the table for the current feature as follows:
                 * - first get the statement for create the table
                 * - execute the statement
                 * - second get the statement for add the geometry (if has one) 
                 * - execute the statement
                 */

                //Initialize the "create table" query.
                column_names = "";
                int column_cnt = 0;
                sql = "CREATE TABLE " + tbl_name;
                sql += " ( PK_UID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT";

                //Get the columns names for the table tbl_name
                for (int i = 0; i < ft.getAttributeCount(); i++) {
                    AttributeDescriptor ad = ft.getDescriptor(i);
                    if (ad.getLocalName() != the_geom) {
                        sql += ", " + prepareColumnHeader(ad);
                        column_names += ad.getLocalName();
                        column_cnt++;
                        if (i < ft.getAttributeCount() - 1) {
                            column_names += ", ";
                        }

                    }
                }
                sql += ");";
                // Finish creating the table

                System.out.println(sql);
                stmt.execute(sql);
                conn.commit();

                int srid = 0;
                //If the table : "tbl_name" has a geometry, then i write the sql to add the geometry
                if (the_geom != null) {
                    sql = "SELECT AddGeometryColumn('" + tbl_name + "', ";
                    //get the geometry type
                    sql += "'" + the_geom + "', ";
                    //get the SRID.
                    srid = getSpatialSRID(ft.getCoordinateReferenceSystem());
                    sql += srid + ", ";
                    //get the Geometry type.
                    String geom_type = getSpatialGeometryType(ft);
                    if (geom_type == null) {
                        throw new WFSException("Error while adding the geometry column in table " + tbl_name
                                + ", unrecognized geometry type");
                    }
                    sql += "'" + geom_type + "', ";
                    //get Dimensions, we only works whit 2 dimensions. 
                    String dimension = "XY";
                    sql += "'" + dimension + "'";
                    sql += " );";
                }
                //finish creating the geometry column.
                System.out.println(sql);
                stmt.execute(sql);
                conn.commit();

                /**
                 * Populates the table for the current feature as follows:
                 * For each row
                 *      - first: configure the statement with the appropriates fields.
                 *      - second: add to the statement the field the_geom if has a geometry.
                 *      - third: configure the statement with the appropriates values.
                 *      (if has a geometry i add that value) 
                 *      - execute the statement
                 * Finally commit.
                 */
                //Start populating the table: tbl_name.
                SimpleFeatureIterator i = fc.features();
                try {
                    while (i.hasNext()) {
                        SimpleFeature row = i.next();
                        sql = "INSERT INTO " + tbl_name + " (" + column_names;
                        //if has a geometry, i add the field the_geom.
                        if (the_geom != null)
                            if (column_cnt > 0) {
                                sql += ", " + the_geom + " ) ";
                            } else {
                                sql += the_geom + ") ";
                            }

                        else {
                            sql += ") ";
                        }

                        //I store the default geometry value, so i can omit it and add at the end.
                        Object geom_data = row.getDefaultGeometry();
                        sql += "VALUES (";
                        for (int j = 0; j < row.getAttributeCount(); j++) {
                            Object rowAtt = row.getAttribute(j);
                            if (!rowAtt.equals(geom_data)) {
                                if (rowAtt != null) {
                                    //We just transform all content to String.
                                    sql += "'" + rowAtt.toString() + "'";
                                }
                                if (j < row.getAttributeCount() - 1) {
                                    sql += ", ";
                                }
                            }
                        }

                        //Finally if has geometry, insert the geometry data.
                        if (the_geom != null) {
                            if (column_cnt > 0) {
                                sql += ", ";
                            }
                            sql += "GeomFromText('" + prepareGeom(geom_data.toString()) + "', " + srid + ")";
                        }
                        sql += ");";
                        System.out.println(sql);
                        stmt.executeUpdate(sql);
                    }
                    conn.commit();
                } finally {
                    fc.close(i);
                }
            }
        } catch (SQLException e) {
            System.out.println(e);
        }

        /**
         * A FileInputStream to read the tempDir in a byte array
         * so i can write this in the OutputStream output and flush it.
         */
        FileInputStream JDBCIn = new FileInputStream(tempDir);
        int longitud = JDBCIn.available();
        byte[] datos = new byte[longitud];
        JDBCIn.read(datos);
        output.write(datos);

        System.out.println(tempDir.getAbsolutePath());

    }

    public String getCapabilitiesElementName() {
        return "SPATIALITE";
    }

    @Override
    public String[][] getHeaders(Object value, Operation operation) throws ServiceException {
        SimpleFeatureCollection fc = (SimpleFeatureCollection) ((FeatureCollectionType) value).getFeature().get(0);
        GetFeatureType request = (GetFeatureType) OwsUtils.parameter(operation.getParameters(),
                GetFeatureType.class);
        String outputFileName = null;
        if (request != null) {
            Map<String, ?> formatOptions = request.getFormatOptions();
            outputFileName = (String) formatOptions.get("FILENAME");
        }
        if (outputFileName == null) {
            outputFileName = ((QName) ((QueryType) request.getQuery().get(0)).getTypeName().get(0)).getLocalPart();
        }
        return (String[][]) new String[][] {
                { "Content-Disposition", "attachment; filename=" + outputFileName + ".sqlite" } };
    }

    //Returns the geometry type of a feature.
    //If the feature doesn't have a recognized geometry type this return a null value.

    private String getSpatialGeometryType(SimpleFeatureType featureType) {
        Class<?> geomType = featureType.getGeometryDescriptor().getType().getBinding();
        return (String) geomTypes.get(geomType);

    }

    //Get the Current SRID of a Feature.
    //If the feature doesn't have a SRID, this return SRID = "-1" (default SpatiaLite SRID)
    private int getSpatialSRID(CoordinateReferenceSystem crs) {
        try {
            return CRS.lookupEpsgCode(crs, true);
        } catch (FactoryException e) {
            System.out.println(e.getMessage());
            LOGGER.log(Level.FINER, e.getMessage(), e);
            return -1;
        }
    }

    //Prepares the column headers whit the format:
    //         "COLUMN_NAME" + "COLUMN_TYPE"
    private String prepareColumnHeader(AttributeDescriptor ad) {
        //I split the binding, and get the last split, that represents the data type
        String[] split = ad.getType().getBinding().getName().split("\\.");
        String column_type = (String) dataTypes.get(split[split.length - 1].toUpperCase());
        return ad.getLocalName().toUpperCase() + " " + column_type;
    }

    /**
     * This method return a prepared MULTIPOINT geometry if is MULTIPOINT (We need do this
     * because MULTIPOINT Feature format is: MULTIPOINT ((x y),(x y),(x y)) and
     * MULTIPOINT Spatialite format is: MULTIPOINT (x y, x y, x y))
     * @param theGeom;
     * @return value;
     */
    //This method return a prepared MULTIPOINT geometry if is MULTIPOINT (We need do this
    //because MULTIPOINT Feature format is: MULTIPOINT ((x y),(x y),(x y)) and
    //MULTIPOINT Spatialite format is: MULTIPOINT (x y, x y, x y))

    //Tener en cuenta que pasa con las GEOMETRY COLLECTIONS!
    private String prepareGeom(String theGeom) {
        String value = theGeom;
        if ((boolean) theGeom.contains("MULTIPOINT")) {
            value = value.replaceAll("\\(\\(", "*");
            value = value.replaceAll("\\)\\)", "#");
            value = value.replaceAll("\\(", "");
            value = value.replaceAll("\\)", "");
            value = value.replaceAll("\\*", "(");
            value = value.replaceAll("\\#", ")");
        }
        return value;
    }

}