Application: gvSIG desktop » gvSIG projections

org.gvsig.geotools.proj.esri.ThreadedHsqlEsriFactory

org.gvsig.geotools.proj.esri.ThreadedHsqlEsriFactory

org.gvsig.geotools.proj.esri.ThreadedHsqlEsriFactory

org.gvsig.geotools.proj.esri.ThreadedHsqlEsriFactory

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2018 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

package org.gvsig.geotools.proj.esri;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.ParseException;

import java.util.Hashtable;

import java.util.LinkedHashSet;

import java.util.Map;

import java.util.Set;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.geotools.factory.Hints;

import org.geotools.metadata.iso.citation.Citations;

import org.geotools.referencing.factory.epsg.DirectEpsgFactory;

import org.geotools.referencing.factory.epsg.FactoryUsingAnsiSQL;

import org.geotools.referencing.wkt.Parser;

import org.geotools.resources.i18n.ErrorKeys;

import org.geotools.resources.i18n.Errors;

import org.opengis.metadata.citation.Citation;

import org.opengis.referencing.FactoryException;

import org.opengis.referencing.IdentifiedObject;

import org.opengis.referencing.NoSuchAuthorityCodeException;

import org.opengis.referencing.crs.CRSFactory;

import org.opengis.referencing.crs.CompoundCRS;

import org.opengis.referencing.crs.CoordinateReferenceSystem;

import org.opengis.referencing.crs.GeocentricCRS;

import org.opengis.referencing.crs.GeographicCRS;

import org.opengis.referencing.cs.CartesianCS;

import org.opengis.referencing.cs.CoordinateSystem;

import org.opengis.referencing.cs.EllipsoidalCS;

import org.opengis.referencing.cs.SphericalCS;

import org.opengis.referencing.cs.VerticalCS;

import org.opengis.referencing.datum.EngineeringDatum;

import org.opengis.referencing.datum.GeodeticDatum;

import org.opengis.referencing.datum.VerticalDatum;

import org.opengis.referencing.operation.Conversion;

import org.opengis.referencing.operation.CoordinateOperation;

import org.opengis.referencing.operation.Projection;

/**

 * A factory to retrieve and create CRSs from ESRI HSQL database.

 *

 * @author Cesar Martinez Izquierdo

 */

class EsriFactoryUsingHSQL extends DirectEpsgFactory {

    /** Cache of parsed CoordinateReferenceSystem WKT by ESRI_NUMBER */

    private Hashtable cache = new Hashtable();

    /** Constructs the factory for the given connection to the HSQL database. */

    public EsriFactoryUsingHSQL(final Hints hints, final Connection connection) {

        super(hints, connection);

    }

    public EsriFactoryUsingHSQL(final Hints hints, final DataSource dataSource) {

        super(hints, dataSource);

    }

    @Override

    public String adaptSQL(String query) {

        return query;

    }

    /**

     * Shutdown the HSQL database engine. This method is invoked automatically at JVM shutdown time

     * just before to close the connection.

     */

    protected void shutdown(final boolean active) throws SQLException {

        if (active) {

            final Statement statement = getConnection().createStatement();

            statement.execute("SHUTDOWN");

            statement.close();

        }

        super.shutdown(active);

    }

    @Override

    public Citation getAuthority() {

    	return Citations.ESRI;

    }

    @Override

	public synchronized CoordinateReferenceSystem createCoordinateReferenceSystem(String code) throws FactoryException {

		String wkt = null;

		String ESRI_NUMBER = code.substring(code.indexOf(":")+1, code.length());

		int cod = Integer.parseInt(ESRI_NUMBER);

		if( cache.contains( ESRI_NUMBER ) ){

            Object value = cache.get( ESRI_NUMBER );

            if( value instanceof Throwable ){

                throw new FactoryException( "WKT for "+code+" could not be parsed", (Throwable) value );

            }

            if( value instanceof CoordinateReferenceSystem){

                return (CoordinateReferenceSystem) value;

            }

        }

		ResultSet result = null;

		try {

            String sentence = "SELECT ESRI_WKT"

            + " FROM ESRI"

            + " WHERE ESRI_CODE = ?";

            String codeStr = code.substring(code.indexOf(":")+1, code.length());

            int codeInt = Integer.parseInt(codeStr);

        	PreparedStatement st = null;

        	st = getConnection().prepareStatement(sentence);

        	st.setInt(1, codeInt);

			result = st.executeQuery();

			st.close();

        	if (result.next()) {

                wkt    = result.getString("esri_wkt");

                if( wkt.indexOf( cod ) == -1){

                	wkt = wkt.trim();

                	wkt = wkt.substring(0, wkt.length()-1 );

                	wkt += ",AUTHORITY[\"ESRI\",\""+cod+"\"]]";

                }

                Parser parser = new Parser();

                return parser.parseCoordinateReferenceSystem(wkt);

            }

        } catch (SQLException exception) {

        	throw new FactoryException(exception);

        } catch (ParseException exception) {

        	throw new FactoryException(exception);

        } finally {

        	if (result!=null) {

        		try {

					result.close();

				} catch (SQLException e) {}

        	}

        };

        throw new NoSuchAuthorityCodeException("No such authority code", "ESRI", code);

	}

