Geotools-PG空间库(Crud,属性查询,空间查询)

建立连接

经过测试,这套连接逻辑除了支持纯PG以外,也支持人大金仓,凡是套壳PG的都可以尝试一下。我这里的测试环境是Geosence创建的pg SDE,数据库选用的是人大金仓。

/**
  * 获取数据库连接资源
  *
  * @param connectConfig
  * @return
  * {@link PostgisNGDataStoreFactory} PostgisNGDataStoreFactory还有跟多的定制化参数可以进去看看
  * @throws Exception
  */
 public static DataStore ConnectDatabase(GISConnectConfig connectConfig) throws Exception {
     if (pgDatastore != null) {
         return pgDatastore;
     }
     //数据库连接参数配置
     Map<String, Object> params = new HashMap<String, Object>();
     // 数据库类型
     params.put(PostgisNGDataStoreFactory.DBTYPE.key, connectConfig.getType());
     params.put(PostgisNGDataStoreFactory.HOST.key, connectConfig.getHost());
     params.put(PostgisNGDataStoreFactory.PORT.key, connectConfig.getPort());
     // 数据库名
     params.put(PostgisNGDataStoreFactory.DATABASE.key, connectConfig.getDataBase());
     //用户名和密码
     params.put(PostgisNGDataStoreFactory.USER.key, connectConfig.getUser());
     params.put(PostgisNGDataStoreFactory.PASSWD.key, connectConfig.getPassword());
     // 模式名称
     params.put(PostgisNGDataStoreFactory.SCHEMA.key, "sde");
     // 最大连接
     params.put( PostgisNGDataStoreFactory.MAXCONN.key, 25);
     // 最小连接
     params.put(PostgisNGDataStoreFactory.MINCONN.key, 10);
     // 超时时间
     params.put( PostgisNGDataStoreFactory.MAXWAIT.key, 10);
     try {
         pgDatastore = DataStoreFinder.getDataStore(params);
         return pgDatastore;
     } catch (IOException e) {
         LOG.error("获取数据源信息出错");
     }
     return null;
 }

查询

  • 查询所有的表格
/**
  * 查询所有的表格
  * @return
  * @throws IOException
  */
 public List<String> getAllTables() throws IOException {
     String[] typeNames = this.dataStore.getTypeNames();
     List<String> tables = Arrays.stream(typeNames).collect(Collectors.toList());
     return tables;
 }

属性查询&&空间查询通用

 /**
    * 查询要素
    * @param layerName
    * @param filter
    * @return
    * @throws IOException
    */
   public  SimpleFeatureCollection queryFeatures(String layerName, Filter filter) throws IOException {
       SimpleFeatureCollection features = null;
       try {
           SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
           features = featureSource.getFeatures(filter);
           return features;
       } catch (Exception e) {
           e.printStackTrace();
       }
       return features;
   }
  • 属性筛选查询
    用数据库查:
    在这里插入图片描述
SELECT *FROM demoWHERE xmbh = '3308812023104'  AND zzdybh = '3308812023104003' AND zzlx = '10'

在这里插入图片描述
用代码查:

 SimpleFeatureCollection simpleFeatureCollection = pgTemplate.queryFeatures("demo", CQL.toFilter("xmbh = '3308812023104'  AND zzdybh = '3308812023104003' AND zzlx = '10'"));

在这里插入图片描述

  • 空间筛选
Geometry geometry = new WKTReader().read("Polygon ((119.13571152004580256 29.96675730309299368, 119.14239751148502933 29.62242874397260195, 119.49341206204465493 29.84975245290645063, 119.23265839591465465 30.0670471746814556, 119.13571152004580256 29.96675730309299368))");
// 直接写SQL
Filter filter = CQL.toFilter("INTERSECTS(shape," + geometry .toString() + ")");
// 或者使用FilterFactory 
Within within = ff.within(ff.property("shape"), ff.literal(geometry));
SimpleFeatureCollection simpleFeatureCollection = pgTemplate.queryFeatures("demo", within);

如果不知道使用的什么关键字就比如相交INTERSECTS,可以点进对应的这个空间关系里去看这个Name,和这个保持一致。
在这里插入图片描述

