Revision: 5507 http://sourceforge.net/p/jump-pilot/code/5507 Author: michaudm Date: 2017-10-06 21:39:34 +0000 (Fri, 06 Oct 2017) Log Message: ----------- Add options to use more precise geometry type in postgis driver
Modified Paths: -------------- core/trunk/src/org/openjump/core/ui/plugin/datastore/DataStoreSaveDriverPanel.java core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStorePanel.java core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStoreWizard.java core/trunk/src/org/openjump/core/ui/plugin/datastore/WritableDataStoreDataSource.java core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISDataStoreDataSource.java core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISSaveDataSourceQueryChooser.java Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/DataStoreSaveDriverPanel.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/DataStoreSaveDriverPanel.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/DataStoreSaveDriverPanel.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -33,11 +33,13 @@ public static final String KEY = DataStoreSaveDriverPanel.class.getName(); - private static final String WRITE_3D_GEOM = I18N.get(KEY + ".write-3d-geometries"); - private static final String CONVERT_NAN_Z = I18N.get(KEY + ".convert-nan-z"); - private static final String CREATE_DB_PK = I18N.get(KEY + ".create-database-primary-key"); - private static final String NORMALIZED_TABLE_NAME = I18N.get(KEY + ".normalized-table-name-key"); - private static final String NORMALIZED_COLUMN_NAMES = I18N.get(KEY + ".normalized-column-names-key"); + private static final String WRITE_3D_GEOM = I18N.get(KEY + ".write-3d-geometries"); + private static final String CONVERT_NAN_Z = I18N.get(KEY + ".convert-nan-z"); + private static final String NARROW_GEOMETRY_TYPE = I18N.get(KEY + ".narrow-geometry-type"); + private static final String CONVERT_TO_MULTIGEOMETRY = I18N.get(KEY + ".convert-to-multigeometry"); + private static final String CREATE_DB_PK = I18N.get(KEY + ".create-database-primary-key"); + private static final String NORMALIZED_TABLE_NAME = I18N.get(KEY + ".normalized-table-name-key"); + private static final String NORMALIZED_COLUMN_NAMES = I18N.get(KEY + ".normalized-column-names-key"); // UI elements private ConnectionPanel connectionPanel; @@ -44,8 +46,9 @@ private JComboBox<String> tableComboBox; private JCheckBox createPrimaryKeyCheckBox; private JCheckBox write3dGeomCheckBox; - //private JCheckBox writeMultiGeomCheckBox; private JTextField convertNaNZTextField; + private JCheckBox narrowGeometryTypeCheckBox; + private JCheckBox convertToMultiGeometryCheckBox; private JCheckBox normalizedTableNameCheckBox; private JCheckBox normalizedColumnNamesCheckBox; private OKCancelPanel okCancelPanel = new OKCancelPanel(); @@ -119,13 +122,6 @@ add(createPrimaryKeyCheckBox); // Geometry dimension key checkbox - //writeMultiGeomCheckBox = new JCheckBox(WRITE_MULTI_GEOM); - //writeMultiGeomCheckBox.setSelected(false); - //gbConstraints.gridy += 1; - //gbLayout.setConstraints(writeMultiGeomCheckBox, gbConstraints); - //add(writeMultiGeomCheckBox); - - // Geometry dimension key checkbox write3dGeomCheckBox = new JCheckBox(WRITE_3D_GEOM); write3dGeomCheckBox.setSelected(false); gbConstraints.gridy += 1; @@ -158,6 +154,21 @@ } }); + // narrowGeometryTypeCheckBox checkbox + narrowGeometryTypeCheckBox = new JCheckBox(NARROW_GEOMETRY_TYPE); + narrowGeometryTypeCheckBox.setSelected(false); + gbConstraints.gridx = 0; + gbConstraints.gridy += 1; + gbLayout.setConstraints(narrowGeometryTypeCheckBox, gbConstraints); + add(narrowGeometryTypeCheckBox); + + // convertToMultiGeometryCheckBox checkbox + convertToMultiGeometryCheckBox = new JCheckBox(CONVERT_TO_MULTIGEOMETRY); + convertToMultiGeometryCheckBox.setSelected(false); + gbConstraints.gridy += 1; + gbLayout.setConstraints(convertToMultiGeometryCheckBox, gbConstraints); + add(convertToMultiGeometryCheckBox); + // Normalize column names checkbox normalizedTableNameCheckBox = new JCheckBox(NORMALIZED_TABLE_NAME); normalizedTableNameCheckBox.setSelected(false); @@ -273,6 +284,14 @@ return Double.parseDouble(convertNaNZTextField.getText()); } + public boolean isNarrowGeometryType() { + return narrowGeometryTypeCheckBox.isSelected(); + } + + public boolean isConvertToMultiGeometry() { + return convertToMultiGeometryCheckBox.isSelected(); + } + public boolean isNormalizedTableName() { return normalizedTableNameCheckBox.isSelected(); } Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStorePanel.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStorePanel.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStorePanel.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -85,6 +85,8 @@ setData(WritableDataStoreDataSource.DATASET_NAME_KEY, null); setData(WritableDataStoreDataSource.GEOM_DIM_KEY, null); setData(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY, null); + setData(WritableDataStoreDataSource.NARROW_GEOMETRY_TYPE_KEY, null); + setData(WritableDataStoreDataSource.CONVERT_TO_MULTIGEOMETRY_KEY, null); setData(WritableDataStoreDataSource.CREATE_PK, null); //setData(WritableDataStoreDataSource.DATAKEY_NORMALIZE_TABLE_NAME, null); setData(WritableDataStoreDataSource.NORMALIZED_COLUMN_NAMES, null); @@ -96,9 +98,10 @@ setData(WritableDataStoreDataSource.CONNECTION_DESCRIPTOR_KEY, getConnectionDescriptor()); setData(WritableDataStoreDataSource.DATASET_NAME_KEY, getData(SaveWizardPlugIn.DATAKEY_SIMPLIFIED_LAYERNAME)); - //setData(WritableDataStoreDataSource.MULTI_GEOMETRY_KEY, writeCreateMultiGeometriesSelected()); setData(WritableDataStoreDataSource.GEOM_DIM_KEY, writeCreate3dGeometriesSelected()?3:2); setData(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY, nanZToValue()); + setData(WritableDataStoreDataSource.NARROW_GEOMETRY_TYPE_KEY, isNarrowGeometryType()); + setData(WritableDataStoreDataSource.CONVERT_TO_MULTIGEOMETRY_KEY, isConvertToMultiGeometry()); setData(WritableDataStoreDataSource.CREATE_PK, isCreatePrimaryKeyColumnSelected()); //setData(WritableDataStoreDataSource.DATAKEY_NORMALIZE_TABLE_NAME, isNormalizedTableName()); setData(WritableDataStoreDataSource.NORMALIZED_COLUMN_NAMES, isNormalizedColumnNames()); Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStoreWizard.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStoreWizard.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/SaveToDataStoreWizard.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -66,13 +66,23 @@ false, context.getWorkbenchContext()); - writableDS.getProperties().put(WritableDataStoreDataSource.CONNECTION_DESCRIPTOR_KEY, dialog.getData(WritableDataStoreDataSource.CONNECTION_DESCRIPTOR_KEY)); - writableDS.getProperties().put(WritableDataStoreDataSource.DATASET_NAME_KEY, dialog.getData(WritableDataStoreDataSource.DATASET_NAME_KEY)); - writableDS.getProperties().put(WritableDataStoreDataSource.CREATE_PK, dialog.getData(WritableDataStoreDataSource.CREATE_PK)); - writableDS.getProperties().put(WritableDataStoreDataSource.GEOM_DIM_KEY, dialog.getData(WritableDataStoreDataSource.GEOM_DIM_KEY)); - writableDS.getProperties().put(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY, dialog.getData(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.CONNECTION_DESCRIPTOR_KEY, + dialog.getData(WritableDataStoreDataSource.CONNECTION_DESCRIPTOR_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.DATASET_NAME_KEY, + dialog.getData(WritableDataStoreDataSource.DATASET_NAME_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.CREATE_PK, + dialog.getData(WritableDataStoreDataSource.CREATE_PK)); + writableDS.getProperties().put(WritableDataStoreDataSource.GEOM_DIM_KEY, + dialog.getData(WritableDataStoreDataSource.GEOM_DIM_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY, + dialog.getData(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.NARROW_GEOMETRY_TYPE_KEY, + dialog.getData(WritableDataStoreDataSource.NARROW_GEOMETRY_TYPE_KEY)); + writableDS.getProperties().put(WritableDataStoreDataSource.CONVERT_TO_MULTIGEOMETRY_KEY, + dialog.getData(WritableDataStoreDataSource.CONVERT_TO_MULTIGEOMETRY_KEY)); if ((boolean)dialog.getData(WritableDataStoreDataSource.CREATE_PK)) { - writableDS.getProperties().put(WritableDataStoreDataSource.EXTERNAL_PK_KEY, WritableDataStoreDataSource.DEFAULT_PK_NAME); + writableDS.getProperties().put(WritableDataStoreDataSource.EXTERNAL_PK_KEY, + WritableDataStoreDataSource.DEFAULT_PK_NAME); } writableDS.getProperties().put( Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/WritableDataStoreDataSource.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/WritableDataStoreDataSource.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/WritableDataStoreDataSource.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -8,6 +8,7 @@ import com.vividsolutions.jts.geom.*; import com.vividsolutions.jump.datastore.DataStoreDriver; +import com.vividsolutions.jump.datastore.GeometryColumn; import com.vividsolutions.jump.datastore.SQLUtil; import com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesDSConnection; import com.vividsolutions.jump.workbench.WorkbenchContext; @@ -51,13 +52,14 @@ public static final String MANAGE_CONFLICTS = "Manage conflicts"; // Update options (write to database) : don't translate, these are map keys - public static final String EXTERNAL_PK_KEY = "External PK"; - public static final String SRID_KEY = "SRID"; - public static final String MULTI_GEOMETRY_KEY = "MultiGeometries"; - public static final String GEOM_DIM_KEY = "Dimension"; - public static final String NAN_Z_TO_VALUE_KEY = "NaN Z to value"; - public static final String CREATE_PK = "Create PK"; - public static final String NORMALIZED_COLUMN_NAMES = "Normalized Column Names"; + public static final String EXTERNAL_PK_KEY = "External PK"; + public static final String SRID_KEY = "SRID"; + public static final String GEOM_DIM_KEY = "Dimension"; + public static final String NAN_Z_TO_VALUE_KEY = "NaN Z to value"; + public static final String NARROW_GEOMETRY_TYPE_KEY = "Narrow geometry type"; + public static final String CONVERT_TO_MULTIGEOMETRY_KEY = "Convert to multigeometry"; + public static final String CREATE_PK = "Create PK"; + public static final String NORMALIZED_COLUMN_NAMES = "Normalized Column Names"; public static final String DEFAULT_PK_NAME = "gid"; @@ -119,7 +121,7 @@ } public void setMultiGeometry(boolean multi) { - getProperties().put(MULTI_GEOMETRY_KEY, multi); + getProperties().put(CONVERT_TO_MULTIGEOMETRY_KEY, multi); } public void setCoordDimension(int dbCoordDim) { @@ -192,11 +194,14 @@ String[] datasetName = SQLUtil.splitTableName((String) getProperties().get(DATASET_NAME_KEY)); schemaName = datasetName[0]; tableName = datasetName[1]; - String geometryColumn = (String)getProperties().get(WritableDataStoreDataSource.GEOMETRY_ATTRIBUTE_NAME_KEY); + //String geometryColumn = (String)getProperties().get(WritableDataStoreDataSource.GEOMETRY_ATTRIBUTE_NAME_KEY); boolean createPrimaryKey = (Boolean)getProperties().get(WritableDataStoreDataSource.CREATE_PK); int srid = getProperties().get(SRID_KEY)==null ? 0 : (Integer)getProperties().get(SRID_KEY); - boolean multi = getProperties().get(MULTI_GEOMETRY_KEY) == null ? - false : (boolean)getProperties().get(MULTI_GEOMETRY_KEY); + boolean narrow = getProperties().get(NARROW_GEOMETRY_TYPE_KEY) != null && + (boolean)getProperties().get(NARROW_GEOMETRY_TYPE_KEY); + boolean multi = getProperties().get(CONVERT_TO_MULTIGEOMETRY_KEY) != null && + (boolean)getProperties().get(CONVERT_TO_MULTIGEOMETRY_KEY); + Class geometryType = getGeometryType(featureCollection, narrow, multi); int dim = getProperties().get(GEOM_DIM_KEY)==null? getGeometryDimension(featureCollection, 3) : (Integer)getProperties().get(GEOM_DIM_KEY); @@ -219,8 +224,14 @@ // if createPrimaryKey=true, it will be re-created // if createPrimaryKey=false, old gid will be considered as a normal attribute featureCollection.getFeatureSchema().removeExternalPrimaryKey(); - createAndPopulateTable(conn, - featureCollection, srid, "GEOMETRY", multi, dim, normalizedColumnNames); + createAndPopulateTable( + conn, + featureCollection, + srid, + geometryType.getSimpleName(), + multi, + dim, + normalizedColumnNames); if (createPrimaryKey) { addDBPrimaryKey(conn, DEFAULT_PK_NAME); // @TODO reload part is kept out of the transaction because it uses @@ -351,13 +362,12 @@ boolean quoted = schema.getAttributeType(schema.getExternalPrimaryKeyIndex()) == AttributeType.STRING; String quoteKey = quoted ? "'" : ""; - StringBuilder sb = new StringBuilder("UPDATE ") - .append(SQLUtil.compose(schemaName, tableName)) - .append(" SET \"").append(schema.getAttributeName(attribute)).append("\" = ?") - .append(" WHERE \"").append(primaryKeyName).append("\" = ") - .append(quoteKey).append(feature.getAttribute(primaryKeyName)).append(quoteKey).append(";"); + String query = "UPDATE " + SQLUtil.compose(schemaName, tableName) + + " SET \"" + schema.getAttributeName(attribute) + "\" = ?" + + " WHERE \"" + primaryKeyName + "\" = " + quoteKey + + feature.getAttribute(primaryKeyName) + quoteKey + ";"; - PreparedStatement pstmt = conn.getJdbcConnection().prepareStatement(sb.toString()); + PreparedStatement pstmt = conn.getJdbcConnection().prepareStatement(query); AttributeType type = schema.getAttributeType(attribute); if (feature.getAttribute(attribute) == null) pstmt.setObject(1, null); else if (type == AttributeType.STRING) pstmt.setString(1, feature.getString(attribute)); @@ -609,4 +619,43 @@ return 2; } + /** + * Determine database geometry type according to + * <ul> + * <li>values present in the feature collection</li> + * <li>narrow attribute : true means that we want to use the most specific + * attribute type able to represent all geometries of the collection</li> + * <li>multi parameter : true means that we previously transform single + * geometry types into multigeometry types to be able to use the same + * type (multi) for geometries of same dimension (single or multi)</li> + * </ul> + */ + private static Class getGeometryType(FeatureCollection coll, boolean narrow, boolean multi) { + if (!narrow && !multi) return Geometry.class; + Class[] classes = new Class[]{ + Point.class, + LineString.class, + Polygon.class, + MultiPoint.class, + MultiLineString.class, + MultiPolygon.class + }; + int[] types = new int[]{0,0,0,0,0,0}; + for (Iterator it = coll.iterator() ; it.hasNext() ; ) { + Geometry geom = ((Feature)it.next()).getGeometry(); + Class currentClazz = geom.getClass(); + if (currentClazz == GeometryCollection.class) return Geometry.class; + int index = geom.getDimension() + ((geom instanceof GeometryCollection)?3:0); + types[index]++; + } + if (multi) types = new int[]{0,0,0,types[0]+types[3],types[1]+types[4],types[2]+types[5]}; + Class firstClass = null, lastClass = null; + for (int i = 0 ; i < 6 ; i++) { + if (firstClass == null && types[i]>0) firstClass = classes[i]; + if (types[i]>0) lastClass = classes[i]; + } + if (firstClass == lastClass) return firstClass; + else return Geometry.class; + } + } Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISDataStoreDataSource.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISDataStoreDataSource.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISDataStoreDataSource.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -80,8 +80,8 @@ } // Get features - FeatureInputStream featureInputStream = null; - FeatureDataset featureDataset = null; + FeatureInputStream featureInputStream; + FeatureDataset featureDataset; try { featureInputStream = pgConnection.execute(adhocQuery); featureDataset = new FeatureDataset(featureInputStream.getFeatureSchema()); @@ -146,19 +146,7 @@ * its reference in the PostGIS's geometry_columns table (PostGIS < 2). */ protected void deleteTableQuery(SpatialDatabasesDSConnection conn) throws SQLException { - try { - // Try to delete dbTable AND the corresponding rows in geometry_columns table - if (schemaName == null) { - conn.getJdbcConnection().createStatement().execute("SELECT DropGeometryTable( '" + - tableName + "' );"); - } else { - conn.getJdbcConnection().createStatement().execute("SELECT DropGeometryTable( '" + - schemaName + "' , '" + tableName + "' );"); - } - } catch(SQLException e) { - // If DropGeometryTable failed, try a simple DROP TABLE statement - conn.getJdbcConnection().createStatement().execute("DROP TABLE " + SQLUtil.compose(schemaName, tableName) + ";"); - } + conn.getJdbcConnection().createStatement().execute("DROP TABLE " + SQLUtil.compose(schemaName, tableName) + ";"); } /** @@ -169,7 +157,7 @@ * @param geometryType geometry type * @param dim geometry dimension * @param normalizedColumnNames whether columns names have to be normalized or not - * @throws SQLException + * @throws SQLException if an exception occured during the query processing */ protected void createAndPopulateTable( SpatialDatabasesDSConnection conn, Modified: core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISSaveDataSourceQueryChooser.java =================================================================== --- core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISSaveDataSourceQueryChooser.java 2017-10-06 21:38:23 UTC (rev 5506) +++ core/trunk/src/org/openjump/core/ui/plugin/datastore/postgis2/PostGISSaveDataSourceQueryChooser.java 2017-10-06 21:39:34 UTC (rev 5507) @@ -42,7 +42,7 @@ this.dataSource = dataSource; this.context = context; panel = new PostGISSaveDriverPanel(context); - properties = new HashMap<String,Object>(); + properties = new HashMap<>(); } /** @@ -72,7 +72,7 @@ ((WritableDataStoreDataSource)query.getDataSource()).setTableAlreadyCreated(false); query.getDataSource().getProperties().put( WritableDataStoreDataSource.NORMALIZED_COLUMN_NAMES, panel.isNormalizedColumnNames()); - List<DataSourceQuery> queries = new ArrayList<DataSourceQuery>(); + List<DataSourceQuery> queries = new ArrayList<>(); queries.add(query); return queries; @@ -136,6 +136,8 @@ properties.put(WritableDataStoreDataSource.CREATE_PK, panel.isCreatePrimaryKeyColumnSelected()); properties.put(WritableDataStoreDataSource.GEOM_DIM_KEY, panel.writeCreate3dGeometriesSelected()?3:2); properties.put(WritableDataStoreDataSource.NAN_Z_TO_VALUE_KEY, panel.nanZToValue()); + properties.put(WritableDataStoreDataSource.NARROW_GEOMETRY_TYPE_KEY, panel.isNarrowGeometryType()); + properties.put(WritableDataStoreDataSource.CONVERT_TO_MULTIGEOMETRY_KEY, panel.isConvertToMultiGeometry()); if (panel.isCreatePrimaryKeyColumnSelected()) { properties.put(WritableDataStoreDataSource.EXTERNAL_PK_KEY, WritableDataStoreDataSource.DEFAULT_PK_NAME); } ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel