Support geometry data type for Postgres (postgis extension)

[pauldewaal-disy]

Is your feature request related to a problem? Please describe.
I am trying to generate geometry data for PostgresDB with postgis extension. I get an DB-error, if I try type="binary".

Describe the solution you'd like
I would like Benerator-CE to support Geometry data types for generating data from one Postgres-schema to the other. For example, take random geometry value from import-schema and write it to export-schema, so from one Postgres table to the other.

Describe alternatives you've considered
I can't think of any alternatives. I tried string and binary types, but they result in DB error.

Additional context
This is Generator log concerning this issue:

This is the relevant part of the generator script. Sorry, I can't share the whole script, it is classified information.

I am trying to read a geometry polygon (contour of a "gemeinde" = city) from my import schema and write it to my export schema).

[ake2l]

Thank you for submitting this issue , would you please also share your Benerator Script or at least part of it with the binary attribute.

[pauldewaal-disy]

Thanks for the reply. I have added the relevant part of the generator script. Sorry, I can't share the whole script, it is classified information. I hope this works for you?

[ake2l]

We are going to check , thanks for adding this additional information, yes it should be fine to reproduce and also extend the tests

[ake2l]

We tested a bit around and added a JDBC handler for GEOMETRY datatype.
The use-case you are describing is possible now, with latest SNAPSHOT VERSION

here is an example script tested with latest postgres with postgis extension

<?xml version="1.0" encoding="UTF-8"?>

<setup xmlns="https://www.benerator.de/schema/3.1.0-jdk-11-SNAPSHOT"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="https://www.benerator.de/schema/3.1.0-jdk-11-SNAPSHOT benerator-3.1.0-jdk-11-SNAPSHOT.xsd"
       defaultEncoding="UTF-8"
       defaultDataset="DE"
       defaultLocale="en_DE"
       defaultLineSeparator="\n">

    <echo>
        Populates a database
    </echo>

    <echo>defining a database that will be referred by the id 'db' later</echo>
    <database id="db"
              url="jdbc:postgresql://localhost:35432/benerator"
              driver="org.postgresql.Driver"
              schema="prod"
              user="benerator"
              password="benerator"/>

    <database id="db2"
              url="jdbc:postgresql://localhost:35432/benerator"
              driver="org.postgresql.Driver"
              schema="test"
              user="benerator"
              password="benerator"/>


    <echo>Drop the testtable if it already exist. If it does not exist yet, the error message is ignored</echo>
    <execute target="db" type="sql" onError="ignore">
        CREATE SCHEMA IF NOT EXISTS prod;
        CREATE SCHEMA IF NOT EXISTS test;
        DROP TABLE IF EXISTS prod.data;
        DROP TABLE IF EXISTS test.data;
    </execute>

    <echo>Creating testtable</echo>
    <execute target="db" type="sql">
        CREATE TABLE prod.data (
        id serial PRIMARY KEY,
        created_on TIMESTAMP NOT NULL,
        genom geometry
        );
        CREATE TABLE test.data (
        id serial PRIMARY KEY,
        created_on TIMESTAMP NOT NULL,
        genom geometry
        );
    </execute>

    <echo>Generating 100 entries for testtable</echo>
    <generate type="data" consumer="db,ConsoleExporter" count="100">
        <id name="id"/>
        <attribute name="genom" constant="polygon((1 1, 1 2, 2 2, 2 1, 1 1))"/>
    </generate>

    <iterate type="data" source="db" consumer="db2"/>
</setup>