    @Override

    public Set<String> getAuthorityCodes(Class<? extends IdentifiedObject> type) throws FactoryException {

    	LinkedHashSet<String> codes = new LinkedHashSet<String>(); 

		ResultSet result = null;

		try {

            String sentence = "SELECT ESRI_CODE"

            + " FROM ESRI ORDER BY ESRI_CODE ASC";

        	Statement st = null;

        	st = getConnection().createStatement();

        	result = st.executeQuery(sentence);

			st.close();

        	while (result.next()) {

                int code = result.getInt(1);

                codes.add(Integer.toString(code));

            }

        } catch (SQLException exception) {

        	throw new FactoryException(exception);

        } finally {

        	if (result!=null) {

        		try {

					result.close();

				} catch (SQLException e) {}

        	}

        };

    	return codes;

    }

}

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2018 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 *

 * This class is based on ThreadedEpsgFactory class from Geotools project.

 * 

 *    GeoTools - The Open Source Java GIS Toolkit

 *    http://geotools.org

 *

 *    (C) 2005-2008, Open Source Geospatial Foundation (OSGeo)

 *

 *    This library is free software; you can redistribute it and/or

 *    modify it under the terms of the GNU Lesser General Public

 *    License as published by the Free Software Foundation;

 *    version 2.1 of the License.

 *

 *    This library is distributed in the hope that it will be useful,

 *    but WITHOUT ANY WARRANTY; without even the implied warranty of

 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU

 *    Lesser General Public License for more details.

 */

package org.gvsig.geotools.proj.esri;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.RandomAccessFile;

import java.nio.channels.FileChannel;

import java.nio.channels.FileLock;

import java.nio.channels.OverlappingFileLockException;

import java.sql.SQLException;

import java.util.logging.Level;

import java.util.logging.LogRecord;

import java.util.logging.Logger;

import java.util.zip.ZipEntry;

import java.util.zip.ZipInputStream;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.naming.NoInitialContextException;

import javax.sql.DataSource;

import org.geotools.factory.GeoTools;

import org.geotools.factory.Hints;

import org.geotools.metadata.iso.citation.Citations;

import org.geotools.referencing.factory.AbstractAuthorityFactory;

import org.geotools.referencing.factory.epsg.ThreadedEpsgFactory;

import org.geotools.resources.i18n.ErrorKeys;

import org.geotools.resources.i18n.Errors;

import org.geotools.resources.i18n.LoggingKeys;

import org.geotools.resources.i18n.Loggings;

import org.geotools.util.Version;

import org.geotools.util.logging.Logging;

import org.gvsig.proj.catalog.CRSCatalogLibrary;

import org.gvsig.proj.catalog.CRSCatalogLocator;

import org.hsqldb.jdbc.JDBCDataSource;

import org.opengis.metadata.citation.Citation;

/**

 * Connection to the ESRI database in HSQL database engine format using JDBC. The ESRI database

 * has been obtained from the gvSIG JCRS library. The SQL scripts are bundled

 * into this plugin. The database version is given in the {@linkplain

 * org.opengis.metadata.citation.Citation#getEdition edition attribute} of the {@linkplain

 * org.opengis.referencing.AuthorityFactory#getAuthority authority}. The HSQL database is read only.

 *

 * <p>

 *

 * <H3>Implementation note</H3>

 *

 * The SQL scripts are executed the first time a connection is required. The database is then

 * created as cached tables ({@code HSQL.properties} and {@code HSQL.data} files) in a temporary

 * directory. Future connections to the ESRI database while reuse the cached tables, if available.

 * Otherwise, the scripts will be executed again in order to recreate them.

 *

 * <p>If the ESRI database should be created in a different directory (or already exists in that

 * directory), it may be specified as a {@linkplain System#getProperty(String) system property}

 * nammed {@value #DIRECTORY_KEY}.

 *

 * @author Cesar Martinez Izquierdo, based on ThreadedEpsgFactory

 */

public class ThreadedHsqlEsriFactory extends ThreadedEpsgFactory {

    /**

     * Current version of ESRI definitions plugin.

     */

    public static final Version VERSION = new Version("1.0jcrs");

    /**

     * The key for fetching the database directory from {@linkplain System#getProperty(String)

     * system properties}.

     */

    public static final String DIRECTORY_KEY = "ESRI-HSQL.directory";

    /** The name of the ZIP file to read in order to create the cached database. */

    private static final String ZIP_FILE = "ESRI.zip";

    /** The database name. */

    public static final String DATABASE_NAME = "ESRI";

    /** The successful database creation marker */

    static final String MARKER_FILE = "ESRI_creation_marker.txt";

    /** The database creation lock file */

    static final String LOCK_FILE = "ESRI_creation_lock.txt";

    /** The prefix to put in front of URL to the database. */

    static final String PREFIX = "jdbc:hsqldb:file:";

    /** The logger name. */

    static final String LOGGER = "org.gvsig.geotools.proj.esri";

    /** The default priority level for this factory. */

    static final int PRIORITY = MAXIMUM_PRIORITY - 10;

    /**

     * Creates a new instance of this factory. If the {@value #DIRECTORY_KEY} {@linkplain

     * System#getProperty(String) system property} is defined and contains the name of a directory

     * with a valid {@linkplain File#getParent parent}, then the {@value #DATABASE_NAME} database

     * will be saved in that directory. Otherwise, a temporary directory will be used.

     */

    public ThreadedHsqlEsriFactory() {

        this(null);

    }

    /**

     * Returns the authority for this EPSG database. This authority will contains the database

     * version in the {@linkplain Citation#getEdition edition} attribute, together with the

     * {@linkplain Citation#getEditionDate edition date}.

     */

    @Override

    public Citation getAuthority() {

        return Citations.ESRI;

    }

    /**

     * Creates a new instance of this data source using the specified hints. The priority is set to

     * a lower value than the {@linkplain FactoryOnAccess}'s one in order to give precedence to the

     * Access-backed database, if presents. Priorities are set that way because:

     *

     * <ul>

     *   <li>The MS-Access format is the primary EPSG database format.

     *   <li>If a user downloads the MS-Access database himself, he probably wants to use it.

     * </ul>

     */

    public ThreadedHsqlEsriFactory(final Hints hints) {

        super(hints, PRIORITY + 1);

    }

    /**

     * Returns the default directory for the EPSG database. If the {@value #DIRECTORY_KEY}

     * {@linkplain System#getProperty(String) system property} is defined and contains the name of a

     * directory with a valid {@linkplain File#getParent parent}, then the {@value #DATABASE_NAME}

     * database will be saved in that directory. Otherwise, a temporary directory will be used.

     */

    private static File getDirectory() {

        try {

        	final File directory = CRSCatalogLocator.getManager().getSystemDataPath();

        	if (directory != null && (directory.isDirectory() || directory.mkdir())) {

                return directory;

            }

        } catch (SecurityException e) {

            /*

             * Can't fetch the base directory from system properties.

             * Fallback on the default temporary directory.

             */

        }

        return new File(getTemporaryDirectory(), DATABASE_NAME + File.separator + "v" + VERSION);

    }

    /** Returns the directory to use in the temporary directory folder. */

    private static File getTemporaryDirectory() {

        File directory = new File(System.getProperty("java.io.tmpdir", "."), "GeoTools");

        if (directory.isDirectory() || directory.mkdir()) {

            directory = new File(directory, "Databases/HSQL");

            if (directory.isDirectory() || directory.mkdirs()) {

                return directory;

            }

        }

        return null;

    }

    /**

     * Extract the directory from the specified data source, or {@code null} if this information is

     * not available.

     */

