Menu
▾
▴
[Geotools-commits] svn - r38821 - in trunk/modules/plugin/jdbc/jdbc-postgis/src: main/java/org/geotools/data/postgis test/java/org/geotools/data/postgis
|
From: <svn...@os...> - 2012-06-20 22:47:51
|
Author: jdeolive
Date: 2012-06-20 15:47:41 -0700 (Wed, 20 Jun 2012)
New Revision: 38821
Modified:
trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGIS3DTestSetup.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGISTestSetup.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisDataStoreAPITestSetup.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNoPrimaryKeyTestSetup.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisSkipColumnTestSetup.java
trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisViewTestSetup.java
Log:
GEOT-4179, adding support for postgis 2.0
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISDialect.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -104,6 +104,8 @@
static final Version V_1_5_0 = new Version("1.5.0");
+ static final Version V_2_0_0 = new Version("2.0.0");
+
public PostGISDialect(JDBCDataStore dataStore) {
super(dataStore);
}
@@ -253,7 +255,7 @@
if (att instanceof GeometryDescriptor) {
// use estimated extent (optimizer statistics)
StringBuffer sql = new StringBuffer();
- sql.append("select AsText(force_2d(Envelope(ST_Estimated_Extent('");
+ sql.append("select ST_AsText(ST_force_2d(ST_Envelope(ST_Estimated_Extent('");
if(schema != null) {
sql.append(schema);
sql.append("', '");
@@ -467,8 +469,12 @@
// fall back on inspection of the first geometry, assuming uniform srid (fair assumption
// an unpredictable srid makes the table un-queriable)
- if(srid == null) {
- String sqlStatement = "SELECT SRID(\"" + columnName + "\") " +
+ //JD: In postgis 2.0 forward there is no way to leave a geometry srid unset since
+ // geometry_columns is a view populated from system tables, so we check for 0 and take
+ // that to mean unset
+
+ if(srid == null || (getVersion(cx).compareTo(V_2_0_0) >= 0 && srid == 0)) {
+ String sqlStatement = "SELECT ST_SRID(\"" + columnName + "\") " +
"FROM \"" + schemaName + "\".\"" + tableName + "\" " +
"WHERE " + columnName + " IS NOT NULL " +
"LIMIT 1";
@@ -662,27 +668,42 @@
if (geomType == null)
geomType = "GEOMETRY";
- // register the geometry type, first remove and eventual
- // leftover, then write out the real one
- String sql =
- "DELETE FROM GEOMETRY_COLUMNS"
- + " WHERE f_table_catalog=''" //
- + " AND f_table_schema = '" + schemaName + "'" //
- + " AND f_table_name = '" + tableName + "'" //
- + " AND f_geometry_column = '" + gd.getLocalName() + "'";
-
- LOGGER.fine( sql );
- st.execute( sql );
-
- sql = "INSERT INTO GEOMETRY_COLUMNS VALUES (''," //
- + "'" + schemaName + "'," //
- + "'" + tableName + "'," //
- + "'" + gd.getLocalName() + "'," //
- + dimensions + "," //
- + srid + "," //
- + "'" + geomType + "')";
- LOGGER.fine( sql );
- st.execute( sql );
+ String sql = null;
+ if (getVersion(cx).compareTo(V_2_0_0) >= 0) {
+ // postgis 2 and up we don't muck with geometry_columns, we just alter the
+ // type directly to set the geometry type and srid
+ //setup the geometry type
+ sql =
+ "ALTER TABLE \"" + schemaName + "\".\"" + tableName + "\" " +
+ "ALTER COLUMN \"" + gd.getLocalName() + "\" " +
+ "TYPE geometry (" + geomType + ", " + srid + ");";
+
+ LOGGER.fine( sql );
+ st.execute( sql );
+ }
+ else {
+ // register the geometry type, first remove and eventual
+ // leftover, then write out the real one
+ sql =
+ "DELETE FROM GEOMETRY_COLUMNS"
+ + " WHERE f_table_catalog=''" //
+ + " AND f_table_schema = '" + schemaName + "'" //
+ + " AND f_table_name = '" + tableName + "'" //
+ + " AND f_geometry_column = '" + gd.getLocalName() + "'";
+
+ LOGGER.fine( sql );
+ st.execute( sql );
+
+ sql = "INSERT INTO GEOMETRY_COLUMNS VALUES (''," //
+ + "'" + schemaName + "'," //
+ + "'" + tableName + "'," //
+ + "'" + gd.getLocalName() + "'," //
+ + dimensions + "," //
+ + srid + "," //
+ + "'" + geomType + "')";
+ LOGGER.fine( sql );
+ st.execute( sql );
+ }
// add srid checks
if (srid > -1) {
@@ -692,7 +713,7 @@
+ "\"" + tableName + "\"" //
+ " ADD CONSTRAINT \"enforce_srid_" //
+ gd.getLocalName() + "\""//
- + " CHECK (SRID(" //
+ + " CHECK (ST_SRID(" //
+ "\"" + gd.getLocalName() + "\"" //
+ ") = " + srid + ")";
LOGGER.fine( sql );
@@ -743,8 +764,10 @@
st.execute(sql);
}
}
- cx.commit();
- } finally {
+ if (!cx.getAutoCommit()) {
+ cx.commit();
+ }
+ } finally {
dataStore.closeSafe(st);
}
}
@@ -760,7 +783,7 @@
value = value.getFactory().createLineString(((LinearRing) value).getCoordinateSequence());
}
- sql.append("GeomFromText('" + value.toText() + "', " + srid + ")");
+ sql.append("ST_GeomFromText('" + value.toText() + "', " + srid + ")");
}
}
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/main/java/org/geotools/data/postgis/PostGISPSDialect.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -191,7 +191,7 @@
public void prepareGeometryValue(Geometry g, int srid, Class binding,
StringBuffer sql) {
if (g != null) {
- sql.append("GeomFromWKB(?, " + srid + ")");
+ sql.append("ST_GeomFromWKB(?, " + srid + ")");
} else {
sql.append("?");
}
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGIS3DTestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGIS3DTestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGIS3DTestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -40,10 +40,10 @@
// insert data
run("INSERT INTO \"line3d\" (\"id\",\"geom\",\"name\") VALUES (0,"
- + "GeomFromText('LINESTRING(1 1 0, 2 2 0, 4 2 1, 5 1 1)', 4326),"
+ + "ST_GeomFromText('LINESTRING(1 1 0, 2 2 0, 4 2 1, 5 1 1)', 4326),"
+ "'l1')");
run("INSERT INTO \"line3d\" (\"id\",\"geom\",\"name\") VALUES (1,"
- + "GeomFromText('LINESTRING(3 0 1, 3 2 2, 3 3 3, 3 4 5)', 4326),"
+ + "ST_GeomFromText('LINESTRING(3 0 1, 3 2 2, 3 3 3, 3 4 5)', 4326),"
+ "'l2')");
}
@@ -57,9 +57,9 @@
// insert data
run("INSERT INTO \"point3d\" (\"id\",\"geom\",\"name\") VALUES (0,"
- + "GeomFromText('POINT(1 1 1)', 4326)," + "'p1')");
+ + "ST_GeomFromText('POINT(1 1 1)', 4326)," + "'p1')");
run("INSERT INTO \"point3d\" (\"id\",\"geom\",\"name\") VALUES (1,"
- + "GeomFromText('POINT(3 0 1)', 4326)," + "'p2')");
+ + "ST_GeomFromText('POINT(3 0 1)', 4326)," + "'p2')");
}
@Override
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGISTestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGISTestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostGISTestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -16,11 +16,15 @@
*/
package org.geotools.data.postgis;
+import java.sql.Connection;
import java.util.Properties;
+import javax.sql.DataSource;
+
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.JDBCDataStoreFactory;
import org.geotools.jdbc.JDBCTestSetup;
+import org.geotools.util.Version;
/**
*
@@ -29,7 +33,25 @@
*/
public class PostGISTestSetup extends JDBCTestSetup {
+ protected Version postgisVersion;
+
@Override
+ protected void initializeDatabase() throws Exception {
+ DataSource dataSource = getDataSource();
+ Connection cx = dataSource.getConnection();
+ try {
+ postgisVersion = new PostGISDialect(new JDBCDataStore()).getVersion(cx);
+ }
+ finally {
+ cx.close();
+ }
+ }
+
+ public boolean isVersion2() {
+ return postgisVersion != null && postgisVersion.compareTo(PostGISDialect.V_2_0_0) >= 0;
+ }
+
+ @Override
protected void setUpDataStore(JDBCDataStore dataStore) {
super.setUpDataStore(dataStore);
@@ -69,11 +91,14 @@
+ "\"doubleProperty\" double precision, " //
+ "\"stringProperty\" varchar)");
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'ft1', 'geometry', 2, '4326', 'POINT')");
+ if (isVersion2()) {
+ run("ALTER TABLE \"ft1\" ALTER COLUMN \"geometry\" TYPE geometry(Point,4326);");
+ }
run("CREATE INDEX FT1_GEOMETRY_INDEX ON \"ft1\" USING GIST (\"geometry\") ");
- run("INSERT INTO \"ft1\" VALUES(0, GeometryFromText('POINT(0 0)', 4326), 0, 0.0, 'zero')");
- run("INSERT INTO \"ft1\" VALUES(1, GeometryFromText('POINT(1 1)', 4326), 1, 1.1, 'one')");
- run("INSERT INTO \"ft1\" VALUES(2, GeometryFromText('POINT(2 2)', 4326), 2, 2.2, 'two')");
+ run("INSERT INTO \"ft1\" VALUES(0, ST_GeometryFromText('POINT(0 0)', 4326), 0, 0.0, 'zero')");
+ run("INSERT INTO \"ft1\" VALUES(1, ST_GeometryFromText('POINT(1 1)', 4326), 1, 1.1, 'one')");
+ run("INSERT INTO \"ft1\" VALUES(2, ST_GeometryFromText('POINT(2 2)', 4326), 2, 2.2, 'two')");
// advance the sequence to 2
run("SELECT nextval(pg_get_serial_sequence('ft1','id'))");
run("SELECT nextval(pg_get_serial_sequence('ft1','id'))");
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisDataStoreAPITestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisDataStoreAPITestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisDataStoreAPITestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -35,13 +35,17 @@
run("CREATE TABLE \"lake\"(\"fid\" serial PRIMARY KEY, \"id\" int, "
+ "\"geom\" geometry, \"name\" varchar )");
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'lake', 'geom', 2, '4326', 'POLYGON')");
+
+ if (((PostGISTestSetup)delegate).isVersion2()) {
+ run("ALTER TABLE \"lake\" ALTER COLUMN \"geom\" TYPE geometry(Polygon,4326);");
+ }
run("CREATE INDEX LAKE_GEOM_INDEX ON \"lake\" USING GIST (\"geom\") ");
// advance the sequence to 1 to compensate for hand insertions
run("SELECT nextval(pg_get_serial_sequence('lake','fid'))");
run("INSERT INTO \"lake\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (0, 0,"
- + "GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ + "ST_GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ "'muddy')");
}
@@ -50,16 +54,20 @@
run("CREATE TABLE \"river\"(\"fid\" serial PRIMARY KEY, \"id\" int, "
+ "\"geom\" geometry, \"river\" varchar , \"flow\" real )");
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'river', 'geom', 2, '4326', 'MULTILINESTRING')");
+
+ if (((PostGISTestSetup)delegate).isVersion2()) {
+ run("ALTER TABLE \"river\" ALTER COLUMN \"geom\" TYPE geometry(MultiLineString,4326);");
+ }
run("CREATE INDEX RIVER_GEOM_INDEX ON \"river\" USING GIST (\"geom\") ");
// advance the sequence to 1 to compensate for hand insertions
run("SELECT nextval(pg_get_serial_sequence('river','fid'))");
run("INSERT INTO \"river\" (\"fid\", \"id\",\"geom\",\"river\", \"flow\") VALUES (0, 0,"
- + "GeomFromText('MULTILINESTRING((5 5, 7 4),(7 5, 9 7, 13 7),(7 5, 9 3, 11 3))',4326),"
+ + "ST_GeomFromText('MULTILINESTRING((5 5, 7 4),(7 5, 9 7, 13 7),(7 5, 9 3, 11 3))',4326),"
+ "'rv1', 4.5)");
run("INSERT INTO \"river\" (\"fid\", \"id\",\"geom\",\"river\", \"flow\") VALUES (1, 1,"
- + "GeomFromText('MULTILINESTRING((4 6, 4 8, 6 10))',4326),"
+ + "ST_GeomFromText('MULTILINESTRING((4 6, 4 8, 6 10))',4326),"
+ "'rv2', 3.0)");
}
@@ -69,6 +77,9 @@
run("CREATE TABLE \"road\"(\"fid\" serial PRIMARY KEY, \"id\" int, "
+ "\"geom\" geometry, \"name\" varchar )");
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'road', 'geom', 2, '4326', 'LINESTRING')");
+ if (((PostGISTestSetup)delegate).isVersion2()) {
+ run("ALTER TABLE \"road\" ALTER COLUMN \"geom\" TYPE geometry(LineString,4326);");
+ }
run("CREATE INDEX ROAD_GEOM_INDEX ON \"road\" USING GIST (\"geom\") ");
// advance the sequence to 2 to compensate for hand insertions
@@ -77,13 +88,13 @@
// insertions
run("INSERT INTO \"road\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (0, 0,"
- + "GeomFromText('LINESTRING(1 1, 2 2, 4 2, 5 1)',4326),"
+ + "ST_GeomFromText('LINESTRING(1 1, 2 2, 4 2, 5 1)',4326),"
+ "'r1')");
run("INSERT INTO \"road\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (1, 1,"
- + "GeomFromText('LINESTRING(3 0, 3 2, 3 3, 3 4)',4326),"
+ + "ST_GeomFromText('LINESTRING(3 0, 3 2, 3 3, 3 4)',4326),"
+ "'r2')");
run("INSERT INTO \"road\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (2, 2,"
- + "GeomFromText('LINESTRING(3 2, 4 2, 5 3)',4326)," + "'r3')");
+ + "ST_GeomFromText('LINESTRING(3 2, 4 2, 5 3)',4326)," + "'r3')");
}
@Override
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNoPrimaryKeyTestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNoPrimaryKeyTestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisNoPrimaryKeyTestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -37,7 +37,7 @@
run("CREATE INDEX LAKE_GEOM_INDEX ON \"lake\" USING GIST (\"geom\") ");
run("INSERT INTO \"lake\" (\"id\",\"geom\",\"name\") VALUES (0,"
- + "GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ + "ST_GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ "'muddy')");
}
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisSkipColumnTestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisSkipColumnTestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisSkipColumnTestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -24,7 +24,7 @@
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'skipcolumn', 'geom', 2, '4326', 'POINT')");
run("CREATE INDEX SKIPCOLUMN_GEOM_INDEX ON \"skipcolumn\" USING GIST (\"geom\") ");
- run("INSERT INTO \"skipcolumn\" VALUES(0, 0, GeometryFromText('POINT(0 0)', 4326), null, 'GeoTools')");
+ run("INSERT INTO \"skipcolumn\" VALUES(0, 0, ST_GeometryFromText('POINT(0 0)', 4326), null, 'GeoTools')");
}
Modified: trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisViewTestSetup.java
===================================================================
--- trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisViewTestSetup.java 2012-06-20 05:20:22 UTC (rev 38820)
+++ trunk/modules/plugin/jdbc/jdbc-postgis/src/test/java/org/geotools/data/postgis/PostgisViewTestSetup.java 2012-06-20 22:47:41 UTC (rev 38821)
@@ -26,7 +26,7 @@
run("INSERT INTO \"lakes_null\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (-1, -1,"
+ "null, 'empty')");
run("INSERT INTO \"lakes_null\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (0, 0,"
- + "GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ + "ST_GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ "'muddy')");
run("CREATE VIEW \"lakes_null_view\" AS SELECT * FROM \"lakes_null\"");
}
@@ -38,8 +38,11 @@
run("INSERT INTO GEOMETRY_COLUMNS VALUES('', 'public', 'lakes', 'geom', 2, '4326', 'POLYGON')");
run("CREATE INDEX LAKES_GEOM_INDEX ON \"lakes\" USING GIST (\"geom\") ");
+ if (((PostGISTestSetup)delegate).isVersion2()) {
+ run("ALTER TABLE \"lakes\" ALTER COLUMN \"geom\" TYPE geometry(Polygon,4326);");
+ }
run("INSERT INTO \"lakes\" (\"fid\", \"id\",\"geom\",\"name\") VALUES (0, 0,"
- + "GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ + "ST_GeomFromText('POLYGON((12 6, 14 8, 16 6, 16 4, 14 4, 12 6))',4326),"
+ "'muddy')");
}
|
