skrueger/db/PGUtil.java

package skrueger.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;
import org.opengis.referencing.crs.CoordinateReferenceSystem;

import schmitzm.lang.LangUtil;

/**
 * Hilfsmethoden für PostgreSQL
 */
public class PGUtil {
        static final Logger log = Logger.getLogger(PGUtil.class);

        /**
         * Querys the <code>geometry_columns</code> table is part of every postgis
         * installation and must/should describe the geometry columns and tables.
         */
        static public String[] getColumnsDescribedInGeometryColumnsTable(Statement s) {
                String[] columns = new String[0];
                try {
                        ResultSet askGeoe = s
                                        .executeQuery("SELECT f_table_name FROM geometry_columns;");
                        while (askGeoe.next()) {
                                columns = LangUtil.extendArray(columns, askGeoe.getString(1));
                        }
                } catch (SQLException e) {
                        log.error(e, e);
                }

                return columns;

        }

        public static int getSridForCRS(CoordinateReferenceSystem crs) {
                String full = crs.toWKT().toUpperCase(); // ...
                // AUTHORITY["EPSG","900913"]]
                String s1 = "AUTHORITY[\"EPSG\",\"";
                int p1 = full.indexOf(s1);
                if (p1 >= 0) {
                        String s = full.substring(p1 + s1.length());
                        int p2 = s.indexOf("\"]");
                        if (p2 > 0) {
                                s = s.substring(0, p2);
                                return Integer.parseInt(s);
                        }
                }
                return -1;
        }

        public static void createOrUpdateGeometrsColumnsEntry(String tableName,
                        String geoColumnName, CoordinateReferenceSystem crs) {
                createOrUpdateGeometrsColumnsEntry(tableName, geoColumnName,
                                getSridForCRS(crs));
        }

        /**
         * Creates or Updates
         * 
         * @param tableName
         * @param geoColumnName
         * @param srid
         */
        public static void createOrUpdateGeometrsColumnsEntry(String tableName,
                        String geoColumnName, int srid) {

                String createGeometryEntrySQL = "INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, \"type\") VALUES ( '', 'public', '"
                                + tableName
                                + "', '"
                                + geoColumnName
                                + "', 2, "
                                + srid
                                + ",'MULTIPOLYGON' ) LIMIT 1;";
        }

        // TODO isGeoColumn();

        /**
         * @param binding
         *            Eine "einfache" Javaklasse, die in PG abgebildet werden soll.
         * @return einen PG-spezifischen Datentypenamen für einen Javatyp zurück,
         *         z.b. "double precision" für <code>Double.class</code>
         */
        public static String getColTypeName(Class<?> binding) {

                if (binding.isAssignableFrom(Double.class)) {
                        return "double precision"; // eg 'number' at oracle
                } else if (binding.isAssignableFrom(String.class)) {
                        return "text";
                } else if (binding.isAssignableFrom(Integer.class)) {
                        return "integer";
                } else if (binding.isAssignableFrom(Long.class)) {
                        return "bigint";
                }

                throw new RuntimeException("DB Type mapping for " + binding
                                + " not yet implemented.");
        }

}
1	package skrueger.db;
2
3	import java.sql.ResultSet;
4	import java.sql.SQLException;
5	import java.sql.Statement;
6
7	import org.apache.log4j.Logger;
8	import org.opengis.referencing.crs.CoordinateReferenceSystem;
9
10	import schmitzm.lang.LangUtil;
11
12	/**
13	* Hilfsmethoden für PostgreSQL
14	*/
15	public class PGUtil {
16	static final Logger log = Logger.getLogger(PGUtil.class);
17
18	/**
19	* Querys the <code>geometry_columns</code> table is part of every postgis
20	* installation and must/should describe the geometry columns and tables.
21	*/
22	static public String[] getColumnsDescribedInGeometryColumnsTable(Statement s) {
23	String[] columns = new String[0];
24	try {
25	ResultSet askGeoe = s
26	.executeQuery("SELECT f_table_name FROM geometry_columns;");
27	while (askGeoe.next()) {
28	columns = LangUtil.extendArray(columns, askGeoe.getString(1));
29	}
30	} catch (SQLException e) {
31	log.error(e, e);
32	}
33
34	return columns;
35
36	}
37
38	public static int getSridForCRS(CoordinateReferenceSystem crs) {
39	String full = crs.toWKT().toUpperCase(); // ...
40	// AUTHORITY["EPSG","900913"]]
41	String s1 = "AUTHORITY[\"EPSG\",\"";
42	int p1 = full.indexOf(s1);
43	if (p1 >= 0) {
44	String s = full.substring(p1 + s1.length());
45	int p2 = s.indexOf("\"]");
46	if (p2 > 0) {
47	s = s.substring(0, p2);
48	return Integer.parseInt(s);
49	}
50	}
51	return -1;
52	}
53
54	public static void createOrUpdateGeometrsColumnsEntry(String tableName,
55	String geoColumnName, CoordinateReferenceSystem crs) {
56	createOrUpdateGeometrsColumnsEntry(tableName, geoColumnName,
57	getSridForCRS(crs));
58	}
59
60	/**
61	* Creates or Updates
62	*
63	* @param tableName
64	* @param geoColumnName
65	* @param srid
66	*/
67	public static void createOrUpdateGeometrsColumnsEntry(String tableName,
68	String geoColumnName, int srid) {
69
70	String createGeometryEntrySQL = "INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, \"type\") VALUES ( '', 'public', '"
71	+ tableName
72	+ "', '"
73	+ geoColumnName
74	+ "', 2, "
75	+ srid
76	+ ",'MULTIPOLYGON' ) LIMIT 1;";
77	}
78
79	// TODO isGeoColumn();
80
81	/**
82	* @param binding
83	* Eine "einfache" Javaklasse, die in PG abgebildet werden soll.
84	* @return einen PG-spezifischen Datentypenamen für einen Javatyp zurück,
85	* z.b. "double precision" für <code>Double.class</code>
86	*/
87	public static String getColTypeName(Class<?> binding) {
88
89	if (binding.isAssignableFrom(Double.class)) {
90	return "double precision"; // eg 'number' at oracle
91	} else if (binding.isAssignableFrom(String.class)) {
92	return "text";
93	} else if (binding.isAssignableFrom(Integer.class)) {
94	return "integer";
95	} else if (binding.isAssignableFrom(Long.class)) {
96	return "bigint";
97	}
98
99	throw new RuntimeException("DB Type mapping for " + binding
100	+ " not yet implemented.");
101	}
102
103	}
Name	Value
svn:eol-style	native
svn:keywords	Id URL
svn:mime-type	text/plain