    private static File getDirectory(final DataSource source) {

        if (source instanceof JDBCDataSource) {

            String path = ((JDBCDataSource) source).getDatabase();

            if (path != null && PREFIX.regionMatches(true, 0, path, 0, PREFIX.length())) {

                path = path.substring(PREFIX.length());

                return new File(path).getParentFile();

            }

        }

        return null;

    }

    /** Returns a data source for the HSQL database. */

    protected DataSource createDataSource() throws SQLException {

        final Logger logger = Logging.getLogger(LOGGER);

        logger.log(Level.FINE, "Building new data source for " + getClass().getName());

        final JDBCDataSource source = new JDBCDataSource();

        File directory = getDirectory();

        if (directory != null) {

            /*

             * Constructs the full path to the HSQL database. Note: we do not use

             * File.toURI() because HSQL doesn't seem to expect an encoded URL

             * (e.g. "%20" instead of spaces).

             */

            final StringBuilder url = new StringBuilder(PREFIX);

            final String path = directory.getAbsolutePath().replace(File.separatorChar, '/');

            if (path.length() == 0 || path.charAt(0) != '/') {

                url.append('/');

            }

            url.append(path);

            if (url.charAt(url.length() - 1) != '/') {

                url.append('/');

            }

            url.append(DATABASE_NAME);

            url.append(";shutdown=true;readonly=true");

            //url.append(";shutdown=true");

            source.setDatabase(url.toString());

        }

        /*

         * If the temporary directory do not exists or can't be created, lets the 'database'

         * attribute unset. If the user do not set it explicitly (through JNDI or by overrding

         * this method), an exception will be thrown when 'createBackingStore()' will be invoked.

         */

        source.setUser("SA"); // System administrator. No password.

        source.setPassword("");

        return source;

    }

    /**

     * Returns {@code true} if the database contains data. This method returns {@code false} if an

     * empty EPSG database has been automatically created by HSQL and not yet populated.

     */

    private static boolean dataExists(File directory) throws SQLException {

        // check if the marker file is there, and all the other database files as well

        // (as some windows cleanup tools delete the .data file only)

        return new File(directory, MARKER_FILE).exists()

                //&& new File(directory, DATABASE_NAME + ".data").exists()

                && new File(directory, DATABASE_NAME + ".properties").exists()

                && new File(directory, DATABASE_NAME + ".script").exists();

    }

    /**

     * Returns the backing-store factory for HSQL syntax. If the cached tables are not available,

     * they will be created now from the SQL scripts bundled in this plugin.

     *

     * @param hints A map of hints, including the low-level factories to use for CRS creation.

     * @return The EPSG factory using HSQL syntax.

     * @throws SQLException if connection to the database failed.

     */

    protected AbstractAuthorityFactory createBackingStore(final Hints hints) throws SQLException {

        final Logger logger = Logging.getLogger(LOGGER);

        logger.log(Level.FINE, "Building backing store for " + getClass().getName());

        final DataSource source = getDataSource();

        final File directory = getDirectory(source);

        directory.mkdirs();

        if (!dataExists(directory)) {

            FileLock lock = null;

            try {

                // get an exclusive lock

                lock = acquireLock(directory);

                // if after getting the lock the database is still incomplete let's work on it

                if (!dataExists(directory)) {

                    /*

                     * HSQL has created automatically an empty database. We need to populate it.

                     * Executes the SQL scripts bundled in the JAR. In theory, each line contains

                     * a full SQL statement. For this plugin however, we have compressed "INSERT

                     * INTO" statements using Compactor class in this package.

                     */

                    final LogRecord record =

                            Loggings.format(

                                    Level.FINE,

                                    LoggingKeys.CREATING_CACHED_EPSG_DATABASE_$1,

                                    VERSION);

                    record.setLoggerName(logger.getName());

                    logger.log(record);

                    InputStream in = ThreadedHsqlEsriFactory.class.getResourceAsStream(ZIP_FILE);

                    ZipInputStream zin =

                            new ZipInputStream(in);

                    ZipEntry ze = null;

                    byte[] buf = new byte[1024];

                    int read = 0;

                    while ((ze = zin.getNextEntry()) != null) {

                        FileOutputStream fout =

                                new FileOutputStream(new File(directory, ze.getName()));

                        while ((read = zin.read(buf)) > 0) {

                            fout.write(buf, 0, read);

                        }

                        zin.closeEntry();

                        fout.close();

                    }

                    zin.close();

                    // mark the successful creation

                    new File(directory, MARKER_FILE).createNewFile();

                }

            } catch (IOException exception) {

                SQLException e = new SQLException(Errors.format(ErrorKeys.CANT_READ_$1, ZIP_FILE));

                e.initCause(

                        exception); // TODO: inline cause when we will be allowed to target Java 6.

                throw e;

            } catch (Exception exc) {

            	exc.printStackTrace();

            }

            finally {

                if (lock != null) {

                    try {

                        lock.release();

                        lock.channel().close();

                        new File(directory, LOCK_FILE).delete();

                    } catch (IOException e) {

                        // does not matter, was just cleanup

                    }

                }

            }

        }

        EsriFactoryUsingHSQL factory =

                new EsriFactoryUsingHSQL(hints, getDataSource()) {

                    @Override

                    protected void shutdown(boolean active) throws SQLException {

                        // Disabled because finalizer shutdown causes concurrent EPSG lookup via

                        // other FactoryUsingHSQL instances using the same database URL and thus

                        // the same org.hsqldb.Database instance to fail in, for example,

                        // GeoServer gs-main unit tests.

                        // Note that createDataSource() opens the database with "shutdown=true"

                        // so the database will be shutdown when the last session is closed.

                    }

                };

        factory.setValidationQuery("CALL NOW()");

        return factory;

    }

