1

I tried to load Postresql data into Geomesa (with a Cassandra datastore), by the JDBC Converter.

Loading from shape works fine, so the Cassandra and GeoMesa setup is okay

Next I tried to load data from PostgreSQL

Command:

echo "SELECT year, geom, grondgebruik, crop_code, crop_name, fieldid, global_id, area, perimeter, geohash FROM v_gewaspercelen2018" | bin/geomesa-cassandra ingest -c catalog -P cassandraserver:9042 -k agrodatacube -f parcel -C geomesa.converters.parcel -u -p

The converter definition file geomesa.converters.parcel looks like this:

geomesa.converters.parcel = {

type = "jdbc"

connection = "dbc:postgresql://postgresserver:5432/agrodatacube"

id-field="toString($5)"

fields = [

    { name = "fieldid",    transform = "$5"        }

    { name = "global_id",    transform = "$6"        }

    { name = "year",    transform = "$0"         }

    { name = "area",  transform = "$7"        }

    { name = "perimeter",  transform = "$8"         }

    { name = "grondgebruik",   transform = "$2"      }

    { name = "crop_code",    transform = "$3"     }

    { name = "crop_name",   transform = "$4"       }

    { name = "geohash",   transform = "$9"     }

    { name = "geom",   transform = "$1"      }

]
}

The geomesa output is:

INFO  Schema 'parcel' exists

INFO  Running ingestion in local mode

INFO  Ingesting from stdin with 1 thread
[                                                         ]   0% complete 0 i[                                                            ]   0% complete 0 ingested 0 failed in 00:00:01

ERROR Fatal error running local ingest worker on <stdin>

[                                                            ]   0% complete 0 i[                                                            ]   0% complete 0 ingested 0 failed in 00:00:01

INFO  Local ingestion complete in 00:00:01

INFO  Ingested 0 features with no failures for file: <stdin>

WARN  Some files caused errors, ingest counts may not be accurate

Does someone have a clue what is wrong here?

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
hugo_wur
  • 11
  • 2

2 Answers2

2

You can check in the logs folder for more detailed errors. However, just at a first glance, the JDBC converter follows standard result set numbering, meaning the first field is $1 (not $0). In addition, you may need to transform your geometry with a transform function, i.e. geometry($2).

Emilio Lahr-Vivaz
  • 1,439
  • 6
  • 5
0

Thanks Emilio, both suggestions made sence!

  1. Made the converter field count start at 1
  2. Inside the converter definition file changed

{ name = "geom", transform = "$2" }

into

{ name = "geom", transform = "geometry($2)" }

  1. The SQL Select command should be:

SELECT year, ST_AsText(geom), .... FROM v_gewaspercelen2018

By the way, username and password are part of the connection-string (which is inside file geomesa.converters.parcel):

connection = "dbc:postgresql://postgresserver:5432/agrodatacube?user=username&password=password"

So the -u and -p flags do not appear in the final command:

echo "SELECT year, ST_AsText(geom), grondgebruik, crop_code, crop_name, fieldid, global_id, area, perimeter, geohash FROM v_gewaspercelen2018" | bin/geomesa-cassandra ingest -c catalog -P cassandraserver:9042 -k agrodatacube -f parcel -C geomesa.converters.parcel

With these changes it works.

Thanks again!

Hugo

hugo_wur
  • 11
  • 2