如何使用JDBC从PostgreSQL向Geomesa导入数据？导入失败求助

问题 #

尝试使用JDBC Converter将PostgreSQL中的数据导入基于Accumulo的Geomesa，PostgreSQL表包含OBJECTID和SHAPE字段（SHAPE为多边形几何数据），但执行导入后所有要素均失败，报错shape字段解析失败。

表结构 #

SFT配置 #

geomesa = {
  sfts = {
    example = {
      attributes = [
        { name = "uid", type = "String", index = true }
        { name = "shape", type = "Polygon",  default = true , srid = 4326  }
      ]
    }
  }
}

Converter配置 #

geomesa.converters.example = {
  type       = "jdbc"
  connection = "jdbc:postgresql://localhost:port/databasename?currentSchema=sde&user=myuser&password=mypassword"
  id-field   = "toString($uid)"
  fields = [
    { name = "uid", type = "string", transform = "$uid"}
    { name = "shape", type = "geometry", transform = "geometry($shape)" }
  ]
}

执行命令 #

echo "SELECT OBJECTID, ST_AsText(shape) FROM table" | geomesa-accumulo ingest -u user -p password -c catalog -s test.sft -C test.conf

执行结果 #

INFO  Schema 'test' exists
INFO  Running ingestion in local mode
2023-06-12 08:31:21,478 DEBUG [org.locationtech.geomesa.convert.jdbc.JdbcConverter] Failed to evaluate field 'shape' on line 1
2023-06-12 08:31:21,479 DEBUG [org.locationtech.geomesa.convert.jdbc.JdbcConverter] Failed to evaluate field 'shape' on line 2
2023-06-12 08:31:21,745 INFO  [org.locationtech.geomesa.tools.user] Local ingestion complete in 00:00:02
2023-06-12 08:31:21,746 INFO  [org.locationtech.geomesa.tools.user] Ingested 0 features and failed to ingest 2 features for file: <stdin>..

解决方法 #

1. 修复PostgreSQL中无效的WKT数据 #

第一条数据的SHAPE字段WKT字符串缺少闭合右括号，Geomesa几何解析器无法识别无效格式，需先清理数据：

UPDATE table SET SHAPE = ST_GeomFromText('POLYGON ((111.25821079700006 -7.167984955999941, 112.39345734300002 -6.982487154999944, 112.60121488000004 -7.613179679999973))') WHERE OBJECTID = 1;

2. 对齐Converter字段映射与查询结果 #

Converter中uid字段引用$uid，但SQL查询返回列名为OBJECTID，需修正映射：

方式一：修改Converter配置

geomesa.converters.example = {
  type       = "jdbc"
  connection = "jdbc:postgresql://localhost:port/databasename?currentSchema=sde&user=myuser&password=mypassword"
  id-field   = "toString($OBJECTID)"
  fields = [
    { name = "uid", type = "string", transform = "$OBJECTID"}
    { name = "shape", type = "geometry", transform = "geometry($shape)" }
  ]
}

方式二：修改SQL查询别名

SELECT OBJECTID AS uid, ST_AsText(shape) AS shape FROM table

3. 明确指定坐标系解析 #

在Converter的transform中显式指定SRID，确保解析时坐标系匹配：

{ name = "shape", type = "geometry", transform = "geometry($shape, 4326)" }

4. 开启详细日志定位问题 #

添加-v参数获取更详细的错误日志，辅助排查：

echo "SELECT OBJECTID, ST_AsText(shape) FROM table" | geomesa-accumulo ingest -u user -p password -c catalog -s test.sft -C test.conf -v

内容的提问来源于stack exchange，提问作者auliama