    /**

     * @param directory

     * @return

     * @throws IOException

     */

    FileLock acquireLock(File directory) throws IOException {

        // Get a file channel for the file

        File file = new File(directory, LOCK_FILE);

        FileChannel channel = new RandomAccessFile(file, "rw").getChannel();

        // Use the file channel to create a lock on the file.

        // This method blocks until it can retrieve the lock.

        FileLock lock = channel.lock();

        // Try acquiring the lock without blocking. This method returns

        // null or throws an exception if the file is already locked.

        while (!lock.isValid()) {

            try {

                lock = channel.tryLock();

            } catch (OverlappingFileLockException e) {

                // File is already locked in this thread or virtual machine

            }

            // wait for the other process to unlock it, should take a couple of seconds

            try {

                Thread.sleep(500);

            } catch (InterruptedException e) {

                // someone waked us earlier, no problem

            }

        }

        return lock;

    }

}

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>org.gvsig</groupId>

  <artifactId>org.gvsig.geotools.proj.esri</artifactId>

  <packaging>jar</packaging>

  <version>1.0.0</version>

  <name>${project.artifactId}</name>

  <parent>

    <groupId>org.gvsig</groupId>

    <artifactId>org.gvsig.geotools.proj</artifactId>

    <version>1.0.0-SNAPSHOT</version>

  </parent>

  <description>Esri definitions and geotools plugin to register ESRI authority</description>

  <organization>

    <name>gvSIG Association</name>

    <url>http://www.gvsig.com/</url>

  </organization>

  <url>http://devel.gvsig.org/sites/${project.artifactId}/${project.version}</url>

  <dependencies>

    <dependency>

      <groupId>org.geotools</groupId>

      <artifactId>gt-referencing</artifactId>

    </dependency>

    <dependency>

      <groupId>org.geotools</groupId>

      <artifactId>gt-epsg-hsql</artifactId>

    </dependency>

    <dependency>

      <groupId>org.hsqldb</groupId>

      <artifactId>hsqldb</artifactId>

    </dependency>

    <dependency>

      <groupId>org.slf4j</groupId>

      <artifactId>slf4j-api</artifactId>

    </dependency>

    <dependency>

      <groupId>org.slf4j</groupId>

      <artifactId>slf4j-api</artifactId>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.proj.catalog.api</artifactId>

    </dependency>

  </dependencies>