总结:这里就在于怎么去写这个Filter,可以直接使用SQL语法。也可以自己去构造,需要借助这两个类

private static FilterFactory2 spatialFilterFc = CommonFactoryFinder.getFilterFactory2(GeoTools.getDefaultHints());
private static FilterFactory propertyFilterFc = CommonFactoryFinder.getFilterFactory(null);

添加要素

/**
 * 
 * @param type 
 * @param features 需要追加的要素
 * @throws IOException
 */
public  void appendFeatures(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    ListFeatureCollection featureCollection = new ListFeatureCollection(type, features);
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    try {
        featureStore.addFeatures(featureCollection);
    } catch (IOException e) {
        e.printStackTrace();
    }
    Transaction transaction = new DefaultTransaction("appendData");
    featureStore.setTransaction(transaction);
    transaction.commit();
}

测试代码:

Geometry geometry = new WKTReader().read("Polygon ((118.41044123299997182 29.89092741100000694, 118.42024576499994737 29.83296547499998042, 118.30907619399994246 29.75101510400003235, 118.19200671200002262 29.74673207400002184, 118.41044123299997182 29.89092741100000694))");
    SimpleFeature build = CustomFeatureBuilder.build(new HashMap<String, Object>() {{
        put("xmbh", "ceshiceshi");
        put("zxmmc", "测试一把");
        put("shape", geometry);
    }}, "demo" , geometry);
    SimpleFeatureType simpleFeatureType = dataStore.getSchema("demo");
    pgTemplate.appendFeatures(simpleFeatureType, Arrays.asList(build));

构建要素的代码如下:

/**
   *  构建一个Feature
   * @param fieldsMap
   * @param typeName
   * @return
   */
  public static SimpleFeature build(Map<String, Object> fieldsMap, String typeName) {
      SimpleFeatureTypeBuilder simpleFeatureTypeBuilder = new SimpleFeatureTypeBuilder();
      List<Object> values = new ArrayList<>();
      fieldsMap.forEach((key, val) -> {
          simpleFeatureTypeBuilder.add(key, val.getClass());
          values.add(val);
      });
      simpleFeatureTypeBuilder.setName(typeName);
      SimpleFeatureType simpleFeatureType = simpleFeatureTypeBuilder.buildFeatureType();
      SimpleFeatureBuilder builder = new SimpleFeatureBuilder(simpleFeatureType);
      builder.addAll(values);
      SimpleFeature feature = builder.buildFeature(null);
      return feature;
  }

在这里插入图片描述
图形也能正常展示:
在这里插入图片描述

/**
  * 通过FeatureWriter 追加要素
  * @param type
  * @param features
  * @throws IOException
  */
 public  void appendFeatureByFeatureWriter(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
     String typeName = type.getTypeName();
     FeatureWriter<SimpleFeatureType, SimpleFeature> featureWriter = dataStore.getFeatureWriterAppend(typeName, new DefaultTransaction("appendData"));
     for (SimpleFeature feature : features) {
         SimpleFeature remoteNext = featureWriter.next();
         remoteNext.setAttributes(feature.getAttributes());
         remoteNext.setDefaultGeometry(feature.getDefaultGeometry());
         featureWriter.write();
     }
    featureWriter.close();
 }

使用FeatureWriter这个时候要注意啦,你插入的时候必须每个字段都设置值,追进源码里面发现它的SQL是写了所有字段的
源码路径:JDBCDataStore#insertNonPS

在这里插入图片描述
所以下面这种方式是不会成功的,要成功的话必须设置所有的字段对应上,我懒得弄了原理就是上面源码那样的:

// 错误示范 需要填所有字段
Geometry geometry = new WKTReader().read("Polygon ((118.41044123299997182 29.89092741100000694, 118.42024576499994737 29.83296547499998042, 118.30907619399994246 29.75101510400003235, 118.19200671200002262 29.74673207400002184, 118.41044123299997182 29.89092741100000694))");
SimpleFeature build = CustomFeatureBuilder.build(new HashMap<String, Object>() {{
    put("xmbh", "writer");
    put("zxmmc", "demo");
    put("zzdybh", "fdsa");
    put("shape", geometry);
}}, "demo" );
SimpleFeatureType simpleFeatureType = dataStore.getSchema("demo");
pgTemplate.appendFeatureByFeatureWriter(simpleFeatureType, Arrays.asList(build));

