How to ingest data in Geomesa from PostgreSQL using JDBC

Question

I'm trying to ingest data to Geomesa (with Accumulo Datastore) using JDBC Converter.

This is my table:

OBJECTID	SHAPE
1	POLYGON ((111.25821079700006 -7.167984955999941, 112.39345734300002 -6.982487154999944, 112.60121488000004 -7.613179679999973)
2	POLYGON ((111.25821079700006 -7.167984955999941, 112.39345734300002 -6.982487154999944, 112.60121488000004 -7.613179679999973))

This is my sft:

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

This is my 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)" }
  ]
}

I'm using this command:

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

And this is the result:

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>..

Any idea how to fix this?

Answer 1

The database fields are identified in the transform by their (1-based) index, so you would need to change your converter to be:

    { name = "uid", transform = "$1"}
    { name = "shape", transform = "polygon($2)" }

See https://www.geomesa.org/documentation/stable/user/convert/jdbc.html#transform-functions