</project>

            	<artifactId>org.gvsig.geotools.proj.esri</artifactId>

                <version>1.0.0</version>

            	<type>jar</type>

        	</dependency>

        	<dependency>

            	<groupId>org.gvsig</groupId>

                <groupId>org.slf4j</groupId>

                <artifactId>slf4j-api</artifactId>

                <version>1.5.5</version>

            </dependency>

            <dependency>

			<dependency>

				<groupId>org.hsqldb</groupId>

				<artifactId>hsqldb</artifactId>

				<version>2.4.1</version>

			</dependency>

        <module>org.gvsig.geotools.proj.esri</module>

        <module>org.gvsig.geotools.proj.epsggriddata</module>

					CoordinateTransformationSelectorComponent selector = manager.createCoordinateTransformSelectorComponent();

					selector.setSourceCoordinateReferenceSystem(crs23030, true);

					selector.setTargetCoordinateReferenceSystem(crs4326, true);

		//INSERT INTO ESRI VALUES(103952,'PROJCS["NAD_1983_HARN_Adj_WI_GreenLake_Feet",GEOGCS["GCS_NAD_1983_HARN_Adj_WI_GreenLake",DATUM["D_NAD_1983_HARN_Adj_WI_GL_MQ",SPHEROID["GRS_1980_Adj_WI_GL_MQ",6378375.601,298.268379664173]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",495000.0],PARAMETER["False_Northing",0.0],PARAMETER["Central_Meridian",-89.24166666666667],PARAMETER["Standard_Parallel_1",43.66666666666666],PARAMETER["Standard_Parallel_2",43.94722222222222],PARAMETER["Latitude_Of_Origin",43.09444444444445],UNIT["Foot_US",0.3048006096012192]]','NAD_1983_HARN_Adj_WI_GreenLake_Feet','GCS_NAD_1983_HARN_Adj_WI_GreenLake','D_NAD_1983_HARN_Adj_WI_GL_MQ')

		manager.getCatalogManager().getCRSDefinition("ESRI:103952");

//		INSERT INTO ESRI VALUES(30179,'PROJCS["Japan_Zone_19",GEOGCS["GCS_Tokyo",DATUM["D_Tokyo",SPHEROID["Bessel_1841",6377397.155,299.15281

//				28]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["False_Easting",0.0],PARA

//				METER["False_Northing",0.0],PARAMETER["Central_Meridian",154.0],PARAMETER["Scale_Factor",0.9999],PARAMETER["Latitude_Of_Origin",26.0]

//				,UNIT["Meter",1.0]]','Japan_Zone_19','GCS_Tokyo','D_Tokyo')

		manager.getCatalogManager().getCRSDefinition("ESRI:30179");

		//INSERT INTO ESRI VALUES(4326,'GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]','','GCS_WGS_1984','D_WGS_1984')

		manager.getCatalogManager().getCRSDefinition("ESRI:4326");

		CoordinateReferenceSystemSelectorComponent selector = manager.createCoordinateReferenceSystemSelectorComponent();

      <artifactId>org.gvsig.i18n</artifactId>

      <scope>runtime</scope>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

      <artifactId>org.gvsig.geotools.proj.esri</artifactId>

      <type>jar</type>

    </dependency>

    <dependency>

      <groupId>org.gvsig</groupId>

/**

 * gvSIG. Desktop Geographic Information System.

 *

 * Copyright (C) 2007-2018 gvSIG Association.

 *

 * This program is free software; you can redistribute it and/or

 * modify it under the terms of the GNU General Public License

 * as published by the Free Software Foundation; either version 2

 * of the License, or (at your option) any later version.

 *

 * This program is distributed in the hope that it will be useful,

 * but WITHOUT ANY WARRANTY; without even the implied warranty of

 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the

 * GNU General Public License for more details.

 *

 * You should have received a copy of the GNU General Public License

 * along with this program; if not, write to the Free Software

 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,

 * MA  02110-1301, USA.

 *

 * For any additional information, do not hesitate to contact us

 * at info AT gvsig.com, or visit our website www.gvsig.com.

 */