追加要素的原理都是借助于空间函数,我们如果调试源码就会发现最终生成了一个SQL类似于下面这个SQL

INSERT INTO "sde"."demo" ( "xmbh","zzdybh","zxmmc","zzlx","zxmlx","zxmbh","zgbm","jsgm","jhtz","xzgdmj","xzgddb","lxrq","wcrq","bz","txmj","bsm","czzj","shzj","gdb_geomattr_data","shape","objectid" ) VALUES ( 'ces','11111','update',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,ST_GeomFromText('POLYGON ((119.1357115200458 29.966757303092994, 119.14239751148503 29.622428743972602, 119.49341206204465 29.84975245290645, 119.23265839591465 30.067047174681456, 119.1357115200458 29.966757303092994))', 4490),2)

源码如下:

{@link JDBCDataStore#insertNonPS}
try {
  for (SimpleFeature feature : features) {
      String sql = insertSQL(featureType, feature, keysFetcher, cx);

      ((BasicSQLDialect) dialect).onInsert(st, cx, featureType);

      LOGGER.log(Level.FINE, "Inserting new feature: {0}", sql);
      if (keysFetcher.hasAutoGeneratedKeys()) {
          st.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
      } else {
          st.executeUpdate(sql);
      }

      keysFetcher.postInsert(featureType, feature, cx, st);
  }
} finally {
  closeSafe(st);
}

更新

  • 更新属性
/**
   * 更新属性
   * @param type
   * @param fieldsMap
   * @param filter
   * @throws IOException
   */
  public  void updateFeatures(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
      String typeName = type.getTypeName();
      List<Name> names =new ArrayList<>();
      FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
      Set<String> keys = fieldsMap.keySet();
      for (String field : keys) {
          Name name = new NameImpl(field);
          names.add(name);
      }
      featureStore.modifyFeatures(names.toArray(new NameImpl[names.size()]), fieldsMap.values().toArray(), filter);
  }

测试代码:

HashMap<String, Object> fieldsMap = new HashMap<String, Object>() {{
    put("xmbh", "testupdate");
    put("zxmmc", "update");
    put("zzdybh", "3308812023104003");
}};
SimpleFeatureType simpleFeatureType = dataStore.getSchema("demo");
pgTemplate.updateFeatures(simpleFeatureType, fieldsMap, CQL.toFilter(" xmbh = 'ceshiceshi'"));

在这里插入图片描述
如果你需要更新几何,只需要设置几何字段即可:

HashMap<String, Object> fieldsMap = new HashMap<String, Object>() {{
     put("xmbh", "ces");
     put("zxmmc", "update");
     put("zzdybh", "3308812023104003");
     put("shape", geometry);
 }};

我们还可以这样写

/**
 * 覆盖更新
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public  void updateFeatureFeatureReader(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    SimpleFeature simpleFeature = CustomFeatureBuilder.build(fieldsMap, typeName);
    // 设置一个 FeatureReader
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = new CollectionFeatureReader(simpleFeature);
    featureStore.setFeatures(featureReader);
    featureReader.close();
}

这里还需要注意一点,featureReaders 是覆盖更新的逻辑,所以使用的时候要谨慎一点
在这里插入图片描述

下面有这么多实现类,具体怎么组合使用就看你的想象力了:
在这里插入图片描述

删除要素

/**
 * 删除数据
 *
 * @param layerName 图层名称
 * @param filter 过滤器
 */
public  boolean deleteData(String layerName, Filter filter) {
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        FeatureStore featureStore = (FeatureStore) featureSource;
        featureStore.removeFeatures(filter);
        Transaction transaction = new DefaultTransaction("delete");
        featureStore.setTransaction(transaction);
        transaction.commit();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

完整DEMO

Demo 代码难免写的比较草率,不要喷我奥,哈哈哈哈哈

public class PgTemplate {
private final DataStore dataStore;

public PgTemplate(DataStore dataStore) {
    this.dataStore = dataStore;
}

/**
 * @param type
 * @param features 需要追加的要素
 * @throws IOException
 */
public void appendFeatures(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    ListFeatureCollection featureCollection = new ListFeatureCollection(type, features);
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    try {
        featureStore.addFeatures(featureCollection);
    } catch (IOException e) {
        e.printStackTrace();
    }
    Transaction transaction = new DefaultTransaction("appendData");
    featureStore.setTransaction(transaction);
    transaction.commit();
}

/**
 * 更新属性
 *
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public void updateFeatures(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    List<Name> names = new ArrayList<>();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    Set<String> keys = fieldsMap.keySet();
    for (String field : keys) {
        Name name = new NameImpl(field);
        names.add(name);
    }
    featureStore.modifyFeatures(names.toArray(new NameImpl[names.size()]), fieldsMap.values().toArray(), filter);
}

/**
 * 覆盖更新
 *
 * @param type
 * @param fieldsMap
 * @param filter
 * @throws IOException
 */
public void updateFeatureFeatureReader(SimpleFeatureType type, Map<String, Object> fieldsMap, Filter filter) throws IOException {
    String typeName = type.getTypeName();
    FeatureStore featureStore = (FeatureStore) dataStore.getFeatureSource(typeName);
    SimpleFeature simpleFeature = CustomFeatureBuilder.build(fieldsMap, typeName);
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = new CollectionFeatureReader(simpleFeature);
    featureStore.setFeatures(featureReader);
    featureReader.close();
}

/**
 * 通过FeatureWriter 追加要素
 *
 * @param type
 * @param features
 * @throws IOException
 */
public void appendFeatureByFeatureWriter(SimpleFeatureType type, List<SimpleFeature> features) throws IOException {
    String typeName = type.getTypeName();
    FeatureWriter<SimpleFeatureType, SimpleFeature> featureWriter = dataStore.getFeatureWriterAppend(typeName, new DefaultTransaction("appendData"));
    for (SimpleFeature feature : features) {
        SimpleFeature remoteNext = featureWriter.next();
        remoteNext.setAttributes(feature.getAttributes());
        remoteNext.setDefaultGeometry(feature.getDefaultGeometry());
        featureWriter.write();
    }
    featureWriter.close();
}

/**
 * 删除数据
 *
 * @param
 * @param
 * @param
 */
public boolean deleteData(String layerName, Filter filter) {
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        FeatureStore featureStore = (FeatureStore) featureSource;
        featureStore.removeFeatures(filter);
        Transaction transaction = new DefaultTransaction("delete");
        featureStore.setTransaction(transaction);
        transaction.commit();
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

/**
 * 查询要素
 *
 * @param layerName
 * @param filter
 * @return
 * @throws IOException
 */
public SimpleFeatureCollection queryFeatures(String layerName, Filter filter) throws IOException {
    SimpleFeatureCollection features = null;
    try {
        SimpleFeatureSource featureSource = dataStore.getFeatureSource(layerName);
        features = featureSource.getFeatures(filter);
        return features;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return features;
}

/**
 * 查询要素
 *
 * @param layerName
 * @param filter
 * @return
 * @throws IOException
 */
public SimpleFeatureCollection queryFeaturesByFeatureReader(String layerName, Filter filter) throws IOException {
    FeatureReader<SimpleFeatureType, SimpleFeature> featureReader = dataStore.getFeatureReader(new Query(layerName, filter), new DefaultTransaction("query"));
    SimpleFeatureType featureType = featureReader.getFeatureType();
    List<SimpleFeature> features = new ArrayList<>();
    while (featureReader.hasNext()) {
        SimpleFeature next = featureReader.next();
        features.add(next);
    }
    return new ListFeatureCollection(featureType, features);
}

/**
 * 查询所有的表格
 *
 * @return
 * @throws IOException
 */
public List<String> getAllTables() throws IOException {
    String[] typeNames = this.dataStore.getTypeNames();
    List<String> tables = Arrays.stream(typeNames).collect(Collectors.toList());
    return tables;
}
  • 27
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值