/**

 * Implementation of CRSCatalogManager based on Geotools

 * 

 * @author Cesar Martinez Izquierdo

 *

 */

		ArrayList<String> result = new ArrayList<String>();

		for (String auth: getAuthorityNames()) {

			List<String> authResult = search(auth, searchString, null, null);

			result.addAll(authResult);

		}

		return result;

		if ("EPSG".equals(authority) || "ESRI".equals(authority)) {

			return search(authority, searchString, null, null);

		if ("EPSG".equals(authority) || "ESRI".equals(authority)) {

			CRSAuthorityFactory factory = ReferencingFactoryFinder.getCRSAuthorityFactory(authority, null);

			if (factory instanceof ThreadedEpsgFactory) {

				ThreadedEpsgFactory epsgFactory = (ThreadedEpsgFactory) factory;

				try {

					DataSource ds = epsgFactory.getDataSource();

					Connection c = ds.getConnection();

					if ("EPSG".equals(authority)) {

						return searchEpsg(searchString, c, includeTypes, excludeTypes);

					}

					if ("ESRI".equals(authority)) {

						return searchEsri(searchString, c, includeTypes, excludeTypes);

					}

				} catch (SQLException e) {

					// TODO Auto-generated catch block

					e.printStackTrace();

				}

			}

    public synchronized List<String> searchEsri(String searchString, Connection conn,

    		CRSType[] includeTypes, CRSType[] excludeTypes) throws SQLException

    {

    	ArrayList<String> codes = new ArrayList<String>();

    	if (searchString == null) {

    		searchString = "";

    	}

    	else if (searchString.startsWith("ESRI:")) {

    		searchString = searchString.substring(5);

    	}

    	StringBuilder builder = new StringBuilder("SELECT ESRI_CODE"

                + " FROM ESRI WHERE (");

    	int codeInt = -1;

    	try {

    		codeInt = Integer.parseInt(searchString);

    		builder.append(" ESRI_CODE = ? OR");

    	}

    	catch (NumberFormatException exc) {}

    	builder.append(" LCASE(ESRI_PROJ) LIKE ? OR LCASE(ESRI_DATUM) LIKE ?"

                + " OR LCASE(ESRI_WKT) LIKE ? )");

    	if (includeTypes != null && includeTypes.length>0) {

    		boolean first = true;

    		for (CRSType type: includeTypes) {

    			String typeCondition = getEsriTypeSqlString(type);

    			if (typeCondition != null) {

    				if (first) {

    					first = false;

    					builder.append(" AND (").append(typeCondition);

    				}

    				else {

    					builder.append(" OR ").append(typeCondition);

    				}

    			}

    		}

    		if (!first) {

    			builder.append(")");

    		}

    	}

    	if (excludeTypes != null && excludeTypes.length>0) {

    		boolean first = true;

    		for (CRSType type: excludeTypes) {

    			String typeCondition = getEsriTypeSqlString(type);

    			if (typeCondition != null) {

    				if (first) {

    					first = false;

    					builder.append(" AND NOT (").append(typeCondition);

    				}

    				else {

    					builder.append(" OR ").append(typeCondition);

    				}

    			}

    		}

    		if (!first) {

    			builder.append(")");

    		}

    	}

        try {

            // FIXME: JOIN query with area of use. Maybe also with  COORD_SYS_CODE to search for coord sys name

        	searchString = "%" + searchString.toLowerCase() + "%";

            final PreparedStatement stmt = conn.prepareStatement(builder.toString());

            int paramNum = 1;

            if (codeInt>=0) {

            	stmt.setInt(paramNum++, codeInt);

            }

            stmt.setString(paramNum++, searchString);

            stmt.setString(paramNum++, searchString);

            stmt.setString(paramNum++, searchString);

            ResultSet result = stmt.executeQuery();

            while (result.next()) {

                final String code    = result.getString(1);//getString(result, 1, code);

                codes.add("ESRI:"+code);

            }

            result.close();

        } catch (SQLException exception) {

            throw exception;

        }

        conn.close();

        return codes;

    }

    protected String getEsriTypeSqlString(CRSType type) {

    	if (type == CRSType.ProjectedCRSType) {

    		return "ESRI_PROJ <> ''";

    	}

    	if (type == CRSType.GeographicCRSType) {

    		return "ESRI_PROJ = ''";

    	}

    	return null;

    }

		} catch (Exception e) {

			throw new UnsupportedCoordinateReferenceSystemException(code, e);

	@Override

	public File getSystemDataPath() {

		String directory = System.getProperty(HsqlEpsgDatabase.DIRECTORY_KEY);

		if (directory!=null) {

			return new File(directory);

		}

		return null;

	}

		if ("ESRI".equals(getAuthorityName())) {

			return export(Format.WKT1, WKTConvention.ESRI, 2);

		}

		String id = getIdentifier();

		String name = getName();

		if (id != null) {

			if (name.startsWith(id)) {

				return name;

			}

			else {

				return id + " - " + name;

			}

		}

		return name;